FAQs: WebLogic JDBC

• When should I use a TxDataSource instead of a DataSource?

• Why do I get "java.sql.SQLException: weblogic.common.ResourceException: Access not allowed" when trying to connect to a DataSource in WebLogic Server 7.0?

• Can I enable requests to a JDBC connection pool for a database connection to wait until a connection is available?

• How can I avoid ResourceExceptions when sending more requests for database connections from the pool than are currently available?

• How should I use MultiPools?

• How can I tell if a database is unavailable?

• Can the WebLogic JDriver for Microsoft SQL Server connect to the database server using a trusted connection on NT/WIN2K?

• Can I use the PointBase DBMS included with WebLogic Server for development or production?

Q. When should I use a TxDataSource instead of a DataSource?

A. If your applications or environment meet any of the following criteria, you should use a Tx Data Source instead of a Data Source:

• Uses the Java Transaction API (JTA)

• Uses the EJB container in WebLogic Server to manage transactions

• Includes multiple database updates within a single transaction

• Accesses multiple resources, such as a database and the Java Messaging Service (JMS), during a transaction

• Uses the same connection pool on multiple servers

With an EJB architecture, it is common for multiple EJBs that are doing database work to be invoked as part of a single transaction. Without XA, the only way for this to work is if all transaction participants use the exact same database connection. WebLogic Server uses the JTS driver and a TxDataSource (with Emulate Two-Phase Commit for non-XA Driver selected) to do this behind the scenes without requiring you to explicitly pass the JDBC connection from EJB to EJB. With XA (requires an XA driver), you can use a Tx Data Source in WebLogic Server for distributed transactions with two-phase commit so that EJBs can use a different database connections for each part of the transaction. In either case (with or without XA), you should use a Tx Data Source.

Q. Why do I get "java.sql.SQLException: weblogic.common.ResourceException: Access not allowed" when trying to connect to a DataSource in WebLogic Server 7.0?

A. You need to create the correct ACL for the DataSource following this procedure in the Administration Console:

1. Compatibility Security => ACLs
   Create a new ACL:
   name : weblogic.jdbc.connectionPool.yourPoolname
   permission : admin
   group : Administrators

2. Compatibility => click Refresh button

3. Services => JDBC => Connection Pool
   Create a new Connection Pool:
   ACL Name : weblogic.jdbc.connectionPool.yourPoolname
   In 'Target' tab, choose server and click the Apply button.

4. Services => JDBC => Data Sources
   You can create a new Data Source using this connection pool successfully

Q. Can I enable requests to a JDBC connection pool for a database connection to wait until a connection is available?

A. No, there's no way to allow a request to wait for a pool connection, and from the system point of view there should not be. Each requests that waits for a connection ties up one of the fixed number of execute threads in the server, which could otherwise be running another server task. Too many waiting requests could tie up all of the execute threads and freeze the server.

Q. How can I avoid ResourceExceptions when sending more requests for database connections from the pool than are currently available?

A. The fundamental problem is too few resources (database connections in the connection pool) for the work load. The correct response is to increase the maximum number of connections in the connection pool. Optimally designed applications only require the server to have one pool connection per execute thread.

The proper application response to a resource exception is not to retry the request in a tight loop, which would tie up execute threads on the server.

You should design your application to gracefully fail if no connections are available. Try to ensure that you get the connection as late as possible in your application code and return them to the pool as early as possible so that you do not see as many NoResource exceptions. It is better to have the connection as a method level variable and close the connection in a finally block as in the following example:

try{
  ...
} catch(Exception handleEx) {
  ...
} finally {
  try{ conn.close(); 
}catch (Exception ignore){} // always return the connection to pool
} 

Q. How should I use MultiPools?

A. You can use MultiPools in one of two ways 1) for high availability in the event a database connection fails, or 2) for load balancing MultiPool. Because you can choose only one option, you need to determine the primary purpose of your MultiPool. For more information, see Using Multipools in the Configuring and Administering WebLogic JDBC Features section of Programming WebLogic JDBC.

Q. How can I tell if a database is unavailable?

A. Fundamentally, there is no way to tell if a database has gone down except by trying to make a connection and failing.

Furthermore, a database can become unavailable at any time after you make and use a connection. We recommend that you write your code to be able to handle unexpected failures, which can come in any form depending on what the client is doing when the database goes down.

WebLogic Server does provide the dbping utility to test the connection between WebLogic Server and your DBMS using a JDBC driver. See dbping in "Using the WebLogic Java Utilities" in the Administration Guide.

Q. Can the WebLogic JDriver for Microsoft SQL Server connect to the database server using a trusted connection on NT/WIN2K?

A. No, the WebLogic JDriver for Microsoft SQL Server does not support trusted connections. Note that the WebLogic JDriver for Microsoft SQL Server is deprecated and will be removed in WebLogic Server 8.1.

Q. Can I use the PointBase DBMS included with WebLogic Server for development or production?

A. PointBase Server is an all-Java DBMS product included in the WebLogic Server distribution soley in support of WebLogic Server evaluation, either in the form of custom trial applications or through packaged sample applications provided with WebLogic Server. Non-evaluation development and/or production use of the PointBase Server requires a separate license be obtained by the end user directly from PointBase.