There's no way to get sql executed automatically after a connection is created. Executing code each time you get a connection from the datasource is also not a great idea since the connections are pooled, you will end up reinitializing your connections over and over. The jca connector spec kind of supports something like this via ConnectionRequestInfo, however I don't know of any way to easily do this within the current jboss framework.
Could you be more specific about what you want to set and how? Are these some kind of session properties that persist over several transactions?
I am using Oracle 8.1.6. The database was created long ago and my application has to access legacy data and legacy business logic.
One of the problem is the locale setting. Oracle uses language dependent sorting and comparing, dependent oh the clients locale. In old applications, which did not pool connections I could initialze the session with the following statements:
ALTER SESSION SET NLS_SORT=BINARY
ALTER SESSION SET NLS_COMP=BINARY
Another issue are "logical databases". We use an additional column in (almost) each table called DBID and access those tables through views with
WHERE DBID = DB_LOGIN.GetDBID The function GetDBID looks into a package variable which should be set after logging in DB_LOGIN.SetDBID(x).
All this settings are part of frozen state, so they will be never changed during the sessions existence. The should survive several transactions and they are equal for all sessions from one application. (So they can be pooled)
All of this issues can be solved with an after login trigger directly in Oracle. But I do not like the idea very much, since it an Oracle specific solution.
Could you please send me more info (url to docu
or spec will be enogh) about the jca conectors in JBoss? I'd love the idea, if there were a possibility to create ones own Connection factory for the Minerva datasources or at least an event, which would be fired after a connection has been created.
For the sorting stuff - can you set this in the database? it looks like it is not user-specific.
For the "getting the right view" you might try per-user pools. I'm not sure if these are available without the jca adapters. In at least jboss 2.4 jboss.jcml there's an example of an adapter set up for Hypersonic. There's a short section in the manual on HowToCx about configuration and I'm working on more extensive documentation for the next version.
The jca spec is also pretty good but not always easy to read I find, and not jboss specific.
All this settings are application specific, no one is user specific. The value used in setDBID stuff is constant for one deployed version of the application.
The database however is used by more applications, so I cannot make them default for the database (at least not the setDBID stuff). But I can use Oracle's "after login" trigger. So I have don't have any real problem. What concerned me, was the question, what would I do, if the databse was Oracle 7.3 and not 8i?
I will read the JCA spec soon, if I'll find a solution, I will write a HOWTO (if the solution will not be ovious form your documentation) . :-)
There is a potential framework in jca for dealing with this sort of info, cci.ConnectionSpec and spi.ConnectionRequestInfo. However the current jdbc wrappers don't take account of these possibilities nor do e.g. JAWS or maybe the ConnectionManagers have the ability to supply the info. Writing an Oracle specific jca wrapper would be pretty easy, it might be a good idea anyway to hide their insistence on their own XID implementation, but I'm less sure how to make e.g. JAWS supply the appropriate info or maybe make ConnectionManager supply a default that you can configure.