Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr From: jfr@locus.com (Jon Rosen) Newsgroups: comp.databases Subject: Re: Embedded SQL and C Message-ID: <23776@dice.la.locus.com> Date: 24 Apr 91 01:48:18 GMT References: Distribution: usa Organization: Locus Computing Corp, Los Angeles Lines: 50 In article gdfwc3!davids@central.sun.com writes: >Is there an ANSI standard for embedded SQL statements in C? Yes >Or in >other words, would the C function calls to send a SQL statement to a >ANSI SQL database be the same for Oracle, Ingres, Sybase, etc? No There is an ANSI definition of embedded SQL statements which roughly follows the IBM-defined mechanism supplied in DB2... That is, each SQL statement is preceded by the words EXEC SQL and is terminated by the appropriate end mechanism for the language (in C, this is a semi-colon)... SQL statements are NOT function calls... a precompiler converts the EXEC SQL statements into internal function calls to some kind of run-time library... The details of this translation are hidden by the precompiler... In Ingres, Oracle and DB2, this is true... In Sybase, you use a direct call-level interface... The run-time library is supplied by the database vendor... The theory is that programs should be portable and indeed the simplest ones are... Unfortunately, every vendor has extensions to ANSI SQL and they are all different... This is even true for some of the simplest statements like SELECT and INSERT... IN addition not all of them support all of the ANSI standard in an indentical manner... Finally, the transaction protocols (lock management and ROLLBACK/COMMIT support) are not always the same. Thus, even though SQL is ostensibly a portable database access language, in practice (like most so-called portable language standards) it really isn't. You can certainly write programs that are subsets of what a variety of vendors support and where the vendors all support embedded SQL the same way, these programs will be reasonably portable. The problem is, no one ever wants to limit themselves to the compatible subset, especially since with SQL this puts pretty serious limits on what you can do. For instance, if I was writing DB2 programs, there is no doubt that I would be using referential integrity constraints... If another target DBMS did not support RI constraints, my programs would have to be completely rewritten in order for them to work the same. Good luck, Jon Rosen