static/dynamic/embedded SQL distinctions

[jrefactors]

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?

How about dynamic SQL? any practical examples?

Please advise. thanks!!

[shakah]

jr********@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?

How about dynamic SQL? any practical examples?

Please advise. thanks!!

My take on that would be...

static SQL
==========
Hard-coded SQL statements in code, e.g.:
java.sql.ResultSet rs = stmt.executeQuery(
"SELECT colA, colB FROM tableA"
) ;

while(rs.next()) {
System.out.println("colA: " + rs.getString(1)) ;
System.out.println("colB: " + rs.getString(2)) ;
}

dynamic SQL
===========
Building the SQL string according to passed-in parameters, e.g.:
public PreparedStatement getStmt(Connection con, String sCity) {
PreparedStatement pstmt = con.prepareStatement(
"SELECT colA, colB from tableA"
+ (null==sCity ? "" : " WHERE city=?")
) ;
if(null!=sCity) {
pstmt.setString(1, sCity) ;
}
return pstmt ;
}

java.sql.ResultSet rs = getStmt(con, "London").executeQuery() ;
while(rs.next()) {
System.out.println("colA: " + rs.getString(1)) ;
System.out.println("colB: " + rs.getString(2)) ;
}

As for embedded SQL, I've always thought of that as putting SQL
statements in code (in a language-neutral way) and using a preprocessor
of some sort to generate code in place, e.g what Pro*C does for Oracle:
http://www-db.stanford.edu/~ullman/f...e/or-proc.html

[--CELKO--]

Dynamic SQL is made up on the fly by a procedure or end user as a
string contianing SQL statements. The ANSI Standards have PREPARE and
EXECUTE statements for this. Microsoft and other vendors will do it
differently, but it is the same idea. Like having Query Analyzer in
your program -- and just as dangerous.

Embedded SQL is placed inside a host program, not built on the fly.
Its statements begin with the keywords "EXEC SQL <sql code>" . A
preprocessor converts these statements into API or CLI calls
appropriate for the host language.

Static SQL is simply native SQL code. Since SQL has to exist in a host
program to talk to the outside world, it will be embedded somewhere.
Or it can be part of a trigger, stored procedure, etc.

You missed the SQL/CLI, ODBC, JDBC, etc. which are call level
interfaces from host languages to an SQL database.
..

[Kenneth P. Turvey]

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

jr********@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 <[e-mail]>

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

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

iD8DBQFDCjmw3naBnF2rJNURAhnqAJ9qkxTWvaFndj0TZmP5RA sO/CBi2QCeIHgD
oAi6MAXrNur7+P+0PBLVj+w=
=oPfU
-----END PGP SIGNATURE-----

[HansF]

On Mon, 22 Aug 2005 12:02:02 -0700, jrefactors interested us by writing:

I want to distinguish between static SQL, dynamic SQL, and embedded
SQL, but couldn't find too much useful resources in the web.

LOL ... Did you check the Oracle documentation at http://docs.oracle.com

From the Oracle9i doc set:

Embedded SQL ... from the Concepts manual

http://download-west.oracle.com/docs...sql.htm#i18523

Dynamic SQL and Static SQL from the "Application Developer's Guide -
Fundamentals" in the Chapter 6, titled "What Is Dynamic SQL?"

http://download-west.oracle.com/docs...dy.htm#1006233

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***