Re: Develop own small database-system - Database Discussion Boards

Don't waste your time trying to develop a data storage engine. There have been hundreds of man-years invested in developing database engines. MS-Access is really quite fast and the license for MS-SQL server is really not that bad, if this is going to be a professional web application.

You speak of "clients" right ? Then they are paying a fee, right? Don't cheap out. Consider it an investment.

See my other post about starting with MS-Access and migrating to a more robust DB when you need it.

If it is a web application, then you could argue that there is only 1 user making an actual connection to the database; the web app itself! Remember that this is not a client server application where you need to create a user logon for everyone who wants to access the database.

Hmmm ... things to think about.

Consider this, it sounds like you really don't know how much data you are going to store or how frequently it will be accessed, so why don't you start with MS-Access as the database and make sure that you design you application in such a manner that it would be relatively easy to change database enginges without causing a massive re-write. It can be done. Make sure you have a good data abstraction layer designed and you could actually have your application capable of running on all kinds of database engines, MS-SQL, MySQL, Oracle. It all starts with the design.

Just a thought. Cool | :cool:

Not bad, my application now, can work with all this connectors. I must only exchange them. But i for example don't know how it is with this MySQL DB licenses!

I have for example 2 Server, one is the DB-Server and one is the "processing Server" (the server where the server-side-application is running) which has a connection to the db-server (and there's the problem with only 1 user, maybe i want to make a server-farm so i have a lot of connections). The client is talking only to the processing server, so i don't give any libraries or something away. Maybe like the PHP<->MySQL relation, there i don't need to pay for it and could make commercial applications. But why does MySQL say, that if i use it commercial i need to pay for a enterprise-license.

Has somebody any idea with this problem? And, now which method is the best only to try to make a own db-system? Should i save it "Binary", something like this?:

Table-schema-file:
Table:Test
Field:ID,isKey="true",length="10",type="string"
Field:sample,isKey="false",length="5",type="string"

Data-File:
TestID1   Hello!
TestID2   Hallo!

So there in the Data-File i have my Data saved, each line is a new record. Byte 0-9 is my Field "ID" (because of length-1) and Byte 10-14 is the "sample"-field.

Or should i save it into XML and read/query and so on it with XQuey? Then is the problem, maybe i have a DB-File which has a size of e.g. 4GB. How to update my DB-Files? I couldn't save every 5 Minutes 4GB files, if i only changed for example 500KB? Is there any solution to do this? I found something like XmlDataDocument and DataSet which works exactly, if i would work with a DB. Has anybody experiences with this?

You don't save 4GB, you save only part of that file. You don't load whole file and then do something with it, you open partition of file this aprtions lets say is field in database, which has more space than it's filled (done so it can only add inside data, thats why database files are so large) and you fill that space with data. You use streams, you don't open file. And, it is really not that easy to make database engine. There is not only read-write, it's problem also in locking, so one does not owerwrite other user data, etc... auditing, transactions, etc...

With your knowladge, there is no way you could make database engine. Not at least you spend really long time on it. And let's say that 10 year licence for sql server would cost less then your working hours developing real database engine for 1 year Smile | :)

But if you insist on xml, etc... why not serialize objects to xml? Maybe a row by row as different file for faster loading, but many files wwould kill windows FS. Maybe create virtual FS. Oh, so much more to considere here, it is really not that easy to make your own database engine.

Yes, that's right, but i have only very simple and "foolish" data. Ok, something like logging and so on, that's right, this could be a problem, but i think, before i overwrite something, i could read it first to check if it's the correct line.

Yes, but i heard, that the XML-Architecture in .NET shouldn't be very fast. I saw a lot of tutorials for using XmlDataDocument in DataSet. I have a simple xst-file where is my schema and my xml-file for the data. With those both files i'm able to work with my data inside of DataSets.

But what's better? My "own" System, save all records byte for byte in a file with streams or use the XmlDataDocument and DataSet. My Application works now with SQL-Databases, so i want to try it with this system, and if i see, that really nothing works, i could buy a mysql or mssql license. Another question, what's with MSDNAA licenses? I get a MSDNAA license of mysql, can i use that? And is it able to use that, if i want the client to pay for their client-application? (Not for the server-app or the db, only for their user-account).

Another idea, which could interest a lot of developer here...

We have a object-oriented programming-language, why not a object-oriented database. With DB4O you can store the data "as it is" as a class into a DB. Something like i would do with XmlSerializer.

You wrote before about XML, fast? not really, but since you really would like to write your own database, and best way would be to have object oriented database, look into serialization that is in .Net, you can "convert" class to xml, and vice versa.

Also, take a look at CP articles, there is a lot of stuff on persistence frameworks.

This project really sounds fun, if you can afford it (by losing time, and not making any sallary out of it) go for it Smile | :)

