Closing JDBC Objects

Closing JDBC Objects

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

Listing 7-1 Closing a JDBC Object

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.

---

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