prepare considerations for SQL

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 Content assist in the EGL Programmer's Guide). You can test this name for error conditions (see "Examples" in this topic).

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;