SQLEXEC( )

Topic group Example

Executes an SQL statement in the current database or on specified dBASE or Paradox tables.

Syntax

SQLEXEC(<SQL statement expC> [,<Answer table expC>])

<SQL statement expC>

A character string that contains an SQL statement. The SQL statement must follow server-specific dialect rules for the current database and must be enclosed in quotes. For Paradox and dBASE tables, the dialect is the same as that used by the InterBase database server, which is ANSI-compliant. Character strings and SQL or BDE reserved words contained within the SQL statement must also be enclosed in either single or double quotation marks. (Single quotation marks are normally used.).

<Answer table expC>

Paradox or DBF table that stores the data returned by an SQL SELECT statement; must also be in quotes. If you specify a file without including its path, dBASE Plus creates the file in the current directory, then in the path you specify with SET PATH. If you specify a file without including its extension, dBASE Plus assumes the default table type specified with the SET DBTYPE command. If you don't specify a table name, dBASE Plus creates a table named Answer with the extension defined by the current DBTYPE setting.

You can also specify the name of an already open database (defined for a file directory location only) as a prefix (enclosed in colons) to the name of the answer table, that is, :database name:table name. You cannot specify the location of an answer table on a database server.

Description

SQLEXEC( ) executes an SQL statement in the current database set by SET DATABASE, or if a database is not set, on tables in the current or a specified directory. (You can preface the name of a table with its directory location or specify an already open database by enclosing the database name in colons, for example, :database name:table name. If you're using Borland SQL Link to connect to a database server, dBASE Plus passes the SQL statement you specify directly to the database server where the database selected by SET DATABASE resides.

When an SQL statement contains SQL or BDE reserved words and you are executing the statement on DBF or Paradox tables, you need to enclose the reserved words in single(‘) or double (") quotation marks and use SQL table aliases (different than the aliases associated with dBASE tables) to identify fields, for example:

SELECT * FROM company.dbf b WHERE b."CHAR" = 'element'

You can use table aliases to qualify fields specified in the SELECT, WHERE, GROUP BY, or ORDER BY clauses of SELECT statements. This is particularly useful when querying data from more than one table.

SQLEXEC( ) returns error codes with the same values as those returned by ERROR( ) and MESSAGE( ); a value of zero indicates that no error occurred as a result of the statement's execution. If an error occurs, you can use DBERROR( ) and DBMESSAGE( ) functions to return BDE errors or use the SQLERROR( ) and SQLMESSAGE( ) functions to obtain information directly from the database server about the cause of an error. (Also, the ERROR( ) function returns an error code of 240 if a server error occurs.)