The company that I work for has a suite of C programs and libraries implemented on Solaris that are tied to Sybase. We are now looking to support Oracle (and perhaps some other databases as well). I had quite a bit of Oracle experience before working here, but that was about 7 years ago. I realize that there are quite a few differences between Sybase and Oracle. And developers have taken advantage of quite a few Sybase specific features.

It appears Sybase and Oracle support ODBC for Windows platforms, but on Unix you have to get ODBC from another vendor (unixODBC and iODBC seem to be standards). Is that true? Does it make sense to use ODBC on Unix? I suspect that some other developers where I work may want to build our own API, but maybe we can model it after ODBC. It appears that EasySoft at one point had some open source ODBC drivers, but has given up on that route. Does anyone out there have open source ODBC drivers?

I have also seen that there are SQL 92 and SQL 99 standards. I assume that to achieve database independence that I should strive for SQL that conforms to one of these. Is there somewhere I can find what Oracle and Sybase claim to conform to. I'm going to have to shoot for some least common denominator between the 2 vendors versions of SQL, but I'm not sure how to determine what that is. Also I remember that Oracle's stored procedures and triggers are quite different from Sybase's, so when striving for database independence, should you just not use them (unless you want to write 2 versions of each).

This must be a very common problem to tackle. There must be some books, websites or something that someone could recommend to me.

Thanks in advance.

Writing 2 versions of each stored procedure is probably the best approach in fact. This allows you to cater for and use the different behaviour and features of the DBMSs. It isn't just a matter of finding the lowest common denominator SQL, it is also about understanding that the same logic, while syntactically valid in both DBMSs, may perform differently, even maybe have different results due to different locking mechansims, treatment of nulls etc.

Oracle guru Tom Kyte goes into this here

Search the page for "database independence". If you search the site you will find other discussions on the topic also.