Variable Containing SQL String Can Be Used in DBExec()

ID: Q108621

2.50 2.50a 2.50b 3.00 WINDOWS

The information in this article applies to:

SUMMARY

The DBExec() command sends an SQL statement to the data source, where the statement is processed. When you are creating a user-defined query that is used to query a remote data source using DBExec(), the SQL statement can be stored in a variable and that variable can be substituted directly into the DBExec() command.

MORE INFORMATION

Most examples in the Connectivity Kit "User's Guide" show DBExec() being executed with the SQL statement directly in the DBExec() function. For example, page 39 of the Connectivity Kit "User's Guide" has the following in the example: 

   retcode=DBExec(handle,"SELECT * FROM authors","cursor")
These kind of examples can be misleading; it seems as if the SQL statement has to be specified directly in the DBExec.

However, DBExec() and any other function contained in the Connectivity Kit behave like other native functions do in FoxPro: a character string can be stored to a variable and then that variable can be substituted when the parameter is a character string (<expC2>).

Page 38 of the Connectivity Kit "User's Guide" shows the syntax for DBExec() as follows: 

   Syntax   DBExec(<expN>,<expC1>[,<expC2>])
Angle brackets (<>) surrounding text indicate information that the user provides. This information can be a filename, an expression, a memory variable name, and so on.

The following example shows that you can build an SQL statement from "x" and "y", store it in variable "z" and use "z" in the DBExec() command: 

   ** Set library to the ODBC library used in Windows
   SET LIBRARY TO SYS(2004)+"\FPSQL.FLL"

   ** ERRVAL=error number, ERRMSG=errror 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.
   PUBLIC errval
   PUBLIC errmsg

   errval=0
   errmsg=' '

   ** Specify source name as seen in ODBC manager.
   ** Specify user and password for server.
   ** This information is specific for each user.
   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,'ConnTimeout',0)   && Wait indefinitely if need be.
   =DBSETOPT(handle,'UseTables',1)     && Put results into 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

   ** We could have obtained x and y from an @ SAY/GET from
   ** a screen also.
   x="select * from"
   y="stores"
   z=ALLTRIM(x)+" "+ALLTRIM(y)

   ** Perform an SQL SELECT and put results in TEST.DBF.
   a=DBEXEC(handle,z,"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 connection handle and library.
   =DBDISCONN(handle)
   SET LIBRARY TO
NOTE: If you are using Visual FoxPro for Windows, use SQLCONNECT(), SQLEXEC(), and SQLDISCONNECT() instead of DBCONNECT(), DBEXEC(), and DBDISCONNECT(). For more information about these functions, search for "SQL" in the online Help.

Additional reference words: VFoxWin 3.00 FoxWin 2.50 2.50a 2.50b ODBC CK Relational Query By Example (RQBE) KBCategory: KBSubcategory: FxtoolCk 

Keywords          : kbcode FxtoolCk 
Version           : 2.50 2.50a 2.50b 3.00
Platform          : WINDOWS

Last Reviewed: May 22, 1998