Add Prepared Statement

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide. Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example: insert into EMP_TAB (Age, Name, Dept No) values (?, ?, ?)

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

To Add Prepared Statements

Note - When using a Prepared Statement, the ResultsAvailable() method will always return true. Although this method is available, you should not use it with a while loop. Doing so would result in an infinite loop at runtime and will stop all of the system’s CPU. If it is used, it should only be used with the if statement.You can process a resultset by looping through the next() method.

1. In the Add Prepared Statements dialog box, click Add.

   Figure 27 Prepared Statement

   
2. Enter the name of a Prepared Statement and create a SQL statement by clicking in the SQL Statement dialog box or by clicking the Statement Builder button. When you are finished creating the statement, click Save As, which gives the statement the name you just entered. This name appears as a node in the OTD (see the figure below). Click OK.

   Figure 28 Prepared SQL Statement

   
3. In the Add Prepared Statement dialog box, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.
4. Click Add if you want to add additional parameters to the Statement, or highlight a row and click Remove to remove the parameter (see the following figure). Click OK.

   Note - Once you save the Prepared Statement, make sure that the ResultSet Column Name, in the Prepared Statement parameters, is a valid alpha-numeric string with no special characters (e.g. no brackets).

   Figure 29 Edit the Prepared Statement Parameters

   
5. To edit the ResultSet Columns, click Edit ResultSet Columns (see the figure below). Although both the Name and Type are editable, Sun recommends that you do not change the Name because it can cause a loss of integrity between the ResultSet and the Database. Click OK.

   Note - The OTD Wizard fails to create OTDs with complex prepared statements that use the same column name in different tables. This problem is resolved by modifying the SQL statement to use column name aliases.

   Figure 30 ResultSet Columns

   
6. In the Add Prepared Statements dialog box, click OK.