Building your own multi-user database is a serious undertaking. I would only do that as a last resort, if I had some really unusual requirements that didn't seem to be handled by any of the existing databases out there. In fact, I probably wouldn't do it even then because I would question why my requirements were so unusual. I guarantee it will turn out to be a lot more work than you expect, and you will spend a long time trying to track down niggling little problems that don't show up until the system is under load.

The licensing terms for MySQL are a bit unclear: most people never bother reading them but just go ahead and use it anyway without thinking about whether they are in breach of the licence or not. Contrary to popular opinion, it is not universally free. Under certain circumstances you will need to buy a commercial licence.

To answer your question: at the end of the day, all databases store their data as a file on the disk somewhere. The exact location of that file and the format of the data inside it and how it is accessed varies from database to database. Database vendors put a lot of time and effort into tweaking this and polishing it and refining it, and they give you loads of configuration options so that your DBAs can tweak it and polish it and refine it even more to suit your requirements. Massive amounts of time and money have been spent trying to figure out the fastest, most efficient way to execute a SQL SELECT statement as a series of reads from the underlying disk files. It sounds trivial, but it's really not.

There are plenty of free databases out there, with very liberal licensing terms. Some are better for certain applications, some may suit your personal taste more. You don't say what technology you are using for your app, but you could take a look at Firebird, Postgres, SapDB, Apache Derby. They are all good databases with open licences. For some of them you can even download the source code and have a look at how they work under the covers if that's what floats your boat.

Consider looking at sqlite.
http://sqlite.org/[^]

_____________________________
When life hands you marmots, make marmalade.

I'm new to SQL and am getting an exception on my webpage when I have an argument to my SQL DB that has spaces in it.

Basically, the page is being passed a name like "Name with spaces", which is being sent as templatename: (in VB)

ra = New SqlDataAdapter("exec sp_GetTemplateByName '" & templatename & "'", TestStatConn)
ra.Fill(dt)

The exception gets thrown at this line only for names that have spaces, not if a templatename is passed like "name_without_spaces". The exception is

System.IndexOutOfRangeException: There is no row at position 0.

ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"

The sp_GetTemplateByName:

Select
..stuff..
from tbl_checklist_templates
where templatename=@TEMPLATENAME
order by stepnumber

Can anyone shed some light on what's going on here?

Edit:

If I remove the single quotes from this line

"exec sp_GetTemplateByName '" &amp; templatename &amp; "'"

and make it

"exec sp_GetTemplateByName " &amp; templatename &amp; ""

everything acts the same, except when I click on a template name with spaces, I get this exception:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '%'.

From line 55:

ra = New SqlDataAdapter("exec sp_GetTemplateByName " & templatename & "", TestStatConn)
           (THIS is lne 55) ra.Fill(dt)

modified on Friday, June 26, 2009 10:43 AM

I guess the error is coming while the control reaches this line

ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"

Just check the count of your datatable, something like this(the code is in c#)

if(dt != null)
{
  if(dt.Rows.Count > 0)
  {

    // your code [ CheckForNullVal(dt.Rows(0).Item("lastchange")) ]
  }

}

N.B.~ I don't know VB.net, henceforth presented the code in C#
Hope this helps
Smile | :)

Niladri Biswas

Hi
Is it possible to write stored procedures and views in ms access 2000

Thanks
Denny

Yes - sort of.
You can create views simply by using the query designer, or do it in VBA code.
You can use VBA to emulate stored procedures or use it to create querydefs that act like stored procedures.
If you want to invoke stored procedures on a linked database (e.g. on SQL Server) you can use pass-through queries.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Hi ,
i have a doubt to choose a datatype in my database.I have a table with 3 fields :

