search

fixer-m / snowflake-db-net-client

Snowflake .NET Client
Apache License 2.0
51 stars 14 forks source link

Run multiple sql statements in a transaction? #41

Open Yash-Poddar opened 1 year ago

Yash-Poddar commented 1 year ago

Hi,

I'm unable to use Begin; ... Commit; in ExecuteAsync(). I'm getting the following error - " Snowflake.Client.Model.SnowflakeException : Query execution failed. Message: Multiple SQL statements in a single API call are not supported; use one API call per statement instead."

Is there a workaround or something so that I can use transaction?

fixer-m commented 1 year ago

Hi,

Multiple statements feature was recently added in official connector and it's not supported yet in this project. One of the issues here is that they check DriverVersion field, for this feature it should be equal or greater than "2.0.18". The problem is that this library uses it's own versioning and sends something like "0.4.5.0".

Otherwise I think it would be enough to change MULTI_STATEMENT_COUNT to 0. Something like this:

var result1 = await _snowflakeClient.ExecuteScalarAsync("ALTER SESSION SET MULTI_STATEMENT_COUNT = 0;");
var result2 = await _snowflakeClient.ExecuteScalarAsync<int>("SELECT 1; SELECT 2;");

I'm gonna work on this in next release.