Re: static/dynamic/embedded SQL distinctions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

jrefactors_at_hotmail.com wrote:

> I want to distinguish between static SQL, dynamic SQL, and embedded
> SQL, but couldn't find too much useful resources in the web.
>
> For example, if we put SQL statements (SELECT, INSERT, UPDATE, etc...)
> inside an application (e.g. Java application, VB application, etc...),
> do we consider those SQL statements as static SQL? or embedded SQL?

OK, here we go:

embedded SQL - Actual SQL code embedded into your Java code and converted to

               Java code by a preprocessor and then compiled normally.

static SQL - SQL that is handled normally. It doesn't change at runtime.

               It is a constant string literal.

dynamic SQL - SQL that may change at runtime. For instance the where

               clause on a SQL statement may be dependent on factors 
               not known at compile time.  

The difference between static and dynamic SQL has to do with when the plan for database access is determined. With static SQL the plan is determined before your program ever runs (or at least could be). This means that the database doesn't have to figure out how to find the data you are interested in at runtime. It also means that if the database statistics change radically the plan used by your query may become out of date.

The plan used to execute dynamic SQL statements is determined at runtime. This means that knowledge only available at runtime may be used to form the SQL statement. It also means that the plan will be up to date with the current database statistics. Unfortunately the database will have to do extra work at runtime to determine what the plan should be.

These concepts have little to do with Java. Databases accessed using other languages run into the same set of tradeoffs.

If you would like sample code you might want to look up SQLJ at Google. There is plenty of source code on the web.

• -- Kenneth P. Turvey <kt_at_squeakydolphin.com>

Currently seeking employment as a Java developer in the St. Louis area.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFDCjmw3naBnF2rJNURAhnqAJ9qkxTWvaFndj0TZmP5RAsO/CBi2QCeIHgD oAi6MAXrNur7+P+0PBLVj+w=
=oPfU
-----END PGP SIGNATURE----- Received on Mon Aug 22 2005 - 15:46:27 CDT