Troubleshooting JDBC

Problems with Oracle Database on UNIX

For database connectivity problems on UNIX, first confirm that the JDK, JDBC driver, database, and operating system combination is certified for this WebLogic Server release. Use the server log and the SQLException error code and message to identify the connection failure. Do not add historical JVM thread-selection options such as -native.

Closing JDBC Objects

Use try-with-resources to close JDBC resources on both success and failure. The following method uses an already configured DataSource and propagates SQLExceptions to its caller.

Here is a general example:

Explicit finally cleanup is an alternative for code that does not use try-with-resources.

Example 8-1 Closing a JDBC Object

void readEmployees(javax.sql.DataSource ds) throws java.sql.SQLException {
    try (java.sql.Connection conn = ds.getConnection();
         java.sql.Statement stmt = conn.createStatement();
         java.sql.ResultSet rs = stmt.executeQuery("select * from emp")) {
        while (rs.next()) {
            // Process the current row.
        }
    }
}

Abandoning JDBC Objects

Keep a reference to the ResultSet returned by executeQuery and close it when processing finishes. A Statement retains its current ResultSet, so discarding the returned reference does not imply immediate garbage collection. Use try-with-resources to close the result reliably.

try (java.sql.PreparedStatement stmt = conn.prepareStatement("select * from emp");
     java.sql.ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
        // Process the current row.
    }
}

conn is an open application-owned JDBC connection and must be closed by its owner.

Using Microsoft SQL Server with Nested Triggers

Learn about the troubleshooting information when using nested triggers with some Microsoft SQL Server databases.

For information on supported data bases and data base drivers, see the Oracle Fusion Middleware Supported System Configurations page at http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html.

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

Review and correct unintended recursive or cascading trigger logic first. If the database administrator determines that cascading AFTER triggers are not required by any application on this SQL Server instance, the administrator can set the server-wide nested triggers option to 0. This changes trigger behavior for the instance; it does not reset a per-request nesting counter. INSTEAD OF trigger nesting is not controlled by this setting.

Consult the SQL Server documentation for required permissions and configuration prerequisites:

Server configuration: nested triggers

If the database administrator has confirmed the condition above, run the following script to disable cascading AFTER triggers:

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

Verify the SQL Server instance setting with EXEC sp_configure 'nested triggers';
```
exec sp_configure 'nested triggers' 
```

Using Triggers and EJBs

Statement.execute() returns a boolean indicating whether the first result is a ResultSet. Obtain update counts with getUpdateCount(), or use executeUpdate() when the statement has the appropriate single-update contract. A trigger can generate additional update-count messages. Where those messages are not part of the application contract, include SET NOCOUNT ON inside the trigger to suppress them and validate the behavior with the application’s JDBC driver. The SQL Server disallow results from triggers option rejects result sets returned by triggers; it is not an update-count repair.

See the following references: