|
|
|
Using jdbcKona/Oracle
I. IntroductionWhat's in this document
Covered in this document are general guidelines for using the WebLogic
jdbcKona If you are using WebLogic JDBC, WebLogic's multitier JDBC implementation, you should also refer to the Developers Guide Using WebLogic JDBC for more information.
Mapping of types between Oracle and jdbcKona/Oracle
II. Using jdbcKona/Oracle
In general, connecting happens in two steps:
Note that both steps describes the JDBC driver, but in a different
format. The full package name There are several variations on this basic pattern which are described here for Oracle. For a full code example, check the beginning sections in the Implementation guide in the overview, Using the jdbcKona family of JDBC drivers. Method 1The simplest way is by passing the URL of the driver that includes the name of the server, along with a username and password, as arguments to the DriverManager.getConnection() method, as in this jdbcKona/Oracle example: Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle:DEMO",
"scott",
"tiger");
where DEMO is the V2 alias of
an Oracle database. Note that the calling Class.forName().newInstance() properly loads and
registers the driver.
Method 2You can also pass a java.util.Properties object with parameters for connection as an argument to the DriverManager.getConnection() method. In this example we illustrate how to connect to the DEMO database: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
If you do not supply a server name ("DEMO" in the example above), the
system will look for an environment variable (ORACLE_SID in the case
of Oracle). You may also choose to add the server name to the URL,
with this format:
"jdbc:weblogic:oracle:DEMO"in which case you do not need to provide a "server" property. You can also set properties in a single URL, for use with products like PowerSoft's PowerJ. For details, see Using URLs with WebLogic products. Method 3If you prefer, you can load the JDBC driver from the command line with the command:
$ java -Djdbc.drivers=weblogic.jdbc.oci.Driver classname where classname is the name of the application you want to run; and then use a Properties object to set parameters necessary for connecting to the DBMS. In this case, you will not need to call the Class.forName().newInstance() method, as shown here: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
Other properties you can set for jdbcKona/OracleThere are other properties that you can set for jdbcKona/Oracle that are covered later in this document. jdbcKona/Oracle also allows setting a property -- allowMixedCaseMetaData -- to the boolean true. This property sets up the Connection to use mixed case in calls to DatabaseMetaData methods. Otherwise, Oracle defaults to UPPERCASE for database meta data. Here is an example of setting up the properties to include this feature: Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
props.put("allowMixedCaseMetaData", "true");
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
If you do not set this property, jdbcKona/Oracle defaults to the Oracle default, which uses UPPERCASE for database meta data. Setting properties for WebLogic JDBC use
If you are using WebLogic JDBC in a multitier General notesAlways call the Connection.close()method to close the Connection when you have finished working with it. Closing objects releases resources on the remote DBMS and within your application, as well as being just good programming practice. Other jdbcKona objects that you should close() after final use:
With release 2.5, WebLogic's jdbcKona/Oracle driver supports Oracle's oopt() C functionality, which allows a client to wait until resources become available. The Oracle C function sets options in cases where requested resources are not available; for example, whether to wait for locks. The developer can set whether a client will wait for DBMS resources, or will receive an immediate exception. Here is an example from the example examples/jdbc/oracle/waiton.java: java.util.Properties props = new java.util.Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "goldengate");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
// You must cast the Connection as a weblogic.jdbc.oci.Connection
// to take advantage of this extension
Connection conn =
(weblogic.jdbc.oci.Connection)
DriverManager.getConnection("jdbc:weblogic:oracle", props);
// After constructing the Connection object, immediately call
// the waitOnResources method
conn.waitOnResources(true);
Note that use of this method can cause several error return codes while waiting for internal resources that are locked for short durations. To take advantage of this feature, you must first cast your Connection object as a weblogic.jdbc.oci.Connection, and then call the waitOnResources() method. This functionality is described in section 4-97 of The OCI Functions for C.
The default transaction mode for JDBC assumes autocommit to be true. You will improve the performance of your programs by setting autocommit to false after creating a Connection object with the statement: Connection.setAutoCommit(false);
jdbcKona/Oracle provides a way to set the codeset from within your
Java code. By setting a connection property, weblogic.codeset, you can override the value stored in
the NLS_LANG environment
variable.
For example, to use the cp932 codeset, create a Properties object and
set the weblogic.codeset property before
calling java.util.Properties props = new java.util.Properties();
props.put("weblogic.codeset", "cp932");
props.put("user", "scott");
props.put("password", "tiger");
String connectUrl = "jdbc:weblogic:oracle";
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection(connectUrl, props);
Note that codeset support can vary with different JVMs
There is a matching property for use in a multitier environment when using jdbcKona/Oracle with WebLogic JDBC: weblogic.t3.blobChunkSize. This defines the buffer size used for streaming blobs between WebLogic and the WebLogic JDBC client
You set a property (java.util.Property) to control the size of the array fetch. The property is weblogic.oci.cacheRows; it is set by default to 100. Here is an example of setting this property to 300, which means that calls to next() only hit the database once for each 300 rows retrieved by the client. Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
props.put("weblogic.oci.cacheRows", "300");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
You can improve client performance and lower the load on the database server by taking advantage of this JDBC extension. Caching rows in the client, however, requires client resources. You should tune your application for the best balance between performance and client resources, depending upon your network configuration and your application. If any columns in a SELECT are of type LONG, the cache size will be temporarily reset to one (1) for the ResultSet associated with that select statement.
Syntax for stored procedures in jdbcKona/OracleWith release 2.5, the syntax for stored procedures in Oracle was altered in the jdbcKona/Oracle examples to match the JDBC specification. (All of the examples also show native Oracle SQL, commented out, just above the correct usage; the native Oracle syntax works as it did in the past.) You can read up on general notes on stored procedures for the jdbcKona drivers in the Overview of the jdbcKona family of JDBC drivers. Note that Oracle does not natively support binding to "?" values in a SQL statement. Instead it uses ":1", ":2", etc. We allow you to use either in your SQL with jdbcKona/Oracle. Binding a parameter to an Oracle cursorWebLogic has created an extension to JDBC (weblogic.jdbc.oci.CallableStatement) that allows you to bind a parameter for a stored procedure to an Oracle cursor. You can create a JDBC ResultSet object with the results of the stored procedure. This allows you to return multiple ResultSets in an organized way. The ResultSets are determined at run time in the stored procedure. Here is an example. First define the stored procedures as follows: create or replace package
curs_types as
type EmpCurType is REF CURSOR RETURN emp%ROWTYPE;
end curs_types;
/
create or replace procedure
single_cursor(curs1 IN OUT curs_types.EmpCurType,
ctype in number) AS BEGIN
if ctype = 1 then
OPEN curs1 FOR SELECT * FROM emp;
elsif ctype = 2 then
OPEN curs1 FOR SELECT * FROM emp where sal > 2000;
elsif ctype = 3 then
OPEN curs1 FOR SELECT * FROM emp where deptno = 20;
end if;
END single_cursor;
/
create or replace procedure
multi_cursor(curs1 IN OUT curs_types.EmpCurType,
curs2 IN OUT curs_types.EmpCurType,
curs3 IN OUT curs_types.EmpCurType) AS
BEGIN
OPEN curs1 FOR SELECT * FROM emp;
OPEN curs2 FOR SELECT * FROM emp where sal > 2000;
OPEN curs3 FOR SELECT * FROM emp where deptno = 20;
END multi_cursor;
/
In your Java code, you will construct CallableStatements with the stored
procedures and register the output parameter as data type
java.sql.Types.OTHER. When you retrieve the data into a ResultSet, use
the output parameter index as an argument for the getResultSet() method.
weblogic.jdbc.oci.CallableStatement cstmt =
(weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
"BEGIN OPEN ? " +
"FOR select * from emp; END;");
cstmt.registerOutParameter(1, java.sql.Types.OTHER);
cstmt.execute();
ResultSet rs = cstmt.getResultSet(1);
printResultSet(rs);
rs.close();
cstmt.close();
weblogic.jdbc.oci.CallableStatement cstmt2 =
(weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
"BEGIN single_cursor(?, ?); END;");
cstmt2.registerOutParameter(1, java.sql.Types.OTHER);
cstmt2.setInt(2, 1);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
cstmt2.setInt(2, 2);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
cstmt2.setInt(2, 3);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
cstmt2.close();
weblogic.jdbc.oci.CallableStatement cstmt3 =
(weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
"BEGIN multi_cursor(?, ?, ?); END;");
cstmt3.registerOutParameter(1, java.sql.Types.OTHER);
cstmt3.registerOutParameter(2, java.sql.Types.OTHER);
cstmt3.registerOutParameter(3, java.sql.Types.OTHER);
cstmt3.execute();
ResultSet rs1 = cstmt3.getResultSet(1);
ResultSet rs2 = cstmt3.getResultSet(2);
ResultSet rs3 = cstmt3.getResultSet(3);
For the full code for this example, including the printResultSet() method, see the
examples in the examples/jdbc/oracle/ directory.
Note that the default size of an Oracle stored procedure string is 256K. Notes on using CallableStatementThe default length of a string bound to an OUTPUT parameter of a CallableStatement is 128 characters. If the value you assign to the bound parameter exceeds that length, you will get the following error: ORA-6502: value or numeric error You can adjust the length of the value of the bound parameter by passing an explicit length with the scale argument to the CallableStatement.registerOutputParameter() method. Here is a code example that binds a VARCHAR that will never be larger than 256 characters: CallableStatement cstmt =
conn.prepareCall("BEGIN testproc(?); END;");
cstmt.registerOutputParameter(1, Types.VARCHAR, 256);
cstmt.execute();
System.out.println(cstmt.getString());
cstmt.close();
Oracle provides a column type called NUMBER, which can be optionally specified with a precision and a scale, in the forms NUMBER(P) and NUMBER(P,S). Even in the simple unqualified NUMBER form, this column can hold all number types from small integer values to very large floating point numbers, with high precision. WebLogic's jdbcKona/Oracle reliably converts the values in a column to the Java type requested when a jdbcKona/Oracle application asks for a value from such a column. Of course, if a value of 123.456 is asked for with getInt(), the value will be rounded. The method getObject(), however, poses a little more complexity. jdbcKona/Oracle guarantees to return a Java object which will represent any value in a NUMBER column with no loss in precision. This means that a value of 1 can be returned in an Integer, but a value like 123434567890.123456789 can only be returned in a BigDecimal. There is no metadata from Oracle to report the maximum precision of the values in the column, so jdbcKona/Oracle must decide what sort of object to return based on each value. This means that one ResultSet may return multiple Java types from getObject() for a given NUMBER column. A table full of integer values may all be returned as Integer from getObject(), whereas a table of floating point measurements may be returned primarily as Double, with some Integer if any value happens to be something like "123.00". Oracle does not provide any information to distinguish between a NUMBER value of "1" and a NUMBER of "1.0000000000". There is some more reliable behavior with qualified NUMBER columns, that is, those defined with a specific precision. Oracle's metadata provides these parameters to the driver so jdbcKona/Oracle will always return a Java object appropriate for the given precision and scale, regardless of the values in the table.
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|
