Berkeley db? :)

The dbs you listed are the only ones I know, and postgres and ms sql are the only ones I've heard of to have stored procedures (though mysql is coming along hot and heavy). I'm pretty sure both postgres and mysql have embedded libraries that can be used under gpl/lgpl/bsd licenses.

I don't think you're going to get something truely cross DB, as even the biggies out there (including mysql/postgres/mssql/oracle/etc) can't even get the sql syntax right, much less supported the same (they all have their evil, evil little quirks).

I've started a collected of links and ideas about this topic at: http://www.nyetwork.org/wiki/Databases

Your best bet is probably to use ODBC (which works on Windows and Unix/Linux/OSX). Use Stored Procedures, but don't support databases that don't allow support them (MySQL doesn't really AFAIK, although MS Access does.)

Every database has its own way of doing Stored Procedures. However, once they are written once and the logic is there, porting them to another database is just tedious. Many vendors (eg PostgreSQL at techdocs.postgresql.org) have hints for porting from other databases. One thing that I ran into with PostgreSQL procedures is that the Win32 ODBC driver doesn't support the EXECUTE syntax for running a stored "function".

Other things to consider include access controls on database objects, which differ between databases as well.

You would think there would be easier ways. I will look more into the ODBC side of things, but I am perplexed on why this is such a hastle. Looks like it is going to be a lot of work to simply build a database with tables and Stored Procedures, and have some iota of saneness in the development time.

I think I may have to break down here and maybe only support PostgreSQL and MSDE/MS SQL Server, and drop the rest. Even though Oracle does have stored procedures, I don't think I will muck with so many different database types. If customers need Oracle and mySQL support, and are willing to pay for it, I will deal with it then I guess.

I think a company could make a pretty penny from software developers like me who need a neutral database abstraction layer to deal with such a simple issue which must plague a LOT of software vendors.

Thanks to everyone who posted publically or via email. Much appreciated.

How complicated will these procedures be? Maybe VIEWs would be sufficient?

By the way, Oracle's PL/SQL is quite similiar to PostgreSQL's PL/pgSQL.

Yea, I think a lot will come down to "do you want to spend gobs of time creating something that 1% of the people will use" and not "give them reasonable defaults and if they *really* want it put it in later when they are paying for it or have promised to buy $large_number of units when it's in. I think one of the traps we fell into at Merilus was putting in features that no one was really going to use (I think you've harped on this more than once :) and more importantly, spending lots of dev time putting them in.

What, you don't like it when I harp about feature creep and trying to keep the dev team focused? :)

I want to make sure I include atleast one cost-effective database that works on different Unixes, as my daemon runs on W2K/XP/Linux/BSD. Having an option for atleast PostgreSQL allows for some vendors to deploy the solution on something like Linux without having to deal with MS's CAL, or 10's of thousands of dollars for a database.

I am trying to keep the dev time down to a minimum, and I would hate to have to write a database abstraction layer and host like 5 or 6 different databases on servers here. I use exclusively PostgrSQL on all our production stuff here, and will build a default install with the MSDE for Windows. Past that, I would just like to have "integration scripts" to build tables, Stored Procedures and set up perms for different systems as required. Hopefully then the ODBC driver can take over and do the rest.

We will have to see.