SQL query does not return error

Queries and Resources
1

I have a simple query targeting Azure SQL:

Delete from towns where town_id={{tblTowns.selectedRow.data.town_id}}

If that record has a foreign key relationship the query should and does fail. However, Retool reports success. If I run the same query (with the same value as in tblTowns.selectedRow.data.town_id) in SSMS I get an error referencing the constraint (The DELETE statement conflicted with the REFERENCE constraint "FK__template___town___7BE56230 ...") as expected.

I can check to see if data.rowsAffected is undefined to know that a record was not deleted, but that is a bit clunky. I also cannot report to the user why it failed.

Ideas?

2

Ok, I am bumping this as it is becoming a major issue for me.

As an example this query returns success:

update invoices  set 
 ship_type_id a bunch of garbage here={{selShipType.value}},, 
 where invoice_id = {{qryInvoicesSelect.data.invoice_id[0]}}

It is not just one app, I tried to intentionally mess up some SQL queries in other apps and they return success. I am using Azure SQL for these apps. I tried another app that uses Postgres (uses Supabase) and that one does return an error is it fails.

So now if the query fails for some reason, there is not notice of it and it looks like it simply "did not work" which is super frustrating.

Help!

3

Hey @bradlymathews, I'm sorry that your issue has been neglected here for so long :slightly_frowning_face: I just tried to reproduce this based on your description on my end, and Retool appears to correctly recognize failed queries to a resource setup with Retool's MSSQL integration. Can you share what Retool version you're running?

4

I am currently using 2.83.10, not on-prem. It also happened yesterday on a different account (different client) to a different database, though it was still Azure SQL.

5

Hi @bradlymathews, Thanks for writing to us in Support directly about this issue as well as flagging this here on the Community Forum. I'm just posting here for visibility that this is a bug on our end with our MSSQL resource. We have a report in place for this issue and I'll update this post with our progress working towards a fix.

This doesn't fully resolve this issue, but you could define a failure condition for MSSQL queries as {{Object.keys(data).length === 0 && !metadata}}. That way Retool can at least recognize the query as failed.

6

I have the exact same issue, the workaround suggested works for now. Do you have an update on this issue?

7

Bumping this. Would sure make my life easier if I could get an error, especially with all of the typos these lazy fingers make.

8

The same here.
Running Snowflake DELETE query via a Run button in the bottom panel.

I get two messages:
SQL compilation error: error line 1 at position 28 invalid identifier 'NAMEX'
Query ran successfully

9

Failure event handler works when set in GUI.
It does not when set in a trigger method - onFailure.

10

Hi @ales! I'm not seeing this same behavior in the trigger method. Could you share a screenshot?

11

Hi all! This bug has been fixed for MSSQL :blush: Let us know if you're still seeing any unexpected behavior

12