Using Prepared Statements

The primary advantage of using prepared statements with the Host Integrator JDBC connector is to allow parameterized SQL statements. Host Integrator does not support compiling SQL statements, which is the traditional reason for using prepared statements; therefore there is no gain in efficiency when executing a prepared statement with Host Integrator JDBC.

However, it is often convenient to create a prepared statement that includes parameters to provide variable aspects of an SQL statement. For example, in a web application, to insert a new record into the host application, parameters can be associated with input items in an HTML form. When the user enters new data into the form, each input item is transferred into the corresponding prepared statement parameter and then the prepared statement is executed.  This results in the insertion of the desired record into the host application via the Host Integrator table layer.

The following Java example illustrates how a prepared statement might appear for an insert operation (written for the CICSAccts model).

	String acctCols = "AcctNum, LastName, FirstName, MiddleInitial, Title, Phone, Address1, Address2,";
	acctCols += "NumCardsIssued, DayIssued, MonthIssued, YearIssued, Reason, CardCode, ApprovedBy";
        String sql = "INSERT INTO Accounts ("+acctCols+") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	PreparedStatement pstmt = myConnection.prepareStatement(sql);
	pstmt.setInt(1, 20005);
	pstmt.setString(2, "WILSON");
	pstmt.setString(3, "MARTIN");
	pstmt.setString(4, "B");
	pstmt.setString(5, "MR");
	pstmt.setString(6, "9876543210");
	pstmt.setString(7, "1234 56TH ST.");
	pstmt.setString(8, "HOUSTON, TX 98765");
	pstmt.setInt(9, 1);
	pstmt.setString(10, "01");
	pstmt.setString(11, "01");
	pstmt.setString(12, "2001");
	pstmt.setString(13, "N");
	pstmt.setString(14, "1");
	pstmt.setString(15, "1");        

When setting parameters, note that the first parameter is 1, and that Host Integrator always sets the parameter value to the equivalent string. For example, the PreparedStatement method setDate(int parameterIndex, java.sql.Date x) converts the supplied date object into a string and sets the parameter specified by parameterIndex to the resulting value.