The EGL prepare statement generates an SQL PREPARE statement, and gives you the option to include details that are known only at run time. Run the prepared SQL code with an EGL execute statement, or (if the SQL code returns a result set) with an EGL open or get statement.

The prepare statement is the typical way to perform dynamic SQL processing. The string expression that you specify can contain variables whose values are known only at runtime, providing powerful options to your program.
One common use for dynamic SQL is user-controlled queries. For example, users might select checkboxes to include information in a report. They might also determine which customer record the program displays by using a WHERE clause. In both cases, you do not know in advance what data the user will select.
You can also use question marks as placeholders for host variables in the string expression (see Host variables). When you run the prepared statement, list those variables as part of a using clause in the prepare, get, or open statement. The second example shows this process. You must supply host variables for all placeholder question marks, and you must also know what these variables are used for.
query STRING;
fieldName STRING;
isFirstField BOOLEAN;
query = "SELECT ";
isFirstField = true;
// function supplies field names selected by user
// returns -1 when all are used
while ((nextFieldName(fieldName)) == 0) // sets fieldName
if (!isFirstField) // more than one field in SELECT
query ::= ", ";
end
query ::= fieldName;
isFirstField = false;
end // of while
// finish up query
query ::= "FROM Customer WHERE customer_number = ?";
prepare myPreparedStatement from query;
try
prepare prep01 from
"INSERT INTO " :: aTableName ::
"(customer_number, customer_name) " ::
"VALUE ?, ?"
for myCustomer;
onException(sqlEx SQLException)
if (myCustomer is unique)
myErrorHandler(8);
else
myErrorHandler(sqlEx);
end
end
try
execute myStatement
using myRecord.empnum,
myRecord.empname;
onException(sqlEx SQLException)
myErrorHandler(sqlEx);
end
As shown in the previous examples, you can use a question mark (?) in place of a host variable. The name of the host variable that is used at run time is placed in the using clause of the execute, open, or get statement that runs the prepared statement.
prepare prep02 from
"update myTable " ::
"set empname = ?, empphone = ? where current of x1" ;
execute prep02 using empname, empphone;
freeSQL prep02;
| Platform | Issue |
|---|---|
| COBOL generation and Debug | In a dynamic SQL prepared statement, you must declare all of your identifiers and SQLRecord variables in the same function where you reference and use them. This restriction does not apply to Java™ generation, which allows the identifiers to be anywhere in the namespace. |
| Java generation | If the string expression from which you prepare
the statement is not valid for some reason, the prepare statement
might not fail. Instead EGL might throw an SQLException when it tries
to use the prepared statement for the first time. An issue might
arise if you use a JDBC driver from Oracle to handle code that is
structured as follows:
The issue is this: to ensure that rows are retrieved, you might need to set the length of the variable to be the same as the length of the database column. No rows are retrieved in the following case:
At this writing, the issue is documented at section 11.3.7.4 of the following web site: http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/datacc.htm. |