DBSQL(database, SQL_statement, field_name, rec_num)

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:

Other remote functions