1 - Code (nvarchar) PK;//in this field there is a code to refer a product.
2 - ProductName (nvarchar);
3 - here i have a doubt ,in this field i need to insert a number (percentage) but i don't know if the best choice is the d float(datatype), the purpose of this field is after the Total of some products we use this percentage to increase the Total for example :
if the Total is 25.000 USD i need calculate the Total + 25,7% so 25.000 +25,7%.
Which is the better datatype to use in this field to calculate the Total ,is it Float(datatype) or isn't?Can you advice me to apply this step in the right way?

Thanks so much for your attention.

Smile | :)

Is your data language English, I note you use a comma as a thousand seperator.

If Yes then use VARCHAR() instead of nvarchar which supports unicode characters.

I use decimal rather than float, there is a slightly larger penalty when using float and IIRC you cannot use a float in an index/constraint.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes wrote:
use VARCHAR() instead of nvarchar which supports unicode characters

Wrong way round, and whats it got to do with thousand separators?

nvarchar() supports unicode - I guess the sentence is ambigious.

Comma as the thousand seperator indicates non english format, reasonable assumption. All too many people accept the SQL Server default format with no knowledge why it may not be valid.

Never underestimate the power of human stupidity
RAH

Money Data Type

Money is compatible for currency with dollar and cent format. The datatype is accurate to 10 thousand times of the monetary unit.
If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.
Also with money, you have the choice to use thousand of separators.

Float/Decimal Data Type

Float/Decimal is more flexible, not specific to currency. Suppose you have to support 10 numbers and 2 decimals. In this context decimal is a better choice. Also, decimal is more precise( since we can set precision) and scale to numbers.

From the above explanations, I think you should go with money data type.
Smile | :)

Niladri Biswas

Hi all, I am a rocky with ACCESS Database

I have a basic question. Basically, I have an ACCESS 2003 database (mydatabase.mdb) in a network drive, I am planning to develop a C# windows application (not web base application) that will be installed (deployed) in many computers in the same network (intranet). These applications will be writing and reading continuously to this database. This has the risk of of accessing the database for writing and reading at the same time for more than one application.

My question is:
Would there be any problem if more than one application would be reading and writing to this database AT THE SAME TIME?

Should I setup an special configuration on the database for this kind of scenario?

Currently I am adding the db to the windows application using: From: Menu -> Data -> Add New Data Source ...

Thanks for your help.

kikeman wrote:
My question is:
Would there be any problem if more than one application would be reading and writing to this database AT THE SAME TIME?

Yes. It's not designed with the intention of serving lots of users at the same time, and it doesn't perform well in that scenario. Microsoft Access is ideal for desktop-use, but rather a pain when you're trying to use it with some people (some being more than one).

kikeman wrote:
Should I setup an special configuration on the database for this kind of scenario?

Depending on your particular Office-installation, you may have a Wizard to upsize your Microsoft Access database to a SQL Server database. SQL Express is a free download, and would perform better on that network drive.

The wizard doesn't convert forms or reports. You can, however, import a link (in the Microsoft Access database) to the tables in SQL Server/SQL Express and base your reports and/or forms on that. That way you can still benefit from the advantages of developing in Access Smile | :)

I are troll Smile | :)

I vote with Eddy, chuck Access, it is the wrong tool for the job. Use SQL Server, it is ideal for departmental processing.

Never underestimate the power of human stupidity
RAH

Thanks for your reply guys,

The problem would be now to "import" the ACCESS_2003 tables to the SQL Server Express Architecture.
Do you know if is there any tool for this convertion?
Does this tool comes with the SQL Server Express?

I think it would be better to move all database to SQL Server architecture, now that the database is not that bit at the moment. The forms and reports will be replaced with some Visual C# GUIs anyway.

Hi all,

I never really played with small applications.Now I want to write an application, really small.Its only going to be used by a homeuser.

Therefore its not really going to be that smart using SQL server.
But I need some suggestions.What will be the best technology?
I thought of XML, but Im not that clued up with it and it gives me the idea it will get tricky with big functionality.And then theres MS Access, but on the net it looks like its not really being used as much anymore.What will be the practical thing to use?
Thank you

My vote is for MS-Access. It isn't going away anytime soon. It will work fine if it is a single user application. Keep things simple. Cool | :cool:

Either go with MS Access or MySql
Smile | :)

Niladri Biswas