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
• Setting the Environment for a Type-4 Third-Party JDBC Driver
• Using the Oracle Thin Driver
• Updating the Sybase jConnect Driver
• Installing and Using the IBM DB2 Type 2 JDBC Driver
• Installing and Using the SQL Server 2000 Driver for JDBC from Microsoft
• Installing and Using the IBM Informix JDBC Driver
• Getting a Connection with Your Third-Party Driver
• Using Vendor Extensions to JDBC Interfaces
• Using Oracle Extensions with the Oracle Thin Driver
• Programming with Oracle Virtual Private Databases
• Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers
• Tables of Oracle Extension Interfaces and Supported Methods

 

---

Overview of Third-Party JDBC Drivers

WebLogic Server works with third-party JDBC drivers that offer the following functionality:

• Are thread-safe
• Can implement transactions using standard JDBC statements

Third-party JDBC drivers that do not implement Serializable or Remote interfaces cannot pass objects to a remote client application.

This section describes how to set up and use third-party JDBC drivers with WebLogic Server and specific instructions for the following third-party JDBC drivers:

• Oracle Thin Driver 9.2.0 (included in the WebLogic Server installation)
• Sybase jConnect and jConnect2 Drivers (included in the WebLogic Server installation)
• IBM DB2 Driver
• Microsoft SQL Server Driver for JDBC
• IBM Informix JDBC Driver

 

Third-Party JDBC Drivers Installed with WebLogic Server

The 9.2.0 version of the Oracle Thin driver (ojdbc14.jar) 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). Therefore, you do not need to add these JDBC drivers to your CLASSPATH. If you plan to use a third-party JDBC driver that is not installed with WebLogic Server, update your CLASSPATH with the path to the driver files.

 

---

Setting the Environment for a Type-4 Third-Party JDBC Driver

If you use a third-party JDBC driver other than the Oracle Thin Driver or Sybase jConnect drivers included in the WebLogic Server installation, 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 Server.

 

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 Server.

 

---

Using the Oracle Thin Driver

The 9.2.0 version of the Oracle Thin driver (ojdbc14.jar) is installed in the WL_HOME\server\lib folder (where WL_HOME is the folder where WebLogic Server is installed) with weblogic.jar. The manifest in weblogic.jar lists this file so that it is loaded when weblogic.jar is loaded (when the server starts). Therefore, you do not need to add the Oracle Thin driver to your CLASSPATH. Note that the ojdbc14.jar file replaces classes12.zip as the source for Oracle Thin driver classes. This version of the driver is for use with a Java 2 SDK version 1.4.

If you plan to use a different version of the driver, replace the ojdbc14.jar file in WL_HOME\server\lib with an updated version of the file from Oracle or add the new file to the front of your CLASSPATH. You can download driver updates from the Oracle Web site at http://otn.oracle.com/software/content.html.

 

Character Set Support with nls_charset12.zip

The Oracle Thin driver supports the following character sets for CHAR and VARCHAR datatypes:

• US7ASCII
• WE8DEC
• ISO-LATIN-1
• UTF-8

If you use other character sets, 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 folder (where WL_HOME is the folder where WebLogic Server is installed). See Setting the Environment for a Type-4 Third-Party JDBC Driver for instructions to set your CLASSPATH.

 

Using the Oracle Thin Driver in Debug Mode

