BUG In SA 11 .NET Driver (2)

[Former Member]

As suggested me Breck, I'm starting this thread for this question.

That's my code:

using System;
using System.Data.Common;

namespace SA11BUG
{
    class Program
    {
        static void MainUsingFactories()
        {
            var factory = DbProviderFactories.GetFactory("iAnywhere.Data.SQLAnywhere");

using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = "dsn=SQL Anywhere 11";
                connection.Open();

using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select ? + ? from DUMMY";
                    command.Parameters.Add(factory.CreateParameter());
                    command.Parameters.Add(factory.CreateParameter());

command.Parameters[0].Value = "SQL";
                    command.Parameters[1].Value = "Anywhere";

#region This code is optional with no behavior change
                    command.Parameters[0].DbType = System.Data.DbType.String;
                    command.Parameters[1].DbType = System.Data.DbType.String;
                    #endregion

var result = (string)command.ExecuteScalar();

Console.WriteLine(result);
                    Console.ReadLine();
                }
            }
        }

static void MainUsingSAClasses()
        {
            using (var connection = new SAConnection("SQL Anywhere 11"))
            {
                connection.Open();

using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select ? + ? from DUMMY";
                    command.Parameters.Add(new SAParameter {Value = "SQL"});
                    command.Parameters.Add(new SAParameter { Value = "Anywhere" });

#region This code is "optional" with no behavior change
                    command.Parameters[0].DbType = System.Data.DbType.String;
                    command.Parameters[1].DbType = System.Data.DbType.String;
                    #endregion

var result = (string)command.ExecuteScalar();

Console.WriteLine(result);
                    Console.ReadLine();
                }
            }
        }
    }
}

And this is the exception:

iAnywhere.Data.SQLAnywhere.SAException was unhandled
  Message="Cannot convert 'SQLAnywhere' to a int"
  Source="SQL Anywhere .NET Data Provider"
  ErrorCode=-2147467259
  NativeError=-157
  StackTrace:
       at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
       at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteScalar()
       at SA11BUG.Program.Main() in C:\\Users\\zote\\Documents\\Visual Studio 2008\\Projects\\SA11BUG\\Program.cs:line 26
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

I'm using C# 3.5 and SA 11.0.1.2355. It's a BUG, I'm I right?!

Here is request log (-zr all option):

0106 162140.600,<,1,CONNECT
=,C,1,UID=bludata
+4,>,1,CONNECT,1
+1,<,1,PREPARE,select db_property('charset'), connection_property('charset'), @@VERSION, connection_property( 'max_statement_count' )
=,>,1,PREPARE,65536
=,<,1,EXEC,65536
+1,P,1,[S]DUMMY<seq>
=,>,1,EXEC
=,<,1,DROP_STMT,65536
=,>,1,DROP_STMT
=,<,1,STATUS_CHANGE_CHAR_CHARSET
=,>,1
=,<,1,STATUS_CHANGE_NCHAR_CHARSET
=,>,1
+7,<,1,EXEC_IMM,SELECT 1
=,P,1,[S]DUMMY<seq>
=,>.,1
+8,<,1,PREPARE,SELECT CURRENT DATABASE, @@version
=,>,1,PREPARE,65537
=,<,1,DESC_OUT,65537
+1,>,1,DESC_OUT
=,<,1,DESC_IN,65537
=,>,1,DESC_IN
+2,<,1,OPEN,65537
=,P,1,[S]DUMMY<seq>
=,>,1,OPEN,65538
+10,<,1,DESCRIPTOR,65538
=,>,1
=,<,1,FETCH,65538,1,Rel,1
=,>,1
=,<,1,PREFETCH,65538
+15,W,1,100,Row not found
=,>,1
+5,<,1,USE_PREFETCH,65538
=,>,1
=,<,1,CLOSE_BY_NAME
=,P,1,[S]DUMMY<seq>
=,>,1
=,<,1,COMMIT
=,>.,1
+1,<,1,DROP_STMT,65537
=,>,1,DROP_STMT
=,<,1,PREPARE,SET TEMPORARY OPTION QUOTED_IDENTIFIER = ON
=,>,1,PREPARE,65539
=,<,1,DESC_OUT,65539
=,>,1,DESC_OUT
=,<,1,DESC_IN,65539
=,>,1,DESC_IN
=,<,1,EXEC,65539
=,>,1,EXEC
=,<,1,COMMIT
=,>.,1
+39,<,1,DROP_STMT,65539
=,>,1,DROP_STMT
=,<,1,PREPARE,select ? + ? from DUMMY
=,>,1,PREPARE,65540
=,<,1,DESC_OUT,65540
=,>,1,DESC_OUT
=,<,1,DESC_IN,65540
=,>,1,DESC_IN
+7,<,1,OPEN,65540
=,H,1,0,nvarchar,'SQL'  
=,H,1,1,nvarchar,'Anywhere'  
=,P,1,[S]DUMMY<seq>
=,>,1,OPEN,65541
=,<,1,DESCRIPTOR,65541
=,>,1
=,<,1,FETCH,65541,1,Rel,1
=,E,1,-157,Cannot convert 'SQLAnywhere' to a int
=,>,1
+10005,P,1,[S]DUMMY<seq>

