gdfwc3 (David Reed Smith) (04/23/91)
Is there an ANSI standard for embedded SQL statements in C? 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? Thanks. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ David Smith davids@gdfwc3, General Dynamics, gdfwc3!davids@central.sun.com, Fort Worth Division or texsun!gdfwc3!davids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
jfr@locus.com (Jon Rosen) (04/24/91)
In article <DAVIDS.91Apr22163724@nimitz.uunet!texsun!gdfwc3> 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
bobm@server.Berkeley.EDU (Bob Muller) (04/26/91)
ANSI X3.168-1989, Database Language--Embedded SQL specifies the syntax and semantics
for the precompiled language used to embed SQL statements, to declare variables
used in those statements from the program (both input and output), and to
handle exceptions raised during execution of the statements. It specifies the
syntax for Ada, C, COBOL, FORTRAN, Pascal, and PL/1. There is a lot of stuff
about the language-specific data types and their correspondence to the standard
SQL data types.
ANSI X3.135-1989, Database Language--SQL with Integrity Enhancement, provides for
the various ways of specifying bound variables, indicator variables, and similar
constructs, and there are some references between the standards.
The draft SQL2 standard, X3.194-199x, integrates both standards. As a side
benefit, it also adds MUMPS support to the standard (immunization is advised :-).
This standard resolves a bunch of semantics issues with the embedded syntax
and is more correct if not clearer than the previous standards.
None of these standards are particularly well implemented by DBMS vendors at
the moment. Most vendors supply a proprietary functional interface to the
DBMS; some supply an embedded syntax based on DB2, which is not exactly the
same as the ANSI syntax. The DB2 documentation often appears cited as a
practical standard.
--
-- Bob Muller
Objectivity, Inc.
bobm@objy.commiket@blia.sharebase.com (Mike Tossy) (04/26/91)
In article <1991Apr25.200437.29570@objy.com>, bobm@server.Berkeley.EDU (Bob Muller) writes: > ANSI X3.168-1989, Database Language--Embedded SQL specifies the syntax and semantics > for the precompiled language used to embed SQL statements, to declare variables > used in those statements from the program (both input and output), and to > handle exceptions raised during execution of the statements. It specifies the > syntax for Ada, C, COBOL, FORTRAN, Pascal, and PL/1. There is a lot of stuff > about the language-specific data types and their correspondence to the standard > SQL data types. > > ANSI X3.135-1989, Database Language--SQL with Integrity Enhancement, provides for > the various ways of specifying bound variables, indicator variables, and similar > constructs, and there are some references between the standards. > . . (reference to the draft SQL2 standard removed) . > > None of these standards are particularly well implemented by DBMS vendors at > the moment. Most vendors supply a proprietary functional interface to the > DBMS; some supply an embedded syntax based on DB2, which is not exactly the > same as the ANSI syntax. The DB2 documentation often appears cited as a > practical standard. > -- > -- Bob Muller > Objectivity, Inc. > bobm@objy.com I strongly disagree with your last paragraph. The National Institute of Standards and Technology (NIST) in Gaithersburg MD (USA) has produced a validation suite which tests compliance with both X3.135-1989 and X3.168-1989. ShareBase III (produced by ShareBase Corporation, then an independant company and now part of Teradata Corp.) first passed the NIST test suite (for both SQL and the referential integrity) in May 1990. Since that time several other companies (eg. IBM with SQL/DS (but not DB2)) have also passed at the test suite for SQL. (I believe only ShareBase III has passed the referntial integrity part.) -- >>>>>> Opinions expressed are those of the guest speaker <<<<<< Mike Tossy ShareBase Coropration miket@sharebase.com 14600 Wichester Blvd (408) 378-7575 ext2200 Los Gatos, CA 95030 (ShareBase is a subsidiary of Teradata Corportation)