Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamic Vs Static SQL

 
Tom Raj
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can somebody elaborate the difference between Static and Dynamic SQL. I think even if we have parameter markers in the SQL, it can be considered static. Does this has to do somehting with compilation/binding of the SQL??

If any body could give some examples that could be great..

Thx
Tom
 
Shaan Shar
Ranch Hand
Posts: 1249
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom this is a very simple issue in static query you are giving the parameters before compile time and then compile and run it whereas in Dynamic query which we used like PreparedStatement in JAVA is dynamically binded at the run time with the parameters. Ya offcourse it's a sort of Dynamic binding in PreparedStatement.

I hope this time you are clear in your mind about Static and Dynamic Query.
 
Bruno Boehr
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom,

The answer largely depends on the context. Normally you would speak of static vs dynamic SQL as applied to stored procedures, in which case static SQL refers to valid SQL constructs as per the SP syntax of the database at hand. Static statements may have parameters to be replaced with bind variables at runtime, but they are still static in the sense that their validity against the schema can be ensured at compile time.

Dynamic SQL, on the other hand, is generated by the stored procedure at runtime by way of concatenation of some sort, and as you can guess, there is no way to tell upfront whether your dynamic query will be correct SQL or not.

In essence, dynamic vs static represents the same old freedom vs safety dilemma, so it's really up to you to decide which approach fits better with the problem you are trying to solve.

Now, if we observe these concepts in the context of Java and JDBC, they won't make much sense since there is no compile-time checking of SQL syntax. In a sense, however, we can regard prepared statements as some sort of "static" SQL, meaning that their underlying SQL strings are predetermined and won't change between invocations of execute().

Prepared statements have lots of advanteges over regular statements in terms of performance (cached execution plans), security (stronger protection from injection attacks), robustness of code (the driver takes care of proper formatting of parameters, including character escaping), etc.

As a general rule of thumb, if you need to use a query that has varying elements in it, but is still the same SQL construct if parameterized, you should use a prepared statement. Otherwise, compose the query on the fly and be prepared to deal with all the side effects that come along with freedom
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic