PRB: Specifying Path for Cursor with DBExec() Causes Error

• Microsoft FoxPro Connectivity Kit, version 2.5
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, and 2.5b

SYMPTOMS

After you execute a DBExec() function and specify a path and table name for the result of an SQL SELECT statement, the DBError() function returns an error number of 200 or 295 and an error message of null/nothing.

CAUSE

DBExec() was executed and the DBSetOpt() function was not set to have the results returned to a table; DBSetOpt() was set to return the results to a cursor. Error 200 will be received if the path is set to a local drive and error 295 will be received if it is set to a network drive.

RESOLUTION

Prior to executing the DBExec() function with the SQL statement, execute DBSetOpt(handle,'UseTable',1), where the 1 indicates that the results should be put into a table.

By default, the result from a DBExec() function will be placed in a cursor instead of a table unless it is specifically indicated, using =DBSetOpt(handle,'UseTable',1), that the results should be put into a table.

MORE INFORMATION

The following code reproduces the problem:

   ** Set Library to the ODBC Library
   SET LIBRARY TO SYS(2004)+"FPSQL.FLL"

   ** errval=ERROR NUMBER, errmsg=ERROR MESSAGE
   ** If an error is received then DBERROR()
   ** will be called and the error number will be
   ** stored in errval and the message in errmsg.

   errval=0
   errmsg=' '

   ** Specify source name as seen in ODBC Manager in the Control Panel.
   ** Specify user and password for server.
   sourcename="test"
   user="sa"
   passwd=""

   ** Get a connection handle.
   handle=DBConnect(sourcename,user,passwd)

   IF handle > 0
      WAIT WINDOW "Successfully Connected"
   ELSE
      error=DBError(0,@errmsg,@errval)
      WAIT WINDOW STR(errval)+" "+errmsg
      =DBDisconn(handle)
   ENDIF

   ** Set various options for session handle.
   =DBSetOpt(handle,'Asynchronous',0)  && Turn synchronous on
   =DBSetOpt(handle,'BatchMode',1)     && Return all results at once
   =DBSetOpt(handle,'ConnTimeout',0)   && wait forever if need be
   =DBSetOpt(handle,'Transact',1)      && Turn Auto on
   ** We should have specified here
   **  =DBSetOpt(handle,'UseTable',1)      && Put results into table
   ** so that the results would be put into a table.


   ** Use the PUBS database (a standard database that comes
   ** with SQL Server).
   a=DBExec(handle,"use pubs")
   IF a > 0
      WAIT WINDOW "NOW USING PUBS DATABASE"
   ELSE
      error=DBError(handle,@errmsg,@errval)
      WAIT WINDOW STR(errval)+" "+errmsg
   ENDIF


   ** Perform an SQL SELECT and put the results in C:\TEST.DBF.
   ** The line below will cause the error because we are
   ** specifying a path instead of just a name.
   a=DBExec(handle,"select * from stores","c:\test.dbf")
   IF a > 0
      WAIT WINDOW "SELECT * FROM STORES EXECUTED"
      BROWSE
   ELSE
      error=DBError(handle,@errmsg,@errval)
      WAIT WINDOW STR(errval)+" "+errmsg
   ENDIF

   ** Release the connection handle.
   =DBDisconn(handle)
   SET LIBRARY TO