SNOW-974746: Cannot use bind parameters with batch/multi statements about snowflake-connector-net HOT 4 CLOSED

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.

from snowflake-connector-net.

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

from snowflake-connector-net.

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

from snowflake-connector-net.

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

from snowflake-connector-net.