Path: utzoo!attcan!uunet!dg-rtp!roadkill!cohend From: cohend@roadkill.rtp.dg.com (Dave Cohen) Newsgroups: comp.databases Subject: Re: SQL Precompilers Message-ID: <1990Aug8.145522.10694@dg-rtp.dg.com> Date: 8 Aug 90 14:55:22 GMT References: <1990Jul26.141643.6361@dg-rtp.dg.com> Sender: usenet@dg-rtp.dg.com (Usenet Administration) Reply-To: cohend@dg-rtp.dg.com Distribution: comp Organization: Data General Corporation, Research Triangle Park, NC Lines: 95 In article , cimshop!davidm@uunet.UU.NET (David S. Masterson) writes: |> |> Why would you want the embedded approach to SQL? Regardless of how well the |> precompiler is implemented, the support for it by other products will never be |> good. Consider how many utilities in Unix are screwed up with embedded SQL in |> a C program (for instance, FCREF, INDENT and VGRIND). In my book, the |> function call approach for sending SQL statements to the database is the |> correct one (ashame I haven't been given the chance to use it). It breaks far |> less utility routines that are very important to the programmer. Also, if you |> shift back and forth between languages (like C and C++), there is no new |> syntax to learn or tricks to apply (like faking an SQL precompiler for C to |> not mess up C++ code). Two important points here, David: 1) C to C++ is a trivial language switch. Howza bout Cobol to C? Function calls will change dramatically, embedded SQL will change very little. 2) There is an ANSI standard for embedded SQL. No such animal exists for function calls, implying no portability among vendors. |> |> I believe that the statement "precompiled applications run faster and are |> easier to code than call level SQL" may be true with what is often supplied, |> but is wrong as a general statement. In order to optimize a precompiled |> statement the way that is suggested, certain aspects of the SQL must be fixed |> for that statement (relations to deal with, columns within the relations, |> operators to be applied). This cuts out any dynamic SQL capability. A very |> good precompiler might determine the difference between a statement that will |> be treated dynamically and one that will not and, therefore, make appropriate |> optimizations. However, that is not always possible (for instance): |> |> SELECT col FROM table |> WHERE col = :var; |> |> A precompiler might make the assumption that everything is known and, |> therefore, optimizable, but what if the user chose to fill "var" in with "1 or |> col = 2" at run-time. It changes the whole complexion of the statement |> (especially from the optimizer's perspective). This can get far more complex, Are you saying that changing a variable's value will slow down a preoptimized statement more than the dynamic statement's costs for lexing and parsing ? This is absurd!!!! |> but it shows the problems that can happen when mixing potentially dynamic |> statements with statements that can be optimized. The decision of what is |> optimizable and what isn't can be put in the hands of the user (he should |> know, right?) by either limiting what they can do to only optimizable |> statements or by extending the SQL statements to have some sentinel to inform |> the precompiler to perform optimizations. In doing this, though, what has |> been bought? (decreased flexibility or increased user confusion) |> |> A function call approach will generally tend toward the sentinel approach |> (although the sentinel is now the name of function, not a change to the SQL |> syntax), so there might be some programmer confusion, but it is just in |> learning new function calls, not a change in the language itself. Also, |> precompilers that worked with database engines typically embedded a set up |> call in the program to inform the database of an optimized function (after |> all, the compiled program might run against a copy of the database that it was |> compiled against which would not know about any statements stored in the |> original database). This could certainly be done by the programmer using a |> function call approach, its just not as automatic. |> |> In particular, I'd like to hear about the 'biggies' : DB2 (pretty sure |> this does), Oracle, Ingres, Informix, Sybase, Empress, etc. |> |> Britton-Lee did it with there early IDL/C precompiler, but I'm not sure if |> they do it anymore (there was more flexibility in not doing it). DEC's |> RDB/VMS precompiles SQL statements into macro assembler code that it then |> calls with a generated function call (that's what it looks like anyway). I |> don't believe Ingres did this in v5, have no idea about v6. Sybase did use a |> function call approach (no precompiler), but they may have expanded their |> repitoire since last I saw. Unsure about the others. |> -- |> =================================================================== |> David Masterson Consilium, Inc. |> uunet!cimshop!davidm Mt. View, CA 94043 |> =================================================================== |> "If someone thinks they know what I said, then I didn't say it!" |> David Cohen | "There's nothin' wrong with goin' cohend@dg-rtp.dg.com | nowhere, baby, but we should be {world}!mcnc!rti!dg-rtp!cohend | should be goin' nowhere fast." Data General Corporation, RTP, NC| - Streets of Fire