Open Database discussion/suggestion

[Douglas C. Horner]

I am venturing into the world of developing an application for sale on the open Market. It is an estimating program that will be used by construction companies.

I have always used MS-SQL for everything and was thinking about using it for this application but the overhead and cost of the database is just to high. So, I am looking for a database that is free like SQLite, easy to use, has the ability to handle multiple users, can handle large amounts of data effectively and can be accessed via report writers such as Crystal etc. I know that sounds like a big order but it never hurts to try.

I have done a lot of Googling and found a couple of databases that may fit the bill. I was hoping someone that has actually used a database like the one I described in their application could give me the run down on the database they used. I want to make sure I have all the possible database candidates before I start on the database coding.

I have another post on SQLite so that is already in the running and looks like a good candidate but I don't want to just look at one and go with it.

So what do you use?

Thanks
Doug

[Michael Mattias]

Why not make your application DBMS-brand agnostic and let the USER pick the database product? It's possible the user already has shelled out a few bucks for Oracle or SQL/Server or some other brand.

If you use one of the standard interfaces (ODBC or OLE), and don't use any DBMS-specific syntax in your SQL, this gives you the best of all worlds... you can run on any database which supports standard SQL and one of these standard interfaces.

eg see the "database" column on this page: http://www.providerpaymentpartner.co...fications.html

I have PPPS users on at least five or six different DBMSs.

MCM

[Chris Holbrook]

Personally I would include a consideration of my support resource vs maximum possible support demand into the selection process. From first principles, this would favour the least complex solution.

[Edwin Knoppert]

Not demanding a specific DB type will bite you.
Imagne the user picks a db type and at some moment he (or you) wants to write a complex query.
The db may fail and is stranded and maybe contacts you for help.
Help which could have been avoided.

I deal with a lot of different databases and for me MS SQL server is the most flexible one.
Focus on T-SQL support is not enough, the driver itself is usually the issue.

Also, the language for JET is different vs T-SQL like Month() and such functions.
It is to simple to tell the programmer to create stored procedures for work around, in my job it is not allowed, why should it be allowed by the OP?
Anyway, you would deviate from your strategy.

[Michael Mattias]

Imagne the user picks a db type and at some moment he (or you) wants to write a complex query

It's perhaps a little different for me, because I started out from the premise that the only party who would be writing any queries would be the publisher (that would be moi). The products woudl be "shelfware" - you get your choice of versions, as long as your choice is the current version.

But yes, I have encountered situations where an DBMS-specific intrisic function (eg month(), to_char(), etc) would have been handy... but I stuck to my guns about being DBMS-agnostic and just went ahead and did what I needed to do "the long way."

Most support problems are solved - or created, I guess - before the first line of code is written.


MCM

[Mike Stefanik]

I have always used MS-SQL for everything and was thinking about using it for this application but the overhead and cost of the database is just to high.

There's Microsoft SQL Server Express. It's their scaled down version of SQL Server that's free, but it has some limitations:

