QueryAsync not passing parameters in OdbcConnection and Sybase (SQL Anywhere)

[Schoof-T]

Hi

I'm trying to execute a procedure to a Sybase database with the following code:

        DynamicParameters parameters = new();
        parameters.Add("TESTPARAM", userId, DbType.Int64, ParameterDirection.Input);

        using (var db = new OdbcConnection(_configuration["ConnectionString"]))
        {
            result.AddRange(await db.QueryAsync<T>("TESTPROCEDURE", parameters, commandType: CommandType.StoredProcedure));
        }

When I check the procedure in the dabase, I can see that the parameter is empty.

When executing as the following, the parameters are send correctly.

        DynamicParameters parameters = new();
        parameters.Add("TESTPARAM", userId, DbType.Int64, ParameterDirection.Input);
        var query = "EXEC TESTPROCEDURE @TESTPARAM=?"

        using (var db = new OdbcConnection(_configuration["ConnectionString"]))
        {
            result.AddRange(await db.QueryAsync<T>(query, parameters));
        }

I also can't seem to work with named Parameters, I need to use the ? and add the parameters in the correct position.

Anything I'm doing wrong here? :)

Thank you

[mgravell]

On the topic of ? vs named forcing DybamcParameters - you could try what Dapper calls pseudo-positional parameters: if you use ?foo? in a command, Dapper will replace the token with ? but send the value from foo. I am not sure why the StoredProcedure usage didn't work, though - I have to assume it is something inside the specific provider. What would I need to try to repro this? Assume I know nothing about Sybase.

[Schoof-T]

Thank you for the reply! Really appreciate it. I'll try to write a short guide on getting you started. But to start you will need the software (SQL Anywhere 17), which can be gotten at: https://www.sap.com/products/technology-platform/sql-anywhere.html

I'll get back to you with a more detailed explanation.

[Schoof-T]

1. Download and install SQL Anywhere 17 (install as a developer for a free license)
2. I have prepared a small database with one procedure "TEST_PROCEDURE", we need to add that database to your SQL Anywhere 17 installation: Test.zip

• Right click on SQL Anywhere 17
• Choose New
• Choose Service
  
• Name it 'Test'
• Choose 'Personal Database Server'
• Continue until it asks for parameters, fill in the following: (replace the first string with the location of the database)
  D:\DATABASES\Test\Test.db -n Test -c 20M -ch 50M -x tcpip(serverport=51299)
  
• Continue until the last screen, check the 'Start the Service Now' checkbox
• Click finish

3. Call the procedure from C# using Dapper and an OdbcConnection

        DynamicParameters parameters = new();
        parameters.Add("TEST_PARAM", "TEST_INPUT", DbType.String, ParameterDirection.Input);

        using (var db = new OdbcConnection("Driver={SQL Anywhere 17};uid=test;pwd=test;servername=test;integrated=NO;host=localhost:51299"))
        {
            result.AddRange(await db.QueryAsync<T>("TEST_PROCEDURE", parameters, commandType: CommandType.StoredProcedure));
        }

The procedure should return the same value as given int he "TEST_PARAM".

I hope this is clear. :)