Microsoft KB Archive/108621

{| = Variable Containing SQL String Can Be Used in DBExec =
 * width="100%"|

ID: Q108621

2.50 2.50a 2.50b 3.00 WINDOWS

The information in this article applies to:


 * Microsoft Visual FoxPro for Windows, version 3.0
 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
 * Microsoft FoxPro Connectivity Kit, version 2.5

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 ().

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

Syntax  DBExec(,[,]) 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
 * }