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 https://github.com/DapperLib/Dapper/issues/1687#issuecomment-885603064, 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 https://github.com/DapperLib/Dapper/issues/1687#issuecomment-923277026, 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 https://github.com/DapperLib/Dapper/issues/1687#issuecomment-954484176, 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.

[TheSythus]

Thank you @mgravell We are very excited about this update to use dapper with Snowflake and solve the parameter issue we have right now.

[ricopetzold]

``@mgravell thanks a lot for the commit to add snowflake functionality. I was updating the latest version this morning. However, my parameter will not be replaced by dapper.

The query as seen in the screenshot is directly processed on the database (without replacing @schema with the value) which of course leads to a database error.

Am I doing something wrong? Thanks for your feedback.

When I try to implement the way you described in one of your previous posts then it also does not work:

Snowflake.Data.Client.SnowflakeDbException: 'SQL compilation error: error line 1 at position 88 Bind variable ? not set.'

SQL is: SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=?schema?

[przemekpobuta]

I found solution, while reading https://medium.com/snowflake/better-net-client-for-snowflake-db-ecb48c48c872 I found info

Snowflake REST API supports two placeholder formats for parameter binding:
Positional — with a ? placeholders
Named — parameter name prefixed with a : char

And occurred that this works as named params 😄 string query = @$"SELECT * FROM TABLE WHERE COLUMN=:Param"; var result = (await conn.QueryAsync<Result>(query, new { Param = "1" })).ToList();

[plaisted]

@ricopetzold I believe you need to set SqlMapper.Settings.UseIncrementalPseudoPositionalParameterNames = true; for the functionality to work with Snowflake.