PreparedStatement Objects |
Active Web |
 |
|
PreparedStatement Objects |
Active Web |
|
The PreparedStatement object represents a pre-compiled SQL statement which can be used to efficiently execute a statement multiple times. The statement contains placeholders for IN parameters.
For example: UPDATE EMP SET SALARY = ? WHERE EMPID = ?
The statement above has a two in parameter placeholders; the first for the employee’s new salary and the second for the employee ID we want to update.
In Active Web PreparedStatement objects are created using the preparedStatement(sql) function call from the Database object. When the Database object tries to create the Prepared Statement object the underlying database connection pre-compiles the supplied SQL and should there be an error in the SQL then the Database objects error property will be set.
Once created the placeholder IN parameters can have values assigned to them using a variety of functions available from the PreparedStatement object. When all IN parameter values have been assigned the statement can be executed using the execute() function. This function returns true if the statement executed correctly and false if there was an error (under this condition the error property of the prepared statement object is set).
Upon successful execution calling the getMoreCursors() function on the statement will indicate whether there are one or more result sets available for processing. The result sets can then be retrieved using the getCursor() function. The returned Cursor objects can then be processed in the usual manner.
Active Web will automatically track the resources allocated to the prepared statement (this includes the Cursor objects generated); at the end of page execution any prepared statement objects created will be closed. Should the developer wish to close the objects then they can call the close() function directly. Any attempt to use a closed PreparedStatement object will result in a runtime error.
The following sections outline the properties and functions available in the PreparedStatement Object; they also outline the mapping from script variable types to IN parameter placeholders.
|
Property |
Description |
|
Error |
The error property is cleared before every function call to a PreparedStatement object; should an error occur during the execution of the function then it will be set with suitable error text. |
The PreparedStatement object contains two distinct sets of functions; one that allows setting of parameter values to the IN parameter place holders; and a second set that perform the other functions such as closing and clearing the parameters set.
|
Function |
Description |
|
execute() |
Executes the prepared statement with the currently configured IN parameters. The function returns true if the statement executes correctly and there are result sets to process using the getCursor() function; false if it executes correctly and there is an update count to retrieve. Should an error occur then the error property will be set with the error text and the function will return an undefined object. |
|
getMoreCursors() |
Returns a Boolean value indicating if the statement has any more result sets available for processing using a Cursor object. |
|
getCursor() |
Returns the next result set from the statement execution as a cursor object. If no result set is available then the function returns null and the error property is set. |
|
getUpdateCount() |
Returns the current cursors update count. If no rows have been updated then it returns -1. If an error occurs then the error property is also set. This function should only be called once per cursor. |
|
close() |
Closes the Prepared statement object; any further attempt to use it will result in a runtime error. |
|
clearParams() |
Clears the current parameter values in this prepared statement immediately. In general, parameter values remain in force for repeated use of a statement. Setting a parameter value automatically clears its previous value. Should an error occur then the function will return null. |
The following functions can be used by the script developer to set values in the prepared statement IN parameters. All of the functions take two parameters; the first being the number of the placeholder IN parameter and the second being the value to be assigned to that parameter. The parameter number starts from 1. The type of parameter being set causes the value to be set in the parameter to be interpreted in a variety of different ways. The table below outlines the setXXX() functions and how their value parameters are interpreted depending on what type of value is being set.
|
Function |
Description |
|
setAsciiStream() |
If the parameter is a File or BinaryFile object then the file referred to will be set as the parameter. If the parameter is not one of the above then it will be treated as a string file name and normal file name resolution rules will apply. If the file cannot be found or an error occurs then error property will be set. |
|
setBigDecimal() |
The parameter is converted to an BigDecimal, first by converting the parameter to a 32-bit integer. Should an error occur then the error property will be set. |
|
setBinaryStream() |
If the parameter is a File or BinaryFile object then the file referred to will be set as the parameter. If the parameter is not one of the above then it will be treated as a string file name and normal file name resolution rules will apply. If the file cannot be found or an error occurs then error property will be set. |
|
setBoolean() |
If the Boolean value of the parameter is set as the value of the parameter in the prepared statement. |
|
setByte() |
The byte value of the parameter is set as the value of the parameter in the prepared statement. |
|
setBytes() |
The byte values (in the default platform encoding) of the supplied string value are set as the value of the parameter in the prepared statement. |
|
setCharacterStream() |
If the parameter is a File or BinaryFile object then the file referred to will be set as the parameter. If the parameter is not one of the above then it will be treated as a string file name and normal file name resolution rules will apply. If the file cannot be found or an error occurs then error property will be set |
|
setDate() |
If the object is a script Date object then the value of that date object will be set as the parameter value; If the object is a Numeric the long integer value of the parameter will be converted to a Date (using number of milliseconds since 01 Jan 1970). In any other case the error property is set. |
|
setDouble() |
The double value of the parameter is set as the value of the parameter in the prepared statement. |
|
setFloat() |
The float value of the parameter is set as the value of the parameter in the prepared statement. |
|
setInt() |
The integer value of the parameter is set as the value of the parameter in the prepared statement. |
|
setLong() |
The long value of the parameter is set as the value of the parameter in the prepared statement |
|
setShort() |
The short value of the parameter is set as the value of the parameter in the prepared statement |
|
setString() |
The String value of the parameter is set as the value of the parameter in the prepared statement |
|
setTime() |
If the parameter is a script Date object then the prepared statement parameter value is set to the number of milliseconds since UTC as represented by the Date object. If the parameter is numeric then the prepared statement parameter is set to a Time represented by the number of milliseconds since UTC as represented by the number. Any other type will cause an error to be set. |
|
setTimestamp() |
If the parameter is a script Date object then the prepared statement Timestamp parameter value is set to the number of milliseconds since UTC as represented by the Date object. If the parameter is numeric then the prepared statement parameter is set to a Timestamp represented by the number of milliseconds since UTC as represented by the number. Any other type will cause an error to be set. |
Prepared Statement objects cannot be constructed explicitly; they are created using the preparedStatement(sql) function found in the Database object.
Topic ID: 150086