Resolving Ignition getKey Failures on SQL Server Inserts

On Ignition 7.8 with SQL Server 2014 and Java 8u65, system.db.runPrepUpdate(qry, [v], getKey=1) inserts the row but never returns the identity value. The script then dies with SQLServerException: A result set was generated for update. The failure is in the JDBC key-retrieval path on the gateway, not in the SQL. The fix that holds is to have SQL Server return the key as an ordinary result set, through a stored procedure or an OUTPUT INSERTED clause. Once you do that, Ignition no longer depends on the driver's generated-keys handling.

Where does the getKey request stop between the button and SQL Server?

Follow the exception chain from the outside in. Each wrapper names the hop that passed the failure along.

The call signature in the trace, (INSERT INTO TEMP (name) Values (?), [I was here], , true, false), reads as query, arguments, database (blank, so the project default connection), getKey, and skipAudit. The client JRE plays no part. The statement runs on the gateway, so the gateway's JRE and driver jar decide the outcome.

Why does the driver report "A result set was generated for update"?

With getKey set, Ignition prepares the statement with the JDBC generated-keys flag, calls executeUpdate(), and then asks the driver for getGeneratedKeys(). SQL Server has no native generated-keys protocol. The driver handles it by appending an identity select, , to the batch and then walking the result stream. It expects an update count first and the key row after that.

executeUpdate() requires the first result to be an update count. If a result set arrives first, the driver throws this exception. The INSERT has already executed on the server by then, which is why the row appears while the key is lost. The usual things that push a result set ahead of the update count are:

• An INSERT trigger on the table that runs a SELECT without SET NOCOUNT ON.
• A driver whose generated-keys handling does not match what the server returns.
• A driver jar or JRE state on the gateway that differs from the one that worked earlier.

Here the table has no trigger, and the fault came and went with the Java runtime state. A Java update had partially deleted files in the 8.0.65 folder. Letting that update finish cleared the error, and then the error came back after a gateway restart with no further Java update. That intermittent pattern points to the driver/JVM layer, not to the schema.

What should you check on the gateway and in the database first?

If a trigger returns rows, add SET NOCOUNT ON and remove the stray SELECT. If no trigger exists, as in this case, stop working the schema and move key retrieval out of the driver.

Which key-retrieval approach holds up: driver keys, string query, or SQL-side return?

Return the key as a result set. Use the stored procedure where you own the database. Use the OUTPUT clause where you cannot add objects. Both go through executeQuery(), where a result set is the expected answer, so the exception cannot occur.

How do you move key retrieval into a stored procedure or OUTPUT clause?

1. Create the procedure. SET NOCOUNT ON suppresses the INSERT's row-count message, so the key row is the first result the driver sees:

2. Grant EXECUTE on dbo.InsertTemp to the login used by the Ignition database connection.
3. Replace the button script. Call the procedure with a query function, not an update function:

   v = 'I was here'
   ds = system.db.runPrepQuery('EXEC dbo.InsertTemp ?', [v])
   newId = ds[0][0]
   print newId

4. Alternatively, skip the procedure and use the OUTPUT clause directly:

   qry = 'INSERT INTO dbo.temp (name) OUTPUT INSERTED.id VALUES (?)'
   ds = system.db.runPrepQuery(qry, ['I was here'])
   newId = ds[0][0]

5. If sys.triggers shows an enabled trigger on the table, SQL Server rejects OUTPUT without INTO. In that case use the procedure form with , or output into a table variable and select from it.

Two pitfalls come up repeatedly with this pattern:

• A batch like sent through runPrepQuery without SET NOCOUNT ON returns the update count first. The driver then reports that the statement did not return a result set, which is the mirror image of the original error.
• @@IDENTITY picks up identities generated by triggers in other tables. Use or OUTPUT.

How do you prove the key path survives restarts and Java updates?

1. Run the new call from the Designer Script Console and confirm newId prints an integer.
2. In SQL Server Management Studio, run SELECT TOP 1 id, name FROM dbo.temp ORDER BY id DESC and confirm the id matches the printed value and that exactly one row was added.
3. Fire the button several times in a row from a Vision client. Confirm the returned ids increment with no gaps caused by failed calls, and that the gateway log shows no SQLServerException.
4. Disable the Java auto-updater on the gateway host so the runtime cannot change under a running gateway. Schedule JRE changes as maintenance with a gateway restart afterward.
5. Restart the gateway, repeat steps 1-3, and confirm the returned id still matches the newest row in dbo.temp.

FAQ