prepare considerations for SQL

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.

Syntax

Syntax diagram for the prepare statement
preparedStatementID
A character string of your choice to identify the SQL code that the prepare statement creates at run time. You can then use this ID with the EGL execute, open, or get statement.
stringExpression
A valid SQL statement in the form of a string expression. Indicate literal strings with quotation marks, and use the "::" operator to combine them with string variables (see "Examples" in this topic).
SQLRecordVariable
The name of an SQL record variable. The EGL editor can provide cues for building stringExpression (see Writing code by using content assist in the EGL Programmer's Guide). You can test this name for error conditions (see "Examples" in this topic).

Dynamic SQL and the prepare 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.

Examples

The following example builds a string dynamically, based on user-selected items from a menu:
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;
After the prepare statement runs, test the record name against an I/O error value to determine whether the statement succeeded, as in the following example. This example uses question marks as placeholders for host variables (see Host variables):
  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.

A prepare statement that acts on a row of a result set can include a phrase of the format WHERE CURRENT OF resultSetIdentifier. This technique is valid only when the following conditions are true:
  • The phrase is coded in a literal.
  • The result set is open when the prepare statement runs.
Assuming that X1 is the result set identifier from the open statement, the following example builds a dynamic prepared statement and runs it:
	 prepare prep02 from
    "update myTable " ::
    "set empname = ?,  empphone = ?  where current of x1" ;
 
  execute prep02 using empname, empphone ;
  freeSQL prep02;

Compatibility

Table 1. Compatibility considerations for prepare
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.

Feedback