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

6

Hello!
I was curious if this task made it to the backlog?

That is something we would also like to have as we're using 2 different warehouse (one that's faster but cost more). At the moment, we need to have the users connect to the 2 instances, but it could easily be fixed by using a use warehouse statement before the queries.

Another solution would be to allow the possibility to define the warehouse in every query using the same resource to connect.

7

Hi @Benoit_Bouchard,

Thanks for checking in! I will add your +1 to the internal ticket we're tracking, but, unfortunately, this request hasn't been prioritized out of our backlog :disappointed: