SQL Trace output concern with mapped DS

[wcydaip]

I have two datasource mappings pointed at the same server-database combination. The two are used by different applications as to differentiate between testing and release code. Each datasource has their own respective login to the database, but, use the same group of classes to access the db.

I just released a new version of one of my apps and found that at times it appears to be confusing the datasource choice. i.e., I tell it to use ds1, however, it looks to be using ds2.

I'm running JBossAS-4.0.2 on a W2K box against SQLServer2K.

Here's the connection object:

...

/**
 *
 * Handles connections to multiple databases.
 */
public class AppHelper
{
 private static AppHelper appHelperInstance = null;
 public static final String SC_DEFAULT_DS_CON = Constants.DEFAULT_DS_CON_VALUE;
 public static final String SC_CATS_DS_MAIN = Constants.CATS_DS_MAIN_VALUE;

 ...

 //--------------------------------------------------------------------
 /**
 * Static method used to retrieve an instance of the object (or to create
 * a new instance, if none exists.)
 * @return AppHelper app helper instance
 */
 public static synchronized AppHelper getInstance() {
 logger.debug("entering getInstance()");
 appHelperInstance = (null == appHelperInstance) ?
 new AppHelper() : appHelperInstance;
 logger.debug("leaving getInstance()");
 return appHelperInstance;
 }

 private AppHelper(){
 logger.debug("creating the AppHelper Singleton");
 logger.debug("leaving AppHelper Singleton");
 }

 ...

 synchronized public Connection getConnection(String ds)throws NamingException, SQLException
 {
 logger.debug("entering getConnection()");
 logger.info("get connection for "+ds);

 DataSource dataSource = initializeDataSource(ds);

 Connection conn = null;

 try {
 conn = dataSource.getConnection();
 logger.info("connection:"+conn.toString());
 } catch (NullPointerException e) {
 logger.error("caught np exception getting connection from cached resource!");
 logger.info("attempting to restart the datasource...");
 /*
 * If the dataSource has been shutdown or perhaps
 * the database server has been restarted, then, these
 * exception handlers will restart the datasource...
 * NOTE: All works fine if a user is in the middle of using the app,
 * however, since the Security relies on JBoss CMP then
 * are unable to authenticate
 */
 conn = resetDataSource(ds);
 logger.info("connection established!");
 } catch (SQLException e) {
 logger.error("caught sql exception getting connection from cached resource!");
 logger.info("sql ex->"+e.getMessage());
 logger.info("attempting to restart the datasource...");
 /*
 * If the dataSource has been shutdown or perhaps
 * the database server has been restarted, then, these
 * exception handlers will restart the datasource...
 * NOTE: All works fine if a user is in the middle of using the app,
 * however, since the Security relies on JBoss CMP then
 * are unable to authenticate
 */
 conn = resetDataSource(ds);
 logger.info("connection established!");
 }

 //conn.setAutoCommit(true);
 logger.debug("leaving getConnection()");
 return conn;
 }

 ...

}

A call that switches ds:

con = AppHelper.getInstance().getConnection(
 AppHelper.SC_DEFAULT_DS_CON);
// appears not to use the default ds con
ps = con.prepareStatement(
 "UPDATE TempNewTest SET" +
 "TC = ? " +
 "WHERE SessionID = ? " +
 "AND TC = ?");

The Constants static lookup:

static {
try {
 resourceBundle = ResourceBundle.getBundle("util");

 DEFAULT_DS_CON_VALUE = resourceBundle.getString(DEFAULT_DS_CON);
 CATS_DS_MAIN_VALUE = resourceBundle.getString(CATS_DS_MAIN);

 } catch(Exception e) {
 System.err.println("Caught exception getting util.properties resource bundle.");
 }
 }

The value:

default.ds.con=java:DefaultDS
cats.ds.con=java:CATS_DS_MAIN

In the trace the expected login for the call is not the one listed in the DefaultDS file. Another strange thing is that it's only with Insert and Update calls. All the Selects are done with the correct login.

If anyone could validate this or perhaps suggest something that I might be able to do to figure this out would be greatly appreciated.

Thanks.

[weston.price]

Can you post your *-ds.xml file? Also, I am interested in the initialization methods

initializeDataSource()
resetX()

Also, as a design point, what is the reason for the AppHelper? Typically this can be done just by JNDI name without the need for a wrapping Singleton to manage this.

[wcydaip]

Weston,

Thanks for the reply.

The AppHelper works like a ServiceLocator. One place to call and get whichever ds is needed. I added the Singleton thought it made sense. :)

I began caching the ds references early on and realized that was a bad choice, so, the reset is basically an old call that I never changed.

