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. For this reason, this syntax is less useful than other forms of the prepare statement.
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. |