Passing null values with odbc variables causing troubles

[mollwe]

I get an exception from sybase anywhere when trying to execute a sql script with two parameters and one of them is null.

[Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables

ODBC doesn't support named parameters it only uses order of parameters and in sql it replaces ? in that order. Feels like dapper isn't passing parameters that is null.

Tried DBNull.Value instead with no luck. Also trired DynamicParameters.RemoveUnused = false.

var parameters = new DynamicParameters();

parameters.RemoveUnused = false;

parameters.Add("a", since ?? DateTime.MinValue, System.Data.DbType.DateTime, System.Data.ParameterDirection.Input, null);
parameters.Add("b", customerCode ?? "", System.Data.DbType.String, System.Data.ParameterDirection.Input, null);

using (var reader = await connection.QueryMultipleAsync(sql, parameters))
{
    var customers = await reader.ReadAsync<Customer>();
    ...

DECLARE @since DATETIME, @customerCode nvarchar(10)
SET @since = ? -- ODBC parameter
SET @customerCode = ? -- ODBC parameter
...

[mgravell]

I can't repro the problem (will reference from test rig in a moment), but: have you tried using pseudo-positional parameters instead? (a dapper feature). Basically: instead of ? you use ?name?, where name maps to the name like a or b in your example, or in a new {a = 123, b = "abc"} object?

Can you provide a full repro that illustrates the problem, ideally including mention of the specific provider you are using? And: please do try with the ?name? trick.

[mollwe]

I'm using SQL Anywhere 12. It seems that the error isn't about null values but about having more than 1 parameter. The ?name? trick did no difference.

In the first test below I try setting variables in a single select which gives me an error stating: System.Data.Odbc.OdbcException: ERROR [08S01] [Sybase][ODBC Driver][SQL Anywhere]Communication error

The second test uses multiple sets but then I get: System.Data.Odbc.OdbcException: ERROR [07002] [Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables

Error description: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/saerrors/errm188.html

Read about host variables only being available in one statement which explains why my latter test is not working:

Host variable references are permitted within batches with the following restrictions:

• only one statement in the batch can refer to host variables
• the statement which uses host variables cannot be preceded by a statement which returns a result set

http://sqlanywhere-forum.sap.com/questions/21948/sql-anywhere-error-188-not-enough-values-for-host-variables

Tests:

string connectionString = @"DRIVER={SQL Anywhere 12};SERVERNAME=****;COMMLINKS=ShMem,tcpip{HOST=****};DBN=****;DBF=****;UID=****;PWD=****";

[TestMethod]
public void MyTestMethod()
{
    using (var connection = new OdbcConnection(connectionString))
    {
        connection.Open();

        DateTime? p1 = DateTime.Today;
        string p2 = "TEST";

        var result = connection.Query("declare @p1 datetime, @p2 nvarchar(100)\r\nselect @p1 = ?, @p2 = ?\r\nselect @p1 a1, @p2 a2", new { p1, p2 }).First();

        Assert.AreEqual(p1, result.a1);
        Assert.AreEqual(p2, result.a2);
    }
}

[TestMethod]
public async Task MyTestMethod2()
{
    using (var connection = new OdbcConnection(connectionString))
    {
        await connection.OpenAsync();

        DateTime? p1 = DateTime.Today;
        string p2 = "TEST";

        var result = connection.Query("declare @p1 datetime, @p2 nvarchar(100)\r\nset @p1 = ?\r\nset @p2 = ?\r\nselect @p1 a1, @p2 a2", new { p1, p2 }).First();

        Assert.AreEqual(p1, result.a1);
        Assert.AreEqual(p2, result.a2);
    }
}

[mollwe]

I just did som additional tests without dapper.
The had the same errors, so it's not dappers fault.

[TestMethod]
public void MyTestMethod3()
{
    using (var connection = new OdbcConnection(connectionString))
    {
        connection.Open();

        DateTime? p1 = DateTime.Today;
        string p2 = "TEST";

        DateTime a1;
        string a2;

        using(var command = connection.CreateCommand())
        {
            var param1 = command.CreateParameter();
            var param2 = command.CreateParameter();

            param1.Value = p1;
            param2.Value = p2;

            command.Parameters.Add(param1);
            command.Parameters.Add(param2);

            command.CommandText = "declare @p1 datetime, @p2 nvarchar(100)\r\nselect @p1 = ?, @p2 = ?\r\nselect @p1 a1, @p2 a2";
            command.CommandType = System.Data.CommandType.Text;

            using (var reader = command.ExecuteReader())
            {
                reader.Read();

                a1 = reader.GetDateTime(0);
                a2 = reader.GetString(1);
            }
        }

        Assert.AreEqual(p1, a1);
        Assert.AreEqual(p2, a2);
    }
}

[TestMethod]
public void MyTestMethod4()
{
    using (var connection = new OdbcConnection(connectionString))
    {
        connection.Open();

        DateTime? p1 = DateTime.Today;
        string p2 = "TEST";

        DateTime a1;
        string a2;

        using (var command = connection.CreateCommand())
        {
            var param1 = command.CreateParameter();
            var param2 = command.CreateParameter();

            param1.Value = p1;
            param2.Value = p2;

            command.Parameters.Add(param1);
            command.Parameters.Add(param2);

            command.CommandText = "declare @p1 datetime, @p2 nvarchar(100)\r\nset @p1 = ?\r\nset @p2 = ?\r\nselect @p1 a1, @p2 a2";
            command.CommandType = System.Data.CommandType.Text;

            using (var reader = command.ExecuteReader())
            {
                reader.Read();

                a1 = reader.GetDateTime(0);
                a2 = reader.GetString(1);
            }
        }

        Assert.AreEqual(p1, a1);
        Assert.AreEqual(p2, a2);
    }
}

[mgravell]

Wow, that seems pretty ... restrictive. If you're happy that dapper isn't to blame, do you want me to close this? or...? Note: in the case of integers and bools, you could probably abuse the literal injection support, via {=id} etc, but: for formatting and injection reasons, we don't support more complex types than that, so it won't help you cheat with strings :(

[mollwe]

One thing I might try is to combine data into a string and then parse it in SQL, far from optimal but it should work. I'm closing this issue as it's not an issue with dapper.