[MarkCulp]

Lookng at the RLL, the problem is precisely what Glenn has indicated: the statement is prepared and described before the host variables are bound and therefore at describe time the server does not know the types of the parameters and therefore assumes them to be INT. As such the result is computed to be an INT.

[Former Member]

OK, now I understand what's going on.

On behalf of your application, ADO.NET is doing a PREPARE-DESCRIBE-OPEN sequence. On the PREPARE call, the server sees only

SELECT ? + ? FROM DUMMY

with no bound host variables, and consequently assumes the ? represent smallints and therefore describes the result as a smallint as well.

At OPEN time, a new statment is constructed by the server, and with the bound hostvars the server (correctly) builds a concatenation function in the query's projection list, rather than numeric add. This is executed successfully; however, ADO.NET has bound the OUTPUT of the SELECT to an integer - based on the prior DESCRIBE - and it is the conversion of the correctly concatenated string ("SQLAnywhere") to an integer that results in the -157.

This is a potential problem for any overloaded builtin function or operator; there is no guarantee after an OPEN call that hostvars are not re-bound to a different (even incompatible) type. In this particular instance, ADO.NET could detect the correct type of the output expression from the SELECT by re-issuing a DESCRIBE call after the cursor has been OPENed - but doing so would rarely be necessary in practice, and is also expensive (another round-trip to the server).

So to me the software is behaving properly. You can work around the intrinsic problems of overloading by explicitly using a CAST in your SELECT, ie

SELECT CAST( (? + ?) AS NVARCHAR) FROM DUMMY

or use the concatenation operator (||), as Volker suggested, which isn't overloaded for other data types.

[Former Member]

Ok, now I understand what's going on. But you agree with me that someone (sybase/ianywhere or microsoft) should fix it?

[Former Member]

As far as I can determine all of these software components are working as designed, so there is nothing to "fix".

Are you aware of another ADO.NET provider that behaves differently?

[VolkerBarth]

It's not my problem, and I don't use ADO.NET yet but I would imagine that the PREPARE would respect the type of the parameters. IIUC, the PREPARE is made during the "command.ExecuteScalar();" call. Isn't it clear at that time that both params are strings? - (Sidenote: I have never ever had do wonder about PREPARE-DESCRIBE-OPEN-sequences and their outcome with SA. And I don't miss that:)) the

[Former Member]

A request-level log (specify SQL + HOSTVAR) would help to diagnose where the problem lies.

Without any host variable bindings, the statement

SELECT ? + ? FROM DUMMY

will be DESCRIBED to return a short int, as the server assumes that the two hostvars are short ints and therefore '+' is numeric add.

However, at OPEN time hostvar types and their values are known to the server, which enables the server to properly choose the particular overloading required.

A request-level log will verify what precisely is being sent to the server.

[Former Member]

Hi Glenn, please check question again. Request log is there now.

[VolkerBarth]

Zote, obviously there is a problem with parameter types here.

What happens when you use

select ? || ? from DUMMY

? What is the type of command.Parameters[n]?

I agree that the following statement that you are generating should work:

select 'SQL' + 'Anywhere'

Just my thoughts:)

[VolkerBarth]

Well, that's the disadvantage of a new question: I have just seen that my first question is already answered in your other question - i.e. it does work with the || operator. So it might be related to the parameters type?

[Former Member]

Volker, I changed parameters type to DbType.String, and I got same error. If you see error message, I can see that dbserver had concatenated parameters. I can't understand why something is trying to cast it as int.

[VolkerBarth]

Seems like SA expects the statement to return an int - and there's no way to specify the command's return type (and I would not know why one should have to). I'm no .Net Coder, so I just can guess - What happens when you use the SAxxx classes instead of the generic ones?

[Former Member]

Same error. I'll edit question to add this code too.