"Connection Must Be Opened Error" When Already open

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

"Connection Must Be Opened Error" When Already open

Post by leeottaway » Fri 29 Aug 2008 08:36

Hi

We are regularly getting the error:

Error Number : 5
Error Description : Connection must be opened.

Error Stack
CoreLab.Common.Utils.CheckConnectionOpen(IDbConnection connection)
CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
System.Data.Common.DbCommand.ExecuteReader()
CoreLab.Common.DbCommandBase.ExecuteNonQuery()
7gvΠ9

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Mon 01 Sep 2008 10:00

So am I to understand that you are just ignoring this problem since everyone else has been getting a response and this hasn't??

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 01 Sep 2008 11:42

We are investigating the reason of this error. You will be notified about the results as soon as possible.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Mon 01 Sep 2008 12:20

OK, just wanted to know that it was being handled.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 05 Sep 2008 12:05

Could you please send me (alexsh at devart.com) the SQL script that creates tables and fills them with data. Give us a clearer example of the code with opening and closing connections and where the error arises.
What is the version of PostgreSQL Server you are using?
We will try to reproduce this problem.

phoebe007
Posts: 11
Joined: Mon 11 Feb 2008 02:35

Post by phoebe007 » Thu 25 Sep 2008 01:23

Hi Was this issue resolved?
I am experiencing the same problem and it is intermittent and happens on different tables.
Thanks
Phoebe

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 25 Sep 2008 07:39

No the issue is not resolved. Devart want a sample project, however I simply don't have the time to be doing this at the moment.

Shalex, the problem seems to have nothing to do with the schema of the table, therefore I'm sure if you create a table with a global connection, put a timer on the form and get it to interrogate the table every minute using a pgsqlcommand you will eventually run into the problem. Obviously this issue is a genuine problem as others are experiencing exactly the same anomally. If it's any help I have noticed that this problem seems to happen more consistently on Vista than XP (surprise surprise).

Phoebe, I have simply placed a globalconnection.open just before I execute the command. I don't know if this will help yet as the latest version of the product has not been released to our customers and it's them that are reporting this issue to us.

irimawi
Posts: 2
Joined: Sat 17 May 2008 18:16

Same Issue

Post by irimawi » Sun 14 Dec 2008 13:26

I am having the same problem, built a search engine, non of the stored procedures take more than half a second, yet I keep getting this error. I am afraid I have to change corelab with any other provider that does not have this problem.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 15 Dec 2008 13:21

Can anybody experiencing the problem above send me (andreyr * devart * com) a small test project that illustrates the problem?
That would greatly help the investigation.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Mon 15 Dec 2008 15:08

It would be pointless, as it's completely unreproducable to order. It's an intermittent problem that seems to occur when doing executenonquery on a global connection. We only use it rarely, for example when we exit one of our forms we run this code:

ExecuteCommand("DELETE * FROM ", gcsAppointmentStaff, " WHERE [ID] = " & goUser.ID)

For Each oItem In lvw.Items
If oItem.Checked Then
ExecuteCommand("INSERT INTO ", gcsAppointmentStaff, " ([ID], [Owner ID]) VALUES (" & goUser.ID & ", " & oItem.Tag & ")")
End If
Next

Simple stuff but it results in a "Connection must be open" error every now and again. Of course this wouldn't happen with a dataadapter because I understand it automatically opens a connection when it needs one anyway, unlike a datareader. I could send a test project with this code in it, but come on, surely you can put one together yourself. Doesn't matter on the table structure, doesn't matter what query you execute. For some reason the global connection thinks it is closed, so something is telling the connection to close somewhere, whether this be a simple length of time open issue, or if the connection genuinely loses connection to the database for a moment (as can happen on any network) and then reestablishes itself and so thinks it is closed...I don't know. I do know that these intermittent, unreproducable errors are the hardest the fix so you have my sympathies.

Lee Ottaway
Fusion Software (UK) Ltd

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 19 Mar 2009 14:57

We cannot reproduce the "Connection must be open" error neither with the 4.0.22 version nor with 4.50.24. We need a test project that illustrates the problem.

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Mon 30 Mar 2009 15:10

Hi there - I am experiencing EXACTLY the same problem.

However, this problem has only started occuring since I updated my application as per your suggestions in this thread :-
http://devart.com/forums/viewtopic.php?t=14287

Just to recap, previously I was using "Pooling=true" in my ConnectionString and I was then creating and disposing a PgsqlConnection object for EVERY database call.

Following your suggestion, I changed this to "Pooling=false" and I *only* have ONE global instance of PgsqlConection which is used throughout the application. This instance is created at program startup and disposed on termination.

Since making this change, a number of users are complaining of receiving a "Connection must be opened" error. Here is a typical Stack Trace which a user emailed me from their error report :-

Code: Select all

System.InvalidOperationException
Connection must be opened.


Server stack trace: 
   at CoreLab.Common.Utils.CheckConnectionOpen(IDbConnection connection)
   at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at CoreLab.Common.DbCommandBase.AsyncExecuteReader(CommandBehavior behavior)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
   at CoreLab.Common.DbCommandBase.a.EndInvoke(IAsyncResult A_0)
   at CoreLab.Common.DbCommandBase.EndExecuteReader(IAsyncResult result)
   at CoreLab.PostgreSql.PgSqlCommand.EndExecuteReader(IAsyncResult result)
   at ClinicOffice.cDB_PGSQL.FillTable(DataTable Table, String SQL)
   at ClinicOffice.cDB.GetDataRow(String SQL)
   at ClinicOffice.cController.CheckStillLoggedOn()
   at ClinicOffice.cController.m_timerSystem_Tick(Object sender, EventArgs e)
   at System.Windows.Forms.Timer.OnTick(EventArgs e)
   at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
This problem never happened before this change. The only way I have managed to reproduce this problem is as follows :-

[1] Create a test app with a single global connection object (which is created and opened at startup) and button which fills a table and assigns the table to a DataView.
[2] Run the program and connect to a Postgres database on a different PC
[3] While the program is running, disable and re-enable your network card.
[4] Click the button to fill the datatable.

You will (more than likely) receive the "Connection must be opened" message or if not, you will still get an error message.

The work-around for this that I have implemented in my application is to manually call CoreLab.Common.Utils.CheckConnectionOpen(my_con); whenever I am about to use the global connection. If that line throws an exception, I call my_con.Close(); my_con.Open(); to re-establish the connection.

This seems to resolve the issue (for me at least). I hope this helps anyone else who is suffering from this problem.

Regards,

Hedley

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 31 Mar 2009 07:23

This workaround can be used. As for disabling and re-enabling network card, you have got the designed behaviour. Because connection is lost when network is not available, and dotConnect for PostgreSQL doesn't have any internal implementation for re-establishing the lost connection.

hmuscroft
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Post by hmuscroft » Wed 01 Apr 2009 14:28

Thanks for your reply.

>> you have got the designed behaviour

But when I had "Pooling=true" and was constantly creating and disposing of the PgsqlConnection object everytime, I never experienced this problem.

In fact, since changing to a single persistent PgsqlConnection object, I have had an absolute slew of technical support problems like the "Connection must be opened" error.

Is there any compelling reason why I shouldn't switch back to the previous method of creating/disposing the connection objects?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 02 Apr 2009 09:04

By "designed behaviour" I mean the error after disabling and re-enabling network card.

We cannot reproduce the "Connection must be opened" issue. We don't know the reason why you are getting this error. Maybe connection is closed before it is used somewhere in your code. Please review your code.

There is no compelling reason to use the approach with one global connection only. You choose yourself the implementation that is more convenient for you.

Post Reply