FIX: Long Numeric Values Lost When Returning Data from SQL

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.

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')

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

Last Reviewed: October 20, 1997