PRB: Selecting BIT Field in SQL Server Returns All True Values

2.60    | 2.60a

The information in this article applies to:

• Microsoft FoxPro for Windows, version 2.6
• Microsoft FoxPro for Macintosh, version 2.6a

SYMPTOMS

When you are using a SELECT command to select a field of type BIT from a SQL Server database, the resulting cursor file contains all TRUE (.T.) values.

RESOLUTION

Change the SELECT command so that it selects the BIT field as a calculated field. See the program below for an example.

MORE INFORMATION

A BIT type field in a SQL Server database is a column that holds the value 1 or 0.

Steps to Reproduce Problem

Run the following program (substituting the correct data source name, user name, and password). Notice that the first Browse window will contain all "T" values (representing True or .T.). The second Browse window will contain the correct numeric values.

   CLOSE DATABASES
   CLEAR

   **Load Library
   IF !"FPSQL"$SET("LIBRARY")
      PRIVATE m.err
      m.err=.F.
      ON ERROR m.err=.T.
      SET LIBRARY TO ;
         LOCFILE("FPSQL","FLL","Where is FPSQL.FLL?") ADDITIVE
      ON ERROR
      IF m.err
         RETURN
      ENDIF
   ENDIF

   **Connect to SQL Server
   WAIT WINDOW "Connecting..." NOWAIT
   * On the following command line, substitute the correct data source
   * name for 'test', the correct user name for 'sa', and the correct
   * password for ''.
   m.handle=DBCONNECT('test','sa','')
   IF m.handle > 0
      WAIT WINDOW "Connected Successfully" NOWAIT
   ELSE
      WAIT WINDOW "No connection made" NOWAIT
      RETURN
   ENDIF

   **Create table with 'bit' type field
   WAIT WINDOW "Creating Table ..." NOWAIT
   m.retval=DBEXEC(m.handle,'CREATE TABLE bitjunk (thebit bit)')
   m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)')
   m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (0)')
   m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)')
   m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (1)')
   m.retval=DBEXEC(m.handle,'INSERT bitjunk VALUES (0)')
   m.retval=dbexec(m.handle,'SELECT * FROM bitjunk','one')
   WAIT WINDOW "Notice all values are True" NOWAIT
   BROWSE
   m.retval=dbexec(m.handle,'SELECT thebit*1 FROM bitjunk','two')
   WAIT WINDOW "Notice values are Correct" NOWAIT
   BROWSE
   m.retval=dbdisconn(m.handle)