Multiple SQL statements in a named query

Ignition
1

Is it possible to execute multiple SQL statements from a single named query?

Something like the following in normal SQL:

BEGIN TRANSACTION

UPDATE tableA…

UPDATE tableB…

COMMIT

2

It is not supposed to be supported in JDBC, but some drivers allow it. If supported, it will almost certainly have to be run via scripting, where the Query vs. Update mode is dictated by the function used, instead of deciphered from the query text.

BTW, you say “normal SQL”. Combining multiple statements into a script is not officially part of the SQL specification, and there are incompatibilities between DB brands.

3

Ultimately, it would work to allow all of the text included to be executed by a JDBC statement would it not? What parsing is going on to get annoyed by the ‘;’ or other statement separator?

4

While not what directly what you are asking about, I am happily doing this into a MS SQL connector via a named query:

IF NOT (SELECT 1 FROM table WHERE id = :id_param)
BEGIN
  INSERT INTO table (id, field) VALUES(:id_param, :field_param)
END

So complex statements are supported to some degree. My suggestion is to try what you want with your DB of choice and see what happens.

5

In case someone else stumbles on this because they looked in the wrong place first, IA provides scripting functions like system.db.beginNamedQueryTransaction and rollbackTransaction that let you wrap multiple named queries into a single transaction block.

2 Likes
6

I recommend if you have a complex SQL query with multiple statements, store it in a SPROC, and just call the SPROC from your named query.

EXEC mySPROC :myparameter
1 Like