DOCUMENT:Q258042 27-JUL-2001 [foxpro] TITLE :PRB: Error Occurs When Binary Output Parameters Are Used PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:5.0a,6.0 OPER/SYS: KEYWORDS:kbDatabase kbSQL kbvfp600 KbDBFDBC kbSQLServ700 kbGrpDSFox kbDSupport kbCodeSnippet kbS ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 5.0a, 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== From Visual FoxPro, call a stored procedure that resides in a Microsoft SQL Server database. (This stored procedure has an output parameter of data type binary.) The following error occurs: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: binary is incompatible with float NOTE: This particular error occurs when a numeric output parameter is passed from Visual FoxPro. Other errors similar to this one occur if parameters of any other Visual FoxPro data type are passed to the SQL Server stored procedure. CAUSE ===== The SQL Server ODBC driver forces the value that was passed as a numeric output parameter to have a float data type before the stored procedure is actually called, as shown by a SQL Server Profiler trace: declare @P2 float set @P2=0. exec fox_getnewid 'Table2', @P2 output select @P2 WORKAROUND ========== Work around this limitation by passing a stored procedure to the SQL Server to handle the binary data type incompatibility. Use the following example as a basis for the workaround: 1. In a SQL Server database, create a table called "RSys_Last_Id" (without the quotation marks) and a stored procedure called "rsp_getnewid" (without the quotation marks). These can be created by running the following code in a script in the Query Analyzer: create table RSys_Last_Id ( Last_Id_Value binary(8), Table_Name varchar(30) ) go insert into RSys_Last_Id ( Last_Id_Value, Table_Name ) values ( 0, 'table2' ) go CREATE PROCEDURE rsp_getnewid @tableName varchar(30), @lastIdValue binary(8) OUTPUT AS BEGIN TRANSACTION UPDATE RSys_Last_Id SET Last_Id_Value = CONVERT(binary(4), LEFT(Last_Id_Value, 4)) + CONVERT(binary(4), (CONVERT(binary(4), RIGHT(Last_Id_Value, 4)) + 1)) WHERE Table_Name = @tableName SELECT @lastIdValue = Last_Id_Value FROM RSys_Last_Id WHERE Table_Name = @tableName COMMIT TRANSACTION RETURN 2. Place the following code into a Visual FoxPro program: set step on local xRet && The output parameter that corresponds to the binary && output parameter in the SQL Server 7.0 stored && procedure. xRet = '0' && Note that the value of this string MUST be an integer in order for the conversion to work properly. nH = sqlconnect( 'MyDSN','sa','') #define CRLF chr(13)+ chr(10) * * Utilize SQL Pass-Through technology to create a SQL Server 7.0 stored * procedure on-the-fly (fox_getnewid) for handling the binary data type * incompatibility. * local lcStoredProc lcStoredProc = ; [create procedure fox_getnewid @tableName varchar(30), @newIdValue char(8) OUTPUT ] + CRLF + ; [as ] + CRLF + ; [ begin ] + CRLF + ; [ declare @xBinConv binary(8) ] + CRLF + ; [ select @xBinConv = convert(binary(8),convert(int,@newIdValue)) ] + CRLF + ; [ exec rsp_getnewid @tablename, @lastIDValue = @xBinConv OUTPUT] + CRLF + ; [ select @newIdValue = convert(char(8), convert(int, @xBinConv)) ] + CRLF + ; [ end] =sqlexec( nH, lcStoredProc) =sqlexec( nH, "{Call fox_getnewid ('Table2',?@xRet )}") * * fox_getnewid converts a string back and forth as follows: * 1. Character string sent from Visual FoxPro program to fox_getnewid. * 2. fox_getnewid converts character string to int and then to binary(8), * and then passes it to the original SQL Server stored procedure * (rsp_getnewid). * 3. Binary return value passed back to fox_getnewid. * 4. fox_getnewid converts the binary string to int and then to char(8) and * returns it to the Visual FoxPro program. * =sqlexec( nH, "drop procedure fox_getnewid") = sqldisconnect( nH) 3. Change the data source name, password, and so forth, to what you normally use to connect to your SQL Server. 4. Save the Visual FoxPro program and execute it. Step through the code and observe the value of the output parameter, xRet. 5. Execute the program again. Step through the code and observe the value of the output parameter, xRet. Note that the binary value (represented as a numeric in Visual FoxPro) has incremented. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- 1. If you haven't already done so, do step 1 of the "Workaround" section. 2. Place the following code into a Visual FoxPro program, change the data source name, password, and so forth, to what you normally use to connect to your SQL Server, and execute it: nH = SQLConnect( "MyDSN","sa","") =sqlsetprop(nH,'DispWarnings',.T.) xRet = 0 ?sqlexe( nH, "{Call rsp_getnewid ('MyTable', ?@xRet)}") Additional query words: ====================================================================== Keywords : kbDatabase kbSQL kbvfp600 KbDBFDBC kbSQLServ700 kbGrpDSFox kbDSupport kbCodeSnippet kbSQLProg Technology : kbVFPsearch kbAudDeveloper kbVFP600 kbVFP500a Version : WINDOWS:5.0a,6.0 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.