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>
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.