Here's the AppHelper with the excluded methods:

 public static synchronized AppHelper getInstance() {
 logger.debug("entering getInstance()");
 appHelperInstance = (null == appHelperInstance) ?
 new AppHelper() : appHelperInstance;
 logger.debug("leaving getInstance()");
 return appHelperInstance;
 }

 private AppHelper(){
 logger.debug("creating the AppHelper Singleton");
 logger.debug("leaving AppHelper Singleton");
 }

 private DataSource initializeDataSource(String dataSourceJNDI) throws NamingException{
 logger.debug("entering initializeDataSource()");
 DataSource dataSource = null;
 try{
 InitialContext ic = new InitialContext();
 dataSource = (DataSource) ic.lookup(dataSourceJNDI);
 }catch (NamingException ne){
 throw new NamingException("NamingException while looking" +
 " up DataSource Connection "
 + ": \n" + ne.getMessage());
 }
 logger.debug("leaving initializeDataSource()");
 return dataSource;
 }

 synchronized public Connection getConnection(String ds)throws NamingException, SQLException
 {
 logger.debug("entering getConnection()");
 logger.info("get connection for "+ds);

 DataSource dataSource = initializeDataSource(ds);

 Connection conn = null;

 try {
 conn = dataSource.getConnection();
 logger.info("connection:"+conn.toString());
 } catch (NullPointerException e) {
 logger.error("caught np exception getting connection from cached resource!");
 logger.info("attempting to restart the datasource...");
 /*
 * If the dataSource has been shutdown or perhaps
 * the database server has been restarted, then, these
 * exception handlers will restart the datasource...
 * NOTE: All works fine if a user is in the middle of using the app,
 * however, since the Security relies on JBoss CMP then
 * are unable to authenticate
 */
 conn = resetDataSource(ds);
 logger.info("connection established!");
 } catch (SQLException e) {
 logger.error("caught sql exception getting connection from cached resource!");
 logger.info("sql ex->"+e.getMessage());
 logger.info("attempting to restart the datasource...");
 /*
 * If the dataSource has been shutdown or perhaps
 * the database server has been restarted, then, these
 * exception handlers will restart the datasource...
 * NOTE: All works fine if a user is in the middle of using the app,
 * however, since the Security relies on JBoss CMP then
 * are unable to authenticate
 */
 conn = resetDataSource(ds);
 logger.info("connection established!");
 }

 //conn.setAutoCommit(true);
 logger.debug("leaving getConnection()");
 return conn;
 }

 private Connection resetDataSource(String dataSourceJNDI)throws NamingException, SQLException{
 // try and restart it
 DataSource ds = initializeDataSource(dataSourceJNDI);
 return ds.getConnection();
 }

The data source's are:

<datasources>
 <local-tx-datasource>
 <jndi-name>DefaultDS</jndi-name>

<connection-url>jdbc:microsoft:sqlserver://####:1433;DatabaseName=Main;SelectMethod=cursor</connection-url>
 <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
 <user-name>reg</user-name>
 <password>****</password>
 <check-valid-connection-sql>SELECT * FROM Site(NOLOCK) WHERE SiteID = 179</check-valid-connection-sql>
 <!-- pooling parameters -->
 <min-pool-size>5</min-pool-size>
 <max-pool-size>10</max-pool-size>
 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>MS SQLSERVER2000</type-mapping>
 </metadata>
 <idle-timeout-minutes>0</idle-timeout-minutes>
 </local-tx-datasource>
</datasources>

And the other:

<datasources>
 <local-tx-datasource>
 <jndi-name>CATS_DS_MAIN</jndi-name>

<connection-url>jdbc:microsoft:sqlserver://####:1433;DatabaseName=Main;SelectMethod=cursor</connection-url>
 <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
 <!-- Use the security domain defined in conf/login-config.xml -->
 <security-domain>PortalCatsEncryptDBPassword</security-domain>
 <!-- ensures a valid connection -->
 <check-valid-connection-sql>SELECT * FROM Site(NOLOCK) WHERE SiteID = 179</check-valid-connection-sql>
 <!--pooling parameters-->
 <min-pool-size>5</min-pool-size>
 <max-pool-size>10</max-pool-size>
 <idle-timeout-minutes>0</idle-timeout-minutes>
 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>MS SQLSERVER2000</type-mapping>
 </metadata>
 </local-tx-datasource>
</data-sources>

and the login-config entry:

<application-policy name = "PortalCatsEncryptDBPassword">
 <authentication>
 <login-module code = "org.jboss.resource.security.SecureIdentityLoginModule"
 flag = "required">
 <module-option name = "username">portal</module-option>
 <module-option name = "password">-1c4fcf3f77be50b7</module-option>
 <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=CATS_DS_MAIN</module-option>
 </login-module>
 </authentication>
 </application-policy>

The trace is done using the SQL Profiler. The expected login was reg, however, the trace shows the Insert and Updates performed by portal.

Thanks again.

Graham

[weston.price]

As a test I would recommend not using your Singleton. Just do a simple JNDI lookup of the appropriate DS as needed. This will eliminate the AppHelper as the cause of the problem and give me a better idea if this is a JBoss issue.

[wcydaip]

Weston,

WOW! What a sneaky little bugger that was. Thanks for the tips. Removing the Singleton was the way to go as it now does what was expected.

Thanks.

Graham

[weston.price]

Well, I had a feeling :-)

Glad it's working.