Insert/Update for Retool database table does not work in SQL mode

Queries and Resources
, ,
1

I have encountered a problem when trying to insert/update a record in a Retool database table while using the SQL mode instead of GUI mode. I have the table BPlus_Anschriften in the Retool database and successfully inserted/updated the records in this table using the GUI mode. Now I try to do the same in SQL mode and it doesn't work.

Reading the data from the table works fine:

SELECT * FROM "BPlus_Anschriften" WHERE "AdrNr" = 79273 AND "AnsNr" = 0

But when trying to update this record with

UPDATE "BPlus_Anschriften" SET "Na2" ='TEST' WHERE "AdrNr" = 79273 AND "AnsNr" = 0

I just get the following error:"syntax error at or near "UPDATE ""

The same happens when I try to insert a new record.

INSERT INTO "BPlus_Anschriften" ("AdrNr", "AnsNr", "Na2") VALUES (11111, 0, 'TEST')

When using a different resource, like a BIG QUERY table, these queries work, but not with the Retool database.

I tried the tablename with and without quotation marks, it does not work in any of the cases. These are just basic insert and update queries. Any idea what the problem could be?

Is it maybe not possible to write into the Retool database while using SQL mode?

2

Try:
UPDATE BPlus_Anschriften SET Na2 ='TEST' WHERE AdrNr = ANY(79273, 0) though 79273 and 0 may need to be put in single quotes each

3

AdrNr and AnsNr are different fields in the table.

Nevertheless, the insert query still doesn't work.

But today the update query

UPDATE "BPlus_Anschriften" SET "Na2" ='TEST' WHERE "AdrNr" = 79273 AND "AnsNr" = 0

suddenly works. Nothing changed, not the table nor the query, and now the query succeeded. How is this even possible?

4

Probably a cache issue - What db are you using?

5

The Retool database

6

I don't understand why you are using quotes, when I am using the update statement using POSTGRES, I don't:
update team set name = {{teamName.value}} ... just as an example.

7

Without the quotes the query doesn't work:

grafik

8

This syntax looks like it should be correct UPDATE "BPlus_Anschriften" SET "Na2" ='TEST' WHERE "AdrNr" = 79273 AND "AnsNr" = 0. I'm not sure if the query is copied directly from the app but I've seen before that using opening/closing quotes instead of straight quotes has caused similar issues.

Would you mind if I took a step in to take a look at it directly @ConDev?