Although many users will appreciate the power and flexibility provided by SQL syntax, AQL (Access Query Language), the traditional D3 query language, may also be used to return result sets back to an ODBC-compliant application. This provides many advantages for users who have pre-existing AQL reports that they want to load into ODBC client applications.
To use AQL, the client application must support the ability to pass non-standard SQL statements to the server.
Many applications offer a feature called pass-through queries, which allows commands to be sent directly to the server. The syntax of these queries does not use a standard SQL format, but is simpler to implement and executes more quickly.
To use this syntax, the client application must not pre-parse the desired statement for valid SQL syntax. In Visual Basic, this can be enabled with an option to the OpenRecordSet method.
Guidelines for writing AQL pass-through statements:
The right bracket character ( ] ) is required and must prefix the AQL statement.
Statements begin with either the LIST or SORT command followed by standard AQL syntax.
No special options are necessary.
The syntax for an AQL pass-through query:
]LIST File[AttributeList][Filter][(Options]
]SORT File[AttributeList][Filter][(Options]
[{W}{B}]LIST File[AttributeList][Filter][(Options]
[{W}{B}]SORT File[AttributeList][Filter][(Options]
| Option | Description |
|---|---|
| W | Enables wide mode. The AQL report is generated using the current terminal width which is normally 79. If the wide mode is enabled, the report is generated with a width of 250. There is no width limit when using the W option. |
| B | Normally, blank lines are stripped from the result set. If the B flag is set, blank lines are not stripped. |
After the command is issued, the server processes the AQL report and passes the output back as a standard ODBC result set with each AQL column becoming an ODBC column and each AQL line of output becoming an ODBC row.
Be aware of the issues below:
The AQL statement must be columnar. If this is not the case, the results are returned in a jumbled manner.
Any header on the AQL statement is stripped.
A detail element may not exceed the column width of a wrap-around column. If this occurs, the wrapped-around data will appear as an element in the next row.
This statement generates a report that lists the names of five of the items from the CUSTOMERS file.
]LIST Customers Organizationname SAMPLING 5
If this example is passed in lieu of an SQL statement to an ODBC client application supporting non-standard SQL syntax, a result set similar to those below is generated.
| Customers | Organization Name |
|---|---|
| 13 | Best Electronic Company |
| 3 | Great Software Inc. |
| 16 | Computers ‘R Us |
| 50 | ComputerLand |
| 17 | Logical Computers |