1. The maximum size of a single database is 4GB; the database itself is stored in a single file (which can be an advantage for applications that don't need access to huge databases, since it can be easily copied)
2. It's limited to a single physical CPU (although it will run multiple schedulers on a system with a single CPU that has multiple cores; see KB article 914278)
3. It will only use up to 1GB of physical memory; the system can have more, but it won't allocate more than that.

If you don't see your application requirements exceeding those limitations, and you prefer working with MS SQL, then it sounds like it might be a good fit for you. The product page for it is http://www.microsoft.com/express/sql/default.aspx

[Edwin Knoppert]

They now have a 'webinstaller', that s***s imo.
Usually they have an alternative download.

?

[Edwin Knoppert]

Hmm, at the bottom you can go to another page and find 4 downloads.

[Edwin Knoppert]

Let me tell my 'happy' story..
Used a plain XP sp1 virtual pc disk for installing.

I was forced to install:
.NET 3.5... oh no.. 3.5+SP1 (so that makes a 2nd install)
Windows installer 3, later on v4.5
During the installation.. 'you need Windows PowerShell', so i ran that one.
Did i mention it required validation, had to run that too.

Finally it installs.. yippieeee.. not!
There is an error in sql.cab

In short, there is no end for this install, numerous updates, pointless .NET versions, idiotic installers and after numerous checks and validation i get an error at the end.

It s****s

[Douglas C. Horner]

I thought about and have used SQL Express. The issue I have with it is what I believe Edwin is referring to.. The lack of easy automated installation. Obviously I don't want the user to have to install the database or even get remotely involved with the installation. It should be seamless. I have yet to see a SQL seamless automated SQL installation. If someone has please do post some information on it.

Most support problems are solved - or created, I guess - before the first line of code is written.

The idea of agnostic database is very appealing and your quote is bang on! I have to consider my target market for this product and what my goals are. I am not targeting the large construction companies so choosing a database, from the customers perspective, is not as important as say, your application (which is very impressive might I add). I am targeting the smaller mom and pop companies that need a professional looking estimate and the ability to see what jobs they have in the que, costs, profits, schedules, P/O's etc. I just don't see more than say 5 users but a user is a user and therefore the database needs to be multi-user.

I have not struck one line of Database code since I am hung up on which one to use. Honestly, I am very comfortable with MS SQL and SQL_Tools. But, considering my target market and the amount of money and installation effort put into MS SQL it almost certainly eliminates itself from the list of possible db's.

Now, I have never used SQL Express in a multi-user environment and I am not sure it is capable of such but I may be able to get away with a 4 gig limit.

I am somewhat surprised nobody is singing the SQLite praise song here. It seems like a nice little database but the syntax does seem more complex than SQL_Tools. Paul indicated I was wrong but I would like to see a SQLite version of this SQL_Tools statement for a comparison. If someone is a SQLite person and would be willing to translate this for me I would appreciate it!

Heck, why limit myself to showing the below code in just SQLite. If someone has a favorite multi-user database and wants to show how to accomplish the code below then please do. I am keeping an open-mind on this one so nothing is out of the question.

I was also thinking that I would be willing to purchase a database as long as the distribution rights, price etc was not cost prohibitive for a smaller application.

What about SQLitening has any used this product from Paul?

    'Initialize and attach to database
    SQL_Initialize 3,4,512,3,3,0,0,hCurInst&
    SQL_SetOptionStr %OPT_TEXT_NULL," "
    SQL_SetOptionSInt %OPT_TEXT_MAXLENGTH, 512
    gResult&=SQL_OpenDataBase (1,"DSN=Rockwood Manufacturing; UID=; PWD=;",%PROMPT_TYPE_COMPLETE)

....
     'Link two tables and print the results
    &"Sum(oeordlin_sql.qty_ordered*oeordlin_sql.unit_price) AS OrderTotal, oeordhdr_sql.entered_dt "_
    &"FROM oeordhdr_sql INNER JOIN oeordlin_sql ON (oeordhdr_sql.ord_no = oeordlin_sql.ord_no) AND (oeordhdr_sql.ord_type = oeordlin_sql.ord_type) "_
    &"GROUP BY oeordhdr_sql.status, oeordhdr_sql.entered_dt, oeordhdr_sql.ord_no, oeordlin_sql.line_seq_no, oeordlin_sql.item_no, oeordlin_sql.qty_ordered, oeordlin_sql.unit_price "_
    &"HAVING (((oeordhdr_sql.status)='9') AND ((oeordhdr_sql.entered_dt)=" & gTodaysDate$ & "))"
    SQL_Statement 1,1, %SQL_STMT_IMMEDIATE,gSqlExport$
    DO
    SQL_ErrorClearAll
    SQL_FetchResult 1,1, %NEXT_ROW
      IF SQL_EndOfData 1,1 THEN EXIT DO
        PRINT #1,"Sum Total: ";",";SQL_ResultColumnFloat(1,1,1)
        'Continue on with what needs done bla bla bla
    LOOP

[Bud Durland]

FWIW, we have a MySQL database at work that's been running for several years. I'veread the docs for the latest version and there are marked inmprovments and new features; we just haven't gotten around to implementing it (if it ain't broke, don't fix it).

[Paul Squires]

I am somewhat surprised nobody is singing the SQLite praise song here. It seems like a nice little database but the syntax does seem more complex than SQL_Tools. Paul indicated I was wrong but I would like to see a SQLite version of this SQL_Tools statement for a comparison. If someone is a SQLite person and would be willing to translate this for me I would appreciate it!

What about SQLitening has any used this product from Paul?

Just to clarify and give credit where credit is due - I originally wrote a version of SQLite client/server but was subsequently approached by Fred Meier with a version of code that he wrote. His code was so much better than mine that we decided to drop my code and concentrate on working with his code. I am happy that I did.

Here is how I would deal with your code using SQLitening: (an entire client/server, or local only, database system in about 700K and simply copy the DLL's to install the program. Simple).

#Include "..\Inc\SQLitening.Inc"

   ' Connect to the server -- omit this call to run in local mode
   ' or use the slSetProcessMods function to explicitly switch to
   ' local mode without dropping the server connection.  No server
   ' name will default to LocalHost.
   slConnect

   ' Open the database
   slOpen "Rockwood Manufacturing"

   ' Select record set (Link two tables and print the results)
   sSQL =  ...    & _
              "Sum(oeordlin_sql.qty_ordered*oeordlin_sql.unit_price) As  OrderTotal, oeordhdr_sql.entered_dt "_
    &"FROM oeordhdr_sql INNER JOIN oeordlin_sql ON (oeordhdr_sql.ord_no = oeordlin_sql.ord_no) AND (oeordhdr_sql.ord_type = oeordlin_sql.ord_type) "_
    &"GROUP BY oeordhdr_sql.status, oeordhdr_sql.entered_dt, oeordhdr_sql.ord_no, oeordlin_sql.line_seq_no, oeordlin_sql.item_no, oeordlin_sql.qty_ordered, oeordlin_sql.unit_price "_
    & "HAVING (((oeordhdr_sql.status)='9') AND ((oeordhdr_sql.entered_dt)=" & gTodaysDate$ & "))"

   ' Send the request to execute the SQL statement and create the recordset
   slSel sSQL

   ' Process records
   Do While slGetRow
      ' When a row is returned, you use the slF, slFN, slFX, slFNX functions. 
      ' Example:
      ' lsA = slF(1) & ", " & slF(2) & "," & slFN("Product")
   loop

   ' Optionally explicitly close the database
   slClose

[Chris Holbrook]

I am somewhat surprised nobody is singing the SQLite praise song here.

I don't do praise, though I occasionally attempt explanation... using SQLite frequently, I prefer to have a (single-file) database to a lot of data files spread around the Windows filesystem for just about any application.

For a description of the distinguishing features of SQLite, see www.sqlite.org

From your brief description of the application, I doubt whether SQLite would fail to do the job, but would suggest that you do the database design first - if you haven't already done so - then look for software to implement.

My guess is that SQLite will have ongoing cost advantages where competitors will attempt to lead you into the ways of paying license fees, but that is just a guess.

[Christopher Carroll]

Relational databases

For what you want to do, Jet (Access) or SQLite would probably suffice.

They are undoubtedly the easiest to deploy. For 5 to 10 users, there should be little problem in dealing with collisions and concurrency, though it is better to put some simple logic in the code to help manage this. There is a recent discussion in these fora about this with regard to SQLite.

Deployment, support, cost, licensing, database size, future growth, platform, end-user demands as to what databases they will use are some of the issues you need to consider; besides the programming ones.

Jet has a 2GB database size limit, but unless you are storing binary data in the database, this is a realistic limit for a business-orientated system.

SQL Express has 4GB's, but I think this is a hybrid marketing animal, and one should either use Jet (Access) or MS-SQL if a Microsoft product is to be used.

I don't like the non-standard syntax that MySql requires to get around various issues. It is not a true database anyway in my opinion as it is arguably not really ACID compliant: if the non-default InnoDB storage engine is used, it has partial compliance (probably good enough though: InnoDB permits storage of values that exceed the data range, violating the Integrity constraint).

MySQL has a commercial licence as well as a GPL/FOSS one. If using the latter, you would have to consider any legal ramifications with regard to your code.

In addition, I would recommend any of the following free databases:
- Firebird
- Oracle Database XE
- PostgreSQL

All are multi-user, can be installed as a service, and can be installed on various platforms (e.g. Windows, Linux, BSD). Oracle XE has a 4GB limit, but you can upgrade this to the full commercial version of Oracle. The other two do not have restricted limits.

Commercially, of course, there is Oracle and MS-SQL (and DB2).

All of the above databases have ODBC support, so you could write with database-agnostic code (giving you more control and flexibility). SQL Tools is good for this, or use the ODBC API directly. OLE is also an excellent method.

SQLite and Firebird are better accessed with their API's (SQLitening is recommended in the case of SQLite).

See also:
- Comparison of relational database management systems
- Comparison of different SQL implementations

- Oracle XE: It's Not Your Typical Oracle
- Latest MySQL Fails to Quiet Licensing Critics - 2004 article but still relevant

[Michael Mattias]

Oracle XE has a 4GB limit, but you can upgrade this to the full commercial version of Oracle.

Um, er, check your budget before you make this move. Oracle is good, but it is NOT inexpensive.

FWIW, with PPPS I offer the user the option to use Jet... and to do so, I include in the install a *.mdb with all the tables and indexes but no data, and programatically create the ODBC datasource (this step not required if you use OLE).

For users who want to use their own DBMS, there is a utility function to create the required tables and indexes, although the user is responsible for first creating the ODBC datasource. This is because depending on the DBMS, there can be numerous 'settings' required in the datasource setup, and I was not going to even try to keep current on all possible options for all possible DBMS products.

MCM

[Knuth Konrad]

SQL Express has 4GB's, but I think this is a hybrid marketing animal, and one should either use Jet (Access) or MS-SQL if a Microsoft product is to be used.

It's a "hybrid marketing animal" just like Oracle's XE. It's the full blown MS SQL Server engine with the above mentioned artifical limit on database size and max. 10 concurrent user connections.

[Mike Stefanik]

It's a "hybrid marketing animal" just like Oracle's XE. It's the full blown MS SQL Server engine with the above mentioned artifical limit on database size and max. 10 concurrent user connections.

Does Express have that limitation? I know MSDE (its predecessor) had limitations on concurrent access, but I thought that was eliminated with Express.

[Douglas C. Horner]

Just to clarify and give credit where credit is due - I originally wrote a version of SQLite client/server but was subsequently approached by Fred Meier with a version of code that he wrote. His code was so much better than mine that we decided to drop my code and concentrate on working with his code. I am happy that I did.

Here is how I would deal with your code using SQLitening: (an entire client/server, or local only, database system in about 700K and simply copy the DLL's to install the program. Simple).

#Include "..\Inc\SQLitening.Inc"

   ' Connect to the server -- omit this call to run in local mode
   ' or use the slSetProcessMods function to explicitly switch to
   ' local mode without dropping the server connection.  No server
   ' name will default to LocalHost.
   slConnect

   ' Open the database
   slOpen "Rockwood Manufacturing"

   ' Select record set (Link two tables and print the results)
   sSQL =  ...    & _
              "Sum(oeordlin_sql.qty_ordered*oeordlin_sql.unit_price) As  OrderTotal, oeordhdr_sql.entered_dt "_
    &"FROM oeordhdr_sql INNER JOIN oeordlin_sql ON (oeordhdr_sql.ord_no = oeordlin_sql.ord_no) AND (oeordhdr_sql.ord_type = oeordlin_sql.ord_type) "_
    &"GROUP BY oeordhdr_sql.status, oeordhdr_sql.entered_dt, oeordhdr_sql.ord_no, oeordlin_sql.line_seq_no, oeordlin_sql.item_no, oeordlin_sql.qty_ordered, oeordlin_sql.unit_price "_
    & "HAVING (((oeordhdr_sql.status)='9') AND ((oeordhdr_sql.entered_dt)=" & gTodaysDate$ & "))"

   ' Send the request to execute the SQL statement and create the recordset
   slSel sSQL

   ' Process records
   Do While slGetRow
      ' When a row is returned, you use the slF, slFN, slFX, slFNX functions. 
      ' Example:
      ' lsA = slF(1) & ", " & slF(2) & "," & slFN("Product")
   loop

   ' Optionally explicitly close the database
   slClose

Interesting Paul..

[Douglas C. Horner]

For what you want to do, Jet (Access) or SQLite would probably suffice.

They are undoubtedly the easiest to deploy. For 5 to 10 users, there should be little problem in dealing with collisions and concurrency, though it is better to put some simple logic in the code to help manage this. There is a recent discussion in these fora about this with regard to SQLite.

Deployment, support, cost, licensing, database size, future growth, platform, end-user demands as to what databases they will use are some of the issues you need to consider; besides the programming ones.

Jet has a 2GB database size limit, but unless you are storing binary data in the database, this is a realistic limit for a business-orientated system.

SQL Express has 4GB's, but I think this is a hybrid marketing animal, and one should either use Jet (Access) or MS-SQL if a Microsoft product is to be used.

I don't like the non-standard syntax that MySql requires to get around various issues. It is not a true database anyway in my opinion as it is arguably not really ACID compliant: if the non-default InnoDB storage engine is used, it has partial compliance (probably good enough though: InnoDB permits storage of values that exceed the data range, violating the Integrity constraint).

MySQL has a commercial licence as well as a GPL/FOSS one. If using the latter, you would have to consider any legal ramifications with regard to your code.

In addition, I would recommend any of the following free databases:
- Firebird
- Oracle Database XE
- PostgreSQL

All are multi-user, can be installed as a service, and can be installed on various platforms (e.g. Windows, Linux, BSD). Oracle XE has a 4GB limit, but you can upgrade this to the full commercial version of Oracle. The other two do not have restricted limits.

Commercially, of course, there is Oracle and MS-SQL (and DB2).

All of the above databases have ODBC support, so you could write with database-agnostic code (giving you more control and flexibility). SQL Tools is good for this, or use the ODBC API directly. OLE is also an excellent method.

SQLite and Firebird are better accessed with their API's (SQLitening is recommended in the case of SQLite).

See also:
- Comparison of relational database management systems
- Comparison of different SQL implementations

- Oracle XE: It's Not Your Typical Oracle
- Latest MySQL Fails to Quiet Licensing Critics - 2004 article but still relevant

Christopher,

A lot of very good information there. Honestly, I never even considered MySql for the reasons you point out above. I have to say that I am leaning toward SQLitening and SQLite. It appears to be a reasonable solution and the learning curve is pretty low.

I have to say though, I wish MS SQL Express had an easier way of automated installation. If Express had a better installation processes (hands-off) I would go with it but no sense in crying over spelled milk.

[Christopher Carroll]

It's a "hybrid marketing animal" just like Oracle's XE.

Agree. Oracle was losing market share to MySQL!

I see no reason to use SQL Express: horrible installer, and one can get a reasonably-priced MS SQL by buying Microsoft's Small Business Server.

Not only can the full version of Oracle become pricey, one way to notch up scars on being a "real" programmer/systems guy is to install this on Linux. It is better now with the Installer than the command line of years gone by.