• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Dynamic Vs Static SQL

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1252
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
That new kid is a freak. Show him this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic