Closed Coder3333 closed 4 months ago
hi, thank you for submitting this issue - we'll take a look
As a reproduction attempt, did the following
CREATE OR REPLACE PROCEDURE test(input INT)
RETURNS FLOAT
LANGUAGE SQL
AS
DECLARE
result INT;
BEGIN
SELECT ID into :result FROM net_gh_860 WHERE ID >= :input;
return result;
END;
then
using System;
using System.Data;
using System.Data.Common;
using Snowflake.Data.Client;
namespace SnowflakeTestProgram
{
class Program
{
private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
private static DataTable dbdataTable;
static void Main(string[] args)
{
try
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = "account=myaccount.eu-central-1; user=admin; password=password; DB=TEST_DB; SCHEMA=DOTNET; warehouse=COMPUTE_WH";
conn.Open();
Console.WriteLine("Connection successful!");
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CALL TEST_DB.PUBLIC.TEST(:1);";
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = 2;
p1.DbType = DbType.Int32;
cmd.Parameters.Add(p1);
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
}
}
catch (DbException exc)
{
Console.WriteLine("Error Message: {0}", exc.Message);
}
}
}
}
properly returns 2
which is the only row in the underlying test table.
Perhaps you're trying to do something entirely different here. If so, could you please provide a detailed reproduction or a runnable script which leads to the issue ? I would like to see if it's a limitation in the Snowflake .NET driver, or the backend itself.
Thank you in advance !
@sfc-gh-dszmolka , modify your test in this way.
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"DECLARE
result INT;
BEGIN
SELECT ID into :result FROM net_gh_860 WHERE ID >= :input;
return result;
END;";
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = 2;
p1.DbType = DbType.Int32;
cmd.Parameters.Add(p1);
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
Begin/End blocks run just fine as long as there are no parameters. How can I get this to run with a parameter? I am trying to avoid writing a full out procedure that has to be deployed.
okay, got it - thank you! Got to this point:
cmd.CommandText = @"
DECLARE
INPUT INT;
RESULT INT;
BEGIN
SELECT ID into :RESULT FROM net_gh_860 WHERE ID >= :INPUT;
return :RESULT;
END;
";
var p1 = cmd.CreateParameter();
p1.ParameterName = "INPUT";
p1.Value = 1;
p1.DbType = DbType.Int32;
cmd.Parameters.Add(p1);
IDataReader reader = cmd.ExecuteReader();
Which does not error out, but does not return anything either. On the Snowflake side, the query is translated into
SELECT ID FROM net_gh_860 WHERE ID >= :INPUT::NUMBER(38,0);
Parameter doesn't seem to be passed. Similarly, this does not even seem to be possible with another Snowflake driver, the Python Connector. Also returns empty result.
You'll need to either create it as a procedure after all, or if thats possible, perhaps dynamically generate the whole anonymous function (+its parameter values) during runtime.
@sfc-gh-dszmolka , right now I am just building the query dynamically as a work around, but I wish there was a way to ensure I am not susceptible to sql injection, which parameters would provide.
Good to know that at least you have a workaround here ! I'm now closing this issue since it is not related to the .NET driver as it looks like now but looks like a backend limitation.
I am using the .Net client to execute Snowflake queries. This works fine when I only try to execute a single sql statement with a parameter.
If I remove the parameter and move the query into a begin/end block, that works, too.
However, if I use a begin/end block and keep the parameter, I get an error about the parameter being undefined.
Should I expect this scenario to work with a parameter in a begin/end block, or is there some reason why this should not work?