Hello there. My team is running into a very challenging issue that we haven't been able to resolve completely. We have a kiosk where a user requests a serial number. The screen looks for the next incrementable serial number for the part, returns the serial number, and IF a valid serial number is returned, will send a print request to a label printer to attach to a document for the part. Below is heavily redacted snippets of our sql meant to show our transaction methodology.
--
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SerialNumber TABLE ([SerialNumber] VARCHAR(46),RN INT);
BEGIN TRANSACTION TR1
BEGIN TRY
IF <STATEMENT IS TRUE>
INSERT INTO <TABLE> (SerialNumber)
OUTPUT INSERTED.[SerialNumber]
INTO @SerialNumber([SerialNumber])
<SELECT STATEMENT>
COMMIT TRANSACTION TR1
END
ELSE
-- Roll back transaction and return error code
BEGIN
ROLLBACK TRANSACTION TR1
INSERT INTO @SerialNumber([SerialNumber],[RN]) VALUES (-1,0)
END
SELECT TOP 1
[SerialNumber]
FROM (
SELECT T.[SerialNumber],T.[RN]
FROM @SerialNumber T
) T
ORDER BY [RN] ASC
END TRY
-- Catch errors, roll back transaction, and return error code
BEGIN CATCH
BEGIN TRY
ROLLBACK TRANSACTION TR1
END TRY
BEGIN CATCH
END CATCH
SELECT TOP 1 CAST('-1' AS VARCHAR) [SerialNumber]
END CATCH
We run this via a named query execution system.db.runNamedQuery. If the query functions as expected, it returns the created serial number. If a non valid serial number returns, such as -1, the script will not continue, and will warn the user without printing a label.
This functions the vast majority of the time. However, at unpredictable, rare moments, this query will return the created serial number without actually inserting the returned row to the target table. This is allowing the user to print labels of serial numbers that are not actually being entered into the database, causing big headaches with traceability. The query does not fail when this happens, making it very difficult to actually catch whatever is happening and when it happens.
I was wondering if perhaps we were having transactions not committing to the database and if anyone else has run into this problem. Could it be our isolation level? Do transactions within Ignition SQL cause potential issues with the driver? Thanks for any help. I can try and give more details on our full query if that helps.
It has been a while since I used transactions but I "believe" the BEGIN TRANSACTION should be INSIDE the BEGIN TRY and then utilize a BEGIN CATCH to return any error codes?
I'm not super familiar with the interactions between the OUTPUT, table variables, and the TRY-CATCH but reading some documentation suggests that the OUTPUT value may still be inserted into the table variable there if the insert fails. I'm not sure how it might get returned to the client if the try-catch errors but maybe it's happening in some other part of your code?
Have you tried logging some of the errors in the CATCH statements? I imagine it might just be unique constraint errors but it might help.
Consider ditching the script. Just ensure that there is a unique index on that column, so a simple INSERT statement will fail on duplicate. Catch the error. No transaction needed.
2 Likes
Tried this, but this table has multiple primary keys, making getKey() not viable. I removed all try-catches and removed the transactions, instead using a namedQuerytransaction to handle it.
However, I still needed to have a select statement to get the output of the insert. Can the JDBC driver really not handle multiple SQL "executions" well as long as the select output is consistent? We have some very heavy queries all throughout our Ignition projects that have never seemed to have given us issues like this before. I was hoping that layering transactions in the SQL on top of however the JDBC driver handles transactions was the culprit of this specific problem.
It cannot. Strictly speaking, vanilla JDBC doesn't support scripts like yours at all. That you can run them is a purely brand-specific.
Perhaps you need system.db.runPrepInsert(), which can return multiple-column keys.
Ooops. Based on your non-standard identifier quoting, it seems like you are using an incompetent DB brand, so maybe not.
But anyways, make a multiple column unique index. Compute the desired new SN in Ignition and simply try to insert it. The unique index will reject dupes for you.
1 Like
Are you using MSSQL?
Can you use a Stored Procedure to run this SQL?
Have you tried a test setup (in the SSMS) where you can run this SQL say 5,000 or 100,000 times and see what happens on each INSERT? Perhaps, as @nicole.c suggested, throwing a SELECT or INSERT statement in the CATCH block might give you something to work with.
Assuming an error IS occurring, SQL Server has error handling functions, as I imagine most DBs do, that can provide additional information.
EDIT: A stored procedure or user-defined function can calculate and return the next viable Serial Number as well.
I don't think there's an inherent problem with using the transactions within SQL and the JDBC driver, it's mostly down to the design of this particular code and how it was handling errors. Practically, your code needs to consider what will happen in an error state and any side effects or aftereffects of those errors, and it doesn't look to me like you are. 
This logic would be better suited in a stored procedure rather than a named query. Additionally, I recommend placing the COMMIT statement at the end of the TRY block. This ensures that if an error occurs, the ROLLBACK will properly undo all inserts, updates, or deletes within the transaction. I also suggest starting the BEGIN TRANSACTION immediately after the BEGIN TRY to clearly scope the transaction within the error handling block.
This is a standard template I use for writing a MSSQL stored procedure with TRY / CATCH and TRANSACTION control.
USE [<dbName>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author>
-- Create date: <date>
-- Description: this stored procedure ....
-- =============================================
CREATE PROCEDURE [dbo].[<procName>] (
@i_param1 AS VARCHAR(50)
, @i_param2 AS NVARCHAR(MAX)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
--
-- do work here
-- insert statement 1
-- insert statement 2
-- update statement 1
--
--commit changes
COMMIT;
END TRY
BEGIN CATCH
-- Rollback any outstanding transactions
IF @@TRANCOUNT > 0
ROLLBACK;
-- Re-throw the original error so that Ignition is notified with the error
THROW;
END CATCH;
END;
In the CATCH block, I include a ROLLBACK to undo any changes. Additionally, you can log the error details along with the serial number into an error log table. I also use THROW at the end of the CATCH block to propagate the error and ensure the caller is notified.
1 Like