|
|||||||||
| PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
| SUMMARY: INNER | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD | ||||||||
This interface defines the Oracle extensions to the standard JDBC interface
java.sql.PreparedStatement. You can use
java.sql.PreparedStatement in your application where you do not
make use of the Oracle extensions. However, when your application uses the
Oracle extensions to java.sql.PreparedStatement you must cast
your prepared statement object to the type
oracle.jdbc.OraclePreparedStatement. Although the type by which
the java compiler will identify the statement object is changed, the object
itself is unchanged.
Extended functionality consists of setXXX() methods for
binding oracle.sql. types and objects into prepared statements,
and methods to support Oracle performance extensions on a
statement-by-statements basis.
About prepared statements : To write changes to the database, such
as for INSERT or UPDATE operations, you will
typically create a PreparedStatement object. This allows you to
execute a statement with varying sets of input parameters. The
prepareStatement() method of your JDBC Connection
object allows you to define a statement that takes variable bind parameters,
and returns a JDBC PreparedStatement object with your statement
definition.
Bind your data : Use setXXX() methods on the
PreparedStatement object to bind data into the prepared
statement to be sent to the database. Again, some of these methods are Oracle
specific and require that you cast your PreparedStatement object
to the OraclePreparedStatement type.
For example, if you have a Connection object named
conn and a RAW named my_raw, then write the RAW
to the database as follows:
PreparedStatement ps = conn.prepareStatement
("INSERT INTO raw_table VALUES(?)");
((OraclePreparedStatement)ps).setRAW(1, my_raw);
ps.execute();
| Field Summary | |
static short |
FORM_CHAR
Specifies the "form of use" for SQL CHAR datatypes (CHAR, VARCHAR2 and CLOB). |
static short |
FORM_NCHAR
Specifies the "form of use" for SQL NCHAR datatypes (NCHAR, NVARCHAR2 and NCLOB). |
| Fields inherited from class oracle.jdbc.OracleStatement |
EXPLICIT,
IMPLICIT,
NEW |
| Method Summary | |
void |
defineParameterType(int param_index,
int type,
int max_size)
Define the type under which you will bind the parameter and the maximum size (in characters) of data you will bind in binds. |
void |
defineParameterTypeBytes(int param_index,
int type,
int max_size)
Define the type under which you will bind the parameter and the maximum size (in bytes) of data you will bind in binds. |
void |
defineParameterTypeChars(int param_index,
int type,
int max_size)
Define the type under which you will bind the parameter and the maximum size (in characters) of data you will bind in binds. |
int |
getExecuteBatch()
Retrieves the batch value of this statement for the Oracle update batching model (default is set by the connection object). |
OracleParameterMetaData |
OracleGetParameterMetaData()
Retrieves the number, types and properties of this PreparedStatement object's parameters. |
int |
sendBatch()
Sends any existing batch (for the Oracle update batching model). |
void |
setARRAY(int paramIndex,
ARRAY arr)
Binds the designated parameter to a oracle.sql.ARRAY. |
void |
setBfile(int paramIndex,
BFILE file)
Binds the designated parameter to a oracle.sql.BFILE value. |
void |
setBFILE(int paramIndex,
BFILE file)
Binds the designated parameter to a oracle.sql.BFILE value. |
void |
setBLOB(int paramIndex,
BLOB lob)
Binds the designated parameter to a oracle.sql.BLOB value. |
void |
setCHAR(int paramIndex,
CHAR ch)
Binds the designated parameter to a oracle.sql.CHAR value. |
void |
setCheckBindTypes(boolean flag)
Enable/disable bind type checking. |
void |
setCLOB(int paramIndex,
CLOB lob)
Binds the designated parameter to a oracle.sql.CLOB value. |
void |
setCursor(int paramIndex,
java.sql.ResultSet rs)
Deprecated. |
void |
setCustomDatum(int paramIndex,
CustomDatum x)
Deprecated. |
void |
setDATE(int paramIndex,
DATE date)
Binds the designated parameter to a oracle.sql.DATE value. |
void |
setDisableStmtCaching(boolean cache)
Don't Cache this object even when the corresponding Connection is Cache Enabled. |
void |
setExecuteBatch(int batchValue)
Sets the batch value of this statement for the Oracle update batching model (the default value is set by the connection object). |
void |
setFixedCHAR(int paramIndex,
java.lang.String x)
Sets the disignated parameter to a String and executes a
non-padded comparison with a SQL CHAR. |
void |
setFormOfUse(int paramIndex,
short formOfUse)
Specifies if the data is bound for a SQL NCHAR datatype. |
void |
setINTERVALYM(int paramIndex,
INTERVALYM x)
Binds the designated parameter to an oracle.sql.INTERVALYM value. |
void |
setNUMBER(int paramIndex,
NUMBER num)
Binds the designated parameter to a oracle.sql.NUMBER value. |
void |
setOPAQUE(int paramIndex,
OPAQUE val)
Binds the designated parameter to a oracle.sql.OPAQUE value. |
void |
setOracleObject(int paramIndex,
Datum x)
Binds the designated parameter to a oracle.sql.Datum value. |
void |
setORAData(int paramIndex,
ORAData x)
Binds the designated parameter to a oracle.sql.ORAData value. |
void |
setPlsqlIndexTable(int paramIndex,
java.lang.Object arrayData,
int maxLen,
int curLen,
int elemSqlType,
int elemMaxLen)
Binds a PL/SQL index-by table parameter in the IN parameter mode. |
void |
setRAW(int paramIndex,
RAW raw)
Binds the designated parameter to a oracle.sql.RAW value. |
void |
setREF(int paramIndex,
REF ref)
Binds the designated parameter to a oracle.sql.REF value. |
void |
setRefType(int paramIndex,
REF ref)
Binds the designated parameter to a oracle.sql.REF value. |
void |
setROWID(int paramIndex,
ROWID rowid)
Binds the designated parameter to a oracle.sql.RAWID value. |
void |
setSTRUCT(int paramIndex,
STRUCT struct)
Binds the designated parameter to a oracle.sql.STRUCT value. |
void |
setStructDescriptor(int paramIndex,
StructDescriptor desc)
Binds the bind type of designated parameter from an oracle.sql.StructDescriptor. |
void |
setTIMESTAMP(int paramIndex,
TIMESTAMP x)
Binds the designated parameter to an oracle.sql.TIMESTAMP value. |
void |
setTIMESTAMPLTZ(int paramIndex,
TIMESTAMPLTZ x)
Binds the designated parameter to an oracle.sql.TIMESTAMPLTZ
value. |
void |
setTIMESTAMPTZ(int paramIndex,
TIMESTAMPTZ x)
Binds the designated parameter to an oracle.sql.TIMESTAMPTZ value. |
| Methods inherited from interface java.sql.PreparedStatement |
addBatch,
clearParameters,
execute,
executeQuery,
executeUpdate,
getMetaData,
setArray,
setAsciiStream,
setBigDecimal,
setBinaryStream,
setBlob,
setBoolean,
setByte,
setBytes,
setCharacterStream,
setClob,
setDate,
setDate,
setDouble,
setFloat,
setInt,
setLong,
setNull,
setNull,
setObject,
setObject,
setObject,
setRef,
setShort,
setString,
setTime,
setTime,
setTimestamp,
setTimestamp,
setUnicodeStream |
| Methods inherited from interface oracle.jdbc.OracleStatement |
clearDefines,
closeWithKey,
creationState,
defineColumnType,
defineColumnType,
defineColumnType,
defineColumnTypeBytes,
defineColumnTypeChars,
getRowPrefetch,
isNCHAR,
setResultSetCache,
setRowPrefetch |
| Field Detail |
public static final short FORM_NCHAR
setFormOfUse().public static final short FORM_CHAR
setFormOfUse().| Method Detail |
public void defineParameterTypeBytes(int param_index,
int type,
int max_size)
throws java.sql.SQLException
Similarly to defineColumnType(). Before executing a Query
you may choose to inform JDBC of the type and maximum length of the bind
parameter. If not, based on the setXXX call, the maximum
length of the type is taken, i.e., for a setString a 4k buffer is
allocated. This API does not reduce the network round trip. It is purely a
memory optimization API.
Note: You should use OracleTypes.CHAR as the type
value if you are going bind later as setCHAR and use
OracleTypes.VARCHAR if you are going to bind as
setString for effective results.
param_index - Index of bind parameter (should be >=0)type - Type to be assigned to bind parameter.
This type could be different from the
native type of the column. Appropriate
conversions will be done.max_size - Maximum length of bind data in bytes
(should be >=0)
public void defineParameterTypeChars(int param_index,
int type,
int max_size)
throws java.sql.SQLException
Similarly to defineColumnType(). Before executing a Query
you may choose to inform JDBC of the type and maximum length of the bind
parameter. If not, based on the setXXX call, the maximum
length of the type is taken, i.e., for a setString a 4k buffer is
allocated. This API does not reduce the network round trip. It is purely a
memory optimization API.
Note: You should use OracleTypes.CHAR as the type
value if you are going bind later as setCHAR and use
OracleTypes.VARCHAR if you are going to bind as
setString for effective results.
param_index - Index of bind parameter (should be >=0)type - Type to be assigned to bind parameter.
This type could be different from the
native type of the column. Appropriate
conversions will be done.max_size - Maximum length of bind data in bytes
(should be >=0)
public void defineParameterType(int param_index,
int type,
int max_size)
throws java.sql.SQLException
public int getExecuteBatch()
To set the batch value for this statement, you can use the
setExecuteBatch method. You can also set a default batch value
with the OracleConnection.setDefaultExecuteBatch.
public int sendBatch()
throws java.sql.SQLException
This method flushes any batched executions before the batch value is
reached. Executions normally happen when the number of executions reaches
the batch value, which is set via the setExecuteBatch method.
It does not do anything if the statement does not use the Oracle batching
model.
public void setARRAY(int paramIndex,
ARRAY arr)
throws java.sql.SQLException
oracle.sql.ARRAY.parameterIndex - the first parameter is 1, the second is 2, ...arr - the parameter value
public void setBfile(int paramIndex,
BFILE file)
throws java.sql.SQLException
oracle.sql.BFILE value.
Note: This is the same as setBFILE.
parameterIndex - the first parameter is 1, the second is 2, ...file - the parameter value
public void setBFILE(int paramIndex,
BFILE file)
throws java.sql.SQLException
oracle.sql.BFILE value.
Note: An instance of the oracle.sql.BFILE class
contains only the locator of the BFILE, not the data. Thus, this method
binds the designated parameter to a BFILE locator not to the data.
For example, assume you want to insert a BFILE locator into a table, and
you have an OraclePreparedStatement object ops to
insert data into a table. The first column is a string (to designate a row
number), the second column is a BFILE, and you have a valid
oracle.sql.BFILE object (bfile). Write the BFILE
to the database as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
("INSERT INTO my_bfile_table VALUES(?,?)");
ops.setString(1, "one");
ops.setBFILE(2, bfile);
ops.execute();
This stores only the locator of the BFILE in the
my_bfile_table. See the BFILE class to get more details
about how to deal with the data.
parameterIndex - the first parameter is 1, the second is 2, ...file - the parameter value
public void setBLOB(int paramIndex,
BLOB lob)
throws java.sql.SQLException
oracle.sql.BLOB value.
Note: An instance of the oracle.sql.BLOB class
contains only the locator of the BLOB, not the data. Thus, this method
binds the designated parameter to a BLOB locator not to the data.
For example, if you have an OraclePreparedStatement object
ops and a BLOB named my_blob, then write the BLOB
to the database as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
("INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
This stores only the locator of the BLOB in the
clob_table. Use the method BLOB.getBinaryOutputStream
to write data to a BLOB.
parameterIndex - the first parameter is 1, the second is 2, ...lob - the parameter value
public void setCHAR(int paramIndex,
CHAR ch)
throws java.sql.SQLException
oracle.sql.CHAR value.
setCHAR checks the character set of the argument. If it is
not the same as the network character set, then the bytes of the argument
are converted to the network character set and those bytes are sent to the
database. The conversion between the argument character set and the network
character set is via the Universal Character Set (UCS2). That is, the
argument is converted to UCS2, then the UCS2 bytes are converted to the
network character set.
Note: If an input character does not have a representation in
UCS2, or if the UCS2 does not have a representation of the network
character set, setCHAR will throw an exception.
parameterIndex - the first parameter is 1, the second is 2, ...ch - the parameter value
public void setCLOB(int paramIndex,
CLOB lob)
throws java.sql.SQLException
oracle.sql.CLOB value.
Note: An instance of the oracle.sql.CLOB class
contains only the locator of the CLOB, not the data. Thus, this method
binds the designated parameter to a CLOB locator not to the data.
For example, if you have an OraclePreparedStatement object
ops and a CLOB named my_clob, then write the CLOB
to the database as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
("INSERT INTO clob_table VALUES(?)");
ops.setCLOB(1, my_clob);
ops.execute();
This stores only the locator of the CLOB in the
clob_table. Use the methods CLOB.getCharacterOutputStream
or CLOB.getAsciiOutputStream
to write data to a CLOB.
parameterIndex - the first parameter is 1, the second is 2, ...lob - the parameter value
public void setCursor(int paramIndex,
java.sql.ResultSet rs)
throws java.sql.SQLException
java.sql.ResultSet. This
is used for RefCursor parameter only.
NOTE: The method is a misnomer. You should use CURSOR only in callable statements.
parameterIndex - the first parameter is 1, the second is 2, ...rs - the parameter value
public void setCustomDatum(int paramIndex,
CustomDatum x)
throws java.sql.SQLException
oracle.sql.CustomDatum
value.
In the 9i release the oracle.sql.CustomDatum has been
deprecated and replaced by the oracle.sql.ORAData
interface. For this reason, this method is also deprecated. It is
recommended to use setORAData() instead.
parameterIndex - the first parameter is 1, the second is 2, ...x - the parameter value
public void setORAData(int paramIndex,
ORAData x)
throws java.sql.SQLException
oracle.sql.ORAData value.parameterIndex - the first parameter is 1, the second is 2, ...x - the parameter value
public void setDATE(int paramIndex,
DATE date)
throws java.sql.SQLException
oracle.sql.DATE value.parameterIndex - the first parameter is 1, the second is 2, ...date - the parameter value
public void setExecuteBatch(int batchValue)
throws java.sql.SQLException
The Oracle update batching model allows you to "batch" multiple executions of a single statement with different bind variables. Actual execution happens when the number of batched executions reaches the "batch value", which is set by this method.
The batch value for this prepared statement can be set at any time via
this method. Changing the batch value will affect all future executes. You can
also use OracleConnection.setDefaultExecuteBatch to set a
default batch value.
The batch value will automatically be set to 1 by the driver if any of
the bind types is of type stream. This overrides any value that the user
might have set. Binds that were performed before any
setXXXStream() is called, and that have not been executed,
will be flushed (i.e. executed).
Note: It is important to be aware that you cannot mix the Oracle update batching model and the standard one. In any single application, you can use the syntax of one model or the other, but not both. The Oracle JDBC driver will throw exceptions when you mix these syntaxes.
batchValue - batch value to be set. It must be greater than or
equal to 1.batchValue is <1
public void setFixedCHAR(int paramIndex,
java.lang.String x)
throws java.sql.SQLException
String and executes a
non-padded comparison with a SQL CHAR.
CHAR data in the database is padded to the column width. This leads to a
limitation in using the setCHAR() method to bind character
data into the WHERE clause of a SELECT
statement--the character data in the WHERE clause must also be
padded to the column width to produce a match in the SELECT
statement. This is especially troublesome if you do not know the column
width.
setFixedCHAR() remedies this. This method executes a
non-padded comparison.
Notes:
OraclePreparedStatement to use the setFixedCHAR()
method.The following example demonstrates the difference between the
setString(), setCHAR() and
setFixedCHAR() methods.
// Schema is : create table my_table (col1 char(10));
// insert into my_table values ('JDBC');
PreparedStatement pstmt = conn.prepareStatement
("select count() from my_table where col1 = ?");
ResultSet rs;
pstmt.setString (1, "JDBC"); // Set the Bind Value
rs = pstmt.executeQuery(); // This does not match any row
// ... do something with rs
CHAR ch = new CHAR("JDB ", null);
((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes
rs = pstmt.executeQuery(); // This matches one row
// ... do something with rs
((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");
rs = pstmt.executeQuery(); // This matches one row
// ... do something with rs
paramIndex - index of the bind variable (first is 1)x - the literal/variable to be bound.
public void setNUMBER(int paramIndex,
NUMBER num)
throws java.sql.SQLException
oracle.sql.NUMBER value.parameterIndex - the first parameter is 1, the second is 2, ...num - the parameter value
public void setOPAQUE(int paramIndex,
OPAQUE val)
throws java.sql.SQLException
oracle.sql.OPAQUE value.parameterIndex - the first parameter is 1, the second is 2, ...val - the parameter value
public void setOracleObject(int paramIndex,
Datum x)
throws java.sql.SQLException
oracle.sql.Datum value.
This is a generic setXXX() method for binding
oracle.sql. data into a prepared statement as an
oracle.sql.Datum object.
parameterIndex - the first parameter is 1, the second is 2, ...x - the parameter value
public void setStructDescriptor(int paramIndex,
StructDescriptor desc)
throws java.sql.SQLException
parameterIndex - the first parameter is 1, the second is 2, ...x - the parameter value
public void setRAW(int paramIndex,
RAW raw)
throws java.sql.SQLException
oracle.sql.RAW value.parameterIndex - the first parameter is 1, the second is 2, ...raw - the parameter value
public void setREF(int paramIndex,
REF ref)
throws java.sql.SQLException
oracle.sql.REF value.parameterIndex - the first parameter is 1, the second is 2, ...ref - the parameter value
public void setRefType(int paramIndex,
REF ref)
throws java.sql.SQLException
oracle.sql.REF value.
Note: This is the same as setREF.
parameterIndex - the first parameter is 1, the second is 2, ...ref - the parameter value
public void setROWID(int paramIndex,
ROWID rowid)
throws java.sql.SQLException
oracle.sql.RAWID value.parameterIndex - the first parameter is 1, the second is 2, ...rowid - the parameter value
public void setSTRUCT(int paramIndex,
STRUCT struct)
throws java.sql.SQLException
oracle.sql.STRUCT value.parameterIndex - the first parameter is 1, the second is 2, ...struct - the parameter value
public void setTIMESTAMP(int paramIndex,
TIMESTAMP x)
throws java.sql.SQLException
paramIndex - specifies the parameter index. Index starts from 1.x - specifies the parameter value.
public void setTIMESTAMPTZ(int paramIndex,
TIMESTAMPTZ x)
throws java.sql.SQLException
oracle.sql.TIMESTAMPTZ value.paramIndex - specifies the parameter index. Index starts from 1.x - specifies the parameter value.
public void setTIMESTAMPLTZ(int paramIndex,
TIMESTAMPLTZ x)
throws java.sql.SQLException
oracle.sql.TIMESTAMPLTZ
value.
In order to use this method, the calling program must first set the
session time zone of the corresponding connection by using
OracleConnection.setSessionTimeZone().
paramIndex - specifies the parameter index. Index starts from 1.x - specifies the parameter value.
public void setINTERVALYM(int paramIndex,
INTERVALYM x)
throws java.sql.SQLException
paramIndex - specifies the parameter index. Index starts from 1.x - specifies the parameter value.public void setCheckBindTypes(boolean flag)
When bind type checking is disabled, the Jdbc driver does not check bind
types for every execute. It is intended to be used as a performance
enhancement if the user knows for fact that bind types do not change across
different executions. The default is true.
flag - set it to false if Bind Types doesn't change
public void setPlsqlIndexTable(int paramIndex,
java.lang.Object arrayData,
int maxLen,
int curLen,
int elemSqlType,
int elemMaxLen)
throws java.sql.SQLException
parameterIndex - This argument indicates the parameter position within
the statement. The first parameter is 1, the second is 2, ...arrayData - This argument is an array of values to be bound to the PL/SQL
index-by table parameter. The value is of type java.lang.Object, and the value
can be a Java primitive type array such as int[] or a Java object array such as
BigDecimal[].maxLen - This argument specifies the maximum table length of the
index-by table bind value which defines the maximum possible curLen for batch
updates. For standalone binds, maxLen should use the same value as curLen. This
argument is required.curLen - This argument specifies the actual size of the index-by table
bind value in arrayData. If the curLen value is smaller than the size of
arrayData, only the curLen number of table elements is passed to the
database. If the curLen value is larger than the size of arrayData, the entire
arrayData is sent to the database.elemSqlType - This argument specifies the index-by table element type
based on the values defined in the OracleTypes class.elemMaxLen - This argument specifies the index-table element maximum
length in case the element type is CHAR, VARCHAR, or RAW. This value is ignored
for other types.
public void setFormOfUse(int paramIndex,
short formOfUse)
FORM_CHAR for SQL CHAR
datatypes and FORM_NCHAR for SQL NCHAR datatypes, where
FORM_CHAR is the default. If the "form of use" is set to
FORM_NCHAR, the JDBC driver will represent the provided data
in the national character set of the server.
The usage of SQL NCHAR datatypes is similar to that of the SQL CHAR
(CHAR, VARCHAR2, and CLOB)
datatypes. JDBC uses the same classes and methods to access SQL NCHAR
datatypes that are used for the corresponding SQL CHAR
datatypes. Therefore, there are no separate, corresponding classes defined
in the oracle.sql package for SQL NCHAR datatypes. Likewise,
there is no separate, corresponding constant defined in the
oracle.jdbc.OracleTypes class for SQL NCHAR datatypes. The
only difference in usage between the two datatypes occur in a data bind
situation: a JDBC program must call the setFormOfUse() method
to specify if the data is bound for a SQL NCHAR datatype.
In order to put this method in effect, you must invoke this method
before setXXX() and registerOutParameter() for
the column.
The following code shows how to access SQL NCHAR data:
//
// Table TEST has the following columns:
// - NUMBER
// - NVARCHAR2
// - NCHAR
//
oracle.jdbc.OraclePreparedStatement pstmt =
(oracle.jdbc.OraclePreparedStatement)
conn.prepareStatement("insert into TEST values(?, ?, ?)");
// oracle.jdbc.OraclePreparedStatement.FORM_NCHAR should be used for all NCHAR,
// NVARCHAR2 and NCLOB data types.
pstmt.setFormOfUse(2, FORM_NCHAR);
pstmt.setFormOfUse(3, FORM_NCHAR);
pstmt.setInt(1, 1); // NUMBER column
pstmt.setString(2, myUnicodeString1); // NVARCHAR2 column
pstmt.setString(3, myUnicodeString2); // NCHAR column
pstmt.execute();
paramIndex - index of the column in the preparedStatmentformOfUse - use FORM_CHAR for CHAR, VARCHAR2 and CLOB data,
or FORM_NCHAR for NCHAR, NVARCHAR2 and NCLOB data.
FORM_CHAR is the default.public void setDisableStmtCaching(boolean cache)
cache - Set it to true, if you don't want this
statement to be cached.
public OracleParameterMetaData OracleGetParameterMetaData()
throws java.sql.SQLException
PreparedStatement object's parameters.OracleParameterMetaData object that
contains information about the number, types and
properties of this PreparedStatement
object's parametersOracleParameterMetaData
|
|||||||||
| PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
| SUMMARY: INNER | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD | ||||||||