Use prepared execution when you must execute the same SQL statement repeatedly. Prepared execution avoids the overhead of creating multiple SQLIStatement objects for a single statement.
There is an advantage to prepared execution when you execute the same SQL statement from within a loop. Instead of creating an object with each iteration of the loop, prepared execution creates an object once and supplies input parameters for each execution of the statement.
Once a stored procedure creates an SQLPStatement object, you can execute the object multiple times, supplying different values for each execution.
The following example extends the previous example to use prepared execution.
CREATE PROCEDURE prepared_insert_customer (
IN cust_number INTEGER,
IN cust_name CHAR(20)
) BEGIN
SQLPStatement p_insert_cust = new SQLPStatement (
"INSERT INTO customer VALUES (?,?) ");
.
.
.
int i;
for (i = 0; i < new_custs.length; i++)
{ p_insert_cust.setParam (1, new_custs[i].cust_number);
p_insert_cust.setParam (2, new_custs[i].cust_name);
p_insert_cust.execute ();
}END
