Microsoft KB Archive/119425

{| = FIX: Long Numeric Values Lost When Returning Data from SQL =
 * width="100%"|

ID: Q119425

2.5x 2.60 MS-DOS kbprg kbfixlist kbbuglist

The information in this article applies to:


 * Microsoft FoxPro for MS-DOS, versions 2.5x, 2.6
 * Microsoft FoxPro Connectivity Kit, versions 2.5x, 2.6

SYMPTOMS
In FoxPro for MS-DOS, when you are querying a SQL Server database using the FoxPro SQL (FPSQL) function library in the FoxPro Connectivity Kit, null values may be returned for numeric data if the data is stored on the server as float with a high number of decimal places.

CAUSE
The problem is caused by FPSQL.PLB not handling long numbers properly. When you are trying to bring in a number such as -0.07, which is stored in SQL Server as something like -0.07000000000000001, FPSQL cannot handle this properly and instead of returning the number, it returns a blank field.

STATUS
This problem has been confirmed in the Microsoft products listed at the beginning of this article. This problem was corrected in version 2.6a of the Microsoft FoxPro Connectivity Kit, which is part of Microsoft FoxPro version 2.6a for MS-DOS Professional Edition.

Steps to Reproduce the Problem
1. Create or use a table on a SQL Server machine that has a float field. If

the table has already been created, use the following commands from FoxPro to load FPSQL.PLB and connect to the database

SET LIBRARY TO LOCFILE("fpsql.plb") handle = DBCONNECT(, , )

where  is the name of the SQL Server,  is the user identifier, and  is the corresponding password. All of  these should be character expressions. There is a fourth parameter on  the DBCONNECT command for a connection string, but it is not required. 2. From FoxPro, insert a new record in the SQL Server table. For example, if there was a table called "Table1" with two fields called "Field1" and "Field2" that were a character and a float respectively, the command would be:

=DBEXEC(handle,'INSERT INTO Table1 VALUES("One",-0.07)') 3. Send a SQL command to the server from FoxPro: =DBEXEC(handle,'SELECT * FROM Table1') Browsing the resulting cursor will show a empty float field for the record that was just inserted. The character field will have the correct data. No error will be returned. This problem occurs with -0.07 because of the way SQL Server stores this number. You can also reproduce this problem by using this code with the field and table names mentioned above:

=DBEXEC(handle,'INSERT INTO Table1 VALUES("TWO",-0.05)') =DBEXEC(handle,"SELECT * FROM Table1") BROWSE * The record will show up and the value in the * float field will show -0.05 =DBEXEC(handle,'UPDATE Table1 SET Field2 = Field2 - 1 ;     WHERE Field1 = "TWO"') =DBEXEC(handle,'UPDATE Table1 SET Field2 = Field2 + 1 ;     WHERE Field1 = "TWO"') =DBEXEC(handle,"SELECT * FROM Table1") BROWSE Once again, the numeric field will be empty. Although the number should be the same by adding and subtracting 1, the UPDATE query will actually make a slight change to the value on the server. NOTE: If you have problems running the code in FoxPro for MS-DOS, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q112482 TITLE    : How to Make an MS-DOS Level SQL Server Connection Additional reference words: FoxDos buglist2.50 buglist2.50a buglist2.50b buglist2.60 fixlist2.60a 2.50 2.50a 2.50b 2.60 ck KBCategory: kbprg kbfixlist kbbuglist KBSubcategory: Solution Type : kbfix
 * }