Using named parameter only when passing integer

Question

Currently I'm trying to work with Named Parameters using SAP Sybase SQL Anywhere 12 with dapper. The following codes runs correctly:

public class Test
{
    public int Str1
    {
        get;
        set;
    }

    public string Str2
    {
        get;
        set;
    }
}

class Program
{
    static void Main(string[] args)
    {
        using (SAConnection connection = new SAConnection("..."))
        {
            connection.Open();

            Test test = connection.Query<Test>("SELECT :Str1 as Str1, :Str2 as Str2",
                new Test() { Str1 = 35, Str2 = "42" }).FirstOrDefault();                    

            Console.WriteLine($"Str1: {test.Str1} | Str2: {test.Str2}");
            Console.ReadLine();
        }
    }
}

But when i change Str2 = "42" to some string, than i get the following exception:

Cast 42a to integer not possible

This exception is thrown when I'm using the following code:

Test test = connection.Query<Test>("SELECT :Str1 as Str1, :Str2 as Str2",
new Test() { Str1 = 35, Str2 = "42a" }).FirstOrDefault();

Is there some known issue? This should work correctly, cause i just want to pass a string around.

Edit

Stack trace:

iAnywhere.Data.SQLAnywhere.SAException (0x80004005): Umwandeln von '42a' auf integer nicht möglich bei iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) bei iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior) bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) bei Dapper.SqlMapper.d__611.MoveNext() bei System.Collections.Generic.List1..ctor(IEnumerable1 collection) bei System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) bei Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) bei DapperSqlAnywhere.Program.Main(String[] args) in C:\Users....\DapperSqlAnywhere\Program.cs:Zeile 35.

Thanks a lot!

@Draken it is always a string/varchar. I'll post the complete stack trace as soon as I'm at home — BendEg, Jun 10 '16 at 15:28
@DocBrown no, thats just for providing a simple example. This query is valid and will returne exactly one row with two columns. For example: `SELECT 123 as Str1, 'hello' as Str2` Will return one row with (`123 | hello`) — BendEg, Jun 13 '16 at 06:12
Is the effect the same if you change the order i.e. `SELECT :Str2 as Str2, :Str1 as Str1`? Did you try to use CAST/CONVERT i.e.: `SELECT :Str1 as Str1, CAST(:Str2 AS Varchar(100)) as Str2`? I don't know SQLAnywhere so the syntax of my query might be wrong. I suspect that SQLAnywhere driver cannot infer data types of parameters if they are not explicitly specified. — Michał Komorowski, Jun 13 '16 at 12:10
Hey, thank you for your answer.Changing the order does not help. Casting as `varchar` works, but this should not be the solution, cause this will make even small queries with man parameters very large. And using `?` as placeholder also works without casting. — BendEg, Jun 13 '16 at 12:14
@MichałKomorowski you maybe gave me a good hint. I thing i got it. I Will create an answer soon. — BendEg, Jun 13 '16 at 13:00

score 0 · Answer 1 · answered Jun 17 '16 at 11:19

0

I don't know a great deal about SQLAnywhere but in your 1st select statement is being parsed as

SELECT 35 as Str1, 42 as Str2

which is fine because they're both integers Your 2nd statement

SELECT 35 as Str1, 42a as Str2

should probably be

SELECT 35 as Str1, '42a' as Str2

So I'd try changing the code to

Test test = connection.Query<Test>("SELECT :Str1 as Str1, ':Str2' as Str2",
            new Test() { Str1 = 35, Str2 = "42" }).FirstOrDefault();

answered Jun 17 '16 at 11:19

JonnyCab

104
7

That's not working, cause than ':Str2' will be the output. And not its value. – BendEg Jun 17 '16 at 12:09
Unfortunately, I cannot replicate your issue because I do not have the SAConnection class but the point is that it's most likely your select statement that's failing because SQL does not recognise the value as a string without the single quotes. You need to find a way of parsing the statement with the single quotes. – JonnyCab Jun 17 '16 at 12:33

Using named parameter only when passing integer

1 Answers1