The DBSQL function returns the value of an SQL query.
database is the name of a database and must include the full path if not in the same directory as the open document.
SQL_statement is a text string containing the full syntax of a SQL statement.
field_name is optional and can be either the name of the field within a table or relation or the column number. If omitted, every row and column of the filtered table is returned.
rec_num is optional (and cannot be specified if field_name is omitted) and determines the record or row number of the returned value. If omitted, the entire column is returned (or table if field_name is also omitted).
The function arguments are case insensitive with the exception that SQL commands within SQL_statement should all be upper case.
For example, a database called wineshop, located in a folder c:\ability\samples, contains a Product table of wines. Products contains three fields as follows:
Title |
Price |
Classification |
Château Haut du Puy |
17.00 |
red |
Volnay-Santenots |
27.00 |
red |
Meursault-Charmes |
54.00 |
white |
Le Chambertin |
53.00 |
red |
Chambolle-Musigny |
28.50 |
red |
DBSQL can be used to obtain the following results:
DBSQL("c:\ability\samples\wineshop.adb", "SELECT * FROM products")
returns the entire table. "Château Haut du Puy", is displayed as it is contents of the first column from the first record.
DBSQL("c:\ability\samples\wineshop.adb", "SELECT * FROM products", 3, 3)
returns "white", the third column from the third record.
ROWS(DBSQL("c:\ability\samples\wineshop.adb", "SELECT Price FROM products WHERE Price > 50"))
returns 2, since there are two wines over 50.
The examples given here only touch on the possibilities with SQL. For a general introduction to SQL, with the full range of supported SQL commands in Ability, see the SQL Reference Guide.
See also: