Re: ODBC Open Database Compatibility

dswaney@norfolk.init.net (Dan) wrote: :I am currently trying to find information on the Open Database :Portability of SQL code between Oracle and Informix. Our system has :to support both Informix and Oracle. We are currently evaluating :products with which to re-write our system with and ODBC is a major :factor. :One of the many key elements is how ODBC handles OUTER JOINS for the :two databases: :- Does ODBC SQL have a standard universal SQL that can be either :translated or used by both databases without programatically modifying :embedded SQL statements? Yes, it does. This is the point of ODBC. What ODBC does is translate the SQL commands it understands to whatever is the required syntax for the engine and similarly translates data coming back from the engine. There is a separate translation layer for each engine of course. Most ODBC drivers to date has implemented this inside the driver on each PC or workstation. You don't want that if you can avoid it. The drivers that implement the translation at the server side are generally much better (such as Openlink and some others). Of course there are things that can be done in different implementations of SQL that can't be done in ODBC. If you want to utilize these you can pass the SQL commands directly through ODBC to be processed by the engine. In this case you obviously loose the portability. The problem is the limited set of functionallity that exists in ODBC and in many cases it would be more than nice to be able to use special functions supported by whatever engine you are using, but not ODBC. Here you also need to be aware of the different levels of ODBC compliance of the different ODBC drivers. You only want to use an ODBC driver that implements at least a very significant part of level 2 ODBC compliance (the highest level). If you have a less compliant driver you will soon get into trouble. The next trouble you will get into is when Informix Universal Server is released. It has so much new functionallity that the current ODBC spesification is next to useless. We can only hope that Informix and Microsoft are currently hard at work to fix this. :- Is there a FAQ on ODBC? There should be some information available at www.microsoft.com Otherwise you might want to get hold of the ODBC development manuals from Microsoft. :Any information would be very helpful and greatly appreciated. :Thanks in advance, Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company