Be PREPAREd

The SQL PREPARE statement is used to define a statement to be executed. This statement can be a SELECT statement or an action statement (DELETE, UPDATE, etc.). Using the PREPARE statement to define an SQL statement has several benefits. First, it gives you the ability to dynamically create the SQL statement within a string variable and use that string variable. That string variable can then be used as the source of the PREPARE. Second, PREPARED SQL statements can include parameter definitions, which allow you to pass changeable values into your statement. Last but not least, the fact that prepared SQL statements are "prepared" ahead of time makes their execution run more efficiently. When a PREPARE statement is executed, the statement is parsed by the database engine. Once a statement has been prepared, the database engine doesn't need to reparse it every time the statement is executed. Figure 1 contains a snippet of embedded SQL code from an ILE RPG program.

Figure 1: This code illustrates defining a PREPARE statement.

In this example, the SQL statement is defined using the character variable SQL_Txt. The field named Qt in this statement represents a constant defined as the hex character x'7D', which represents the single quote character ('). The PREPARE statement shown then creates a new prepared statement called SQL_STMT using the string SQL_Txt. The DECLARE statement is then executed to associate the CURSOR named Stmt with the prepared SQL statement. Finally, the OPEN statement opens a connection to the data contained in the Stmt cursor. When the PREPARE statement is executed, the SQL statement in our SQL_Txt variable is parsed and verified by the database. The data set isn't actually opened until the OPEN statement is executed.

The great thing about this is that, since the SQL statements are built using character strings, you can dynamically (and conditionally) build your SQL statements within your program. As I mentioned earlier, it's also possible to insert changeable parameter markers, identified by a question mark character (?). The values for these parameter markers are defined when the resulting SQL cursor is opened. Figure 2 shows a slightly different version of the example in Figure 1, this time using parameters.

Figure 2: This version of the code from Figure 1 uses parameters.

Note that the value for each parameter is supplied on the OPEN statement via the USING clause. The parameter values should appear in the same order in which they appear in the prepared SQL statement. Since our SELECT statement is optimized by the PREPARE statement, concurrent OPEN statements that are based on the same prepared SQL statement will perform better than if the entire statement were rebuilt with each execution. The PREPARE/DECLARE/OPEN method, however, can be used only with SELECT statements. To execute "non-SELECT" (or action) SQL statements, you must use a different method.

PREPARE to EXECUTE

To perform action SQL statements (DELETE, INSERT, UPDATE, etc.) using PREPARE, you must use the EXECUTE statement. When using EXECUTE with a prepared SQL statement, you can also use parameter markers. This allows the use of action SQL statements and provides all of the benefits of prepared SQL statements. Figure 3 shows an example of using a PREPARE/EXECUTE sequence of SQL statements.

Figure 3: This code uses a PREPARE/EXECUTE sequence of SQL statements.

This code prepares an INSERT SQL statement with seven parameter markers. The values for these parameter markers are supplied through the USING clause of the EXECUTE statement in much the same way parameter values are supplied on the OPEN statement. As with the OPEN statement, concurrent executions of the statement work more efficiently than they would if we simply coded the INSERT statement directly.

Are You PREPAREd?

Whether you're using embedded SQL to select records from a table in your database or to add or delete records in a table in your database, a little PREPARE-ation goes a long way in improving SQL performance.

Mike Faust is an application programmer for Fidelity Integrated Financial Solutions in Maitland, Florida. Mike is also the author of the books The iSeries and AS/400 Programmer's Guide to Cool Things, and Active Server Pages Primer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Mike Faust available now on the MC Press Bookstore.

		Active Server Pages Primer Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application. List Price $79.00 Now On Sale
		JavaScript for the Business Developer Learn how JavaScript can help you create dynamic business applications with Web browser interfaces. List Price $44.95 Now On Sale
		SQL Built-in Functions and Stored Procedures Unleash the full power of SQL with these highly useful tools. List Price $49.95 Now On Sale