Re: [JBoss-user] SQLJ in EJB connecting to Oracle

[Toby A. <tob...@pe...>]

On Tue, Apr 17, 2001 at 07:14:26PM +0200, Jose Ramon Diaz wrote:
> Hi,
> 
>    I have written a little EJB accesing to Oracle database 8.1.7 with
> SQLJ.
>    Is there any known bug known?
>    I have the next problem...
> 
>    I have a little EJB which has several methods that acces database. I
> have defined a Connection pool to the database, ands I obtain the
>                       connection like this:
> 
>                         InitialContext ctx = new InitialContext();
>                         DataSource ds = (DataSource)
> ctx.lookup("java:/OraclePool");
>                         c = ds.getConnection();
>                         DefaultContext.setDefaultContext(new
> DefaultContext(c));
> 
>                         After I use the connection I close it.
>                         My problem is that if two clients are at the
> same time accesing the EJB (everyone has made the home.create() ), they
> are sharing the  connections, because suddenly one of the clients says
> the connection is closed!

Are you closing the connection through the DefaultContext? When you set the
default context, you are setting it for the entire JVM. What you should do
is create new context for the connection and explictly use it in your SQLJ.

I can't remember the exact syntax or class names, but it'll be something like:

Connection c = ds.getConnection();
ConnectionContext cc = new ConnectionContext(c);
#sql[cc] {
    SELECT ...
};
cc.close();

Also, it looks like the variable c is an instance variable. You need to be
aware that a single instance of your bean can be used to service method
calls from many clients (just only one at a time).

Toby.