Error when passing negative number as parameter to MSSQL stored procedure
in [Visual Objects]
|
Prev: Advice on multi-threading
Next: Error when passing negative number as parameter to MSSQL stored procedure
From: Ervin Steckl on 13 Apr 2010 08:03 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?
From: Ervin Steckl on 13 Apr 2010 12:21
On ápr. 13, 14:19, "Willie Moore" <will...(a)wmconsulting.com> wrote: > 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.ste...(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 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? |