9 Troubleshooting JDBC
- Problems with Oracle Database on UNIX
If you have problems with an Oracle database running on Unix, check the threading model being used. When using Oracle drivers, WebLogic recommends that you use native threads. You can specify this by adding the-nativeflag when you start Java. - Thread-related Problems on UNIX
On UNIX, two threading models are available: green threads and native threads. You can determine what type of threads you are using by checking the environment variable calledTHREADS_TYPE. - Closing JDBC Objects
Oracle recommends—and good programming practice dictates—that you always close JDBC objects, such asConnections,Statements, andResultSets, in afinallyblock to make sure that your program executes efficiently. - Using Microsoft SQL Server with Nested Triggers
Learn about the troubleshooting information when using nested triggers with some Microsoft SQL Server databases.
Problems with Oracle Database on UNIX
If you have problems with an Oracle database running on Unix, check the threading model being used. When using Oracle drivers, WebLogic recommends that you use native threads. You can specify this by adding the -native flag when you start Java.
Parent topic: Troubleshooting JDBC
Thread-related Problems on UNIX
On UNIX, two threading models are available: green threads and native threads. You can determine what type of threads you are using by checking the environment variable called THREADS_TYPE.
For more information, read about the JDK for the Solaris operating environment at http://www.oracle.com/technetwork/java/index.html.
To determine the type of threads you are using check 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. If you are using Oracle drivers, use native threads.
Parent topic: Troubleshooting JDBC
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:
Example 9-1 Closing a JDBC Object
try {
Driver d =
(Driver)Class.forName("oracle.jdbc.OracleDriver").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) {}
}Parent topic: Troubleshooting JDBC
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.
Parent topic: Closing JDBC Objects
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).
To work around this issue, do the following:
Parent topic: Using Microsoft SQL Server with 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 overrideParent topic: Using Microsoft SQL Server with Nested Triggers