Skip navigation.
Downloads | Product Documentation | Support

Programming WebLogic JDBC

# Testing JDBC Connections and Troubleshooting

The following sections describe how to test, monitor, and troubleshoot JDBC connections:

- Monitoring JDBC Connectivity
- Validating a DBMS Connection from the Command Line
- Troubleshooting JDBC
- Troubleshooting Problems with Shared Libraries on UNIX
- Using Microsoft SQL with Nested Triggers

# Monitoring JDBC Connectivity

The Administration Console provides tables and statistics to enable monitoring the connectivity parameters for each of the subcomponents—Connection Pools, MultiPools and DataSources.

You can also access statistics for connection pools programmatically through the `JDBCConnectionPoolRuntimeMBean;` see WebLogic Server Partner's Guide and the WebLogic Javadoc. This MBean is the same API that populates the statistics in the Administration Console. Read more about monitoring connectivity in JDBC Connection Pools .

For information about using MBeans, see Programming WebLogic JMX Services.

# Validating a DBMS Connection from the Command Line

Use the `utils.dbping` BEA utility to test two-tier JDBC database connections after you install WebLogic Server. To use the `utils.dbping` utility, you must complete the installation of your JDBC driver. Make sure you have completed the following:

- For Type 2 JDBC drivers, such as WebLogic jDriver for Oracle, set your `PATH` (Windows) or shared/load library path (UNIX) to include both your DBMS-supplied client installation and the BEA-supplied native libraries.
- For all drivers, include the classes of your JDBC driver in your `CLASSPATH`.
- Configuration instructions for the BEA WebLogic jDriver JDBC drivers are available at:

- Using WebLogic jDriver for Oracle
- Using WebLogic jDriver for Microsoft SQL Server

Use the utils.dbping utility to confirm that you can make a connection between Java and your database. The dbping utility is only for testing a two-tier connection, using a WebLogic two-tier JDBC driver like WebLogic jDriver for Oracle.

### Syntax

>       $ java utils.dbping DBMS user password DB

### Arguments

#### DBMS

Use: `ORACLE` or `MSSQLSERVER4`

#### user

Valid username for database login. Use the same values and format that you use with `isql` for SQL Server or `sqlplus` for Oracle.

#### password

Valid password for the user. Use the same values and format that you use with `isql` or `sqlplus`.

#### DB

Name of the database. The format varies depending on the database and version. Use the same values and format that you use with `isql` or `sqlplus`. Type 4 drivers, such as MSSQLServer4, need additional information to locate the server since they cannot access the environment.

### Examples

#### Oracle

Connect to Oracle from Java with WebLogic jDriver for Oracle using the same values that you use with sqlplus.

If you are not using SQLNet (and you have `ORACLE_HOME and ORACLE_SID` defined), follow this example:

`$ java utils.dbping ORACLE scott tiger`

If you are using SQLNet V2, follow this example:

`$ java utils.dbping ORACLE scott tiger TNS_alias`

where `TNS_alias` is an alias defined in your local `tnsnames.ora` file.

#### Microsoft SQL Server (Type 4 driver)

To connect to Microsoft SQL Server from Java with WebLogic jDriver for Microsoft SQL Server, you use the same values for `user` and `password` that you use with `isql`. To specify the SQL Server, however, you supply the name of the computer running the SQL Server and the TCP/IP port the SQL Server is listening on. To log into a SQL Server running on a computer named `mars` listening on port 1433, enter:

`$ java utils.dbping MSSQLSERVER4 sa secret mars:1433`

You could omit "`:1433`" in this example since 1433 is the default port number for Microsoft SQL Server. By default, a Microsoft SQL Server may not be listening for TCP/IP connections. Your DBA can configure it to do so.

# Troubleshooting JDBC

The following sections provide troubleshooting tips.

## JDBC Connections

If you are testing a connection to WebLogic, check the WebLogic Server log. By default, the log is kept in a file with the following format:

>     domain\server\server.log

Where `domain` is the root folder of the domain and `server` is the name of the server. The server name is used as a folder name and in the log file name.

### Windows

If you get an error message that indicates that the .`dll` failed to load, make sure your `PATH` includes the 32-bit database-related `.dlls`.

### UNIX

If you get an error message that indicates that an .`so` or an .`sl` failed to load, make sure your `LD_LIBRARY_PATH` or `SHLIB_PATH` includes the 32-bit database-related files.

## Codeset Support

WebLogic supports Oracle codesets with the following consideration:

