Hi Declan,
As you are pointing out, the issue is with statements that affects zero rows, both delete and insert statements. The workaround we are using, is to add a IF statement to the SQL output, and only executing it there i a rowcount <> zero.
Workaround for
DELETE FROM [SCHEMA].[TABLE] WHERE X = Y would then be:
IF ( (SELECT COUNT(1) FROM [SCHEMA].[TABLE] WHERE X = Y) <> 0) BEGIN DELETE FROM [SCHEMA].[TABLE] WHERE X = Y ENDIt can be cumbersome to go trough all processes, especially for longer SQL statements. We have stopped upgrading any customers beyond 2.0.9.12 due to the issues. We are also seeing issues importing from some sources, using non-unicode data, where processes are taking 100x longer then before.
------------------------------
Emil Malmberg Fosdal
Solution Architect
Cognitech A/S
------------------------------
Original Message:
Sent: Tue August 02, 2022 05:10 PM
From: Declan Rodger
Subject: 2.0.9.14 - ODBCOutput Errors
A customer has recently upgraded their PA servers to 2.0.9.14, since then the behavior of ODBCOutputs has changed.
They have a TI process that has a simple:
ODBCOutput ( 'MyDSN', Expand ( 'DELETE FROM [MyTable] WHERE Version = ''XYZ'';') );
Previously (in older versions) if the table contained 0 rows where the version field was equal to XYZ, the process would just delete 0 rows and then continue.
In 2.0.9.14 if the WHERE clause returns 0 rows then the process aborts with an empty error message:
Error: Prolog procedure line (8): Error executing SQL query: "DELETE FROM [MyTable] WHERE Version = 'XYZ';". Error: ""
In 2.0.9.14, if the WHERE clause returns 1 or more rows then the process completes successfully.
The customer in question is running an Oracle DB, I have just spun up a test environment and encountered the same issue on an MS SQL DB also.
So it is not localized to a specific Database software.
Is this a known change in behavior? And if so is there a wider reach where other ODBC connectivity that worked previously may also now start causing errors?
I am aware of workarounds in terms of coding that I can employ to resolve this issue but just looking at this stage to understand if this is considered "expected behavior" or potentially a bug.
And if it's expected behavior then what else do I need to look out for?
Thanks,
Declan
------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------
#PlanningAnalyticswithWatson