You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Sailaja Ravipati <sr...@opentext.com> on 2016/05/17 10:58:49 UTC
Prepared statements are not cached with XA in tomcat jdbc
connection pool implementation
Hi
I am using TomEE version Apache Tomcat Version 7.0.63.
I have the following test program.
public static void main(String[] args) throws Exception
{
final TransactionManager transactionManager = TransactionManagerFactory
.getTransactionManager();
final PoolProperties poolProperties = new PoolProperties();
SQLServerDataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
dataSource.setUser("sa");
dataSource.setPassword("$9Lserver");
dataSource.setURL("jdbc:sqlserver://sdwivedi63ks022:1433;sendStringParametersAsUnicode=false");
dataSource.setDatabaseName("himalaya");
poolProperties.setDataSource(dataSource);
final String jdbcInterceptors = "org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,callable=true)";
poolProperties.setJdbcInterceptors(jdbcInterceptors);
final org.apache.tomcat.jdbc.pool.DataSource pooledOracleDatasource = new org.apache.tomcat.jdbc.pool.XADataSource(
poolProperties);
final javax.sql.DataSource oracleDataSource = new org.apache.openejb.resource.jdbc.managed.xa.ManagedXADataSource(
pooledOracleDatasource, transactionManager,
TransactionProvider.getTransactionSynchronizationRegistry());
Connection connection = oracleDataSource.getConnection();
for(int i=0; i<50; i++)
{
PreparedStatement preparedStatement = connection.prepareStatement("insert into MyTableNew values (" + i + ")");
System.out.println(preparedStatement.getClass().getName());
preparedStatement.execute();
preparedStatement.close();
}
connection.close();
}
If I run the above program, the output I see is:
com.sun.proxy.$Proxy11
If I just change the above program to use XA datasource, i.e.
Change the following line
SQLServerDataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
To
SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
The output is :
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
So, if I use XA datasource, prepared statements are not cached. How do I log a defect ?
Regards,
Sailaja.
Re: Prepared statements are not cached with XA in tomcat jdbc
connection pool implementation
Posted by Woonsan Ko <wo...@apache.org>.
I don't know if it is a bug or not, but I learned today that
org.apache.tomcat.jdbc.** is a different tomcat implementation from
the default dbcp based implementation (org.apache.tomcat.dbcp.**).
And, you seem to use the former one.
Therefore, I think you should file a bug in tomcat, not in commons-dbcp, IMHO:
- http://tomcat.apache.org/bugreport.html
Regards,
Woonsan
On Wed, May 18, 2016 at 12:51 AM, Sailaja Ravipati
<sr...@opentext.com> wrote:
> Thank you. Will do that.
>
> Regards,
> Sailaja.
>
> -----Original Message-----
> From: Christopher Schultz [mailto:chris@christopherschultz.net]
> Sent: 18 May 2016 01:24
> To: Tomcat Users List
> Subject: Re: Prepared statements are not cached with XA in tomcat jdbc connection pool implementation
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Sailaja,
>
> On 5/17/16 6:58 AM, Sailaja Ravipati wrote:
>> I am using TomEE version Apache Tomcat Version 7.0.63. I have the
>> following test program.
>>
>> public static void main(String[] args) throws Exception { final
>> TransactionManager transactionManager = TransactionManagerFactory
>> .getTransactionManager(); final PoolProperties poolProperties = new
>> PoolProperties(); SQLServerDataSource dataSource = new
>> com.microsoft.sqlserver.jdbc.SQLServerDataSource();
>> dataSource.setUser("sa"); dataSource.setPassword("$9Lserver");
>> dataSource.setURL("jdbc:sqlserver://sdwivedi63ks022:1433;sendStringPar
> ametersAsUnicode=false");
>>
>>
> dataSource.setDatabaseName("himalaya");
>> poolProperties.setDataSource(dataSource); final String
>> jdbcInterceptors =
>> "org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,
> callable=true)";
>>
>>
> poolProperties.setJdbcInterceptors(jdbcInterceptors);
>> final org.apache.tomcat.jdbc.pool.DataSource pooledOracleDatasource =
>> new org.apache.tomcat.jdbc.pool.XADataSource( poolProperties); final
>> javax.sql.DataSource oracleDataSource = new
>> org.apache.openejb.resource.jdbc.managed.xa.ManagedXADataSource(
>> pooledOracleDatasource, transactionManager,
>> TransactionProvider.getTransactionSynchronizationRegistry());
>> Connection connection = oracleDataSource.getConnection(); for(int i=0;
>> i<50; i++) { PreparedStatement preparedStatement =
>> connection.prepareStatement("insert into MyTableNew values (" + i +
>> ")"); System.out.println(preparedStatement.getClass().getName());
>> preparedStatement.execute(); preparedStatement.close(); }
>> connection.close(); }
>>
>> If I run the above program, the output I see is:
>> com.sun.proxy.$Proxy11
>>
>> If I just change the above program to use XA datasource, i.e.
>> Change the following line SQLServerDataSource dataSource = new
>> com.microsoft.sqlserver.jdbc.SQLServerDataSource(); To
>> SQLServerXADataSource dataSource = new
>> com.microsoft.sqlserver.jdbc.SQLServerXADataSource(); The output is
>> : com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
>>
>> So, if I use XA datasource, prepared statements are not cached. How do
>> I log a defect ?
>
> The problem would be in commons-dbcp, so you should file a bug report in JIRA here:
>
> http://commons.apache.org/proper/commons-dbcp/issue-tracking.html
>
> Bug reports with complete information (or even a patch!) will get more attention than those without.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Comment: GPGTools - http://gpgtools.org
> Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
>
> iQIcBAEBCAAGBQJXO3bFAAoJEBzwKT+lPKRY1oAP/iy4c6LQNx/Gcg+24MJry0/t
> l30JkJsgeCa6uXgs7JUjCLxmaPjhCF3Fw4FtzwzqUgEY13yTF7jlFhvuq1elOoQx
> w0eHpcW39iQ0DJvGvbz6cEpsR902mSSXyt6Ar7AAeZoAs7AlYKhLmp9wKBdqq1pq
> PSJcBhrwxA0OcIgDscCj667diUYmmi914LRSUXNwiCCH3ymAMsoXFi63FD1/VxEf
> W3vqKxJvmKwhlP1Jia0T9mCusqmwQfct2lJXuU+W9dDyCogkBdv4anDBivdRVJA8
> VYRzHPjQQQOg7dwmXkulRLp1vToqFFaAFDhUVL+RSTX+R+Ee9fg+vqssUD4U6ba/
> GCTXMTMck2/n06dDcNcpfF5M20nFDQ8eBUDqsmbP+NBMrTZfdyUTihCaoRW1x1rv
> +YVJuD5TA6bNC6n70bWMLT2ybs3cxplB7lwPueO0t88nBehDqsz+XRSS64Si6hXn
> wjKaW+CO7tVMhRmf8g9yKFKppklIUCpWX12iyJ4SRWtAVRIBcZ47HFgXfukPo03A
> DcUwpjsSHgvyOUpwWe31mVeQMyY5VDu8lFSjqmaHthqQGvRnGaYUPD3VJd71mobV
> AqkNTk3xcySN/sirUx6KVm6rwYClIvN2xQswn5cvFeRjRFO6BCGemjsW7+aF40Mu
> +Q3ahYmlTYBownFshBV2
> =Vau+
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Prepared statements are not cached with XA in tomcat jdbc
connection pool implementation
Posted by Sailaja Ravipati <sr...@opentext.com>.
Thank you. Will do that.
Regards,
Sailaja.
-----Original Message-----
From: Christopher Schultz [mailto:chris@christopherschultz.net]
Sent: 18 May 2016 01:24
To: Tomcat Users List
Subject: Re: Prepared statements are not cached with XA in tomcat jdbc connection pool implementation
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Sailaja,
On 5/17/16 6:58 AM, Sailaja Ravipati wrote:
> I am using TomEE version Apache Tomcat Version 7.0.63. I have the
> following test program.
>
> public static void main(String[] args) throws Exception { final
> TransactionManager transactionManager = TransactionManagerFactory
> .getTransactionManager(); final PoolProperties poolProperties = new
> PoolProperties(); SQLServerDataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerDataSource();
> dataSource.setUser("sa"); dataSource.setPassword("$9Lserver");
> dataSource.setURL("jdbc:sqlserver://sdwivedi63ks022:1433;sendStringPar
ametersAsUnicode=false");
>
>
dataSource.setDatabaseName("himalaya");
> poolProperties.setDataSource(dataSource); final String
> jdbcInterceptors =
> "org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,
callable=true)";
>
>
poolProperties.setJdbcInterceptors(jdbcInterceptors);
> final org.apache.tomcat.jdbc.pool.DataSource pooledOracleDatasource =
> new org.apache.tomcat.jdbc.pool.XADataSource( poolProperties); final
> javax.sql.DataSource oracleDataSource = new
> org.apache.openejb.resource.jdbc.managed.xa.ManagedXADataSource(
> pooledOracleDatasource, transactionManager,
> TransactionProvider.getTransactionSynchronizationRegistry());
> Connection connection = oracleDataSource.getConnection(); for(int i=0;
> i<50; i++) { PreparedStatement preparedStatement =
> connection.prepareStatement("insert into MyTableNew values (" + i +
> ")"); System.out.println(preparedStatement.getClass().getName());
> preparedStatement.execute(); preparedStatement.close(); }
> connection.close(); }
>
> If I run the above program, the output I see is:
> com.sun.proxy.$Proxy11
>
> If I just change the above program to use XA datasource, i.e.
> Change the following line SQLServerDataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerDataSource(); To
> SQLServerXADataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerXADataSource(); The output is
> : com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
>
> So, if I use XA datasource, prepared statements are not cached. How do
> I log a defect ?
The problem would be in commons-dbcp, so you should file a bug report in JIRA here:
http://commons.apache.org/proper/commons-dbcp/issue-tracking.html
Bug reports with complete information (or even a patch!) will get more attention than those without.
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJXO3bFAAoJEBzwKT+lPKRY1oAP/iy4c6LQNx/Gcg+24MJry0/t
l30JkJsgeCa6uXgs7JUjCLxmaPjhCF3Fw4FtzwzqUgEY13yTF7jlFhvuq1elOoQx
w0eHpcW39iQ0DJvGvbz6cEpsR902mSSXyt6Ar7AAeZoAs7AlYKhLmp9wKBdqq1pq
PSJcBhrwxA0OcIgDscCj667diUYmmi914LRSUXNwiCCH3ymAMsoXFi63FD1/VxEf
W3vqKxJvmKwhlP1Jia0T9mCusqmwQfct2lJXuU+W9dDyCogkBdv4anDBivdRVJA8
VYRzHPjQQQOg7dwmXkulRLp1vToqFFaAFDhUVL+RSTX+R+Ee9fg+vqssUD4U6ba/
GCTXMTMck2/n06dDcNcpfF5M20nFDQ8eBUDqsmbP+NBMrTZfdyUTihCaoRW1x1rv
+YVJuD5TA6bNC6n70bWMLT2ybs3cxplB7lwPueO0t88nBehDqsz+XRSS64Si6hXn
wjKaW+CO7tVMhRmf8g9yKFKppklIUCpWX12iyJ4SRWtAVRIBcZ47HFgXfukPo03A
DcUwpjsSHgvyOUpwWe31mVeQMyY5VDu8lFSjqmaHthqQGvRnGaYUPD3VJd71mobV
AqkNTk3xcySN/sirUx6KVm6rwYClIvN2xQswn5cvFeRjRFO6BCGemjsW7+aF40Mu
+Q3ahYmlTYBownFshBV2
=Vau+
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Prepared statements are not cached with XA in tomcat jdbc
connection pool implementation
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Sailaja,
On 5/17/16 6:58 AM, Sailaja Ravipati wrote:
> I am using TomEE version Apache Tomcat Version 7.0.63. I have the
> following test program.
>
> public static void main(String[] args) throws Exception { final
> TransactionManager transactionManager = TransactionManagerFactory
> .getTransactionManager(); final PoolProperties poolProperties = new
> PoolProperties(); SQLServerDataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerDataSource();
> dataSource.setUser("sa"); dataSource.setPassword("$9Lserver");
> dataSource.setURL("jdbc:sqlserver://sdwivedi63ks022:1433;sendStringPar
ametersAsUnicode=false");
>
>
dataSource.setDatabaseName("himalaya");
> poolProperties.setDataSource(dataSource); final String
> jdbcInterceptors =
> "org.apache.tomcat.jdbc.pool.interceptor.StatementCache(prepared=true,
callable=true)";
>
>
poolProperties.setJdbcInterceptors(jdbcInterceptors);
> final org.apache.tomcat.jdbc.pool.DataSource pooledOracleDatasource
> = new org.apache.tomcat.jdbc.pool.XADataSource( poolProperties);
> final javax.sql.DataSource oracleDataSource = new
> org.apache.openejb.resource.jdbc.managed.xa.ManagedXADataSource(
> pooledOracleDatasource, transactionManager,
> TransactionProvider.getTransactionSynchronizationRegistry());
> Connection connection = oracleDataSource.getConnection(); for(int
> i=0; i<50; i++) { PreparedStatement preparedStatement =
> connection.prepareStatement("insert into MyTableNew values (" + i +
> ")"); System.out.println(preparedStatement.getClass().getName());
> preparedStatement.execute(); preparedStatement.close(); }
> connection.close(); }
>
> If I run the above program, the output I see is:
> com.sun.proxy.$Proxy11
>
> If I just change the above program to use XA datasource, i.e.
> Change the following line SQLServerDataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerDataSource(); To
> SQLServerXADataSource dataSource = new
> com.microsoft.sqlserver.jdbc.SQLServerXADataSource(); The output is
> : com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
>
> So, if I use XA datasource, prepared statements are not cached. How
> do I log a defect ?
The problem would be in commons-dbcp, so you should file a bug report
in JIRA here:
http://commons.apache.org/proper/commons-dbcp/issue-tracking.html
Bug reports with complete information (or even a patch!) will get more
attention than those without.
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJXO3bFAAoJEBzwKT+lPKRY1oAP/iy4c6LQNx/Gcg+24MJry0/t
l30JkJsgeCa6uXgs7JUjCLxmaPjhCF3Fw4FtzwzqUgEY13yTF7jlFhvuq1elOoQx
w0eHpcW39iQ0DJvGvbz6cEpsR902mSSXyt6Ar7AAeZoAs7AlYKhLmp9wKBdqq1pq
PSJcBhrwxA0OcIgDscCj667diUYmmi914LRSUXNwiCCH3ymAMsoXFi63FD1/VxEf
W3vqKxJvmKwhlP1Jia0T9mCusqmwQfct2lJXuU+W9dDyCogkBdv4anDBivdRVJA8
VYRzHPjQQQOg7dwmXkulRLp1vToqFFaAFDhUVL+RSTX+R+Ee9fg+vqssUD4U6ba/
GCTXMTMck2/n06dDcNcpfF5M20nFDQ8eBUDqsmbP+NBMrTZfdyUTihCaoRW1x1rv
+YVJuD5TA6bNC6n70bWMLT2ybs3cxplB7lwPueO0t88nBehDqsz+XRSS64Si6hXn
wjKaW+CO7tVMhRmf8g9yKFKppklIUCpWX12iyJ4SRWtAVRIBcZ47HFgXfukPo03A
DcUwpjsSHgvyOUpwWe31mVeQMyY5VDu8lFSjqmaHthqQGvRnGaYUPD3VJd71mobV
AqkNTk3xcySN/sirUx6KVm6rwYClIvN2xQswn5cvFeRjRFO6BCGemjsW7+aF40Mu
+Q3ahYmlTYBownFshBV2
=Vau+
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org