asp sql server: jdbc

I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000 under
Tomcat App server.
I wish to control the number of physical connections made to the database to
50. Can somebody guide me about this.
At the moment even with as low as 30 concurrent hits we see a very high
number of phyiscal connection to the database as high as 200.
I am using the following settings in SERVER.XML
<Resource name="jdbc/ECTDB" auth="Container"
type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
<ResourceParams name="jdbc/ECTDB">
<parameter><name>maxActive</name><value>30</value></parameter>
<parameter><name>maxIdle</name><value>20</value></parameter>
<parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.SQLServe rDataSource</value></parameter>
<parameter><name>user</name><value>..</value></parameter>
<parameter><name>password</name><value>..</value></parameter>
<parameter><name>driverName</name><value>SQLServer</value></parameter>
<parameter><name>serverName</name><value>127.0.0.1</value></parameter>
<parameter><name>portNumber</name><value>1433</value></parameter>
<parameter><name>databaseName</name><value>..</value></parameter>
<parameter><name>selectMethod</name><value>cursor</value></parameter>
<parameter><name>loginTimeout</name><value>60</value></parameter>
</ResourceParams>
Thanks a lot
| Thread-Topic: connection pool problem using in Tomcat
| thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
| X-WBNR-Posting-Host: 202.134.106.86
| From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
| Subject: connection pool problem using in Tomcat
| Date: Fri, 27 Aug 2004 21:55:03 -0700
| Lines: 30
| Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
under
| Tomcat App server.
|
| I wish to control the number of physical connections made to the database
to
| 50. Can somebody guide me about this.
|
| At the moment even with as low as 30 concurrent hits we see a very high
| number of phyiscal connection to the database as high as 200.
| I am using the following settings in SERVER.XML
| <Resource name="jdbc/ECTDB" auth="Container"
| type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
| <ResourceParams name="jdbc/ECTDB">
| <parameter><name>maxActive</name><value>30</value></parameter>
| <parameter><name>maxIdle</name><value>20</value></parameter>
| <parameter><name>maxWait</name><value>10000</value></parameter>
<parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
rDataSourceFactory</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
SQLServerDataSource</value></parameter>
| <parameter><name>user</name><value>..</value></parameter>
| <parameter><name>password</name><value>..</value></parameter>
| <parameter><name>driverName</name><value>SQLServer</value></parameter>
| <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
| <parameter><name>portNumber</name><value>1433</value></parameter>
| <parameter><name>databaseName</name><value>..</value></parameter>
| <parameter><name>selectMethod</name><value>cursor</value></parameter>
| <parameter><name>loginTimeout</name><value>60</value></parameter>
| </ResourceParams>
|
|
| Thanks a lot
|
|
If you are using SelectMethod=direct, then it is possible that you are
seeing an increase in connections due to the creation of cloned
connections. These additional connections are necessary because each
statement requires its own connection. Your Tomcat settings have no
bearing on this particular behavior, as it is inherent in the JDBC driver
itself.
Are you using transactions in your code? Can you provide code that
reproduces the problem behavior?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien ,
Yes, I use connection in my code and use a following function for getting
conncection.
public static Connection getConnection() throws Exception {
Connection dbConnection = null;
InitialContext ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
ConnectionPoolDataSource ds =
(ConnectionPoolDataSource) envCtx.lookup("jdbc/ECTDB");
Connection conn = ds.getPooledConnection().getConnection();
conn.setAutoCommit(false);
return conn;
}
Is this funciton cause the problem?
Thanks a lot.
kaicall
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: connection pool problem using in Tomcat
> | thread-index: AcSMuy5kABS/93PSRZ6MtRi1273r1w==
> | X-WBNR-Posting-Host: 202.134.106.86
> | From: "=?Utf-8?B?a2FpY2FsbA==?=" <kaicall@.discussions.microsoft.com>
> | Subject: connection pool problem using in Tomcat
> | Date: Fri, 27 Aug 2004 21:55:03 -0700
> | Lines: 30
> | Message-ID: <22AD57F5-91A6-48C5-81BF-F9B84365D03F@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6282
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I use "SQL Server 2000 Driver for JDBC SP3" to connect SQLServer2000
> under
> | Tomcat App server.
> |
> | I wish to control the number of physical connections made to the database
> to
> | 50. Can somebody guide me about this.
> |
> | At the moment even with as low as 30 concurrent hits we see a very high
> | number of phyiscal connection to the database as high as 200.
> | I am using the following settings in SERVER.XML
> | <Resource name="jdbc/ECTDB" auth="Container"
> | type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource"/>
> | <ResourceParams name="jdbc/ECTDB">
> | <parameter><name>maxActive</name><value>30</value></parameter>
> | <parameter><name>maxIdle</name><value>20</value></parameter>
> | <parameter><name>maxWait</name><value>10000</value></parameter>
> <parameter><name>factory</name><value>com.microsoft.jdbcx.sqlserver.SQLServe
> rDataSourceFactory</value></parameter>
> <parameter><name>driverClassName</name><value>com.microsoft.jdbcx.sqlserver.
> SQLServerDataSource</value></parameter>
> | <parameter><name>user</name><value>..</value></parameter>
> | <parameter><name>password</name><value>..</value></parameter>
> | <parameter><name>driverName</name><value>SQLServer</value></parameter>
> | <parameter><name>serverName</name><value>127.0.0.1</value></parameter>
> | <parameter><name>portNumber</name><value>1433</value></parameter>
> | <parameter><name>databaseName</name><value>..</value></parameter>
> | <parameter><name>selectMethod</name><value>cursor</value></parameter>
> | <parameter><name>loginTimeout</name><value>60</value></parameter>
> | </ResourceParams>
> |
> |
> | Thanks a lot
> |
> |
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
> If you are using SelectMethod=direct, then it is possible that you are
> seeing an increase in connections due to the creation of cloned
> connections. These additional connections are necessary because each
> statement requires its own connection. Your Tomcat settings have no
> bearing on this particular behavior, as it is inherent in the JDBC driver
> itself.
> Are you using transactions in your code? Can you provide code that
> reproduces the problem behavior?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
This is a little scary isn't it? If I have a J2EE app that starts out
pre-preparing (for subsequent re-use) N prepared statements per
connection and I have M concurrent clients using the app then we'll
require N*M physical database connections. Is that correct? That
number could be pretty huge for even a mid size app..
Is there any plans to address this in any future driver?
Grant M
|||grantmitchell7@.yahoo.com wrote:

> CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<zzuzMF7jEHA.2516@.cpmsftngxa10.phx.gbl>...
>
> This is a little scary isn't it? If I have a J2EE app that starts out
> pre-preparing (for subsequent re-use) N prepared statements per
> connection and I have M concurrent clients using the app then we'll
> require N*M physical database connections. Is that correct? That
> number could be pretty huge for even a mid size app..
Not to mention if you plan to do anything transactionally, or use temp tables,
which would be limited to one real connection, and not involve all the
under-the-covers connections involved in one logical connection.
I am confident that an upcoming version of the free MS driver will
address this. In the mean time you have the option of using commercial
drivers, or ensuring you run with selectMethod=cursor. As I recall, the
original poster actually already showed he was running with selectMethod=cursor...
Joe Weinstein at BEA
> Is there any plans to address this in any future driver?
> Grant M
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<4136035E.9000108@.bea.com>...[vbcol=seagreen]
> grantmitchell7@.yahoo.com wrote:
>
> Not to mention if you plan to do anything transactionally, or use temp tables,
> which would be limited to one real connection, and not involve all the
> under-the-covers connections involved in one logical connection.
> I am confident that an upcoming version of the free MS driver will
> address this. In the mean time you have the option of using commercial
> drivers, or ensuring you run with selectMethod=cursor. As I recall, the
> original poster actually already showed he was running with selectMethod=cursor...
> Joe Weinstein at BEA
Yes the transactional problem is alarming.. Does the driver transact
all the cloned connections for statement when the original connection
is transacted? Who knows but I suppose a test would be relativly
quick.
In regards to selectMethod=cursor - our tests show that result sets
generated with server side cursors can be up to 8 times more expensive
on the database (and slower) than forward only, 'client side' result
sets (depending on the characteristics of the cursor). So instead of
having N*M connections we might end up with N*M server side cursors.
I'm not sure which is worse..
Therefore I would argue that server cursor based result sets are a
major obstacle to scalability and should be avoided at all costs
(except possibly for singleton processes that need a table snapshot
like a report for example).
I hope you are right that an upcoming release resolves this although I
remain skeptical since my guesss is that changing this behavior would
mean a major structural change in the driver's architecture.
On this newsgroup there has been recent reference by Microsoft to a
"new" JDBC driver for SQL 2005. However when I tried to get more
details on this driver there was no response. Perhaps someone from
Microsoft could comment on the "new" driver, and specifically comment
on whether it resolve this serious issue..
Grant

Hi mates,

I am getting this strange error while working on SQL server through JDBC.
I am using a connection-pooling mechanism. Have written my own connection pool. The pool holds a number of connections and has methods for requesting and returning connections from the clients. The issue is - when I get a connection from the pool and execute a select query, it works fine. Then I return the connection to the pool (at the top). On next request for connection, this same connection object is provided. When another select query is fired on this connection, I get an error - "connection is busy with results for another hstmt"... The error occurs at the time of executing the query and not while obtaining the statement object from the connection object.
If I don't use the connection pool and create new connection every time, everything works fine (except the performance!)...
I'll be grateful if anyone can shed some light on this....
Cheers!That's because each connection can only have 1 active (pending results) statement per connection.

It sounds like you are not closing/dropping the statements, or the driver is not. It might be your connection handling code. You might also try a different jdbc driver. Are you using an sql specific driver? It you use the default jdbc-odbc bridge, you can get bad behavior. That driver doen't work well. There are 3 kinds of replacements:

1) Free Jdbc drivers for SQL Server. MS provides one, and there is an open source one out there.

2) A ready for deployment JDBC-ODBC Bridge from a vendor. There are several.

3) A commercial JDBC Driver.

What you need is dependant on your budget, time and architecture.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

Sunday, March 25, 2012

Connection Problem

Thursday, March 22, 2012

connection pool problem using in Tomcat

Friday, February 24, 2012

Connection busy with results for another hstmt...

asp sql server

Blog Archive

About Me