Connection pool needed?

From what I understand, some database drivers provide automatic database pooling, in the sense that when an application closes a connection, it is not dropped until after a certain timeout. If the same, or another application, request a connection then the "pooled" one is returned.

What's the situation with C#, .NET Compact Edition and SQLce? Is it a performance hit to open and close a connection, or is some kind of automatic pooling in effect?

Thanks.

Martin

SQL CE is a single user database, so I find that having a single connection during the lifetime of your application is best. There is some overhead in opening and closing connections.

Connection closes often

Hi Team,

I get the below error very often.

java.sql.SQLException: I/O Error: Connection reset Caused by: java.net.SocketException: Connection reset
Followed by a number of "java.sql.SQLException: Invalid state, the Connection object is closed.".
Some time Invalid state, the Statement object is closed."
I have checked out the network problem and time out of queries. And both does not seems to be an issue for me. Because my application send queries continuously (Checked it with mssql profiler all queries executed in minimum interval) and MSSQL runs in the local machine. Meantime i do not see any wrong packet exception.
Is there any other thing i could check out. Any kind of Help is much appreciated.

Thanks for your help.

Hi,

Could you post a full stack trace of the exception(s)?

Thanks,

--David Olix

JDBC Development

|||Hi David,

Thanks for your reply.

Please find the trace below.

16:06:46:454 PM Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
16:06:46:454 PM at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1213)
16:06:46:454 PM at net.sourceforge.jtds.jdbc.ConnectionJDBC2.createStatement(ConnectionJDBC2.java:1735)
16:06:46:454 PM at com.adventnet.persistence.standalone.LogicalConnection.createStatement(LogicalConnection.java:72)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.createStatement(RelationalAPI.java:952)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.createStatement(RelationalAPI.java:947)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.executeQuery(RelationalAPI.java:750)
16:06:46:454 PM at com.adventnet.persistence.internal.GetUtil.get(GetUtil.java:89)

Thanks,
Navaneeth
|||

I had assumed that you were using the new Microsoft SQL Server 2005 JDBC driver... My apologies. I probably won't be much help to you then.

You may be able to figure out why the connection is being closed by running the SQL Server Profiler to gather a trace while you run your app. You might also want to submit your question to the jTDS or adventnet folks.

Or if you can reproduce your problem with Microsoft's JDBC driver, I might be able to provide some assistance. :-)

--David Olix

JDBC Development

Connection closes often

Hi Team,

I get the below error very often.

java.sql.SQLException: I/O Error: Connection reset Caused by: java.net.SocketException: Connection reset
Followed by a number of "java.sql.SQLException: Invalid state, the Connection object is closed.".
Some time Invalid state, the Statement object is closed."
I have checked out the network problem and time out of queries. And both does not seems to be an issue for me. Because my application send queries continuously (Checked it with mssql profiler all queries executed in minimum interval) and MSSQL runs in the local machine. Meantime i do not see any wrong packet exception.
Is there any other thing i could check out. Any kind of Help is much appreciated.

Thanks for your help.

Hi,

Could you post a full stack trace of the exception(s)?

Thanks,

--David Olix

JDBC Development

|||Hi David,

Thanks for your reply.

Please find the trace below.

16:06:46:454 PM Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
16:06:46:454 PM at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1213)
16:06:46:454 PM at net.sourceforge.jtds.jdbc.ConnectionJDBC2.createStatement(ConnectionJDBC2.java:1735)
16:06:46:454 PM at com.adventnet.persistence.standalone.LogicalConnection.createStatement(LogicalConnection.java:72)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.createStatement(RelationalAPI.java:952)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.createStatement(RelationalAPI.java:947)
16:06:46:454 PM at com.adventnet.db.api.RelationalAPI.executeQuery(RelationalAPI.java:750)
16:06:46:454 PM at com.adventnet.persistence.internal.GetUtil.get(GetUtil.java:89)

Thanks,
Navaneeth
|||

I had assumed that you were using the new Microsoft SQL Server 2005 JDBC driver... My apologies. I probably won't be much help to you then.

You may be able to figure out why the connection is being closed by running the SQL Server Profiler to gather a trace while you run your app. You might also want to submit your question to the jTDS or adventnet folks.

Or if you can reproduce your problem with Microsoft's JDBC driver, I might be able to provide some assistance. :-)

--David Olix

JDBC Development

|||

Hi ,

i also facing the same problem .i am using the latest jdbc driver from microsoft .

Connection closes abruptly

My application consists of two modules, one in VB6, which performs Inserts and Updates on an SQL Express Server, and the other in VB.NET which performs Select operations on the same server.

From last two days the .NET module (which performs heavy select queries), randomly gives an error : "Connection closed for this operation", and rarely gives an error : "SqlException: A severe error occurred on the current command".

I don't know whether these two errors are linked to each other. But the former error is a very high priority for me, as it is application wide.

This problem is occuring only in the .NET module. The VB6 module which uses the same server is working fine.

Please help.

Girish

Hi Girish,

It sounds like you're trying to open a connection once and then just leave it open all the while the application is running. You probably shouldn't do this, rather, you should only open the connection when you are activly querying against it. You can certainly run a number of queries in a row against the same connection, but once your immediate need for the connection is past, you should close it.

Connection pooling in .NET and SQL 2005 will automatically manage having connections available to you when you need to re-open them for the next batch of queries. My guess is that if you maintain your connection for shorter times, you'll problem with the connection closing will go away.

You might also consider asking this question on the .NET Data Access forum to see if that group has additional ideas.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Hi Girish,

Any luck with this issue?

Mike