|
 |
| e-docs > WebLogic Server > Programming WebLogic JDBC > Using Third-Party Drivers with WebLogic Server |
|
Programming WebLogic JDBC
|
Using Third-Party Drivers with WebLogic Server
The following sections describe how to set up and use third-party JDBC drivers:
Overview of Third-Party JDBC Drivers
WebLogic Server works with third-party JDBC drivers that offer the following functionality:
This section describes how to set up and use the following third-party JDBC drivers with WebLogic Server:
In WebLogic Server version 6.1, a version of the Oracle Thin Driver and the Sybase jConnect Driver were bundled within weblogic.jar. In version 7.x, third-party JDBC drivers are no longer bundled within weblogic.jar. Instead, the 10g version of the Oracle Thin driver (classes12.zip) and the 4.5 (jConnect.jar) and 5.5 (jconn2.jar) versions of the Sybase jConnect driver are installed in the WL_HOME\server\lib folder (where WL_HOME is the folder where WebLogic Platform is installed) with weblogic.jar. The manifest in weblogic.jar lists these files so that they are loaded when weblogic.jar is loaded (when the server starts).
Note: In WebLogic Server 7.0SP5, the default version of the Oracle Thin driver was changed to the 10g driver (the version in WL_HOME\server\lib). In WebLogic Server 7.0SP2, SP3, and SP4, the 9.2.0 version of the Oracle Thin driver was the default version of the driver. In releases of WebLogic Server 7.0 prior to the Service Pack 2 release, the 8.1.7 version of the Oracle Thin driver was the default version.
The WL_HOME\server\ext\jdbc folder (where WL_HOME is the folder where WebLogic Platform is installed) of your WebLogic Server installation includes subfolders for Oracle and Sybase JDBC drivers. See Figure 5-1.
Figure 5-1 Directory Structure for JDBC Drivers Installed with WebLogic Server
The oracle folder includes versions of the Oracle Thin driver, including the 10g version, which is also included in the WL_HOME\server\lib folder, as previously mentioned. You can copy one of these files to the WL_HOME\server\lib folder to change the version of the Oracle Thin driver or to revert to the default version. See Changing or Updating the Oracle Thin Driver for more details.
The sybase folder contains the 4.5 version of the Sybase jConnect driver and a subfolder with the 5.5 version of the Sybase jConnect driver and other supporting files. These drivers—jConnect.jar and jconn2.jar, without the directory structure and additional supporting files—are also included in the WL_HOME\server\lib folder, as previously mentioned. WebLogic Server uses the files in the WL_HOME\server\lib folder during runtime. You can use the additional copies in the WL_HOME\server\ext\jdbc\sybase folder as a backup in the event that you update the drivers with a defective or unsupported version of the driver.
If you plan to use the default version of these drivers, you do not need to make any changes. If you plan to use a different version of these drivers, you must replace the files in WL_HOME\server\lib with a file from WL_HOME\server\ext\jdbc\oracle\version, where version is the version of the JDBC driver you want to use, or with a file from the DBMS vendor—Oracle or Sybase.
Because the manifest in weblogic.jar lists the class files for the Oracle Thin driver and Sybase jConnect driver in WL_HOME\server\lib, the drivers are loaded when weblogic.jar is loaded (when the server starts). Therefore, you do not need to add the JDBC driver to your CLASSPATH. If you plan to use a third-party JDBC driver that is not installed with WebLogic Server, you must add the path to the driver files to your CLASSPATH.
Setting the Environment for Your Third-Party JDBC Driver
If you use a third-party JDBC driver other than the Oracle Thin Driver or Sybase jConnect Driver included in the WebLogic Server 7.0 installation, you must add the path for the JDBC driver classes to your CLASSPATH. The following sections describe how to set your CLASSPATH for Windows and UNIX when using a third-party JDBC driver.
CLASSPATH for Third-Party JDBC Driver on Windows
Include the path to JDBC driver classes and to weblogic.jar in your CLASSPATH as follows:
set CLASSPATH=DRIVER_CLASSES;WL_HOME\server\lib\weblogic.jar;
%CLASSPATH%
Where DRIVER_CLASSES is the path to the JDBC driver classes and WL_HOME is the directory where you installed WebLogic Platform.
CLASSPATH for Third-Party JDBC Driver on UNIX
Add the path to JDBC driver classes and to weblogic.jar to your CLASSPATH as follows:
export CLASSPATH=DRIVER_CLASSES:WL_HOME/server/lib/weblogic.jar:
$CLASSPATH
Where DRIVER_CLASSES is the path to the JDBC driver classes and WL_HOME is the directory where you installed WebLogic Platform.
Changing or Updating the Oracle Thin Driver
WebLogic Server ships with the Oracle Thin Driver version 10g (10.1.0.2.0) preconfigured and ready to use. To use a different version, you replace WL_HOME\server\lib\classes12.zip with a different version of the file. For example, if you want to use the 9.2.0 version of the Oracle Thin Driver, you must copy classes12.zip from the WL_HOME\server\ext\jdbc\oracle\920 folder and place it in WL_HOME\server\lib to replace the 10g version in that folder.
Note: In WebLogic Server 7.0SP5, the default version of the Oracle Thin driver was changed to the 10g driver (the version in WL_HOME\server\lib). In WebLogic Server 7.0SP2, SP3, and SP4, the 9.2.0 version of the Oracle Thin driver was the default version of the driver. In releases of WebLogic Server 7.0 prior to the Service Pack 2 release, the 8.1.7 version of the Oracle Thin driver was the default version.
Follow these instructions to use Oracle Thin Driver version 9.2.0, 9.0.1, or 8.1.7:
To revert to version 10g (the default), follow the instructions above, but copy from the following folder: WL_HOME\server\ext\jdbc\oracle\10g.
To update a version of the Oracle Thin driver with a new version from Oracle, replace classes12.zip in WL_HOME\server\lib with the new file from Oracle. You can download driver updates from the Oracle Web site at http://otn.oracle.com/software/content.html.
Note: You cannot include multiple versions of the Oracle Thin driver in your CLASSPATH. Doing so may cause clashes for various methods.
Package Change for Oracle Thin Driver 9.x and 10g
For Oracle 8.x and previous releases, the package that contained the Oracle Thin driver was oracle.jdbc.driver. When configuring a JDBC connection pool that uses the Oracle 8.1.7 Thin driver, you specify the DriverName (Driver Classname) as oracle.jdbc.driver.OracleDriver. For Oracle 9.x and 10g, the package that contains the Oracle Thin driver is oracle.jdbc. When configuring a JDBC connection pool that uses the Oracle 9.x or 10g Thin driver, you specify the DriverName (Driver Classname) as oracle.jdbc.OracleDriver. You can use the oracle.jdbc.driver.OracleDriver class with the 9.x and 10g drivers, but Oracle may not make future feature enhancements to that class.
See the Oracle documentation for more details about the Oracle Thin driver.
Note: The package change does not apply to the XA version of the driver. For the XA version of the Oracle Thin driver, use oracle.jdbc.xa.client.OracleXADataSource as the DriverName (Driver Classname) in a JDBC connection pool.
Character Set Support with nls_charset12.zip
The Oracle Thin driver includes Globalization Support for all Oracle character sets for CHAR and NCHAR datatypes not retrieved or inserted as part of an Oracle object or collection type.
However, in the case of the CHAR and VARCHAR data portion of Oracle objects and collections, the Oracle Thin driver includes Globalization Support support for only the following character sets:
If you use other character sets with CHAR and NCHAR data in Oracle object types and collections, you must include nls_charset.zip in your CLASSPATH. If this file is not in your CLASSPATH, you will see the following exception:
java.sql.SQLException: Non supported character set: oracle-character-set-178
The nls_charset12.zip file is installed with WebLogic Server in the WL_HOME\server\ext\jdbc\oracle\920 and WL_HOME\server\ext\jdbc\oracle\10g folders (where WL_HOME is the folder where WebLogic Server is installed). See Setting the Environment for Your Third-Party JDBC Driver for instructions to set your CLASSPATH.
Updating Sybase jConnect Driver
WebLogic Server ships with the Sybase jConnect driver versions 4.5 and 5.5 preconfigured and ready to use. To use a different version, you replace WL_HOME\server\lib\jConnect.jar or jconn2.jar with a different version of the file from the DBMS vendor.
To revert to versions installed with WebLogic Server, copy the following files and place them in the WL_HOME\server\lib folder:
Installing and Using the IBM Informix JDBC Driver
If you want to use Weblogic Server with an Informix database, BEA recommends that you use the IBM Informix JDBC driver, available from the IBM Web site at http://www-3.ibm.com/software/data/informix/tools/jdbc/. The IBM Informix JDBC driver is available to use for free without support. You may have to register with IBM to download the product. Download the driver from the JDBC/EMBEDDED SQLJ section, and follow the instructions in the install.txt file included in the downloaded zip file to install the driver.
After you download and install the driver, follow these steps to prepare to use the driver with WebLogic Server:
set CLASSPATH=%WL_HOME%\server\lib\ifxjdbc.jar;%WL_HOME%\server\lib\ifxjdbcx.jar;%CLASSPATH%
Connection Pool Attributes when using the IBM Informix JDBC Driver
Use the attributes as described in Table 5-1 and Table 5-2 when creating a connection pool that uses the IBM Informix JDBC driver.
An entry in the config.xml file may look like the following:
<JDBCConnectionPool
DriverName="com.informix.jdbc.IfxDriver"
InitialCapacity="3"
LoginDelaySeconds="1"
MaxCapacity="10"
Name="ifxPool"
Password="xxxxxxx"
Properties="informixserver=ifxserver;user=informix"
Targets="examplesServer"
URL="jdbc:informix-sqli:ifxserver:1543"
/>
Note: In the Properties string, there is a space between portNumber and =.
An entry in the config.xml file may look like the following:
<JDBCConnectionPool CapacityIncrement="2"
DriverName="com.informix.jdbcx.IfxXADataSource"
InitialCapacity="2" MaxCapacity="10"
Name="informixXAPool"
Properties="user=informix;url=jdbc:informix-sqli:
//111.11.11.11:1543/db1:informixserver=lcsol15;
password=informix;portNumber =1543;databaseName=db1;
serverName=dbserver1;ifxIFXHOST=111.11.11.11"
SupportsLocalTransaction="true" Targets="examplesServer"
TestConnectionsOnReserve="true" TestTableName="emp"/>
Note: If you create the connection pool using the Administration Console, you may need to stop and restart the server before the connection pool will deploy properly on the target server. This is a known issue.
Programming Notes for the IBM Informix JDBC Driver
Consider the following limitations when using the IBM Informix JDBC driver:
Installing and Using the SQL Server 2000 Driver for JDBC from Microsoft
The Microsoft SQL Server 2000 Driver for JDBC is available for download to all licensed SQL Server 2000 customers at no charge. The driver is a Type 4 JDBC driver that supports a subset of the JDBC 2.0 Optional Package. When you install the Microsoft SQL Server 2000 Driver for JDBC, the supporting documentation is optionally installed with it. You should refer to that documentation for the most comprehensive information about the driver. Also, see the release manifest for known issues.
Installing the MS SQL Server JDBC Driver on a Windows System
Follow these instructions to install the SQL Server 2000 Driver for JDBC on a Windows server:
Installing the MS SQL Server JDBC Driver on a Unix System
Follow these instructions to install the SQL Server 2000 Driver for JDBC on a UNIX server:
tar -xvf mssqlserver.tar
install.ksh
Connection Pool Attributes when using the Microsoft SQL Server Driver for JDBC
Use the following attributes when creating a connection pool that uses the Microsoft SQL Server Driver for JDBC:
user=<myuserid>
databaseName=<dbname>
selectMethod=cursor
An entry in the config.xml file may look like the following:
<JDBCConnectionPool
Name="mssqlDriverTestPool"
DriverName="com.microsoft.jdbc.sqlserver.SQLServerDriver"
URL="jdbc:microsoft:sqlserver://lcdbnt4:1433"
Properties="databasename=lcdbnt4;user=sa;
selectMethod=cursor"
Password="{3DES}vlsUYhxlJ/I="
InitialCapacity="4"
CapacityIncrement="2"
MaxCapacity="10"
Targets="examplesServer"
/>
Note: You must add selectMethod=cursor to the list of connection properties in order to use connections in a transactional mode. This enables your applications to have multiple concurrent statements open from a given connection, which is required for pooled connections.
Without setting selectMethod=cursor, this JDBC driver creates an internal cloned connection for each concurrent statement, each as a different DBMS user. This makes it impossible to concurrently commit transactions and may cause deadlocks.
Getting a Connection with Your Third-Party Driver
The following sections describe how to get a database connection using a third-party, Type 4 driver, such as the Oracle Thin Driver and Sybase jConnect Driver. BEA recommends that you use connection pools, data sources, and a JNDI lookup to establish your connection.
Using Connection Pools with a Third-Party Driver
First, you create the connection pool and data source using the Administration Console, then establish a connection using a JNDI Lookup.
Creating the Connection Pool and DataSource
See Configuring and Using Connection Pools and Configuring and Using DataSources for instructions to create a JDBC connection pool and a JDBC DataSource.
Using a JNDI Lookup to Obtain the Connection
To access the driver using JNDI, obtain a Context from the JNDI tree by providing the URL of your server, and then use that context object to perform a lookup using the DataSource Name.
For example, to access a DataSource called "myDataSource" that is defined in the Administration Console:
Context ctx = null;
Hashtable ht = new Hashtable();
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://hostname:port");
try {
ctx = new InitialContext(ht);
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myDataSource");
java.sql.Connection conn = ds.getConnection();// You can now use the conn object to create
// a Statement object to execute
// SQL statements and process result sets:
Statement stmt = conn.createStatement();
stmt.execute("select * from someTable");
ResultSet rs = stmt.getResultSet();
// Do not forget to close the statement and connection objects
// when you are finished:
stmt.close();
conn.close();
}
catch (NamingException e) {
// a failure occurred
}
finally {
try {ctx.close();}
catch (Exception e) {
// a failure occurred
}
}
(Where hostname is the name of the machine running your WebLogic Server and port is the port number where that machine is listening for connection requests.)
In this example a Hashtable object is used to pass the parameters required for the JNDI lookup. There are other ways to perform a JNDI lookup. For more information, see Programming WebLogic JNDI.
Notice that the JNDI lookup is wrapped in a try/catch block in order to catch a failed look up and also that the context is closed in a finally block.
Getting a Physical Connection from a Connection Pool
When you get a connection from a connection pool, WebLogic Server provides a logical connection rather than a physical connection so that WebLogic Server can manage the connection with the connection pool. This is necessary to enable connection pool features and to maintain the quality of connections provided to applications. In some cases, you may want to use a physical connection, such as if you need to pass the connection to a DBMS vendor-specific method that requires the vendor's connection class. WebLogic Server includes the getVendorConnection() method in the weblogic.jdbc.extensions.WLConnection interface that you can use to get the underlying physical connection from a logical connection. See the WebLogic Javadocs.
Note: BEA strongly discourages using a physical connection instead of a logical connection from a connection pool. See Limitations for Using a Physical Connection.
You should only use the physical database connection for vendor-specific needs. Your code should continue to make most JDBC calls to the logical connection.
When you are finished with the connection, you should close the logical connection. Do not close the physical connection in your code.
Whenever a physical database connection is exposed to application code, the connection pool cannot guarantee that the next user of that connection will be the only user with access to it. Therefore, when the logical connection is closed, WebLogic Server returns the logical connection to the connection pool, but discards the underlying physical connection and opens a new physical connection for the logical connection in the pool. This is safe, but it is also slow. It is possible that every request to the connection pool will entail making a new database connection.
Code Sample for Getting a Physical Connection
To get a physical database connection, you first get a connection from a connection pool as described in Using a JNDI Lookup to Obtain the Connection, then do one of the following:
//Import this additional class and any vendor packages
//you may need.
import weblogic.jdbc.extensions.WLConnection
.
.
.
myJdbcMethod()
{
// Connections from a connection pool should always be
// method-level variables, never class or instance methods.
Connection conn = null;
try {
ctx = new InitialContext(ht);
// Look up the data source on the JNDI tree and request
// a connection.
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myDataSource"); // Always get a pooled connection in a try block where it is
// used completely and is closed if necessary in the finally
// block.
conn = ds.getConnection();
// You can now cast the conn object to a WLConnection
// interface and then get the underlying physical connection.
java.sql.Connection vendorConn =
((WLConnection)conn).getVendorConnection();
// do not close vendorConn
// You could also cast the vendorConn object to a vendor
// interface, such as:
// oracle.jdbc.OracleConnection vendorConn = (OracleConnection)
// ((WLConnection)conn).getVendorConnection()
// If you have a vendor-specific method that requires the
// physical connection, it is best not to obtain or retain
// the physical connection, but simply pass it implicitly
// where needed, eg:
//vendor.special.methodNeedingConnection(((WLConnection)conn)).getVendorConnection());
// As soon as you are finished with vendor-specific calls,
// nullify the reference to the connection.
// Do not keep it or close it.
// Never use the vendor connection for generic JDBC.
// Use the logical (pooled) connection for standard JDBC.
vendorConn = null;
... do all the JDBC needed for the whole method...
// close the logical (pooled) connection to return it to
// the connection pool, and nullify the reference.
conn.close();
conn = null;
}
catch (Exception e)
{
// Handle the exception.
}
finally
{
// For safety, check whether the logical (pooled) connection
// was closed.
// Always close the logical (pooled) connection as the
// first step in the finally block.
if (conn != null) try {conn.close();} catch (Exception ignore){}
}
}
Limitations for Using a Physical Connection
BEA strongly discourages using a physical connection instead of a logical connection from a connection pool. However, if you must use a physical connection, for example, to create a STRUCT, consider the following costs and limitations:
Using Oracle Extensions with the Oracle Thin Driver
Oracle extensions provide additional proprietary methods for working with data from an Oracle database. These methods extend the standard JDBC interfaces. BEA supports the following Oracle extensions for use with the Oracle Thin driver or another driver that supports these extensions:
The following sections provide code samples for Oracle extensions and tables of supported methods. For more information, please refer to the Oracle documentation.
Limitations When Using Oracle JDBC Extensions
Please note the following limitations when using Oracle extensions to JDBC interfaces:
Sample Code for Accessing Oracle Extensions to JDBC Interfaces
The following code examples show how to access the WebLogic Oracle extensions to standard JDBC interfaces. The first example uses the OracleConnection and OracleStatement extensions. You can use the syntax of this example for the OracleResultSet, OraclePreparedStatement, and OracleCallableStatement interfaces, when using methods supported by WebLogic Server. For supported methods, see Tables of Oracle Extension Interfaces and Supported Methods.
For examples showing how to access other Oracle extension methods, see the following sections:
If you selected the option to install server examples with WebLogic Server, see the JDBC examples, typically at WL_HOME\samples\server\src\examples\jdbc, where WL_HOME is the folder where you installed WebLogic Platform.
Import Packages to Access Oracle Extensions
Import the Oracle interfaces used in this example. The OracleConnection and Oracle Statement interfaces are counterparts to oracle.jdbc.OracleConnection and oracle.jdbc.OracleStatement and can be used in the same way as the Oracle interfaces when using the methods supported by WebLogic Server.
import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import weblogic.jdbc.vendor.oracle.*;
Establish the database connection using JNDI, DataSource and connection pool objects. For information, see Using a JNDI Lookup to Obtain the Connection.
// Get a valid DataSource object for a connection pool.
// Here we assume that getDataSource() takes
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();
Retrieve the Default Row Prefetch Value
The following code fragment shows how to use the Oracle Row Prefetch method available through the Oracle Thin Driver.
// Cast to OracleConnection and retrieve the
// default row prefetch value for this connection.
int default_prefetch =
((OracleConnection)conn).getDefaultRowPrefetch();
System.out.println("Default row prefetch
is " + default_prefetch);java.sql.Statement stmt = conn.createStatement();
// Cast to OracleStatement and set the row prefetch
// value for this statement. Note that this
// prefetch value applies to the connection between
// WebLogic Server and the database.
((OracleStatement)stmt).setRowPrefetch(20);
// Perform a normal sql query and process the results...
String query = "select empno,ename from emp";
java.sql.ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
java.math.BigDecimal empno = rs.getBigDecimal(1);
String ename = rs.getString(2);
System.out.println(empno + "\t" + ename);
}rs.close();
stmt.close();
conn.close();
conn = null;
}
In your WebLogic Server server-side applications, you can materialize an Oracle Collection (a SQL ARRAY) in a result set or from a callable statement as a Java array.
To use ARRAYs in WebLogic Server applications:
The following sections provide more details for these actions.
Note: You can use ARRAYs in server-side applications only. You cannot use ARRAYs in client applications.
You can use the getArray() methods for a callable statement or a result set to get a Java array. You can then use the array as a java.sql.array to use standard java.sql.array methods, or you can cast the array as a weblogic.jdbc.vendor.oracle.OracleArray to use the Oracle extension methods for an array.
The following example shows how to get a java.sql.array from a result set that contains an ARRAY. In the example, the query returns a result set that contains an object column—an ARRAY of test scores for a student.
try {conn = getConnection(url);
stmt = conn.createStatement();
String sql = "select * from students";
//Get the result set
rs = stmt.executeQuery(sql);
while(rs.next()) {
BigDecimal id = rs.getBigDecimal("student_id");
String name = rs.getString("name");
log("ArraysDAO.getStudents() -- Id = "+id.toString()+", Student = "+name);
//Get the array from the result set
Array scoreArray = rs.getArray("test_scores");
String[] scores = (String[])scoreArray.getArray();
for (int i = 0; i < scores.length; i++) {
log(" Test"+(i+1)+" = "+scores[i]);
}
}Updating ARRAYs in the Database
To update an ARRAY in a database, you can Follow these steps:
String sqlUpdate = "UPDATE SCOTT." + tableName + " SET col1 = ?";
conn = ds.getConnection();
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setArray(1, array);
pstmt.executeUpdate();
Using Oracle Array Extension Methods
To use the Oracle extension methods for an ARRAY, you must first cast the array as a weblogic.jdbc.vendor.oracle.OracleArray. You can then make calls to the Oracle extension methods for ARRAYs. For example:
oracle.sql.Datum[] oracleArray = null;
oracleArray = ((weblogic.jdbc.vendor.oracle.OracleArray)scoreArray).getOracleArray();
String sqltype = null
sqltype = oracleArray.getSQLTypeName()
In your WebLogic Server applications, you can access and manipulate objects from an Oracle database. When you retrieve objects from an Oracle database, you can cast them as either custom Java objects or as STRUCTs (java.sql.struct or weblogic.jdbc.vendor.oracle.OracleStruct). A STRUCT is a loosely typed data type for structured data which takes the place of custom classes in your applications. The STRUCT interface in the JDBC API includes several methods for manipulating the attribute values in a STRUCT. Oracle extends the STRUCT interface with several additional methods. WebLogic Server implements all of the standard methods and most of the Oracle extensions.
Note: Please note the following limitations when using STRUCTs:
To use STRUCTs in WebLogic Server applications:
The following sections provide more details for steps 3 through 5.
To get a database object as a STRUCT, you can use a query to create a result set and then use the getObject method to get the STRUCT from the result set. You then cast the STRUCT as a java.sql.Struct so you can use the standard Java methods. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");struct = (java.sql.Struct)(rs.getObject(2));
Object[] attrs = ((java.sql.Struct)struct).getAttributes();
WebLogic Server supports all of the JDBC API methods for STRUCTs:
Oracle supports the standard methods as well as the Oracle extensions. Therefore, when you cast a STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct, you can use both the standard and extension methods.
Using OracleStruct Extension Methods
To use the Oracle extension methods for a STRUCT, you must cast the java.sql.Struct (or the original getObject result) as a weblogic.jdbc.vendor.oracle.OracleStruct. For example:
java.sql.Struct struct =
(weblogic.jdbc.vendor.oracle.OracleStruct)(rs.getObject(2));
WebLogic Server supports the following Oracle extensions:
To get the value for an individual attribute in a STRUCT, you can use the standard JDBC API methods getAttributes() and getAttributes(java.util.Dictionary map), or you can use the Oracle extension method getOracleAttributes().
To use the standard method, you can create a result set, get a STRUCT from the result set, and then use the getAttributes() method. The method returns an array of ordered attributes. You can assign the attributes from the STRUCT (object in the database) to an object in the application, including Java language types. You can then manipulate the attributes individually. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");//The third column uses an object data type.
//Use getObject() to assign the object to an array of values.
struct = (java.sql.Struct)(rs.getObject(2));
Object[] attrs = ((java.sql.Struct)struct).getAttributes();
String address = attrs[1];
In the preceding example, the third column in the people table uses an object data type. The example shows how to assign the results from the getObject method to a Java object that contains an array of values, and then use individual values in the array as necessary.
You can also use the getAttributes(java.util.Dictionary map) method to get the attributes from a STRUCT. When you use this method, you must provide a hash table to map the data types in the Oracle object to Java language data types. For example:
java.util.Hashtable map = new java.util.Hashtable();
map.put("NUMBER", Class.forName("java.lang.Integer"));map.put("VARCHAR", Class.forName("java.lang.String"));Object[] attrs = ((java.sql.Struct)struct).getAttributes(map);
String address = attrs[1];
You can also use the Oracle extension method getOracleAttributes() to get the attributes for a STRUCT. You must first cast the STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct. This method returns a datum array of oracle.sql.Datum objects. For example:
oracle.sql.Datum[] attrs =
((weblogic.jdbc.vendor.oracle.OracleStruct)struct).getOracleAttributes();
oracle.sql.STRUCT address = (oracle.sql.STRUCT) attrs[1];
Object address_attrs[] = address.getAttributes();
The preceding example includes a nested STRUCT. That is, the second attribute in the datum array returned is another STRUCT.
Using STRUCTs to Update Objects in the Database
To update an object in the database using a STRUCT, you can use the setObject method in a prepared statement. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
ps = conn.prepareStatement ("UPDATE SCHEMA.people SET EMPLNAME = ?,
EMPID = ? where EMPID = 101");ps.setString (1, "Smith");
ps.setObject (2, struct);
ps.executeUpdate();
WebLogic Server supports all three versions of the setObject method.
Creating Objects in the Database
STRUCTs are typically used to materialize database objects in your Java application in place of custom Java classes that map to the database objects. In WebLogic Server applications, you cannot create STRUCTs that transfer to the database. However, you can use statements to create objects in the database that you can then retrieve and manipulate in your application. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);
Note: You cannot create STRUCTs in your applications. You can only retrieve existing objects from a database and cast them as STRUCTs. To create STRUCT objects in your applications, you must use a non-standard Oracle STRUCT descriptor object, which is not supported in WebLogic Server.
Automatic Buffering for STRUCT Attributes
To enhance the performance of your WebLogic Server applications that use STRUCTs, you can toggle automatic buffering with the setAutoBuffering(boolean) method. When automatic buffering is set to true, the weblogic.jdbc.vendor.oracle.OracleStruct object keeps a local copy of all the attributes in the STRUCT in their converted form (materialized from SQL to Java language objects). When your application accesses the STRUCT again, the system does not have to convert the data again.
Note: Buffering the converted attributes my cause your application to use an excessive amount of memory. Consider potential memory usage when deciding to enable or disable automatic buffering.
The following example shows how to activate automatic buffering:
((weblogic.jdbc.vendor.oracle.OracleStruct)struct).setAutoBuffering(true);
You can also use the getAutoBuffering() method to determine the automatic buffering mode.
A REF is a logical pointer to a row object. When you retrieve a REF, you are actually getting a pointer to a value in another table. The REF target must be a row in an object table. You can use a REF to examine or update the object it refers to. You can also change a REF so that it points to a different object of the same object type or assign it a null value.
Note: Please note the following limitations when using REFs:
To use REFs in WebLogic Server applications, follow these steps:
You can also create and update a REF in the database.
The following sections describe these steps 3 and 4 in greater detail.
To get a REF in an application, you can use a query to create a result set and then use the getRef method to get the REF from the result set. You then cast the REF as a java.sql.Ref so you can use the built-in Java method. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");rs.next();
//Cast as a java.sql.Ref and get REF
ref = (java.sql.Ref) rs.getRef(1);
Note that the WHERE clause in the preceding example uses dot notation to specify the attribute in the referenced object.
After you cast the REF as a java.sql.Ref, you can use the Java API method getBaseTypeName, the only JDBC 2.0 standard method for REFs.
When you get a REF, you actually get a pointer to a value in an object table. To get or manipulate REF values, you must use the Oracle extensions, which are only available when you cast the sql.java.Ref as a weblogic.jdbc.vendor.oracle.OracleRef.
Using OracleRef Extension Methods
In order to use the Oracle extension methods for REFs, you must cast the REF as an Oracle REF. For example:
oracle.sql.StructDescriptor desc = ((weblogic.jdbc.vendor.oracle.OracleRef)ref).getDescriptor();
WebLogic Server supports the following Oracle extensions:
Oracle provides two versions of the getValue() method—one that takes no parameters and one that requires a hash table for mapping return types. When you use either version of the getValue() method to get the value of an attribute in a REF, the method returns a either a STRUCT or a Java object.
The example below shows how to use the getValue() method without parameters. In this example, the REF is cast as an oracle.sql.STRUCT. You can then use the STRUCT methods to manipulate the value, as illustrated with the getAttributes() method.
oracle.sql.STRUCT student1 =
(oracle.sql.STRUCT)((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue ();
Object attributes[] = student1.getAttributes();
You can also use the getValue(dictionary) method to get the value for a REF. You must provide a hash table to map data types in each attribute of the REF to Java language data types. For example:
java.util.Hashtable map = new java.util.Hashtable();
map.put("VARCHAR", Class.forName("java.lang.String"));map.put("NUMBER", Class.forName("java.lang.Integer"));oracle.sql.STRUCT result = (oracle.sql.STRUCT)
((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue (map);
When you update a REF, you can do any of the following:
To use the setValue(object) method to update a REF value, you create an object with the new values for the REF, and then pass the object as a parameter of the setValue method. For example:
STUDENT s1 = new STUDENT();
s1.setName("Terry Green");s1.setAge(20);
((weblogic.jdbc.vendor.oracle.OracleRef)ref).setValue(s1);
When you update the value for a REF with the setValue(object) method, you actually update the value in the table to which the REF points.
To update the location to which a REF points using a prepared statement, you can follow these basic steps:
try {conn = ds.getConnection();
stmt = conn.createStatement();
//Get the REF.
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");rs.next();
ref = (java.sql.Ref) rs.getRef(1); //cast the REF as a java.sql.Ref
}
//Create and execute the prepared statement.
String sqlUpdate = "update t3 s2 set col = ? where s2.col.ob1=20";
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setRef(1, ref);
pstmt.executeUpdate();
To use a callable statement to update the location to which a REF points, you prepare the stored procedure, set any IN parameters and register any OUT parameters, and then execute the statement. The stored procedure updates the REF value, which is actually a location. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
ref1 = (java.sql.Ref) rs.getRef(1);
// Prepare the stored procedure
sql = "{call SP1 (?, ?)}";cstmt = conn.prepareCall(sql);
// Set IN and register OUT params
cstmt.setRef(1, ref1);
cstmt.registerOutParameter(2, getRefType(), "USER.OB");
// Execute
cstmt.execute();
Creating a REF in the Database
You cannot create REF objects in your JDBC application—you can only retrieve existing REF objects from the database. However, you can create a REF in the database using statements or prepared statements. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);
cmd = "create table t2 (col ref ob)";
stmt.execute(cmd);
cmd = "insert into t2 select ref(p) from t1 where p.ob1=5";
stmt.execute(cmd);
The preceding example creates an object type (ob), a table (t1) of that object type, a table (t2) with a REF column that can point to instances of ob objects, and inserts a REF into the REF column. The REF points to a row in t1 where the value in the first column is 5.
Programming with BLOBs and CLOBs
This section contains sample code that demonstrates how to access the OracleBlob interface. You can use the syntax of this example for the OracleBlob interface, when using methods supported by WebLogic Server. See Tables of Oracle Extension Interfaces and Supported Methods.
Note: When working with BLOBs and CLOBs (referred to as "LOBs"), you must take transaction boundaries into account; for example, direct all read/writes to a particular LOB within a transaction. For additional information, refer to Oracle documentation about "LOB Locators and Transaction Boundaries" at the Oracle Web site.
Query to Select BLOB Locator from the DBMS
The BLOB Locator, or handle, is a reference to an Oracle Thin Driver BLOB:
String selectBlob = "select blobCol from myTable where blobKey = 666"
Declare the WebLogic Server java.sql Objects
The following code presumes the Connection is already established:
ResultSet rs = null;
Statement myStatement = null;
java.sql.Blob myRegularBlob = null;
java.io.OutputStream os = null;
In this try catch block, you get the BLOB locator and access the Oracle BLOB extension.
try { // get our BLOB locator..
myStatement = myConnect.createStatement();
rs = myStatement.executeQuery(selectBlob);
while (rs.next()) {
myRegularBlob = rs.getBlob("blobCol");
}
// Access the underlying Oracle extension functionality for
// writing. Cast to the OracleThinBlob interface to access
// the Oracle method.
os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream();
.....
........
} catch (SQLException sqe) {
System.out.println("ERROR(general SQE): " +
sqe.getMessage());
}Once you cast to the Oracle.ThinBlob interface, you can access the BEA supported methods.
Updating a CLOB Value Using a Prepared Statement
If you use a prepared statement to update a CLOB and the new value is shorter than the previous value, the CLOB will retain the characters that were not specifically replaced during the update. For example, if the current value of a CLOB is abcdefghij and you update the CLOB using a prepared statement with zxyw, the value in the CLOB is updated to zxywefghij. To correct values updated with a prepared statement, you should use the dbms_lob.trim procedure to remove the excess characters left after the update. See the Oracle documentation for more information about the dbms_lob.trim procedure.
Programming with Oracle Virtual Private Databases
Starting with WebLogic Server 7.0 SP3, WebLogic Server provides support for Oracle Virtual Private Databases (VPDs). A VPD is an aggregation of server-enforced, application-defined fine-grained access control, combined with a secure application context in the Oracle 9i database server.
To use VPDs in your WebLogic Server application, you would typically do the following:
Note: If you are using an XA-enabled version of the JDBC driver, you must set KeepXAConnTillTxComplete=true. See "Additional XA Connection Pool Properties" in the Administration Guide.
import weblogic.jdbc.vendor.oracle.OracleConnection;
// get a connection from a WLS JDBC connection pool
Connection conn = ds.getConnection();
// cast to the Oracle extension and set CLIENT_IDENTIFIER
// (which will be accessible from USERENV naming context on
// the database server side)
((OracleConnection)conn).setClientIdentifier(clientId);
/* perform application specific work */
// clean up connection before returning to WLS JDBC connection pool
((OracleConnection)conn).clearClientIdentifier(clientId);
// close the connection
conn.close();
Tables of Oracle Extension Interfaces and Supported Methods
The following tables describe the Oracle interfaces and supported methods you use with the Oracle Thin Driver (or another driver that supports these methods) to extend the standard JDBC (java.sql.*) interfaces.
Note: Typically, when a new version of the Oracle Thin driver is released, some extension methods are removed from the driver. WebLogic Server cannot support methods that are no longer included in the driver. For the Oracle 9.2.0 Thin driver (and the WebLogic Server 7.0 Service Pack 2 release), the following methods were removed:
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||