- If your NLS_LANG environment variable is not set, or if it is set to either US7ASCII or WE8ISO8859-1, the driver always operates in 8859-1.
- If the NLS_LANG environment variable is set to a different value than the codeset used by the database, the Oracle Thin driver and the WebLogic jDriver for Oracle use the client codeset when writing to the database.

For more information, see Codeset Support in Using WebLogic jDriver for Oracle.

## Other Problems with Oracle on UNIX

Check the threading model you are using. Green threads can conflict with the kernel threads used by OCI. When using Oracle drivers, WebLogic recommends that you use native threads. You can specify this by adding the `-native` flag when you start Java.

## Thread-related Problems on UNIX

On UNIX, two threading models are available: green threads and native threads. For more information, read about the JDK for the Solaris operating environment on the Sun Web site`.`

You can determine what type of threads you are using by checking the environment variable called `THREADS_TYPE.` If this variable is not set, you can check the shell script in your Java installation bin directory.

Some of the problems are related to the implementation of threads in the JVM for each operating system. Not all JVMs handle operating-system specific threading issues equally well. Here are some hints to avoid thread-related problems:

- If you are using Oracle drivers, use native threads.
- If you are using HP UNIX, upgrade to version 11.x, because there are compatibility issues with the JVM in earlier versions, such as HP UX 10.20.
- On HP UNIX, the new JDK does not append the green-threads library to the `SHLIB_PATH`. The current JDK can not find the shared library (`.sl`) unless the library is in the path defined by `SHLIB_PATH`. To check the current value of `SHLIB_PATH`, at the command line type:

>     $ echo $SHLIB_PATH

Use the `set` or `setenv` command (depending on your shell) to append the WebLogic shared library to the path defined by the symbol `SHLIB_PATH`. For the shared library to be recognized in a location that is not part of your `SHLIB_PATH`, you will need to contact your system administrator.

## Closing JDBC Objects

BEA Systems recommends—and good programming practice dictates—that you always close JDBC objects, such as `Connections`, `Statements`, and `ResultSets`, in a `finally` block to make sure that your program executes efficiently. Here is a general example:

>     try {

>     Driver d =
>     (Driver)Class.forName("weblogic.jdbc.oci.Driver").newInstance();

>     Connection conn = d.connect("jdbc:weblogic:oracle:myserver",
>                                       "scott", "tiger");

>         Statement stmt = conn.createStatement();
>         stmt.execute("select * from emp");
>         ResultSet rs = stmt.getResultSet();
>         // do work

>         }

>         catch (Exception e) {

>           // handle any exceptions as appropriate

>         }

