Hi, On Wed, Feb 19, Sebastian Wormser wrote:
judging from the problems arising with only three databases (mysql, postgesql and sapdb), I think there is no way to provide a single statement that fulfills the requirements of all databases.
I can give you two more databases, where you will have the same problems: DB2 & Informix. Probably Oracle will also bark.
IMO there are two ways to solve the problem:
Provide for each statement different versions for each db and implement something like gettext for sql. Then you can define a db and the software automagically uses the correct sql syntax.
The other way would be to create data type definition schemes for each database. In the functions you would use no sql any more, but abstract commands that will be interpreted by some kind of sql parser, that would translate the abstract command into correct SQL for the specific database.
I am not quite sure wich of these solutions will be the best, maybe you have opinions about it or other ideas.
I would vote for a third options: Stay away from commands / options that are marked as a addon special to this database. SQL92 does not define very much, but the common base runs on all databases. The problem for special syntax should only arise when you create the database and the initial tables, which might need special keywords that are different in every database. This can be handled with different create scripts and should be no problem. The system operation however should be based on a common standard, which SQL92 is. Every database on the market today supports SQL92. greets, Daniel Rahn -- SuSE Linux AG | phone: +49 (0) 911 74053 0 Enterprise Support Services | fax: +49 (0) 911 74053 674 Deutschherrnstr. 15-19 | http://www.suse.de/en/support 90443 Nuernberg | GERMANY