sql

Topic group Related topics Example

The SQL statement that describes the query.

Property of

Query

Description

The sql property of a Query object contains an SQL SELECT statement that describes the rowset to be generated. To use a stored procedure in an SQL server that returns a rowset, use the procedureName property of a StoredProc object instead.

The sql property must be assigned before the Query object is activated.

The SQL SELECT statement may contain an ORDER BY clause to set the row order, a WHERE clause to select a subset of rows, perform a JOIN, or any other SQL SELECT clause.

But to take full advantage of the data objects’ features—such as locating and filtering—with SQL-server-based tables, the SQL SELECT used to access a table must be a simple SELECT: all the fields from a single table, with no options. For example,

select * from CUSTOMER

If the SQL statement is not a simple SELECT, locating and filtering is performed locally, instead of by the SQL server. If the result of the SELECT is a small rowset, local searching will be fast; but if the result is a large rowset, local searching will be slow. For large rowsets, you should use a simple SELECT, or use parameters in the SQL statement and requery( ) as needed instead of relying on the Locate and Filter features.

Master-detail linking through the masterRowset and masterFields properties with SQL-server-based tables also requires a simple SELECT. An alternative is master-detail linking though Query objects with the masterSource property and parameters in the SQL statement. There is no simple SELECT restriction when using Standard tables.

Parameters in an SQL statement are indicated by a colon. For example,

select * from CUST where CUST_ID = :cust_id

Whenever the SQL property is assigned, it is scanned for parameters. dBASE Plus automatically creates corresponding elements in the query’s params array, with the name of the parameter as the array index. For more information, see the params property.

In addition to assigning the SQL statement directly to the sql property, you may also use an SQL statement in an external file. To use an external file, place an "@" symbol before the file name in the sql property. For example,

@ORDERS.SQL

The external file must be a text file that contains an SQL statement.