#11 Support of AQOracleDriver sessions

[Dorte Munk-Jakobsen]

Could you please look at the following and tell me
whether there is any chance that this can work with
Proxool in the near future.
Thank you
Dorte Munk

Due to problems with our current connection pool we are
currently evaluating the Proxool connection pool. There I
get the following error when I try create an AQ session
(for oracle queueing) on a connection returned by the
Proxool pool:
oracle.AQ.AQException: JMS-112: Connection is invalid
backtrace holds the following:
Erreur com.sun.jdi.ClassNotPreparedException lors de la
recherche de méthode pour le sélecteur "toString" avec
la signature "()Ljava/lang/String;"

The program below reproduces the problem, you need
aqapi.jar, it is a Oracle library. We use Oracle 8.1.7 and
JDK 1.3.

Example.java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.AQ.AQDriverManager;
import oracle.AQ.AQException;
import oracle.AQ.AQSession;

import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;

public class Example {
/**
* properties of the connection pool
*/
private Properties info = new Properties();

String driver = "oracle.jdbc.driver.OracleDriver";
String connection
= "jdbc:oracle:thin:@<machine>:<port>:<dbname>";

public Example() {
}

public void doit() {
Connection conn1 = null;
Example tp = new Example();
try {
Class.forName
("org.logicalcobwebs.proxool.ProxoolDriver");
} catch (ClassNotFoundException e) {
System.out.println
("ConnectionPool(): " + e);
}
info.setProperty("proxool.maximum-
connection-count", "500");
info.setProperty("proxool.minimum-
connection-count", "3");
info.setProperty(
"proxool.fatal-sql-exception",
"1034,Connection reset by
peer,Broken pipe");
info.setProperty("user", "usr");
info.setProperty("password", "pwd");
String url = "proxool." + "example"
+ ":" + driver + ":" + connection;
try {

ProxoolFacade.registerConnectionPool(url, info);
} catch (ProxoolException e) {
System.out.println
("ConnectionPool(): " + e);
}
try {
conn1 =
DriverManager.getConnection("proxool.example");
} catch (SQLException e) {
System.out.println("could
not get connection " + e);
}
try {
Class.forName
("oracle.AQ.AQOracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("" + e);
}

/* Create an AQ Session: */
try {
AQSession aqSession =
AQDriverManager.createAQSession(conn1);
} catch (AQException e1) {
System.out.println("problem
getting session " + e1);
return;
}
System.out.println("got connection 1"
+ conn1.toString());
String query = "select count(*) from
content";
try {
Statement stmt =
conn1.createStatement();
ResultSet rs =
stmt.executeQuery(query);
rs.close();
} catch (SQLException e) {
System.out.println(e);
}
try {
conn1.close();
} catch (SQLException e) {
System.out.println(e);
}
}

public static void main(String[] args) {
Example tcp = new Example();
tcp.doit();
}
}

[Bill Horsman]

Logged In: YES
user_id=91747

As a test, can you:

a) Use the conn1 you make to do something else to the database.

b) pass a "hand made" connection (i.e. not pooled) to the
AQDriverManager

[Dorte Munk-Jakobsen]

Logged In: YES
user_id=957432

I have already tried both
a) works without problems
b) works without problems

my current work around solution is to use the pooled
connection to get the delegate connection in the cases
where we use the AQSession, this implies quite a number of
changes to our code that I had hoped to avoid, but it works.
On the other hand I would like to know if there is an easy
way to get the id of a pooled connection in order to be able
to use killConnecton(id) when the connection is in a
completely unreparable state?
thanks

[Bill Horsman]

Logged In: YES
user_id=91747

Thanks for running thet tests. It would seem that the AQ
Session requires an Oracle connection. And, you're right,
the only way of coping with that is to ask Proxool for the
delegate connection. In which case, you have to be careful
to close the original Proxool connection (which returns it
to the pool) rather than the delegate connection (which will
close the actual connection to the database).

As for getting the id so that you can kill it... There is no
API to allow you to do that at this time.

The whole field of working with specific connection types is
a can of worms, IMHO. If your code relies on those features
then you are tied into that database and that driver. I
guess you are happy with that situation though.

So, I think we could add something to the API in order for
you to lookup the connection information for a specific
connection. That would be a feature request though - so I'm
making this bug into one. Never done that before. Let's see
if it works.

- Bill

[Dorte Munk-Jakobsen]

Logged In: YES
user_id=957432

It is not the specific connection id I need it's the id for the
ProxoolConnection, I need it in order to be able to call
ProxoolFacade.killConnecton(id) with the feeble hope that we
will be able to get rid off ie close connection in states where
the OracleConnectionCacheImpl is no longer able to rid us of
the connections. It is a case that should never occur in
production, but since we are able to provoke it in test it is
sure to occur some day, so better safe than sorry.
I have a way to get the id but is is quite clumsy, running
through all the connections until I find the one that has my
interest, therefor the question.
Hmm I might actually cheat, the main problem is that
ProxoolConnection is private (or protected) so I can't access
it, but since I have the source I could turn it public. Is there
any specific reason for it to be hidden?
Dorte

[Bill Horsman]

Logged In: YES
user_id=91747

The reason it is hidden to be keep the Proxool API clean.
Once you start using classes like that you are going to have
to recode if we change any of them. We change "internal"
classes at the drop of a hat. We change the public API only
if we absolutely have to.

I would prefer to do one of:

a) add a getConnectionId(String alias, Connection
delegateConnection) method to ProxoolFacade, or

b) override killConnection to take a connection object. And
make it flexible enough to work with either the proxy
connection or the delegate connection.

Are you confident that your application closes the Proxool
connection properly?

Also, remember that by using the delegate connection you
bypass a lot of Proxool's features. Like fatal sql
connection trapping, onExecute listener events, trace
logging, etc.

Good luck.
- Bill

[Bill Horsman]

Logged In: YES
user_id=91747

Option b was implemented in 0.9.0RC1:

b) override killConnection to take a connection object. And
make it flexible enough to work with either the proxy
connection or the delegate connection.