>         finally {

>           try {rs.close();}
>           catch (Exception rse) {}
>           try {stmt.close();}
>           catch (Exception sse) {}
>           try {conn.close();
>           catch (Exception cse) {}

>         }

### Abandoning JDBC Objects

You should also avoid the following practice, which creates abandoned JDBC objects:

>     //Do not do this.
>     stmt.executeQuery();
>     rs = stmt.getResultSet();

>     //Do this instead
>     rs = stmt.executeQuery();

The first line in this example creates a result set that is lost and can be garbage collected immediately.

# Troubleshooting Problems with Shared Libraries on UNIX

When you install a native two-tier JDBC driver, configure WebLogic Server to use performance packs, or set up BEA WebLogic Server as a Web server on UNIX, you install shared libraries or shared objects (distributed with the WebLogic Server software) on your system. This document describes problems you may encounter and suggests solutions for them.

The operating system loader looks for the libraries in different locations. How the loader works differs across the different flavors of UNIX. The following sections describe Solaris and HP-UX.

## WebLogic jDriver for Oracle

Use the procedures for setting your shared libraries as described in this document. The actual path you specify will depend on your Oracle client version, your Oracle Server version and other factors. For details, see Installing WebLogic jDriver for Oracle.

## Solaris

To find out which dynamic libraries are being used by an executable you can run the `ldd` command for the application. If the output of this command indicates that libraries are not found, then add the location of the libraries to the `LD_LIBRARY_PATH` environment variable as follows (for C or Bash shells):

>     # setenv LD_LIBRARY_PATH weblogic_directory/lib/solaris/oci817_8

Once you do this, `ldd` should no longer complain about missing libraries.

## HP-UX

### Incorrectly Set File Permissions

The shared library problem you are most likely to encounter after installing WebLogic Server on an HP-UX system is incorrectly set file permissions. After installing WebLogic Server, make sure that the shared library permissions are set correctly with the `chmod` command. Here is an example to set the correct permissions for HP-UX 11.0:

>     % cd WL_HOME/lib/hpux11/oci817_8

>     % chmod 755 *.sl

If you encounter problems loading shared libraries after you set the file permissions, there could be a problem locating the libraries. First, make sure that the `WL_HOME``/server/lib/hpux11` is in the `SHLIB_PATH` environment variable:

>     % echo $SHLIB_PATH

If the directory is not listed, add it:

>     # setenv SHLIB_PATH WL_HOME/server/lib/hpux11:$SHLIB_PATH

Alternatively, copy (or link) the .sl files from the WebLogic Server distribution to a directory that is already in the `SHLIB_PATH` variable.

If you still have problems, use the chatr command to specify that the application should search directories in the `SHLIB_PATH` environment variable. The +s enabled option sets an application to search the `SHLIB_PATH` variable. Here is an example of this command, run on the WebLogic jDriver for Oracle shared library for HP-UX 11.0:

>     # cd weblogic_directory/lib/hpux11

>     # chatr +s enable libweblogicoci38.sl

Check the `chatr` man page for more information on this command.

### Incorrect SHLIB_PATH

You may also encounter a shared library problem if you do not include the proper paths in your `SHLIB_PATH` when using Oracle 9. SHLIB_PATH should include the path to the driver (`oci901_8`) and the path to the vendor-supplied libraries (`lib32`). For example, your path may look like:

>     export SHLIB_PATH=
>     $WL_HOME/server/lib/hpux11/oci901_8:$ORACLE_HOME/lib32:$SHLIB_PATH

Note also that your path cannot include the path to the Oracle 8.1.7 libraries, or clashes will occur. For more instructions, see Setting Up the Environment for Using WebLogic jDriver for Oracle.

# Using Microsoft SQL with Nested Triggers

The following section provides troubleshooting information when using nested triggers on some Microsoft SQL databases:

- Exceeding the Nesting Level
- Using Triggers and EJBs

For information on supported data bases and data base drivers, see Supported Configurations.

## Exceeding the Nesting Level

You may encounter a SQL Server error indicating that the nesting level has been exceeded on some SQL Server databases.

For example:

>     CREATE TABLE EmployeeEJBTable (name varchar(50) not null,salary int, card  varchar(50), primary key (name))

>     CREATE TABLE CardEJBTable (cardno varchar(50) not null, employee varchar(50), primary key (cardno), foreign key (employee) references EmployeeEJB Table(name) on delete cascade)

>     CREATE TRIGGER  card on EmployeeEJBTable for delete as delete CardEJBTable  where employee in (select name from deleted)

>     CREATE TRIGGER  emp on CardEJBTable for delete as delete EmployeeEJBTable where card in (select cardno from deleted)

>     insert into EmployeeEJBTable values ('1',1000,'1')

>     insert into CardEJBTable values ('1','1')

>     DELETE FROM CardEJBTable WHERE cardno = 1

Results in the following error message:

>     Maximum stored procedure, function, trigger, or view nesting level exceeded  (limit 32).

To work around this issue, do the following:

1. Run the following script to reset the nested trigger level to 0:

>     -- Start batch
>     exec sp_configure 'nested triggers', 0  -- This set's the new value.
>     reconfigure with override -- This makes the change permanent
>     -- End batch

2. Verify the current value the SQL server by running the following script:

>     exec sp_configure 'nested triggers'

## Using Triggers and EJBs

Applications using EJBs with a Microsoft driver may encounter situations when the return code from the `execute()` method is 0, when the expected value is 1 (1 record deleted).

For example:

>     CREATE TABLE EmployeeEJBTable (name varchar(50) not null,salary int, card  varchar(50), primary key (name))

>     CREATE TABLE CardEJBTable (cardno varchar(50) not null, employee varchar(50), primary key (cardno), foreign key (employee) references EmployeeEJB Table(name) on delete cascade)

>     CREATE TRIGGER  emp on CardEJBTable for delete as delete EmployeeEJBTable where card in (select cardno from deleted)

>     insert into EmployeeEJBTable values ('1',1000,'1')

>     insert into CardEJBTable values ('1','1')

>     DELETE FROM CardEJBTable WHERE cardno = 1

The EJB code assumes that the record is not found and throws an appropriate error message.

To work around this issue, run the following script:

>     exec sp_configure 'show advanced options', 1
>     reconfigure with override
>     exec sp_configure 'disallow results from triggers',1
>     reconfigure with override