How to enable multi-statement queries for snowflake connections?

💬 Queries and Resources
1

I'm trying to run a transaction in snowflake's query. Transactions are by nature are multi-statement. Whenever I run a multi-statement query I'm getting error:

error:"Actual statement count 7 did not match the desired statement count 1."

the query itself is pretty simple

BEGIN TRANSACTION;
SET CUSTOMER_ID = customer_seq.nextval;

INSERT INTO settings_customers (
    CUSTOMER_ID, CUSTOMER_NAME, TENANT_ID, SCHEMA_NAME, VAT_NUMBER, EMAIL, PHONE, CUSTOMER_STATUS, CUSTOMER_TYPE, SYNC_FREQUENCY, REPORT_GRANULARITY, CREATED_DATE, CREATED_BY, UPDATED_DATE
)
VALUES (
  $CUSTOMER_ID,
  {{ customer_name }} ,
  {{ tenant_id }} ,
  {{ schema_name }}, {{ vat_number }}, {{ email }}, {{ phone }}, {{ customer_status }}, {{ customer_type }}, {{ sync_frequency }}, {{ report_granularity }}, CURRENT_TIMESTAMP, {{ created_by }}, CURRENT_TIMESTAMP
);
SELECT $CUSTOMER_ID;
COMMIT;

Snowflake itself allows multi-statement queries. This is retool issue. How to solve it? Can it be configured somewhere? Any workarounds?

3

Hi @Kostiantyn_Kostiuk,

Thanks for reaching out! Unfortunately, it looks like we don't support multi-statement Snowflake queries :disappointed: We are tracking requests for this. It isn't currently being worked on, but I will reach out here if that changes.

For the insert, if you skip the id entirely, it should insert your record with the next value. On success, you could have another query that returns the id of the largest primary key, if it auto-increments. You could also look into creating a stored procedure

4

Hi Tess,

Has there been any progress around multi-statement support for Snowflake?

We're trying to run pretty simple queries that update 2 different tables on one button click. We are currently creating stored procs in Snowflake or having to change together multiple Retool queries which isn't great from a code management prospective.

5

Hi @paul.narup,

Thanks for checking in! I added your feedback to the ticket. Unfortunately, this request is in our backlog, so I don't have a timeline yet