Dynamic Sql

An SQL query that is defined at run-time from variable data. For example, in PHP:

 <?php
 // Connect to a database named "mydb"
 $dbconn = pg_connect("dbname=mydb");

 // Obtain a random number between 1 and 10.
 $rnum = rand(1, 10);

 // Create a dynamic SQL query.
 $qry = "INSERT INTO records(rnum) VALUES($rnum)";

 // Execute the query.
 $result = pg_query($dbconn, $qry);
 ?>

For comparison, see PreparedStatement.

(Topic title not to be confused with DynamicRelational)

Usage of PreparedStatements under the hood wouldn't change your classification it appears because one is talking about the query itself and not necessarily "strings". Thus, even if using a PS with a parameterized value (the random value) where the non-value portion of the SQL is a string literal, using a randomly generated value in the query would make the query "dynamic". You seem to be conflating "dynamic strings" and "dynamic queries" in the PS topics. I don't consider "query" to be excluding values: the query is the whole shebang: statements, values, etc. Strings are an implementation detail.

It's the querying that's dynamic, because the specific SQL text is being defined at run-time based on variable data. Think of it as software-generated ad-hoc(ish) SQL queries. PreparedStatements can be parameterised by passing arguments to parameters when they're executed, but a given PreparedStatement is static for at least the lifetime of the PreparedStatement, and usually the lifetime of the application.

But we are talking about a "query" in an abstract sense, not how the hardware processes queries. Conceptually such a query is "dynamic" because it could have contain value 7 during one run and a value of 4 during another. How the values/literals are processed under the hood is an implementation detail.

We could perhaps say that under typical PS usage, portions of queries are statically defined (don't change during run-time), but if some portions are static and some dynamic (values/literals, typically passed as parameters), then generally the entire query is considered "dynamic". Essentially, "static part + dynamic part = dynamic whole" in normal usage.

The key distinction, in comparison with PreparedStatements, is this: If you're not using PreparedStatements (i.e., you're using dynamic SQL), every time you wish to run a given query with a different value -- say a value of 7 in one run and a value of 4 during another -- you have to construct and send the whole query to the DBMS for execution. If you're using PreparedStatements, you send the query text -- including specified parameters -- to the DBMS only once for definition. Then you can run it as many times as needed, passing to the DBMS only a reference the pre-defined PreparedStatement and the parameter argument values.

From the application's perspective, it's still a "dynamic query". PS's are essentially caching parts, but that's an implementation detail, as caching typically is. Note that non-PS technology can also use caching or caching-like technology to avoid repeating certain steps also for queries that vary slightly.

You may consider a PreparedStatement to be a "dynamic query" in a loose sense, and PreparedStatements may -- also in a loose sense -- be "caching parts", but there is a fundamental difference between PreparedStatements and what is usually called dynamic SQL: Dynamic SQL -- call it non-PreparedStatements, if you feel more comfortable with that name -- is executed in a single step and has no means to define parameters. PreparedStatements are defined in one step with explicit parameters and executed in another with parameter arguments.

Sometimes one talks about a "pre-compiled query" for PS's, but that's PS-specific terminology. It's perhaps a shortcut for "pre-compiled query plan", but a query plan is not necessary the same as "query" in a colloquial or general sense. The "SQL" is still dynamic if literals can vary from the app's side. Counting the literals as separate from "query" or "SQL" is either wrong or not universal (English being vague as typically used).

The terminology I've used is conventional; that doesn't necessarily make it ideal, but it is what it is.

Ditto with mine. English sucks. In practice people tend to use terms relative to their concern(s) of the moment. Something would be "dynamic" relative to some point of view they are analyzing. Often one has to ask further questions to understand their intended meaning and perspective. Everything on Earth is ultimately dynamic, a meteor or nearby supernova explosion can make any source code or disk "dynamic" by turning it into fine powder or plasma. It's only "static" relative to some stated event or period. If something is dynamic or static in the low-level guts of an RDBMS, it may not matter to an application developer: it's not something they can directly "see" such that calling their in-app SQL snippets "static" or "dynamic" based on what the low-level guts do is not very useful in practice and may even be misleading.

Be that as it may, the terms "prepared statement" and "dynamic SQL" have specific conventional meanings -- especially in relation to each other -- that are recognised by application developers. Calling (say) a typical prepared statement "dynamic SQL" is potentially misleading. It's certainly needlessly confusing.

