FIX: Long Numeric Values Lost When Returning Data from SQL

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.

MORE INFORMATION

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(<Data Source Name>, <User>, <Password>)
   

   where <Data Source Name> is the name of the SQL Server, <User> is the user identifier, and <Password> 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')
   

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

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