Executing a prepared statement

The prepare keyword constructs an SQL statement from a string. Then, you can run that prepared statement with another data-access statement.

Using prepared statements instead of explicit SQL code can improve performance for data access operations that you use repeatedly. When you create a prepared statement, the database performs much of the processing necessary for the statement ahead of time. Then, the database has to perform less processing when you execute the prepared statement. Whether you use a prepared statement or explicit SQL depends on how many times you use the data access operation.

Fundamentally, executing a prepared statement is no different from executing any explicit SQL code. The following two functions are equivalent:

function executeExplicitSQL()
    myCustomers myCustomers[0];
    get myCustomers with #sql{
        SELECT CUSTOMER_ID, LAST_NAME
        FROM MYSCHEMA.MYTABLE
    };
end

function executePreparedStatement()
    myCustomers myCustomers[0];
    prepare myStatement from 
        "SELECT CUSTOMER_ID, LAST_NAME FROM MYSCHEMA.MYTABLE";
    get myCustomers with myStatement;
end

The previous examples used the get statement to execute the prepared statement, but you can also use execute or open. In each case, the prepared statement must be appropriate for the data access statement. In other words, if you can execute the string as explicit SQL, you can also prepare and execute the string as a prepared statement. You can use prepare for standard SQL statements such as SELECT, for dynamic SQL statements that include variables, and for calls to stored procedures.

Note:

The EGL prepare statement is not the same as the Java™ PreparedStatement object, though both can improve performance. For information on the Java™ PreparedStatement object, see prepareAllSQLStatements.

Preparing and executing the statement

To prepare the statement, first create a string variable to hold the statement. Like any string, you can assign a value to the variable directly or assemble the value from multiple strings or variables:

myString string = "SELECT ";
myString += "CUSTOMER_ID, LAST_NAME ";
myString += "FROM MYSCHEMA.MYTABLE";

Then, use prepare to create the prepared statement from the variable, assigning the statement to a new identifier:

prepare myStatement from myString;

Also, you can add a for clause to specify the SQLRecord to use with the prepared statement:

myCustomer myCustomers;
prepare myStatement2 from myString for myCustomer;

Finally, execute the statement and put the results into a variable:

myCustomerArray myCustomers[];
get myCustomerArray with myStatement2;

Using variables in the prepared statement

Prepared statements are especially useful when you want to insert variables into the statement. Variables in prepared statements are even more powerful than host variables in normal explicit SQL code, because you can do more than just insert an EGL variable value; you can change which variable the dynamic statement uses.

When creating a prepared statement, you use a question mark (?) to represent variables. The question mark acts as a placeholder into which you can insert a value later with the using clause:

myCustomerID int = 5;
myCustomerArray myCustomers[];
myHostVarString string = "SELECT CUSTOMER_ID, LAST_NAME";
myHostVarString += " FROM MYSCHEMA.MYTABLE";
myHostVarString += " WHERE CUSTOMER_ID = ?";
prepare myStatement from myHostVarString;
get myCustomerArray with myHostVarString using myCustomerID;

In this case, you can change the using myCustomerID clause to use different variables in different situations.

Creating a detailed prepared statement

EGL also provides a tool that creates a prepare statement and the related execute, get, or open statement.

Within a function in your logic part, right-click a blank line and then click Add SQL Prepare Statement. The Add SQL Prepare Statement window opens.
In the Prepared statement identifier field, type a name to identify the EGL prepare statement. For rules, see "Naming conventions" in the EGL Language Reference.
In the SQL record variable name field, select a record variable from the list or type a name for a new variable and then select an SQL record part using the Browse button. You must eventually define an SQL record variable with that name in the EGL source code.
In the Execution statement type field, select execute,get, or open.
If you selected open in the Execution statement type field, type an identifier for the result set in the Result set identifier field.
Click OK. EGL creates the prepare statement and related data access statement.