4 Replies Latest reply: Sep 8, 2009 4:52 PM by Joe Weinstein-Oracle RSS

    Connection pool test table not working with SQL option

    user65579
      I have set up a connection pool data source for the Microsoft latest 2.0 driver (March, 2009). I copied the sqljdbc.jar to weblogic-10.3\wlserver_10.3\server\lib and put the jar in the classpath by editing setDomainEnv.cmd. When I create the DataSource, I can test it successfully, but after I associate it with the application and try to test it, I get

      Message icon - Error Warning! Connectivity to backend database not verified. This is either because required connection pool attribute "TestConnectionsOnReserve" has not been enabled, or an invalid value has been specified for attribute "TestTableName". Please check the server log for more details..

      The log shows:

      <Error> <JDBC> <WCLARK> <examplesServer> <[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1252442736017> <BEA-001112> <Test "select count(*) from sql select 1" set up for pool "ccDataSource" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'sql'.".>

      As you can see from the contents of weblogic-10.3\wlserver_10.3\samples\domains\wl_server\config\jdbc\ccDataSource-7381-jdbc.xml below, I am using the "sql select 1", which is documented to be treated as a sql statement, and not a table name. From the look of the log message, it is being treated as a table name. Can you spot anything I am doing wrong?

      <?xml version='1.0' encoding='UTF-8'?>
      <jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/jdbc-data-source" xmlns:sec="http://www.bea.com/ns/weblogic/90/security" xmlns:wls="http://www.bea.com/ns/weblogic/90/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bea.com/ns/weblogic/jdbc-data-source http://www.bea.com/ns/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd">
      <name>ccDataSource</name>
      <jdbc-driver-params>
      <url>jdbc:sqlserver://wclark:2008;databasename=pxuni;user=sa</url>
      <driver-name>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-name>
      <properties>
      <property>
      <name>sendStringParametersAsUnicode</name>
      <value>true</value>
      </property>
      </properties>
      <password-encrypted>{3DES}lGtt42wOTnE=</password-encrypted>
      </jdbc-driver-params>
      <jdbc-connection-pool-params>
      <initial-capacity>1</initial-capacity>
      <max-capacity>15</max-capacity>
      <capacity-increment>1</capacity-increment>
      <shrink-frequency-seconds>900</shrink-frequency-seconds>
      <highest-num-waiters>2147483647</highest-num-waiters>
      <connection-creation-retry-frequency-seconds>0</connection-creation-retry-frequency-seconds>
      <connection-reserve-timeout-seconds>10</connection-reserve-timeout-seconds>
      <test-frequency-seconds>120</test-frequency-seconds>
      <test-connections-on-reserve>false</test-connections-on-reserve>
      <ignore-in-use-connections-enabled>true</ignore-in-use-connections-enabled>
      <inactive-connection-timeout-seconds>0</inactive-connection-timeout-seconds>
      <test-table-name>SQL SELECT 1</test-table-name>
      <login-delay-seconds>0</login-delay-seconds>
      <init-sql></init-sql>
      <statement-cache-size>0</statement-cache-size>
      <statement-cache-type>LRU</statement-cache-type>
      <remove-infected-connections>true</remove-infected-connections>
      <seconds-to-trust-an-idle-pool-connection>10</seconds-to-trust-an-idle-pool-connection>
      <statement-timeout>-1</statement-timeout>
      <pinned-to-thread>false</pinned-to-thread>
      </jdbc-connection-pool-params>
      <jdbc-data-source-params>
      <jndi-name>ccDataSource</jndi-name>
      <global-transactions-protocol>None</global-transactions-protocol>
      </jdbc-data-source-params>
      </jdbc-data-source>