Error when passing negative number as parameter to MSSQL stored procedure
in [Visual Objects]
|
Prev: Error when passing negative number as parameter to MSSQL stored procedure
Next: Qout does not show any terminal window
From: Willie Moore on 13 Apr 2010 08:19 Ervin, This might sound strange but have you tried running your query with the -50000 like this '-50000'? local cValue AS String cValue := "-50000" // minus fiftythousand) oSel:SQLString := "EXEC dbo.spname ?" oSel:Execute(cValue) Regards, Willie "Ervin Steckl" <ervin.steckl(a)gmail.com> wrote in message news:9934b037-6af1-462b-81a7-14b31791dd3d(a)w42g2000yqm.googlegroups.com: > Hi all, > > I have a problem which seems to be serious. I use CAVO 2.0. > When I call a stored procedure which has parameters and one of the > numeric parameters is a negative number, I get the following SQL error > (it was translated from Hungarian so the English version may not be > fully accurate): > > NativeError: 0; SQLState: S1090 > [Microsoft][ODBC Driver Manager] Invalid string or buffer length. > > The direct cause of this is when I pass a number (Longint inside CAVO) > to the stored procedure (INT MSSQL type), the number is negative and > is less than -32767. An SQL INT type can hold far larger numbers, so > does the longint in CAVO. > > The actual code is (oSel is an SQLSelect object): > > local nValue AS Longint > nValue := -50000 // minus fiftythousand) > oSel:SQLString := "EXEC dbo.spname ?" > oSel:Execute(nValue) > > When the number is positive, it can be a high number (greater than > 32768). > > My idea is that the ODBC layer interprets the numeric parameter as a > 16 bit signed int - but only in case of negative numbers - and that > causes the problem. But how can I control this? > > This is how I connect to the DB (which is actually MSSQL Express > 2008): > > oConn := SQLConnection{} > cConnString := "Driver={SQL Server Native Client 10.0}; Server= > MyInstanceName; Database=MyDatabase; Trusted_Connection=yes" > oConn:DriverConnect(NIL, SQL_DRIVER_NOPROMPT, cConnString) > > The connection is successful. > > Any ideas? __________ Information from ESET NOD32 Antivirus, version of virus signature database 5024 (20100413) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Willie Moore on 13 Apr 2010 12:45 Ervin, SQL server will always try to convert the datatype that is passed in to the datatype specified in the stored procedure. I knew that technique would work with ADO but I wasn't sure if it would work with the VO classes. I am glad that you got it working. Regards, Willie __________ Information from ESET NOD32 Antivirus, version of virus signature database 5026 (20100413) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Geoff Schaller on 20 Apr 2010 23:46
Ervin, The reason is simple enough. You aren't packaging the parameter correctly and the VO SQL classes suck bigtime. What you are suffering is dynamic data type conversion so converting from a string is the easiest thing for the sql engine to do. You passed an INT and I suspect the SP was needing a different and very specific data type. Supplying it as a string forces the SQL engine to choose the correct conversion for you. Geoff > It IS actually strange, but the thing is that it works. Very > surprisingly for me. > Thank you Willie for the tip, I've spent several hours researching it > and now I have a workaround. > > Anyway, any idea WHY it works now? |