SQL statement (non-GUI), how to throw exception on update

💬 Queries and Resources
1

  • Goal: When in SQL mode (non-GUI) when an update statement affects 0 rows, trigger the onFailure() event.

  • Steps: I can achieve this using the GUI, but do not see how to achieve this using a traditional SQL query. I have tried adding "SELECT @@ROWCOUNT" at the end of my SQL statement.

  • Details: I cannot use GUI mode because GUI mode uses an OUTPUT statement, which causes an error involving the table triggers. The SQL statement (non-GUI) seems to work, but I have no way of knowing whether it affected 1 or 0 rows.

As stated above, I cannot use GUI mode due to the database setup, but SQL mode seems to work. I am now trying to implement some error handling/retry code, but in order to do that I need to know whether the statement affected the desired row or not.

cleaned/simplified SQL statement:

UPDATE tableA
SET colA = 'Y', colB = {{ newAmt }}
WHERE Id = {{ idValue }}

For a simple example, if the Id does not exist in the database, the update statement will update 0 rows but will not throw an exception.

2

Hello @GaDwags88!

Could you use the T-SQL THROW statement and combine it with a CASE or other some such arrangement in your statement?

3

I thought about that too. When I add the following code to the end of the UPDATE statement:

SELECT CASE WHEN @@ROWCOUNT = 1 THEN @@ROWCOUNT ELSE THROW 51000, 'Err' END

I get the following error in the console window:

Incorrect syntax near near THROW.

then the entire UPDATE statement fails, and it fires the onFailure event handler. Also odd, this particular query is called inside a Promise.all(), and this Promise.all also has a .catch() at the end. The Promise.all() is not catching any error, it is behaving as though the query executed successfully.

At any rate, that won't work because the UPDATE statement will never succeed, even when it affects the correct number of rows. Thanks for the suggestion though.

1 Like
4

Hey, as a workaround you can throw in the transformer.
E.g. you could return the updated objects and check the length of the return value

Query:

UPDATE tableA
SET colA = 'Y', colB = {{ newAmt }}
WHERE Id = {{ idValue }}
RETURNING *;

Transformer:

if (data.id.length === 0) {
  throw new Error('Applied empty update')
}
return data

Or you could return a boolean value

image
image483×496 26.2 KB

5

Regarding this: Retool query trigger functions never throw errors directly.
Instead, you can handle outcomes by passing onSuccess and onFailure callbacks.

If you prefer you can wrap your query in a Promise like this:

await new Promise((resolve, reject) =>  brokenQuery.trigger({
  onSuccess: d => resolve(d),
  onFailure: e => reject(e),
}));
6

@GaDwags88,
If the transformer workaround that @TobiasOhlsson suggested does not work for you, you can always run an onSuccess handler and check the length of the data, and if it is zero, run your onFailure code.

7

@GaDwags88,
Just checking in to see if your issue has been resolved.