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.
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.
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;
myCustomer myCustomers; prepare myStatement2 from myString for myCustomer;
myCustomerArray myCustomers[]; get myCustomerArray with myStatement2;
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.
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.
EGL also provides a tool that creates a prepare statement and the related execute, get, or open statement.