Using the SQL condition

The STRING created in the query-by-example must be used to build a complete SQL SELECT statement to be sent to the database server for execution.

First, build the complete SQL SELECT statement by using the SQL condition, for example:

LET sql_text = "SELECT COUNT(*) FROM customer WHERE " || p_where_clause

For a single row query, the SQL statement can be parsed with the PREPARE instruction:

PREPARE cust_cnt_stmt FROM sql_text

The prepared SQL statement can then be executed with the EXECUTE instruction:

EXECUTE cust_cnt_stmt INTO cust_cnt

Since the SQL statement will only return one row (containing the count) the INTO syntax of the EXECUTE instruction can be used to store the count in the local variable cust_cnt. (The function cust_select illustrates the use of database cursors with SQL SELECT statements.)

When a prepared statement is no longer needed, the FREE instruction will release the resources associated with the statement:

FREE cust_cnt_stmt

For SQL SELECT statements returning a result set, you need to declare an SQL cursor to fetch rows from the database.