SNOW-974746: Cannot use bind parameters with batch/multi statements

[paulverbeke]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of .NET driver are you using?
   2.1.2.0

2. What operating system and processor architecture are you using?
   W10 64bits

3. What version of .NET framework are you using?
   .net framework 4.8

4. What did you do?
   I constructed a query of 2 statements INSERT INTO.
   INSERT INTO MY_TABLE VALUES (:1,:2,:3,:4,:5);INSERT INTO MY_TABLE VALUES (:6,:7,:8,:9,:10);
   They both have bind parameters that are (all 10 of them, I checked) correctly added to the ParametersCollection.
   Then I added the last parameter "MULTI_STATEMENT_COUNT" with a value of 0 for enabling batch statements.

5. What did you expect to see?

   10 new rows should be inserted correctly but instead at execution I got an error:

SQL compilation error: error line 1 at position 45
Bind variable :1 not set. in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
stackstrace: 
SYSTEM$MULTISTMT line: 10 SqlState: P0000, VendorCode: 100132, QueryId: 01b0751e-0103-1ca4-0000-9ba90328a4be

I can send batch statements INSERT INTO without any problems - OK
I can send one INSERT INTO statement with bind parameters without any problems - OK
But as soon as there is severals statements in a batch AND bind parameters I receive an error - KO

I finally found this in the documentation of Snowflake, small note. "Snowflake does not currently support variable binding in multi-statement SQL requests."
https://docs.snowflake.com/en/developer-guide/sql-api/submitting-multiple-statements

Is this true ? Then why is it not documented in the readme of this connector ?

[sfc-gh-dszmolka]

hi and thank you for submitting this issue! We observed the exactly same behaviour in other Snowflake drivers as well, which are absolutely independent from each other and even use different programming languages. See snowflakedb/snowflake-connector-nodejs#704 for reference

So current suspicion is that this is something more 'central' and related to the Snowflake engine itself, instead of the drivers.

The document you found is probably the most relevant in this case, it is indeed a bit confusing it only seems to address SQL API , yet the behaviour seems to be relevant for everything else, not just the SQL REST API.

We're currently confirming this with the relevant dev team and will work on updating the drivers' individual documentation page, once confirmed. Since for this driver the README is the documentation ,I'm going to update it now.

Until then, please assume that it won't work and the bit documented for SQL API is relevant for all the drivers, which is likely the case.

As a side note, and this is probably an unexpected behaviour: with some other drivers (node.js, Python) the tests show that the qmark-style notation ? for the bind variables work in the same multi-statement queries, where the numeric-style notation :1 doesn't work. Really not sure if this is expected so please don't take dependency on it, but perhaps could be tested as a workaround.

The real workaround should be to not depending on multi-statement queries and issue them one by one, where you need to use the :1 variable binding.

[sfc-gh-dszmolka]

created #819 for the documentation bit. Closing this issue as the current behaviour is very likely expected.

[paulverbeke]

Hi @sfc-gh-dszmolka and thanks for your comprehensive answer.
Yes we tested the python connector and had the same limitations.

I didn't even tried the qmark-style notation ? for the bind variables since I'm building INSERT statements containing 16384 values expressions each, and each containing 24 columns, so that would make for a lot of anonymous variables. Maybe it would have worked 🤷 ?

My feature is finished now but in the end I've done as you suggested: I abandonned multi-statement/batch queries and sent statements one by one with the named bind parameters :1.

Thanks for updating the documentation

[sfc-gh-dszmolka]

hi @paulverbeke thank you for the feedback and the additional informations; glad you're unblocked now!