SQL Property

Returns or sets the SQL statement that defines the query executed by an rdoQuery object or a RemoteData control.

Syntax

object.SQL [= value]

The SQL property syntax has these parts:

Part	Description
object	An object expression that evaluates to an object in the Applies To list.
value	A string expression that contains a value as described in Settings. (Data type is String.)

Settings

The settings for value are:

Setting	Description
A valid SQL statement	An SQL query using syntax appropriate for the data source.
A stored procedure	The name of a stored procedure supported by the data source preceded with the keyword "Execute".
An rdoQuery	The name of one of the rdoQuery objects in the rdoConnection object's rdoQueries collection.
An rdoResultset	The name of one of the rdoResultset objects in the rdoConnection object's rdoResultsets collection.
A table name	The name of one of the populated rdoTable objects defined in the rdoConnection object's rdoTables collection.

Remarks

The SQL property contains the structured query language statement that determines how rows are selected, grouped, and ordered when you execute a query. You can use a query to select rows to include in an rdoResultset object. You can also define action queries to modify data without returning rows.

You cannot provide a table name at design time for the SQL property. However, you can either use a simple query like SELECT * FROM <table>, or at runtime, populate the rdoTables collection and use one of the table names returned in the collection. The rdoTables collection is populated as soon as it is associated with an active connection and referenced.

The SQL syntax used in a query must conform to the SQL dialect as defined by the data source query processor. The SQL dialect supported by the ODBC interface is defined by the X/Open standard. Generally, a driver scans an SQL statement looking for specific escape sequences that are used to identify non-standard operands like timestamp literals and functions.

When you need to return rows from a query, you generally provide a SELECT statement in the SQL property. The SELECT statement specifies:

The name of each column to return or "*" to indicate all columns of the specified tables are to be returned. Ambiguous column names must be addressed to include the table name as needed. You can also specify aggregate expressions to perform arithmetic or other functions on the columns selected.
The name of each table that is to be searched for the information requested. If you specify more than one table, you must provide a WHERE clause to indicate which column(s) are used to cross-reference the information in the tables. Generally, these columns have the same name and meaning. For example the CustomerID column in the Customers table and the Orders table might be referenced.
(Optionally) a WHERE clause to specify how to join the tables specified and how to limit or filter the number and types of rows returned. You can use parameters in the WHERE clause to specify different sets of information from query to query.
(Optionally) other clauses such as ORDER BY to set a particular order for the rows or GROUP BY to structure the rows in related sets.

Each SQL dialect supports different syntax and different ancillary clauses. See the documentation provided with your remote server for more details.

Specifying Parameters

If the SQL statement includes question mark parameter markers (?) for the query, you must provide these parameters before you execute the query. Until you reset the parameters, the same parameter values are applied each time you execute the query. To use the rdoParameters collection to manage SQL query parameters, you must include the "?" parameter marker in the SQL statement. Input, output, input/output and return value parameters must all be identified in this manner. In some cases, you must use the Direction property to indicate how the parameter will be used.

Note When executing stored procedures that do not require parameters, do not include the parenthesis in the SQL statement. For example, to execute the "MySP" procedure use the following syntax: {Call MySP }.

Note When using Microsoft SQL Server 6 as a data source, the ODBC driver automatically sets the Direction property. You also do not need to set the Direction property for input parameters, as this is the default setting.

If the user changes the parameter value, you can re-apply the parameter value and re-execute the query by using the Requery method against the rdoResultset (MyRs).

Cpw(0) = Text1.Text
MyRs.Requery

You can also specify parameters in any SQL statement by concatenating the parameters to the SQL statement string. For example, to submit a query using this technique, you can use the following code:

QSQL$ = "SELECT * FROM Authors WHERE Au_Lname = '" _
 & Text.Text & "'"
Set CPw = cn.CreateQuery("",QSQL$)
Set MyRs = Cpw.OpenResultSet()

In this case, the rdoParameters collection is not created and cannot be referenced. To change the query parameter, you must rebuild the SQL statement with the new parameter value each time the query is executed, or before you use the Requery method.

The SQL statement may include an ORDER BY clause to change the order of the rows returned by the rdoResultset or a WHERE clause to filter the rows.

Note You can't use the rdoTable object names until the rdoTables collection is referenced. When your code references the rdoTables collection by enumerating one or more of its members, RDO queries the data source for table meta data. This results in population of the rdoTables collection. This means that you cannot simply provide a table name for the value argument without first enumerating the rdoTables collection.

RemoteData Control

When used with the RemoteData control, the SQL property specifies the source of the data rows accessible through bound controls on your form.

If you set the SQL property to an SQL statement that returns rows or to the name of an existing rdoQuery, all columns returned by the rdoResultset are visible to the bound controls associated with the RemoteData control.

After changing the value of the SQL property at run time, you must use the Refresh method to activate the change.

Note Whenever your rdoQuery or SQL statement returns a value from an expression, the column name of the expression is determined by the wording of the SQL query. In most cases you'll want to alias expressions so you know the name of the column to bind to the bound control.

Make sure each bound control has a valid setting for its DataField property. If you change the setting of a RemoteData control's SQL property and then use Refresh, the rdoResultset identifies the new object. This may invalidate the DataField settings of bound controls and cause a trappable error.