DynamicParameters and Snowflake syntax?

[dariusz-wozniak]

Hi, is it possible to use DynamicParameters with the Snowflake SQL?

Example of the Snowflake query using variable:

set id = 2;

SELECT * FROM NATION N
WHERE N.N_NATIONKEY = $id

[mgravell]

There's no specific difference between DynamicParameters and other approaches, so the real question is: does it work with snowflake.

Now, there's two questions there:

1. does the library support $ as a parameter prefix - I believe it does, but I haven't checked
2. does the snowflake ADO.NET provider follow the usual rules and work correctly - I have no idea, I haven't used snowflake

So: turning the question around - have you tried it? What happened?

[dariusz-wozniak]

For given code - when using id with a dollar sign:

using var connection = _connection.Create();

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("$id", key);

var sql = $"SELECT * FROM NATION N WHERE N.N_NATIONKEY = $id";

var nations = await connection.QueryAsync(sql, dynamicParameters);

There's an exception thrown on QueryAsync:

Snowflake.Data.Client.SnowflakeDbException: 'SQL compilation error: error line 1 at position 45
Session variable '$ID' does not exist'

When using at-sign:

dynamicParameters.Add("@id", key);

var sql = $"SELECT * FROM NATION N WHERE N.N_NATIONKEY = @id";

The exception is:

Snowflake.Data.Client.SnowflakeDbException: 'SQL compilation error: syntax error line 1 at position 45 unexpected '@id'.'

[mgravell]

The relevant code to run this should really be something like

using var connection = _connection.Create();
var nations = await connection.QueryAsync<Nation>("SELECT * FROM NATION N WHERE N.N_NATIONKEY = $id", new { id = key});

Changes:

1. no need for DynamicParameters here - the an anoymous type should be fine but it needs to actually be passed to Query
2. using Query<T> rather than the dynamic version

However: I've checked, and currently we support ?, @ and : as parameter prefixes. It would be pretty trivial to add $ there are 3 lines that contain [?@:] that would need updating; maybe try tweaking that locally with the Dapper code, as I don't have snowflake to hand to test.

[dariusz-wozniak]

Thank you for the response, @mgravell.

When using the following code:

var sql = "SELECT * FROM NATION N WHERE N.N_NATIONKEY = $id";

var nations = await connection.QueryAsync<Nation>(sql, new { id = key });

...I'm receiving the exception:

Snowflake.Data.Client.SnowflakeDbException: 'SQL compilation error: error line 1 at position 45
Session variable '$ID' does not exist'

When I modified Dapper and replaced all occurences of the [?@:] by [?@:$] in the SqlMapper.cs, then I have the same exception as the above one.

On:

but it needs to actually be passed to Query

That is indeed true! :) Was wondering if not passing dynamicParameters produces the same results and pasted the code with not-passing-dynamicParameters-version by a mistake.

And, as a result of experimentation - passing dynamicParameters to the Query yields the same results, i.e. the same exceptions. (Edited the code in the above post.)

[mgravell]

I don't have snowflake setup here. What is involved in doing that? I can try debugging it to see what is happening, but I'd need a working db

…

On Fri, 23 Jul 2021 at 13:27, Dariusz Woźniak ***@***.***> wrote: Thank you for the response, @mgravell <https://github.com/mgravell>. When using the following code: var sql = "SELECT * FROM NATION N WHERE N.N_NATIONKEY = $id"; var nations = await connection.QueryAsync<Nation>(sql, new { id = key }); ...I'm receiving the exception: Snowflake.Data.Client.SnowflakeDbException: 'SQL compilation error: error line 1 at position 45 Session variable '$ID' does not exist' When I modified Dapper and replaced all occurences of the [?@:] by [?@:$] in the SqlMapper.cs, then I have the same exception as the above one. On: *but* it needs to actually be passed to Query That is indeed true! :) Was wondering if not passing dynamicParameters produces the same results and pasted the code with *not-passing-dynamicParameters-version* by a mistake. And, as a result of experimentation - passing dynamicParameters to the Query yields the same results, i.e. the same exceptions. (Edited the code in the above post.) — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1687 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAEHMEJHDSQ2FHJ4LATWD3TZFNZRANCNFSM5AX6NWUA> .

-- Regards, Marc

[mgravell]

Right; got to the bottom of it; the explanation here is that $id is not a parameter - it is a global session variable.

Parameters are expressed using positional bind variables which is ... a pain. Fortunately, Dapper has the notion of "pseudo-positional" parameters which can help make this easier, but: it would need a minor Dapper tweak to fixup a nuance around naming required by snowflake. The following works with the above PR:

[Fact]
public void ParameterizedQuery()
{
    using var connection = GetConnection();
    const int region = 1;
    var nations = connection.Query<Nation>(@"SELECT * FROM NATION WHERE N_REGIONKEY=?region?", new { region }).AsList();
    Assert.NotEmpty(nations);
    Output.WriteLine($"nations: {nations.Count}");
    foreach (var nation in nations)
    {
        Output.WriteLine($"{nation.N_NATIONKEY}: {nation.N_NAME} (region: {nation.N_REGIONKEY}), {nation.N_COMMENT}");
    }
}

[plaisted]

I built from source and this works great for Snowflake, any plan to create a release?

[mgravell]

Yeah, we should do that. I'll find some time.

…

On Mon, 20 Sep 2021, 21:35 Michael Plaisted, ***@***.***> wrote: I built from source and this works great for Snowflake, any plan to create a release? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1687 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAEHMHFTBC2QJYSIUX6VLLUC6LJ5ANCNFSM5AX6NWUA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[TangoMikeOscar]

Been working with snowflake over a year now, we have our own solution for getting round this problem. Very looking forward to getting official named parameter support, please release as soon as you can, thanks!

[meshalbafian]

Is there any new update with this issue? I downloaded the newer version from AppGet (2.0.120) but still get the same exception when trying to insert into a table in snowflake with dynamic parameter binding (with $)

SQL compilation error: error line 1 at position 29
Session variable '$NAME' does not exist

[ChristophMalassa]

We are also waiting for this and just checked out the pre-release which unfortunately also didn't include this fix. We are now looking for alternative solutions but would much rather appreciate a rough timeline so that we can maybe just sit this one out. Thank you so much!

[mgravell]

I'll get this merged and deployed next week. AFK this week.

…

On Fri, 29 Oct 2021, 08:07 ChristophMalassa, ***@***.***> wrote: We are also waiting for this and just checked out the pre-release which unfortunately also didn't include this fix. We are now looking for alternative solutions but would much rather appreciate a rough timeline so that we can maybe just sit this one out. Thank you so much! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1687 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAEHMB3ADNTU764XXRDJVLUJJI3TANCNFSM5AX6NWUA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.