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 11.0.1.2355. It's a BUG, I'm I right?! Here is request log (-zr all option):
asked 06 Jan '10, 11:41 Zote |
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 Glenn Paulley Ok, now I understand what's going on. But you agree with me that someone (sybase/ianywhere or microsoft) should fix it? 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? 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 |
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 Glenn Paulley |
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 Volker Barth 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? 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. 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? Same error. I'll edit question to add this code too. |
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.