As suggested me Breck, I'm starting this thread for this question.
That's my code:
And this is the exception:
I'm using C# 3.5 and SA 184.108.40.2065. It's a BUG, I'm I right?!
Here is request log (-zr all option):
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.
answered 06 Jan '10, 23:01
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.
answered 06 Jan '10, 17:07
Zote, obviously there is a problem with parameter types here.
What happens when you use
? What is the type of command.Parameters[n]?
I agree that the following statement that you are generating should work:
Just my thoughts:)
answered 06 Jan '10, 12:05