473,839 Members | 1,454 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

static/dynamic/embedded SQL distinctions

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!!

Aug 22 '05 #1
4 4790
jr********@hotm ail.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.Result Set rs = stmt.executeQue ry(
"SELECT colA, colB FROM tableA"
) ;

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

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

java.sql.Result Set rs = getStmt(con, "London").execu teQuery() ;
while(rs.next() ) {
System.out.prin tln("colA: " + rs.getString(1) ) ;
System.out.prin tln("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

Aug 22 '05 #2
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.
..

Aug 22 '05 #3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

jr********@hotm ail.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@squeakydolp hin.com>

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

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

iD8DBQFDCjmw3na BnF2rJNURAhnqAJ 9qkxTWvaFndj0TZ mP5RAsO/CBi2QCeIHgD
oAi6MAXrNur7+P+ 0PBLVj+w=
=oPfU
-----END PGP SIGNATURE-----
Aug 22 '05 #4
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 "Applicatio n 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 ***

Aug 22 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
5837
by: Dumitru Sipos | last post by:
Hello everybody! is there possible to have a function that is both static and virtual? Dumi.
15
3049
by: rwf_20 | last post by:
I just wanted to throw this up here in case anyone smarter than me has a suggestion/workaround: Problem: I have a classic producer/consumer system which accepts 'commands' from a socket and 'executes' them. Obviously, each different command (there are ~20 currently) has its own needed functionality. The dream goal here would be to remove all knowledge of the nature of the command at runtime. That is, I don't want ANY switch/cases...
2
1416
by: Dominik Stadler | last post by:
Hi, We are trying to migrate an application from Oracle to DB2. In Oracle we used some functionality to attach a dynamic SQL - connection to the static SQL Connection in order to perform SQL in one transaction in both static and dynamic SQL code. We would like to connect to the database using EXEC SQL CONNECT TO... and then use one of the CLI-methods to perform SQL in the same transaction.
13
14630
by: Krivenok Dmitry | last post by:
Hello all! Perhaps the most important feature of dynamic polymorphism is ability to handle heterogeneous collections of objects. ("C++ Templates: The Complete Guide" by David Vandevoorde and Nicolai M. Josuttis. Chapter 14.) How to implement analogue of this technique via static polymorphism? Perhaps there is special design pattern for this purpose...
5
3180
by: pittendrigh | last post by:
There must be millions of dynamically generated html pages out there now, built by on-the-fly php code (and jsp, perl cgi, asp, etc). Programatic page generation is transparently useful. But querying a database, negotiatinig lots of if-then-else logic and echo'ing html code out on port 80 every time a page is requested has to be a huge waste of resources. Why not use that logic to print static html instead of dynamic?
24
19107
by: Ken | last post by:
In C programming, I want to know in what situations we should use static memory allocation instead of dynamic memory allocation. My understanding is that static memory allocation like using array is faster than malloc, but dynamic memory allocation is more flexible. Please comment... thanks.
7
8228
by: Jo | last post by:
Hi, How can i differentiate between static and dynamic allocated objects? For example: void SomeFunction1() { CObject *objectp = new CObject; CObject object;
20
4309
by: Nickolai Leschov | last post by:
Hello all, I am programming an embedded controller that has a 'C' library for using its system functions (I/O, timers, all the specific devices). The supplied library has .LIB and .H files. How can I dynamically load a LIB file and access all its functions? Surely someone has solved similar task? My intention is to use a Forth system for programming the controller,
6
7975
by: =?ISO-8859-1?Q?Tim_B=FCthe?= | last post by:
Hi, we are building a Java webapplication using JSF, running on websphere, querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and PreparedStatements only (aka dynamic SQL). Every night, there is a ETL which deletes most of the data in the database and fills it all new. We observed very bad performance for some statements that ran for minutes (The queried table is about 100,000 records and the result is about 500 rows)....
0
9855
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9697
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10908
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10586
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10648
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10293
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5682
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4484
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.