Coding SQL Statements

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 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 of the RemoteData control — as you can when using DAO. However, you can either use a simple query like SELECT * FROM <table>, or at run time 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.

Your query's SQL syntax 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 nonstandard 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. Aggregate or computed columns must also be aliased to provide name references for them in the rdoColumns collection.
  
  
• The name of each table to be searched for the information requested. If you specify more than one table, you should 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 used to join the two tables on a common column.
  
  
• 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 user-supplied parameters in the WHERE clause to specify different sets of information from query to query. If you need to provide WHERE clause criteria at run time, you must create a parameter 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.

For More Information   See "Creating RDO Parameter Queries."