Re: Conformance to ANSI/ISO SQL

George Harding wrote:
>
> Please can anyone point me towards practical references on writing ANSI/ISO
> compliant SQL.
>
> Our developers mainly use Pro C with embedded SQL under ORACLE 7.1

Hello.
Sorry, I cannot recommend a good book. I can offer a suggestion about the practical application of ANSI and ISO standardized SQL code in an single-database environment. It sounds like you have a single database environment, Oracle.

Writing ANSI/ISO compliant SQL will result in your developers not being able to use Oracle-specific features of Pro*C. For example, the FIPS flagger (checks pro*c generated code for compilance to the ANSI and ISO standards for SQL) would the flag the use of all VARCHAR datatypes because VARCHAR is not recognized as part of the ANSI and ISO standard that pro*c conforms to. Thus, if you wanted to use VARCHAR datatypes in your pro*c files, they would not *conform* to the standards and are flagged by the FIPS flagger during compile.

I use the Oracle extensions to the SQL standards to take advantage of Oracle Pro*c features, so the SQL code in my pro*c files is not ANSI or ISO compliant. However, my C code is ANSI compliant because ANSI C compiliance requires more compile-time error checking and this reduces the number of possible run-time errors.

I would recommend that all your C file (whether generated by pro*c or not) are coded to the ANSI or ISO standard. Both require more error checking by the compiler to (hopefully) catch potential run-time errors before they are compiled. To do this, turn on all warnings for your C compiler, prototype your functions, and make explicit casts. In addition, you will have to use the code=ANSI flag in your pro*c precompiled C files, to generate ANSI-compliant C code. Note that code=ANSI only pertains to the C code generated by pro*c--it has nothing to do with the ANSI compliance of the SQL code contained in your pro*c files.

Finally, don't forget that ISO and ANSI are two different standards.

Good luck!
Jeff

-- 
Jeffery C. Cann
Senior Software Engineer
Fairway Systems, Inc.