Prepared statement setting alternative solution?

Workflows
1

I’m trying to prepare a query in a code block and insert it in a retool db block without GUI. Since it’s a bulk upsert with with composite primary key. This is not supported with the GUI as far as I know.

IMG_3972
IMG_3972692×665 91.7 KB

const query = `INSERT INTO
  app_po_items (po_id, variant_id, barcode)
VALUES
${values}
ON CONFLICT (po_id, variant_id) DO
UPDATE
SET
  barcode = EXCLUDED.barcode;`;

return query;

In the RT query block I have this:

{{`${code4.data}`}}

IMG_3971
IMG_3971651×1070 69.2 KB

This just outputs the result of the previous block that’s the query variable that was returned.

I get the error:

Error in converting query to prepared statement. You can disable this feature by checking the 'Disable converting queries to prepared statements' checkbox on the resource configuration page

When changing this setting my apps stop working because they need this setting. The workflow works when this setting is changed though.

So I’m stuck. Is there another way to get this working? I need the values inserted in this query somehow.

2

hey @Steven_W we struggle with the same issue.

I ended up disabling it for the entire resource and manually updated the 5-10 apps broken from this change, but will probably need to re-enable due to security of the query injection prevention.

Can you maybe duplicate the resource, with one for prepared statements disabled?

3

It’s in a workflow. I guess I will have to create a postgresql resource just for this. But I’m not sure if that works with the retool database.

I was hoping there’s some solution where I can insert / spread the values in the query block.

4

Prepared statements doesn't support dynamic table names (i.e. you can't have the table name inside {{}}), so we usually recommend @trz-justin-dev's suggestion of duplicating the resource. Having a separate resource with prepared statements off limits security concern & helps to ensure your other queries continue to work

5

I’m using retool database. Is it possible to duplicate that resource?

6

Would it work if I just inserted the query in the query box and spread the values there? I’m not sure how to go about this.

INSERT INTO
  app_po_items (po_id, variant_id, barcode)
VALUES

//values here somehow?

ON CONFLICT (po_id, variant_id) DO
UPDATE
SET
  barcode = EXCLUDED.barcode;

This above in this replacing code4.data:

IMG_0458
IMG_0458608×1000 70.2 KB

7

I would probably try to do this as a loop with where as much of the query as possible is hardcoded (inserting one record at a time if needed)