Named queries exception

Ignition
1

Attempting to set up a named query to insert a row if matching one isn’t found, and update part quantity once matching row is in the table. Running the query in database browser works, but when I place the query in a named query and add parameters, i am getting java.lang.ArrayIndexOutOfBoundsException. All columns in this test database are INTEGER, and tried all of the Int# data types for parameter.

Query:

INSERT INTO testDB (style, color, qty)
SELECT :nStyle, :nColor, 0
WHERE NOT EXISTS (
SELECT 1
FROM testDB
WHERE style = :nStyle AND color = :nColor
);
UPDATE testDB
SET qty = qty + :nQty
WHERE style = :nStyle AND color = :nColor

2

Is the named query marked as an update query? Also, I don't believe that the named queries can handle the return from multiple update statements in the same query.

1 Like
3

Try without the Update.. I'm thinking you can't do multiple statements in one named query.

4 Likes
4

This is ok as long as you are expecting the parameters you pass in to be the results in your dataset, but if you’re trying to dynamically use column names these have to query strings just fyi.

5

it is set as an update query. Removing the update portion and running test execution, get error: query does not return results.

The parameters that pass in are values from OPC tags, not dynamic column names. When they select style 1, color 1, with qty 2 on the skid, and press go, i want to look in db for style 1 color 2 and add qty 2. If no row with style 1 color 1, create one and set qty to 2.

6

You are trying to use SQL scripts in JDBC. While partially supported by some drivers, SQL scripts are not officially supported. Use separate statements in separate named queries.