I'm not aware of that verbal association, but have not taken any formal surveys.

Not sure I follow -- you mean you've not heard of a PreparedStatement being described as "dynamic SQL", or you have?

I don't remember ever hearing "dynamic SQL" used to describe SQL that is not associated with PS's. In other words, I've never heard it used as an alternative/contrast to "prepared statements". Typically something like "direct SQL" would be used to describe SQL passed to the database that doesn't go through PS's. Although, I will admit I never paid close attention. Your usage just "rings wrong".

Google "dynamic SQL" to find about 734,000 results that appear to accord with my usage.

My googling does not bear that out at all. "Dynamic SQL" generally seems to refer to SQL statements generated (in part) by software instead of hand coded by programmers. For example, the criteria (WHERE...) and column-selection (SELECT...) clauses generated by QueryByExample. One won't know those clauses at software authoring time and thus will not be hand-coding the criteria clauses up front. This software-generated usage is pretty clear from my googling. That fact that you "saw" results that were highly PS-related is yet more confirmation that you hallucinate to see what you want to in order to protect your ego from being wrong. You've been caught with your objectivity down again. You are highly biased. Whether or not that's an AdHominem attack, it's my honest assessment of your personality (and a very common human flaw in general). And if you think I'm wrong, we'll LetTheReaderDecide whose hallucinations are correct.
Curiously, we seem to be in ViolentAgreement about what DynamicSql is. All the DynamicSql examples on this page and on the PreparedStatement and ValidatingPreparedStatementDefinitions pages are precisely what you described: "SQL statements generated (in part) by software instead of hand coded by programmers." That's exactly what DynamicSql is. Note, however, that the PreparedStatement examples are all static. There is no portion of any PreparedStatement example on the PreparedStatement page or the ValidatingPreparedStatementDefinitions page where the PreparedStatement SQL is "generated (in part) by software instead of hand coded by programmers." Even the example using static string concatenation is not constructing SQL programmatically per se, it's just using compile-time to modularly join programmer-defined static strings.
You seem to be extrapolating a very specific and narrow example to PS's in general. I'm talking generalities in this topic. One can use PS's with QueryByExample, for example, and have the statements generated dynamically by the software at run-time based on user-given selections and input. (It's expected that the "values" will be given through the value-intended portions of PS API's.)
Yes, you can dynamically construct PreparedStatement definitions from a subset of a collection of static strings. However, by convention, such constructs are not called DynamicSql. They're usually called "dynamically-constructed PreparedStatement definitions", if for no other reason than to distinguish them from "dynamic SQL" which means "not PreparedStatements".
You are quibbling. Everything is ultimately "static" if split (examined) at a small enough level. Tautology. And I got zero hits for "dynamically-constructed PreparedStatement definitions" in Google. You appear to be making things up.
"Dynamically-constructed PreparedStatement definitions" are what they are, if they're dynamically-constructed. It's a descriptive sentence, not a term. If you prefer, you may call DynamicSql "executing SQL statements directly", which I note is used on the WikiPedia PreparedStatement page at http://en.wikipedia.org/wiki/Prepared_statement or you can call it "immediate SQL", as from http://rpbouman.blogspot.co.uk/2005/11/mysql-5-prepared-statement-syntax-and.html However, note that "dynamic SQL" is the usual term, as evidenced by (for example) http://stackoverflow.com/questions/3553120/what-are-prepared-statements-how-are-they-different-from-dynamic-sql or http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
Two links is not enough to make it "usual". You'd have to do a proper statistical sampling to establish that. Anyhow, I don't dispute some may use it that way, but again, it's an overloaded term, like "type" and "value".
Of course "two links is not enough to make it 'usual'". The two links are examples of what is usual, not proof of what is usual. That's why I prefixed the two links with "for example". If you want proof, you need only search for "dynamic SQL". If you have difficulty with "dynamic SQL" being overloaded and find it objectionable to disambiguate the term by its context, then I suggest you mentally translate "dynamic SQL" to "immediate SQL" (or whatever term you prefer) when you see it used here (and elsewhere) to contrast with PreparedStatements.
I already told you what I found, above. At least we agree it's overload. But that's why I recommend a local working definition be used instead in PS-related topics.
That's would be reasonable if there's frequent confusion about, or frequent difficulty distinguishing, PreparedStatements from DynamicSql. I don't see any evidence that it's frequent.
Most online material is about solving specific issues, not general classifications, philosophy, or vocabulary. If one shows a code snippet and sample I/O for it, it does not really matter what one calls because it does what it does regardless of labels used to describe it.
Be that as it may, there does not appear to be frequent confusion about, or frequent difficulty distinguishing, PreparedStatements from DynamicSql.
Like I said, it's because understanding can be and often is conveyed in ways besides words or as supplements to words. Just because understanding is achieved does not necessarily mean that understanding was achieved by words or words alone.
Note that many may know how to use PS's to get their work done, but may not know or care how they work under the hood. They are just told to use them and find examples showing how to use them, not giving much care about the deep guts. Most developers I encounter are not deep thinkers: they focus on making their customers and boss happy and getting paid. If the shop tells them to use purple gerbils to process queries and they are paid to use purple gerbils, they will use purple gerbils.
There's no mention here of how PreparedStatements "work under hood", only of how they differ -- from a (highly relevant) application developer's point of view -- on the client-side of the DBMS.
Bullshit! The API's are almost entirely an arbitrary interface design choice. I will agree that people tend to use them "out of the box" as-is, but that's not an inherent difference, but rather a human preference of a developer/manager/shop.
The SQL definitions are the same, independent of APIs (i.e., it doesn't matter whether you're using JDBC, ODBC, some native interface, Perl DBI, PHP PDO, PostgreSQL's libpq, etc.) because it's about creating SQL. PreparedStatements and DynamicSql are different in (and exposed by) the DBMS.
Above you said, "There's no mention here of how PreparedStatements "work under hood"...", but NOW you are talking about under the hood ("in...the DBMS"). It appears to be a contradiction to me. The API's typically used by developers generally don't expose the under-the-hood processing difference between traditional processing and PS processing: by adjusting the API's, they can be swapped without a developer noticing the difference (under normal circumstances, performance profiles aside). Thus, DYNAMIC-NESS, AS TYPICALLY PERCEIVED BY DEVELOPERS, IS NOT DETECTABLE BY LOOKING AT QUERY-RELATED API'S AND THEIR RESULTS alone (tradition and habit patterns aside).
I'm not talking about how PreparedStatements are implemented in the DBMS, I'm talking about how they are perceived by developers. PreparedStatement definitions are typically (and should be) static. I.e., the query definition does not change, only the parameter values change. It's similar to how a function definition can be static, but the parameter arguments it's passed on invocation can change. DynamicSql requires that the SQL itself changes, which is why it's called "dynamic SQL". Using the same analogy, it's like writing and compiling a block of code every time some value in it needs to change.
But you comparing portions of SQL (the "non-value" portions, for lack of a better term) to full SQL such that they are not equivalent things, making the comparisons useless. And how they are "perceived by developers" is shaped largely by the query API they are using, which can mask (abstract away) processing-related issues.
Sorry, I'm not following you. I don't know what you mean by "comparing portions of SQL ... to full SQL such that they are not equivalent things." Whilst it's true that some query APIs significantly shape developer perceptions -- object-relational mapping layers, for instance -- DynamicSql and PreparedStatements are recognised as low-level SQL and RDBMS constructs independent of the particular APIs used to send them to the DBMS.
It seems pretty strait-forward; I don't know why you don't know what I'm talking about. I'll try to think of a different way to say it, but right now I'm at a loss.

By the way, PreparedStatements are not run through some pre-processor inside the DBMS to internally generate "direct SQL", so it wouldn't make sense for there to be "SQL passed to the database that doesn't go through PS's". SQL doesn't "go through" PreparedStatements. A PreparedStatement is one type of SQL construct; DynamicSql is another.

"Go through" and "processes as a 'type of construct'" are not necessarily mutually exclusive. Thus, I don't understand your complaint.
Perhaps there's a terminological misunderstanding. When you wrote "SQL passed to the database that doesn't go through PS's", what did you mean by SQL "that doesn't go through PS's"? It sounds to me like you meant that SQL either passes through PreparedStatements as some sort of data transfer, or SQL that is translated from PreparedStatement syntax to some other SQL syntax.

[I'll also add that googling "direct SQL" resulted in a page that consisted primarily of frameworks. The term appears to primarily be used to describe a method of sending ad-hoc SQL to the database instead of having the framework generate the SQL for you.]

Okay, I won't challenge that observation of google results. But the fact remains that there is no universal term for SQL that is not processed through PS mechanisms. Nobody has established that "Dynamic Sql" is the hands-down favorite; and per above, strongly leans toward meaning software-generated SQL (which is orthogonal to PS usage).

If it helps, whenever you see one of us use the term "DynamicSql", whisper "SQL that's not PreparedStatements" to yourself.

I'd rather not use an overloaded term. What about we use "STNPS" as a local working term to avoid general confusion.

Please, let's not construct PrivateLanguage merely because you don't like conventional terminology. The reason this page exists is to disambiguate PreparedStatements from Dyn- Sorry. From what this is page is about.

Well, it's not working. "Dynamic" is ultimately a relative term and generally is used from a specific and/or situational perspective or scope.

It's not working for you, perhaps. I don't see your particular difficulties appearing elsewhere. Notably, the differences between PreparedStatements and whatever-you-call-it seems to be clear pretty much wherever both appear.

No, it's not clear to me.

Do you understand the difference between...

 $qry = "SELECT * FROM customers WHERE name = '" + pg_escape_string($userInput) + "'";
 $result = pg_query($dbconn, $qry);

...and the following?

 pg_prepare($dbconn, "myquery", 'SELECT * FROM customers WHERE name = $1');
 $result = pg_execute($dbconn, "myquery", array($userInput));

Yes, it's a query API (middle-ware) design choice. PS's and filters can each use the other "style" if API's are so adjusted. Other than perhaps naming "hints", it tells us nothing concrete about the "guts" of SQL processing or whether program-constructed SQL is ultimately used by PS's somewhere along the line.

No, that's incorrect. Look up "pg_query()" and "pg_execute()" in the on-line PHP manual.

You seem to be missing my point. But I'm trying to kick my addiction to PS-related replies and thus will not give further explanations.

Trying to make a point instead of answering my question will only result in confusion. Do you understand the difference between PreparedStatements and immediate SQL / DynamicSql / executing SQL statements directly?

Yes, but you are only showing an interface, not the technical processing steps that are key to the difference.

Do you mean you want to see the DBMS internals for PreparedStatements? Why would "the technical processing steps" be relevant here? And if you do understand the difference between PreparedStatements and immediate SQL / DynamicSql / executing SQL statements directly, why did you write above that the differences between PreparedStatements and DynamicSql are "not clear to me".

Your writing is not clear to me.

You're relying only on my writing about PreparedStatements and DynamicSql in order to understand the differences between them?

No, I'm trying to figure out what the fucking hell you are talking about.

If you can't understand the difference between PreparedStatements and DynamicSql, the problem isn't my writing.

I am not certain what YOU mean by both those terms. I thought I did early, but peculiar responses by you suggests I got it wrong.

What responses of mine do you think are "peculiar"?

I'd have to empty a new highlighter answering that.

That's evasive, and borders on being an AdhominemAttack. It suggests that you have no rational answer to my question.

Yes, I understand the difference perfectly well.

That's not the question here. What responses of mine do you think are "peculiar"?

I do not feel it would help anything to answer that. All instances I checked already indicate problems with the text in replies. It would only create redundancy to duplicate that.

I think the problem lies entirely with your understanding, not the replies themselves.

You are welcome to your own screwy opinion on that.

Again, this is bordering on being an AdhominemAttack without offering any new information, evidence, or rhetoric.

Accusing somebody of "not understanding" can also be an AdhominemAttack, AND too general to be useful. It looks like hypocrisy to me.

If the problem appears to lie entirely with your understanding, then I can only say that the problem lies entirely with your understanding. That's not an AdhominemAttack but an assessment of the situation. If I said I thought your lack of understanding is because you're an idiot (which I'm not saying, by the way), that would be an AdhominemAttack. You're right that identifying a lack of understanding is too general to be useful, but at this point I'm not sure what would be useful because you seem unwilling to tell me what responses of mine you think are "peculiar". I am left wondering if that was merely a spurious insult.

If it doesn't convey new and use-able information it probably should be skipped because it's usually considered un-friendly and is often frustrating because it cannot be processed further.

MayFourteen