The WL_HOME\server\ext\jdbc\oracle\920 folder (where WL_HOME is the folder where WebLogic Server is installed) includes the ojdbc14_g.jar file, which is the version of the oracle Thin driver with classes to support to use for debug and trace. To use the Oracle Thin driver in debug mode, add the path to this file at the beginning of your CLASSPATH and turn on JDBC logging (see "Enabling JDBC Logging in the WebLogic Server Administration Console Online Help).

 

---

Updating the 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:

• WL_HOME\server\ext\jdbc\sybase\jConnect.jar
• WL_HOME\server\ext\jdbc\sybase\jConnect-5_5\classes\jconn2.jar

 

---

Installing and Using the IBM DB2 Type 2 JDBC Driver

The IBM DB2 client installation includes a type 2 JDBC driver that you can use to create connections to a DB2 database in a connection pool. By default, the DB2 client uses a JDBC 1.x version of the driver. To use the JDBC 2.0-compliant version of the driver, follow the steps below.

Note: You must install the DB2 client on each machine that you want to use the DB2 type 2 JDBC driver to connect to the database. As with all type 2 drivers, the DB2 driver relies on libraries in the database client to access the database.

1. Stop the DB2 JDBC Applet Server Windows Service.
2. In the db2_install_path\java12 directory, where db2_install_path is the directory in which you installed the DB2 client, run the usejdbc2.bat batch file.

   This batch file creates a folder for the JDBC 1.2 version of JDBC driver and then replaces files in the db2_install_path\java folder with the JDBC 2.0 version of the driver.

3. Start the DB2 JDBC Applet Server Windows service.
4. Check the contents of the db2_install_path\java12\inuse file. If JDBC 2.0 is being used, the file will contain JDBC 2.0.

Before you can use the DB2 type 2 JDBC driver in a connection pool, add the driver classes to your CLASSPATH and the DB2 client libraries to your PATH. You may want to do this in the start scripts for your domain. For example:

set CLASSPATH=db2_install_path\java\db2java.zip;%CLASSPATH%



set PATH=db2_install_path\bin;%PATH%

Where db2_install_path is the directory in which you installed the DB2 client.

If you plan to use the XA version of the IBM DB2 driver, see "Using the IBM DB2 Type 2 JDBC Driver in Programming WebLogic JTA for configuration instructions.

To create a connection pool with connections that use the DB2 type 2 driver, you can use the JDBC Connection Pool Assistant in the Administration Console (see "JDBC Connection Pools in the Administration Console Online Help) or the JMX API (see Creating a Connection Pool Dynamically).

 

Connection Pool Attributes when using the IBM DB2 Type 2 JDBC Driver

Use the attributes as described in Table 5-1 and Table 5-2 when creating a connection pool that uses the IBM DB2 Type 2 JDBC Driver.

Attribute	Value
URL	jdbc:db2:dbname
Driver Class Name	COM.ibm.db2.jdbc.app.DB2Driver
Properties	user=username DatabaseName=dbname
Password	password
TestConnectionsOnCreate	true
TestConnectionsOnReserve	true
TestTableName	SYSIBM.SYSTABLES
Target	serverName

The database name in the URL and in the Properties string must be a database configured for use in the DB2 client, such as a database listed in the Client Configuration Assistant. Also, the database user must be able to select from the table specified in TestTableName.

An entry in the config.xml file may look like the following:

    <JDBCConnectionPool DriverName="COM.ibm.db2.jdbc.app.DB2Driver"


      Name="MyJDBC Connection Pool"


      Password="{3DES}Pd8QwSJ5FtLEfuiA/vcy3g=="


      Properties="user=dbuser;DatabaseName=db1"


      Targets="myserver" 


      TestConnectionsOnCreate="true"


      TestConnectionsOnReserve="true"


      TestTableName="SYSIBM.SYSTABLES" 


      URL="jdbc:db2:db1"/>

Attribute	Value
URL	jdbc:db2:dbname
Driver Class Name	COM.ibm.db2.jdbc.DB2XADataSource
Properties	user=username DatabaseName=dbname
Password	password
TestConnectionsOnCreate	true
TestConnectionsOnReserve	true
TestTableName	SYSIBM.SYSTABLES
KeepXAConnTillTxComplete	true
Target	serverName

The database name in the URL and in the Properties string must be a database configured for use in the DB2 client, such as a database listed in the Client Configuration Assistant.

DB2 requires that all processing for a global transaction occurs on a single database connection, so set KeepXAConnTillTxComplete to true.

An entry in the config.xml file may look like the following:

    <JDBCConnectionPool DriverName="COM.ibm.db2.jdbc.DB2XADataSource"


      KeepXAConnTillTxComplete="true"


      Name="My XA JDBC Connection Pool"


      Password="{3DES}Pd8QwSJ5FtLEfuiA/vcy3g=="


      Properties="user=dbuser;DatabaseName=db1"


      Targets="myserver" 


      TestConnectionsOnCreate="true"


      TestConnectionsOnReserve="true" 


      TestTableName="SYSIBM.SYSTABLES" 


      URL="jdbc:db2:db1"/>

 

---

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.

The following sections describe how to install and configure the Microsoft SQL Server 2000 Driver for JDBC.

 

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:

1. Download the Microsoft SQL Server 2000 Driver for JDBC (setup.exe file) from the Microsoft MSDN Web site. Save the file in a temporary directory on your local computer.
2. Run setup.exe from the temporary directory and follow the instructions on the screen.
3. Add the path to the following files to your CLASSPATH:
   • install_dir/lib/msbase.jar
   • install_dir/lib/msutil.jar
   • install_dir/lib/mssqlserver.jar

   Where install_dir is the folder in which you installed the driver. For example:

   set CLASSPATH=install_dir\lib\msbase.jar;
   
   
   
   install_dir\lib\msutil.jar;install_dir\lib\mssqlserver.jar;
   
   
   
   %CLASSPATH%
   

 

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:

1. Download the Microsoft SQL Server 2000 Driver for JDBC (mssqlserver.tar file) from the Microsoft MSDN Web site. Save the file in a temporary directory on your local computer.
2. Change to the temporary directory and untar the contents of the file using the following command:

   tar -xvf mssqlserver.tar
   

3. Execute the following command to run the installation script:

   install.ksh
   

4. Follow the instructions on the screen. When prompted to enter an installation directory, make sure you enter the full path to the directory.
5. Add the path to the following files to your CLASSPATH:
   • install_dir/lib/msbase.jar
   • install_dir/lib/msutil.jar
   • install_dir/lib/mssqlserver.jar

   Where install_dir is the folder in which you installed the driver. For example:

   export CLASSPATH=install_dir/lib/msbase.jar:
   
   
   
   install_dir/lib/msutil.jar:install_dir/lib/mssqlserver.jar:
   
   
   
   $CLASSPATH
   

 

Connection Pool Attributes when using the Microsoft SQL Server Driver for JDBC

Use the attributes in Table 5-3 when creating a connection pool that uses the Microsoft SQL Server Driver for JDBC.

Attribute	Value
URL	jdbc:microsoft:sqlserver://server_name:port
Driver Class Name	com.microsoft.jdbc.sqlserver.SQLServerDriver
Properties	user=username DatabaseName=dbname selectMethod=cursor
Password	password
Target	serverName

An entry in the config.xml file may look like the following:

    <JDBCConnectionPool 


      Name="mssqlPool" 


      DriverName="com.microsoft.jdbc.sqlserver.SQLServerDriver"


      URL="jdbc:microsoft:sqlserver://db4:1433" 


      Properties="databasename=db4;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.

 

 

---

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:

1. Copy ifxjdbc.jar and ifxjdbcx.jar files from INFORMIX_INSTALL\lib and paste it in WL_HOME\server\lib folder, where:

   INFORMIX_INSTALL is the root directory where you installed the Informix JDBC driver, and

   WL_HOME is the folder where you installed WebLogic Platform, typically c:\bea\weblogic81.

2. Add the path to ifxjdbc.jar and ifxjdbcx.jar to your CLASSPATH. For example:

   set CLASSPATH=%WL_HOME%\server\lib\ifxjdbc.jar;%WL_HOME%\server\lib\ifxjdbcx.jar;%CLASSPATH%
   

   You can also add the path for the driver files to the set CLASSPATH statement in your start script for WebLogic Server.

 

Connection Pool Attributes when using the IBM Informix JDBC Driver

Use the attributes as described in Table 5-4 and Table 5-5 when creating a connection pool that uses the IBM Informix JDBC driver.

Attribute	Value
URL	jdbc:informix-sqli:dbserver_name_or_ip:port/dbname:informixserver=ifx_server_name
Driver Class Name	com.informix.jdbc.IfxDriver
Properties	user=username url=jdbc:informix-sqli:dbserver_name_or_ip:port/dbname:informixserver=ifx_server_name portNumber=1543 databaseName=dbname ifxIFXHOST=ifx_server_name serverName=dbserver_name_or_ip
Password	password
Login Delay Seconds	1
Target	serverName

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"


  />

Attribute	Value
URL	leave blank
Driver Class Name	com.informix.jdbcx.IfxXADataSource
Properties	user=username url=jdbc:informix-sqli://dbserver_name_or_ip:port_num/dbname:informixserver=dbserver_name_or_ip password=password portNumber =port_num; databaseName=dbname serverName=dbserver_name ifxIFXHOST=dbserver_name_or_ip
Password	leave blank
Supports Local Transaction	true
Target	serverName

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:

• Batch updates fail if you attempt to insert rows with TEXT or BYTE columns unless the IFX_USEPUT environment variable is set to 1.
• If the Java program sets autocommit mode to true during a transaction, IBM Informix JDBC Driver commits the current transaction if the JDK is version 1.4 and later, otherwise the driver rolls back the current transaction before enabling autocommit.

 

---

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. BEA recommends 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");

  Connection conn = null;


Statement stmt = null;


ResultSet rs = null;

  try {


  ctx = new InitialContext(ht);


  javax.sql.DataSource ds 


    = (javax.sql.DataSource) ctx.lookup ("myDataSource");


  conn = ds.getConnection();

   // You can now use the conn object to create 


 //  Statements and retrieve result sets:

    stmt = conn.createStatement();


  stmt.execute("select * from someTable");


  rs = stmt.getResultSet(); 

...

//Close JDBC objects as soon as possible


  stmt.close();


  stmt=null;

    conn.close();


  conn=null;

 }


catch (Exception e) {


  // a failure occurred


  log message;


}



finally {    


try { 


  ctx.close(); 


} catch (Exception e) {


   log message; }


try { 


  if (rs != null) rs.close(); 


} catch (Exception e) {  


   log message; }


try { 


  if (stmt != null) stmt.close(); 


} catch (Exception e) {  


   log message; }


try { 


  if (conn != null) conn.close(); 


} catch (Exception e) {  


   log message; }



}

(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 method that checks the class name of the object for a particular 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.

When you close the logical connection, Weblogic Server either discards the underlying physical connection and creates a new connection to replace it, or returns it to the connection pool for reuse. You specify the behavior by setting the RemoveInfectedConnectionsEnabled attribute on a connection pool using either the setRemoveInfectedConnectionsEnabled(boolean enable) method on the JDBCConnectionPool MBean or by manually adding the attribute to a JDBCConnectionPool tag in the config.xml file. (With the Service Pack 2 release of WebLogic Server 8.1, the Remove Infected Connections Enabled attribute is available in the Administration Console.)

Because there is no way for WebLogic Server to guarantee the quality of the connection or to effectively manage the connection after the physical connection is exposed, by default the physical connection is not returned to the connection pool after you close the logical connection (RemoveInfectedConnectionsEnabled=true). Instead, the physical connection is closed.

When you set RemoveInfectedConnectionsEnabled=false, when you close the logical connection, the physical connection is returned to the connection pool. If you use this setting, make sure that the database connection is suitable for reuse by other applications.

When you set the RemoveInfectedConnectionsEnabled attribute on a connection pool, the config.xml file may look like:

<JDBCConnectionPool CapacityIncrement="1"


  DriverName="com.pointbase.jdbc.jdbcUniversalDriver"


  InitialCapacity="1" MaxCapacity="10" Name="demoPool"


  Password="{3DES}pvMEDMqz47oQYFJBE8gz7w=="


  Properties="user=examples" RefreshMinutes="0"


  RemoveInfectedConnectionsEnabled="false"


  ShrinkPeriodMinutes="15" ShrinkingEnabled="true"


  Targets="examplesServer" TestConnectionsOnRelease="false"


  TestConnectionsOnReserve="true" TestTableName="SYSTABLES"


  URL="jdbc:pointbase:server://localhost/demo"/>

 

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:

• Implicitly pass the physical connection (using the getVendorConnection() method) within a method that requires the physical connection.
• Cast the connection as a WLConnection and call getVendorConnection().

For example:

//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 use a physical connection, for example, to create a STRUCT, consider the following costs and limitations:

• The physical connection can only be used in server-side code.
• When you use a physical connection, you lose all of the connection management benefits that WebLogic Server offer, error handling, statement caching, and so forth.
• You should use the physical connection only for the vendor-specific methods or classes that require it. Do not use the physical connection for generic JDBC, such as creating statements or transactional calls.
• By default, the connection is not reused. When you close the connection, it is not returned to the connection pool. Instead, the physical connection is closed and the connection pool creates a new connection to replace the one passed as a physical connection. Because the connection is not reused, there is a performance loss when using a physical connection because of the following:
  • By default, the physical connection is replaced with a new database connection in the connection pool, which uses resources on both the application server and the database server.
  • The statement cache for the original connection is closed and a new cache is opened for the new connection. Therefore, the performance gains from using the statement cache are lost.

 

---

Using Vendor Extensions to JDBC Interfaces

Some database vendors provide additional proprietary methods for working with data from a database that uses their DBMS. These methods extend the standard JDBC interfaces. In previous releases of WebLogic Server, only specific JDBC extensions for a few vendors were supported. The current release of WebLogic Server supports all extension methods exposed as a public interface in the vendor's JDBC driver.

If the driver vendor does not expose the methods you need in a public interface, you should submit a request to the vendor to expose the methods in a public interface. WebLogic Server does provide support for extension methods in the Oracle Thin Driver for ARRAYs, STRUCTs, and REFs, even though the extension methods are not exposed in a public interface. See Using Oracle Extensions with the Oracle Thin Driver.

In general, WebLogic Server supports using vendor extensions in server-side code. To use vendor extensions in client-side code, the object type or data type must be serializable. Exceptions to this are the following object types:

• CLOB
• BLOB
• InputStream
• OutputStream

WebLogic Server handles de-serialization for these object types so they can be used in client-side code. Note that there are interoperability limitations when using different versions of WebLogic Server clients and servers. See Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers.

To use the extension methods exposed in the JDBC driver, include these steps in your application code:

• Import the driver interfaces from the JDBC driver used to create connections in the connection pool.
• Get a connection from the connection pool.
• Cast the connection object as the vendor's connection interface.
• Use the vendor extensions as described in the vendor's documentation.

The following sections provide details in code examples. For information about specific extension methods for a particular JDBC driver, refer to the documentation from the JDBC driver vendor.

 

Sample Code for Accessing Vendor Extensions to JDBC Interfaces

The following code examples use extension methods available in the Oracle Thin driver to illustrate how to use vendor extensions to JDBC. You can adapt these examples to fit methods exposed in your JDBC driver.

 

Import Packages to Access Vendor Extensions

Import the interfaces from the JDBC driver used to create the connection in the connection pool. This example uses interfaces from the Oracle Thin Driver.

import java.sql.*;



import java.util.*;



import javax.naming.Context;



import javax.naming.InitialContext;



import javax.sql.DataSource;



import oracle.jdbc.*;

// Import driver interfaces. The driver must be the same driver



// used to create the database connection in the connection pool.

 

Get a Connection

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();

 

Cast the Connection as a Vendor Connection

Now that you have the connection, you can cast it as a vendor connection. This example uses the OracleConnection interface from the Oracle Thin Driver.

orConn = (oracle.jdbc.OracleConnection)conn;



// This replaces the deprecated process of casting the connection



// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:



// orConn = (weblogic.jdbc.vendor.oracle.OracleConnection)conn;

 

Use Vendor Extensions

The following code fragment shows how to use the Oracle Row Prefetch method available from the Oracle Thin driver.

// Cast to OracleConnection and retrieve the 



// default row prefetch value for this connection.

int default_prefetch = 


        ((oracle.jdbc.OracleConnection)conn).getDefaultRowPrefetch();



// This replaces the deprecated process of casting the connection



// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:



// ((weblogic.jdbc.vendor.oracle.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.

      ((oracle.jdbc.OracleStatement)stmt).setRowPrefetch(20);

   // This replaces the deprecated process of casting the


 // statement to a weblogic.jdbc.vendor.oracle.OracleStatement.


 // For example:


 // ((weblogic.jdbc.vendor.oracle.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;


  }

 

---

Using Oracle Extensions with the Oracle Thin Driver

For most extensions that Oracle provides, you can use the standard technique as described in Using Vendor Extensions to JDBC Interfaces. However, the Oracle Thin driver does not provide public interfaces for its extension methods in the following classes:

• oracle.sql.ARRAY
• oracle.sql.STRUCT
• oracle.sql.REF
• oracle.sql.BLOB
• oracle.sql.CLOB

WebLogic Server provides its own interfaces to access the extension methods for those classes:

• weblogic.jdbc.vendor.oracle.OracleArray
• weblogic.jdbc.vendor.oracle.OracleStruct
• weblogic.jdbc.vendor.oracle.OracleRef
• weblogic.jdbc.vendor.oracle.OracleThinBlob
• weblogic.jdbc.vendor.oracle.OracleThinClob

The following sections provide code samples for using the WebLogic Server interfaces for Oracle extensions. For a list of supported methods, see Tables of Oracle Extension Interfaces and Supported Methods. For more information, please refer to the Oracle documentation.

Note: You can use this process to use any of the WebLogic Server interfaces for Oracle extensions listed in the Tables of Oracle Extension Interfaces and Supported Methods. However, all but the interfaces listed above are deprecated and will be removed in a future release of WebLogic Server.

 

Limitations When Using Oracle JDBC Extensions

Please note the following limitations when using Oracle extensions to JDBC interfaces:

• You can use Oracle extensions for ARRAYs, REFs, and STRUCTs in server-side applications that use the same JVM as the server only. You cannot use Oracle extensions for ARRAYs, REFs, and STRUCTs in remote client applications.
• You cannot create ARRAYs, REFs, and STRUCTs in your applications. You can only retrieve existing ARRAY, REF, and STRUCT objects from a database. To create these objects in your applications, use a non-standard Oracle descriptor object, which is not supported in WebLogic Server.
• There are interoperability limitations when using different versions of WebLogic Server clients and servers. See Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers.

 

Sample Code for Accessing Oracle Extensions to JDBC Interfaces

The following code examples show how to access the WebLogic Server interfaces for Oracle extensions that are not available as public interfaces, including interfaces for:

• ARRAYs - See Programming with ARRAYs.
• STRUCTS - See Programming with STRUCTs.
• REFs - See Programming with REFs.
• BLOBs and CLOBs - See Programming with BLOBs and CLOBs.

If you selected the option to install server examples with WebLogic Server, see the JDBC examples for more code examples, typically at WL_HOME\samples\server\src\examples\jdbc, where WL_HOME is the folder where you installed WebLogic Server.

 

Programming with ARRAYs

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:

1. Import the required classes.
2. Get a connection and then create a statement for the connection.
3. Get the ARRAY using a result set or a callable statement.
4. Use the ARRAY as either a java.sql.Array or a weblogic.jdbc.vendor.oracle.OracleArray.
5. Use the standard Java methods (when used as a java.sql.Array) or Oracle extension methods (when cast as a weblogic.jdbc.vendor.oracle.OracleArray) to work with the data.

The following sections provide more details for these actions.

Note: You can use ARRAYs in server-side applications only. You cannot use ARRAYs in remote client applications.

 

Import Packages to Access Oracle Extensions

Import the Oracle interfaces used in this example. The OracleArray interface is counterpart to oracle.sql.ARRAY and can be used in the same way as the Oracle interface 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 Connection

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();

 

Getting an ARRAY

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:

1. Create an array in the database using PL/SQL, if the array you want to update does not already exist in the database.
2. Get the ARRAY using a result set or a callable statement.
3. Work with the array in your Java application as either a java.sql.Array or a weblogic.jdbc.vendor.oracle.OracleArray.
4. Update the array in the database using the setArray() method for a prepared statement or a callable statement. For example:

   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, 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()

 

Programming with STRUCTs

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:

• STRUCTs are supported for use with Oracle only. To use STRUCTs in your applications, use the Oracle Thin Driver to communicate with the database, typically through a connection pool. The WebLogic jDriver for Oracle does not support the STRUCT data type.
• You can use STRUCTs in server-side applications only. You cannot use STRUCTs in client applications.

To use STRUCTs in WebLogic Server applications:

1. Import the required classes. (See Import Packages to Access Oracle Extensions.)
2. Get a connection. (See Establish the Connection.)
3. Use getObject to get the STRUCT.
4. Cast the STRUCT as a STRUCT, either java.sql.Struct (to use standard methods) or weblogic.jdbc.vendor.oracle.OracleStruct (to use standard and Oracle extension methods).
5. Use the standard or Oracle extension methods to work with the data.

The following sections provide more details for steps 3 through 5.

 

Getting a STRUCT

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:

• getAttributes()
• getAttributes(java.util.Dictionary map)
• getSQLTypeName()

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, 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:

• getDescriptor()
• getOracleAttributes()
• getAutoBuffering()
• setAutoBuffering(boolean)

 

Getting STRUCT Attributes

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, 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, 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.

 

Programming with REFs

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:

• REFs are supported for use with Oracle only. To use REFs in your applications, use the Oracle Thin Driver to communicate with the database, typically through a connection pool. The WebLogic jDriver for Oracle does not support the REF data type.
• You can use REFs in server-side applications only.

To use REFs in WebLogic Server applications, follow these steps:

1. Import the required classes. (See Import Packages to Access Oracle Extensions.)
2. Get a database connection. (See Establish the Connection.)
3. Get the REF using a result set or a callable statement.
4. Cast the result as a STRUCT or as a Java object. You can then manipulate data using STRUCT methods or methods for the Java object.

You can also create and update a REF in the database.

The following sections describe these steps 3 and 4 in greater detail.

 

Getting a REF

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, 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, 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:

• getDescriptor()
• getSTRUCT()
• getValue()
• getValue(dictionary)
• setValue(object)

 

Getting a Value

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);

 

Updating REF Values

When you update a REF, you can do any of the following:

• Change the value in the underlying table with the setValue(object) method.
• Change the location to which the REF points with a prepared statement or a callable statement.
• Set the value of the REF to null.

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:

1. Get a REF that points to the new location. Use this REF to replace the value of another REF.
2. Create a string for the SQL command to replace the location of an existing REF with the value of the new REF.
3. Create and execute a prepared statement.

For example:

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"), 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;

 

Begin SQL Exception Block

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

An Oracle Virtual Private Database (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:

1. Create a JDBC connection pool in your WebLogic Server configuration that uses either the Oracle Thin driver or the Oracle OCI driver. See Configuring and Using WebLogic JDBC or "Creating and Configuring a JDBC Connection Pool in the Administration Console Online Help.

   Note: If you are using an XA-enabled version of the JDBC driver, set KeepXAConnTillTxComplete=true. See "Additional XA Connection Pool Properties in the Administration Console Online Help.

   The WebLogic jDriver for Oracle cannot propagate the ClientIdentifier, so it is ineffective to use the driver with VPDs.

2. Create a data source in your WebLogic Server configuration that points to the connection pool. See Configuring and Using DataSources or "Creating and Configuring a JDBC Data Source in the Administration Console Online Help.
3. Do the following in your application:

import weblogic.jdbc.extensions.WLConnection

// get a connection from a WLS JDBC connection pool



Connection conn = ds.getConnection();

// Get the underlying vendor connection object



oracle.jdbc.OracleConnection orConn = (oracle.jdbc.OracleConnection)


 (((WLConnection)conn).getVendorConnection());

// Set CLIENT_IDENTIFIER (which will be accessible from 



// USERENV naming context on the database server side)



orConn.setClientIdentifier(clientId);

/* perform application specific work, preferably using conn instead of orConn */

// clean up connection before returning to WLS JDBC connection pool



orConn.clearClientIdentifier(clientId);

// As soon as you are finished with vendor-specific calls,  



// nullify the reference to the physical connection. 



orConn = null; 

// close the pooled connection



conn.close();

Note: This code uses an underlying physical connection from a pooled (logical) connection. See Getting a Physical Connection from a Connection Pool for usage guidelines.

 

Oracle VPD with WebLogic Server 8.1SP2

Starting with WebLogic Server 8.1 SP2, WebLogic Server provides support for the oracle.jdbc.OracleConnection.setClientIdentitfier and oracle.jdbc.OracleConnection.clearClientIndentifier methods without using the underlying physical connection from a pooled connection. To use VPDs in your WebLogic Server application, you would typically do the following:

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)



((weblogic.jdbc.vendor.oracle.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();

 

---

Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers

Because the way WebLogic Server supports vendor JDBC extensions was changed in WebLogic Server 8.1, interoperability between versions of client and servers is affected.

When a WebLogic Server 8.1 client interacts with a WebLogic Server 7.0 or earlier server, Oracle extensions are not supported. When the client application tries to cast the JDBC objects to the Oracle extension interfaces, it will get a ClassCastException. However, when a WebLogic Server 7.0 or earlier client interacts with a WebLogic Server 8.1 server, Oracle extensions are supported.

This applies to the following Oracle extension interfaces:

• weblogic.jdbc.vendor.oracle.OracleConnection
• weblogic.jdbc.vendor.oracle.OracleStatement
• weblogic.jdbc.vendor.oracle.OraclePreparedStatement
• weblogic.jdbc.vendor.oracle.OracleCallableStatement
• weblogic.jdbc.vendor.oracle.OracleResultSet
• weblogic.jdbc.vendor.oracle.OracleThinBlob
• weblogic.jdbc.vendor.oracle.OracleThinClob
• weblogic.jdbc.vendor.oracle.OracleArray
• weblogic.jdbc.vendor.oracle.OracleRef
• weblogic.jdbc.vendor.oracle.OracleStruct

Note: Standard JDBC interfaces are supported regardless of the client or server version.

 

---

Tables of Oracle Extension Interfaces and Supported Methods

In previous releases of WebLogic Server, only the JDBC extensions listed in the following tables were supported. The current release of WebLogic Server supports most extension methods exposed as a public interface in the vendor's JDBC driver. See Using Vendor Extensions to JDBC Interfaces for instructions for using vendor extensions. Because the new internal mechanism for supporting vendor extensions does not rely on the previous implementation, several interfaces are no longer needed and are deprecated. These interfaces will be removed in a future release of WebLogic Server. See Table 5-6. BEA encourages you to use the alternative interface listed in the table.

Deprecated Interface (supported in WebLogic Server 7.0 and earlier)	Instead, use this interface from Oracle (supported in WebLogic Server version 8.1 and later)
weblogic.jdbc.vendor.oracle. OracleConnection	oracle.jdbc.OracleConnection
weblogic.jdbc.vendor.oracle. OracleStatement	oracle.jdbc.OracleStatement
weblogic.jdbc.vendor.oracle. OracleCallableStatement	oracle.jdbc.OracleCallableStatement
weblogic.jdbc.vendor.oracle. OraclePreparedStatement	oracle.jdbc.OraclePreparedStatement
weblogic.jdbc.vendor.oracle. OracleResultSet	oracle.jdbc.OracleResultSet

The interfaces listed in Table 5-7 are still valid because Oracle does not provide interfaces to access these extension methods.

Oracle Interface
weblogic.jdbc.vendor.oracle.OracleArray
weblogic.jdbc.vendor.oracle.OracleRef
weblogic.jdbc.vendor.oracle.OracleStruct
weblogic.jdbc.vendor.oracle.OracleThinClob
weblogic.jdbc.vendor.oracle.OracleThinBlob

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.

Extends	Method Signature
OracleConnection extends java.sql.Connection(This interface is deprecated. See Table 5-6.)	void clearClientIdentifier(String s) throws java.sql.SQLException; boolean getAutoClose() throws java.sql.SQLException; String getDatabaseProductVersion() throws java.sql.SQLException; String getProtocolType() throws java.sql.SQLException; String getURL() throws java.sql.SQLException; String getUserName() throws java.sql.SQLException; boolean getBigEndian() throws java.sql.SQLException; boolean getDefaultAutoRefetch() throws java.sql.SQLException; boolean getIncludeSynonyms() throws java.sql.SQLException; boolean getRemarksReporting() throws java.sql.SQLException; boolean getReportRemarks() throws java.sql.SQLException;
OracleConnection extends java.sql.Connection(continued)(This interface is deprecated. See Table 5-6.)	boolean getRestrictGetTables() throws java.sql.SQLException; boolean getUsingXAFlag() throws java.sql.SQLException; boolean getXAErrorFlag() throws java.sql.SQLException; boolean isCompatibleTo816() throws java.sql.SQLException; (Deprecated) byte[] getFDO(boolean b) throws java.sql.SQLException; int getDefaultExecuteBatch() throws java.sql.SQLException; int getDefaultRowPrefetch() throws java.sql.SQLException; int getStmtCacheSize() throws java.sql.SQLException; java.util.Properties getDBAccessProperties() throws java.sql.SQLException; short getDbCsId() throws java.sql.SQLException; short getJdbcCsId() throws java.sql.SQLException; short getStructAttrCsId() throws java.sql.SQLException; short getVersionNumber() throws java.sql.SQLException; void archive(int i, int j, String s) throws java.sql.SQLException;
OracleConnection extends java.sql.Connection(continued)(This interface is deprecated. See Table 5-6.)	void close_statements() throws java.sql.SQLException; void initUserName() throws java.sql.SQLException; void logicalClose() throws java.sql.SQLException; void needLine() throws java.sql.SQLException; void printState() throws java.sql.SQLException; void registerSQLType(String s, String t) throws java.sql.SQLException; void releaseLine() throws java.sql.SQLException; void removeAllDescriptor() throws java.sql.SQLException; void removeDescriptor(String s) throws java.sql.SQLException; void setAutoClose(boolean on) throws java.sql.SQLException; void setClientIdentifier(String s) throws java.sql.SQLException; void clearClientIdentifier(String s) throws java.sql.SQLException; void setDefaultAutoRefetch(boolean b) throws java.sql.SQLException; void setDefaultExecuteBatch(int i) throws java.sql.SQLException; void setDefaultRowPrefetch(int i) throws java.sql.SQLException; void setFDO(byte[] b) throws java.sql.SQLException; void setIncludeSynonyms(boolean b) throws java.sql.SQLException;
OracleConnection extends java.sql.Connection(continued)(This interface is deprecated. See Table 5-6.)	void setPhysicalStatus(boolean b) throws java.sql.SQLException; void setRemarksReporting(boolean b) throws java.sql.SQLException; void setRestrictGetTables(boolean b) throws java.sql.SQLException; void setStmtCacheSize(int i) throws java.sql.SQLException; void setStmtCacheSize(int i, boolean b) throws java.sql.SQLException; void setUsingXAFlag(boolean b) throws java.sql.SQLException; void setXAErrorFlag(boolean b) throws java.sql.SQLException; void shutdown(int i) throws java.sql.SQLException; void startup(String s, int i) throws java.sql.SQLException;

Extends	Method Signature
OracleStatement extends java.sql.statement(This interface is deprecated. See Table 5-6.)	String getOriginalSql() throws java.sql.SQLException; String getRevisedSql() throws java.sql.SQLException; (Deprecated in Oracle 8.1.7, removed in Oracle 9i.) boolean getAutoRefetch() throws java.sql.SQLException; boolean is_value_null(boolean b, int i) throws java.sql.SQLException; byte getSqlKind() throws java.sql.SQLException; int creationState() throws java.sql.SQLException; int getAutoRollback() throws java.sql.SQLException; (Deprecated) int getRowPrefetch() throws java.sql.SQLException; int getWaitOption() throws java.sql.SQLException; (Deprecated) int sendBatch() throws java.sql.SQLException;
OracleStatement extends java.sql.statement(continued)(This interface is deprecated. See Table 5-6.)	void clearDefines() throws java.sql.SQLException; void defineColumnType(int i, int j) throws java.sql.SQLException; void defineColumnType(int i, int j, String s) throws java.sql.SQLException; void defineColumnType(int i, int j, int k) throws java.sql.SQLException; void describe() throws java.sql.SQLException; void notify_close_rset() throws java.sql.SQLException; void setAutoRefetch(boolean b) throws java.sql.SQLException; void setAutoRollback(int i) throws java.sql.SQLException; (Deprecated) void setRowPrefetch(int i) throws java.sql.SQLException; void setWaitOption(int i) throws java.sql.SQLException; (Deprecated)

Extends	Method Signature
OracleResultSet extends java.sql.ResultSet(This interface is deprecated. See Table 5-6.)	boolean getAutoRefetch() throws java.sql.SQLException; int getFirstUserColumnIndex() throws java.sql.SQLException; void closeStatementOnClose() throws java.sql.SQLException; void setAutoRefetch(boolean b) throws java.sql.SQLException; java.sql.ResultSet getCursor(int n) throws java.sql.SQLException; java.sql.ResultSet getCURSOR(String s) throws java.sql.SQLException;

.

Extends

Method Signature

OracleCallableStatement extends java.sql.CallableStatement(This interface is deprecated. See Table 5-6.)

void clearParameters() throws java.sql.SQLException; void registerIndexTableOutParameter(int i, int j, int k, int l) throws java.sql.SQLException; void registerOutParameter (int i, int j, int k, int l) throws java.sql.SQLException; java.sql.ResultSet getCursor(int i) throws java.sql.SQLException; java.io.InputStream getAsciiStream(int i) throws java.sql.SQLException; java.io.InputStream getBinaryStream(int i) throws java.sql.SQLException; java.io.InputStream getUnicodeStream(int i) throws java.sql.SQLException;

.

Extends	Method Signature
OraclePreparedStatement extends OracleStatement and java.sql. PreparedStatement(This interface is deprecated. See Table 5-6.)	int getExecuteBatch() throws java.sql.SQLException; void defineParameterType(int i, int j, int k) throws java.sql.SQLException; void setDisableStmtCaching(boolean b) throws java.sql.SQLException; void setExecuteBatch(int i) throws java.sql.SQLException; void setFixedCHAR(int i, String s) throws java.sql.SQLException; void setInternalBytes(int i, byte[] b, int j) throws java.sql.SQLException;

Extends	Method Signature
OracleArray extends java.sql.Array	public ArrayDescriptor getDescriptor() throws java.sql.SQLException; public Datum[] getOracleArray() throws SQLException; public Datum[] getOracleArray(long l, int i) throws SQLException; public String getSQLTypeName() throws java.sql.SQLException; public int length() throws java.sql.SQLException; public double[] getDoubleArray() throws java.sql.SQLException; public double[] getDoubleArray(long l, int i) throws java.sql.SQLException; public float[] getFloatArray() throws java.sql.SQLException; public float[] getFloatArray(long l, int i) throws java.sql.SQLException; public int[] getIntArray() throws java.sql.SQLException; public int[] getIntArray(long l, int i) throws java.sql.SQLException; public long[] getLongArray() throws java.sql.SQLException; public long[] getLongArray(long l, int i) throws java.sql.SQLException;
OracleArray extends java.sql.Array(continued)	public short[] getShortArray() throws java.sql.SQLException; public short[] getShortArray(long l, int i) throws java.sql.SQLException; public void setAutoBuffering(boolean flag) throws java.sql.SQLException; public void setAutoIndexing(boolean flag) throws java.sql.SQLException; public boolean getAutoBuffering() throws java.sql.SQLException; public boolean getAutoIndexing() throws java.sql.SQLException; public void setAutoIndexing(boolean flag, int i) throws java.sql.SQLException;

Extends

Method Signature

OracleStruct extends java.sql.Struct

public Object[] getAttributes() throws java.sql.SQLException; public Object[] getAttributes(java.util.Dictionary map) throws java.sql.SQLException; public Datum[] getOracleAttributes() throws java.sql.SQLException; public oracle.sql.StructDescriptor getDescriptor() throws java.sql.SQLException; public String getSQLTypeName() throws java.sql.SQLException; public void setAutoBuffering(boolean flag) throws java.sql.SQLException; public boolean getAutoBuffering() throws java.sql.SQLException;

Extends	Method Signature
OracleRef extends java.sql.Ref	public String getBaseTypeName() throws SQLException; public oracle.sql.StructDescriptor getDescriptor() throws SQLException; public oracle.sql.STRUCT getSTRUCT() throws SQLException; public Object getValue() throws SQLException; public Object getValue(Map map) throws SQLException; public void setValue(Object obj) throws SQLException;

Extends	Method Signature
OracleThinBlob extends java.sql.Blob	int getBufferSize()throws java.sql.Exception int getChunkSize()throws java.sql.Exception int putBytes(long, int, byte[])throws java.sql.Exception int getBinaryOutputStream()throws java.sql.Exception

Extends

Method Signature

OracleThinClob extends java.sql.Clob

public OutputStream getAsciiOutputStream() throws java.sql.Exception; public Writer getCharacterOutputStream() throws java.sql.Exception; public int getBufferSize() throws java.sql.Exception; public int getChunkSize() throws java.sql.Exception; public char[] getChars(long l, int i) throws java.sql.Exception; public int putChars(long start, char myChars[]) throws java.sql.Exception; public int putString(long l, String s) throws java.sql.Exception;