5 Replies Latest reply on Aug 25, 2001 12:18 AM by David Jencks

    Initializing JDBC Connection

    Gregor Rayman Newbie

      Hi,

      I need to initialize the SQL Connection after it has been created. I am using Oracle8i and I have to set up the sorting and comparing language, some package variables etc.

      Is there a possibility, to tell JBoss/Minerva to execute some SQL Statements right after the connection has been created? Or do I have to "initialize" the connection each time get it from the datasource?

      (I know, I could use Oracle's log in trigger, but I don't like the solution, since it uses proprietary features of the database.

      Thanks for any hints.

      --
      gR

        • 1. 3709
          David Jencks Master

          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?

          • 2. Re: Initializing JDBC Connection
            Gregor Rayman Newbie

            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.

            --
            gR

            • 3. Re: Initializing JDBC Connection
              David Jencks Master

              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.

              • 4. Re: Initializing JDBC Connection
                Gregor Rayman Newbie

                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) . :-)

                --
                gR

                • 5. Re: Initializing JDBC Connection
                  David Jencks Master

                  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.