Executing an SQL statement

sql

Execute an SQL statement


SYNTAX

sql cursor_id = “SQL_statement” [in destination] [with params][err = label | IGNORE]

Executes the specified SQL statement, and associates the set of records selected by the statement with the cursor.

If present, the optional in, with and err clauses must be in the stated order.

For sql commands that have no select or parameter variables, the statement is executed using the ODBC function SQLExecDirect(), otherwise the statement is prepared using SQLPrepare() and then executed using SQLExecute().

cursor_id

The identifier of a cursor. The cursor must have been declared within the +|–database definition.

SQL_statement

An ODBC-compatible SQL statement.

[in destination]

A list of variables into which the retrieved values are stored. The list can contain both field_ids and record_ids - if a record_id is used, this is equivalent to listing each field in the record. If the number of fields listed exceeds the number of values returned, the extra fields are ignored; if the number of values exceeds the number of fields, the extra values are discarded.

[with params]

If the SQL statement contains “?” characters, these indicate parameters. The with params clause provides the values to be substituted. If more values are listed than are required, the extra values are ignored. If fewer values are listed than are required, the extra parameters are set to NULL.

[err = label]

Traps an error in the sql statement and passes control to the line indicated by label, instead of generating an ODBC debug message. The err = clause must be the last clause in the statement. If the form err = IGNORE is used, errors are ignored altogether.

When the err = trap occurs, Sculptor returns error information in the following system variables:

sys.Error

The native database error code.

sys.SQLState

The SQL state code.


NOTES

  • This is a built-in command, not to be confused with Sculptor SQL, a stand-alone, command line program that executes queries on a Sculptor file or files.


EXAMPLE

+database db1 {
     type = DBT_ODBC
     source = "hotel"
!file acc "account"
!cursor c1
}
     sql c1 = "SELECT * from account where balance < 800" in acc err = C1ERR

RELATED TOPICS

ODBC

Sculptor commands used with ODBC