Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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>

asked 06 Jan '10, 11:41

Zote's gravatar image

Zote
1.7k364051
accept rate: 43%

edited 06 Jan '10, 18:24

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.

(06 Jan '10, 22:33) Mark Culp

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.

permanent link

answered 06 Jan '10, 23:01

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k577106
accept rate: 43%

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

(07 Jan '10, 10:40) Zote

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?

(07 Jan '10, 12:19) Glenn Paulley

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

(08 Jan '10, 15:38) Volker Barth

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.

permanent link

answered 06 Jan '10, 17:07

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k577106
accept rate: 43%

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

(06 Jan '10, 18:25) Zote

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:)

permanent link

answered 06 Jan '10, 12:05

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

1

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?

(06 Jan '10, 12:09) Volker Barth

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.

(06 Jan '10, 12:40) Zote
1

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?

(06 Jan '10, 13:09) Volker Barth

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

(06 Jan '10, 13:42) Zote
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×143
×43
×5

question asked: 06 Jan '10, 11:41

question was seen: 4,530 times

last updated: 06 Jan '10, 23:01