6.3.7.5.10 Develop the Code to Perform the CallableStatement Query
Open the Select button click event handler in the FacultyFrame Form window, move to the Java Callable Method block and add the code shown in Figure 6.35 to this part. The newly added parts are in bold.
Let’s have a closer look at this piece of modified code to see how it works.
A. An else if block is added immediately after the if block for the Java execute method block.
This block is used to execute the Java Callable method.
B. A CallableStatement object, cstmt, is generated and initialized first.
FIGURE 6.33 The opened Run PL/SQL wizard (Copyrighted by Oracle and used with permission).
FIGURE 6.34 The run result of the package FACULTYINFO (Copyrighted by Oracle and used with permission).
C. A query statement is created with SQL92 syntax, which is used to call the Oracle pack-age we built in the last section with an embedded stored procedure. The stored procedure, SelecteFacultyInfo(), must be prefixed with the package name FacultyInfo, and
FIGURE 6.35 The added code for the Java Callable Method.
this is required by Oracle databases. Two dynamic positional parameters, the first one an input
and the second an output, are represented by two question marks as the arguments for this call.
D. Then the prepareCall() method is executed to create a CallableStatement object.
E. A system setter method, setString(), is used to set up the input parameter, which is a selected faculty name from the Faculty Name combo box by the user.
F. The second positional parameter, which is an output parameter, is registered with the reg-isterOutParameter() method. The point is that the data type of this parameter is an Oracle Cursor, which is equivalent to a ResultSet, and it can be used to return a collec-tion of queried data; it can be considered a data Table in which the returned data is stored. An Oracle extension Java type, oracle.jdbc.OracleTypes.CURSOR, is used here to define this returned data type. This is a necessary step to register this output parameter.
G. Then the CallableStatement object is executed to perform this data query.
H. The getObject(2) method is used to pick up the returned data and assigned to a ResultSet object, rs.
I. The getMetaData() method is also executed to get detailed information, such as data format and property, about the returned data stored in the Cursor.
J. A while loop is executed with the next() method as the loop condition. The first next() method moves the cursor one step down to point to the current returned row (exactly one row is returned).
K. Inside the while loop, a for loop is used to pick up all seven columns and assign them to the related TextFields to display the queried faculty information, which really happens in step M, until it gets the last column, which can be identified by using a system method, getColumnCount(), and this column stores the queried faculty image.
L. Then we need to use getBlob() to retrieve this image as a binary large object and assign it to our local object, fimgBlob. A break instruction is executed to finish this for loop when the desired faculty image has been obtained.
M. The first seven columns, which store the queried faculty information, are picked up one by one and assigned to the related TxtField via the f _ field[] array.
N. Any possible exception or error will be caught by the catch block and displayed in our msgDlg dialog.
Now we need to Figure out how to select and display the queried faculty image involved in this data query in this FacultyFrame Form with related code.