05.08.2024
15:58
Hi,
this may sound strange, but so was the reason, why I'm proposing this.
My suggestion is that the expression editor for a SQL command inserts the instance id variable in a way:
Currently it is inserted as: {WFD_ID}
My suggestion is that it is inserted within single quotes: '{WFD_ID}'
The reason for this is, that it would prevent errors which may only surface in production.
This is the issue what I encountered.
A process was tested with a test protocol and transported from validation environment to production.
The process worked for a while just fine, than it failed with the error:
** The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction **
Digging deeper brought up the actual error:
** Conversion failed while converting value 'xxx' to integer **
The root cause was that I forgot to add the single quotes in a where condition using the id of a choose field
Wrong statement: and {WFCONCOL_ID:544} = {WFD_ID}
Correct statement as of WEBCON BPS 2023 with the calculated id columns: and {WFCONCOL_ID:544} = '{WFD_ID}'
The big question is, why was it working in the first place and only failed in production?
The production database contains way more data and the SQL server decided to execute the query differently.
Test system: The index of the choose column wasn't used, which "magically" allowed the query to work.
Production system: The index of the choose column was used which contains text and integer and the query fails.
While I'm aware, that adding the single quotes may lead to:
- A slower execution, if the variable is used in other cases, where no casting would be necessary
- Manual removing the single quotes, if this would break the existing statement
I prefer a slower execution and an actual error than an error popping up at some point and explaining it to a customer. If an error happens ones, what can be done to prevent it in the future and what needs to be done now, to verify that the same error hasn't been made in other cases?
Of course, this won't actually prevent an error, but this change will make it less likely.
I'm wondering whether all variables from the values tab should be inserted with single quotes. I know a lot of other situations, where I forgot to add them but at least they caused an error immediately. :)
Best regards,
Daniel
