2 Replies Latest reply on Jan 11, 2005 8:41 AM by starksm64

    NewConnection SQL execution

    kalyan120 Jan 10, 2005 6:45 PM

      Hi,

      We are having a problem while creating a new connection from a connection pool. We have some temp tables that are to be created once the connection is created and before it is returned to the user. We have specified these tables in the new-connection-sql element of the *-ds.xml. For some of the connection pools this is working fine. And for some others, this isn't working fine. For some reason (unknown to me), we are getting a connection that is not initialized with the give sql. Hence when we try to execute a query, we are getting 'temp table not found' SQL exception. But this particular temp table is defined in the SQL given in new-connection-sql element.

      How can I make sure that the sql is executed whenever a connection is created in the pool? I have looked at the source code, and tried to search for any references to the method getNewConnectionSQL() in BaseWrapperManagedConnectionFactory class, but found none.

      Thanks,
      Kalyan.

      • 2558 Views
      • Tags:
        • 1. Re: NewConnection SQL execution
          kalyan120 Jan 10, 2005 11:38 PM (in response to kalyan120)

          We are using JBoss application server in our application as a middle-tier. We are using JBoss JCA to create connection pools to the required databases. In order to do this, we are generating a sybase-ds.xml file at the server startup and then JBoss loads this service and creates the specified connection pools. We require some temporary tables to be created for every connection that's created in the connection pool. So, in the sybase-ds.xml file, we are specifying the <new-connection-sql> element to contain the required SQL. We have quite a big SQL in some cases, that needs to be executed once the connection is created and is returned to the user for further use (It's as big as 1200 lines of SQL code).

          In instances where the SQL is small, the connection returned is initialized with the temp tables and everything works fine. But in situations where the SQL is big, only part of the SQL is getting executed. Only some temp tables were getting created and some of them were not getting created. Hence, we are facing problems when we run queries on these connections.

          I have looked at the code of JBoss to figure out how this <new-connection-sql> is getting used. The sybase-ds.xml file is transformed into an XML form of Mbean before JBoss tries to create the connection pools.
          This particular variable is present in org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory class. This class has accessor methods for this attribute. But, when I tried to do a search on the references to this variable, or any of these accessor methods, I wasn't able to find any. I want to output the SQL string that's being used to execute once the connection is created.

          Is there any way in which I can get to print this SQL string? Also, is anybody aware of any limits on the new-connection-sql property (it's a String object, so I don't think there would be any limit)?

            • Actions
          • 2. Re: NewConnection SQL execution
            starksm64
            starksm64 Jan 11, 2005 8:41 AM (in response to kalyan120)

            org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection uses it:

             /**
 * Checks whether a connection is valid
 */
 SQLException isValidConnection(Connection c)
 {
 // Already got a checker
 if (connectionChecker != null)
 return connectionChecker.isValidConnection(c);
 // Class specified
 if (validConnectionCheckerClassName != null)
 {
 try
 {
 ClassLoader cl = Thread.currentThread().getContextClassLoader();
 Class clazz = cl.loadClass(validConnectionCheckerClassName);
 connectionChecker = (ValidConnectionChecker) clazz.newInstance();
 return connectionChecker.isValidConnection(c);
 }
 catch (Exception e)
 {
 log.warn("Exception trying to create connection checker (disabling):", e);
 connectionChecker = new NullValidConnectionChecker();
 }
 }
 // SQL statement specified
 if (checkValidConnectionSQL != null)
 {
 connectionChecker = new CheckValidConnectionSQL(checkValidConnectionSQL);
 return connectionChecker.isValidConnection(c);
 }
 // No Check
 return null;
 }



              • Actions