Devart Forums

hmuscroft

Thanks Shalex - got it up-and-running now. There's a typo in the documentation. It says :-

- client certificate (for example client.crt) - used to encrypt and decrypt data during connection.
- private key (for example client.crt) - proves client certificate sent by owner; does not indicate certificate owner is trustworthy.

The second item should read "private key (for example client.key)".

Thanks again.

hmuscroft

I have an urgent requirement to implement SSL in my application.

However, the documentation you have provided is out of date and incorrect. The documentation gives this example :-

Code: Select all

PgSqlConnection conn = new PgSqlConnection("user id=postgres;password=postgres;host=localhost;");
conn.SslOptions.Cert = "E:\Test\client.p12";
conn.SslOptions.Password = "";
conn.SslOptions.TargetHost = "pg_server";
conn.SslOptions.SslMode = SslMode.Require;
conn.Open();

This is completely incorrect and won't even compile. According to IntelliSense, the actual members of 'SslOptions' are :-

CACert = location of authority certificate
Cert = location of client certificate
CipherList = list of ciphers the client agrees to use
Key = location of users private key
SslMode =

Please will you provide an in-depth guide on how to set this up? Also, according to the PostgreSQL documentation here :-
http://www.postgresql.org/docs/8.1/static/ssl-tcp.html

When the root.crt file is not present, client certificates will not be requested or checked. In this mode, SSL provides communication security but not authentication.

...client authentication is optional and we should be able to omit it and only encrypt the communication without any client authentication. I have already implemented this with my client's PostgreSQL installation - please can you let me know how to implement this with your provider?

Thanks, Hedley

hmuscroft

Hi - I'm trying desperately to implement SSL in my application.

Please can you post the updated documentation or let us know when it will be availalable, as I'm struggling to get it working.

Thanks.

hmuscroft

If you set Pooling to true and call the Close() method of your PgSqlConnection object, actually the connection will not be closed - it will be only placed to a pool.

What about Dispose()? If "Pooling=True" and I dispose of the PgSqlConnection object then does that physically close the connection or does it also just return it to the pool?

Thanks.

hmuscroft

This getter code checks whether the m_connection variable is set to an instance of an object or not. But it doesn't check the state of the connection object

Correct but as I stated earlier, nowhere in my code do I *ever* call .Close() on the connection object.

In fact, the connection object iteself is only accessible internally to the DATA class. The rest of my app simply call utliity functions on the DATA class e.g. public DataTable GetQuery(string SQL) in order to retrieve data. The connection object itself is not exposed so it's not hard to clearly see that I never call .Close();

Are you suggesting that the PgSqlConnection object could be closed by some other process? Perhaps by your implementation of one of the other ADO data classes like PgSqlDataAdapter for example?

hmuscroft

Thanks for your reply Shalex.

Maybe connection is closed before it is used somewhere in your code. Please review your code.

No, the connection is NEVER closed. It is opened at startup and disposed at on termination of the application. To be specific, I have a DATA class which encapsulates all DB functions. This class has a public DbConnection property (called Connection). The Connection property 'getter' is as follows :-

Code: Select all

get
{
  if (m_connection == null)
  {
     m_connection = new PgSqlConnection(m_constr);
     m_connection.Open();
  }
  return m_connection;
}

That is the ONLY place my application can get the connection object and 'm_connection' is a private PgSqlConnection object. On program shutdown, the DATA class calls m_connection.Close() and m_connection.Close(). Nowhere in my code is there a single call to .Close apart from in the destructor for the DATA class, which is a PUBLIC STATIC class and is not destroyed until the application terminates.

HOWEVER...

I have now changed my code back so that the ConnectionString contains "Pooling=true" (it was false with the previous code) and the getter code now reads as follows :-

Code: Select all

get
{
  PgSqlConnection con = new PgSqlConnection(m_constr);
  con.Open();
  return con;
}

Each routine which references data.Connection now gets its own connection object and it also disposes of the connection as soon as it's finished with it.

The result? NO PROBLEMS. Clearly there is an issue when applications hold on to a single connection object and keep it open throughout the lifetime of the application.

>> We cannot reproduce the "Connection must be opened" issue.

However, I am sure that you accept there clearly is a problem? I am not your only customer with this issue. leeottaway, AndreyR, irimawi and phoebe007 have all also reported the exact same problem (and I would bet there are others watching this forum with the same problem who haven't weighed in).

Please also bear in mind I myself can't reproduce the problem on my network (apart from by disconnecting the cable), but about 50% of my clients are experiencing this issue and I have observed the error on their systems when it occurs.

Perhaps it might be wise to revise the advice given in this post :-
http://devart.com/forums/viewtopic.php?t=14287
... and to suggest that developers use "Pooling=true" and that we create/dispose of PgSqlConnection objects explicitly as and when they are needed?

Kind Regards,

Hedley

hmuscroft

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?

hmuscroft

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

hmuscroft

Thanks Shalex - last question : should 'Pooling' be set to true or false in this scenario or does it not matter?

hmuscroft

Thanks for your reply.

I do actually use the Asynchronous mode, but it's only to keep the application responsive during longer database operations. For example :-

Code: Select all

        using (PgSqlConnection con = new PgSqlConnection(ConnectionStr))
        {
          con.Open();
          PgSqlCommand cmd = new PgSqlCommand(sql, con);
          IAsyncResult res = cmd.BeginExecuteNonQuery(null, null);
          while (!res.IsCompleted)
            Application.DoEvents();
          cmd.EndExecuteNonQuery(res);
        }

So if I change con to a global instance as you recommend while the above be safe, or should I perhaps lock the con object whenever it's used to make sure? i.e.

Code: Select all

        lock (con)
        {
          PgSqlCommand cmd = new PgSqlCommand(sql, con);
          IAsyncResult res = cmd.BeginExecuteNonQuery(null, null);
          while (!res.IsCompleted)
            Application.DoEvents();
          cmd.EndExecuteNonQuery(res);
        }

One last question... if using a global PgsqlConnection object, should it be opened and closed before/after each operation, or simply left open throughout the lifetime of the app?

Many thanks for your help.

hmuscroft

I know that the usual ADO.NET recommendation for DbConnection objects is to use deterministic destruction (i.e. create and use the PgsqlConnection object and then call .Close and .Dispose immmedately when we're done with it).

However, for a desktop application which (unlike a web application) has a consistent connection to the pgsql server, is this necessary?

In other words, is there any harm in just have one global instance of the PgsqlConnection object and using it throughout the application? Are there any benefits/drawbacks in terms of performance or memory consumption to either approach?

Many thanks!

hmuscroft

Thanks for the quick reply Alexey. Please can I just ask : I notice that your dump procedure seems to be exporting raw SQL in which case how does it handle BLOB data?

hmuscroft

I just wanted to say that I too need this functionality ASAP. I am just in the middle of implementing Backup/Restore feature in my application and I need to be able to dump an ENTIRE database and then restore an entire database.

I actually upgraded to the Professional version of the CoreLab's PGSQL provider exactly because I needed the backup/restore (which wasn't available in your Standard version).

Please can you tell me how long it will take to implement this as it's a pretty urgent requirement for me? Many thanks.

hmuscroft

I have written a conversion utility to convert a database from DBISAM to PGSQL and am using your provider in my C# application.

The conversion works for about 80% of my customers databases, but for some customers who have very large databases, the sometimes throws the following exception :-

CoreLab.PostgreSql.PgSqlException was unhandled by user code
Message="A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"
Source="CoreLab.Data"
ErrorCode=-2147467259
LineNumber=0
Position=0
StackTrace:
at CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
at CoreLab.Common.DbScript.Execute()
at co2005_to_v4.frmMain.ExecPGSQL(String sql) in E:\vs2005\source\co2005_to_v4\co2005_to_v4\frmMain.cs:line 253

The inner exception reports a "System.Net.Sockets.SocketError.TimedOut" error with error code 10060.

After a lot of testing, the problem seems to occur after a particularly long script (i.e. > 1 minute).

Is there any workaround for this, or perhaps a timeout value that I can tweak somewhere to make this work? Thanks.

hmuscroft

Hi Guys - you're probably aware that PGSQL 8.3 BETA 2 is now available and being tested and that we're not too far away from 8.3 final release.

We are soon to release a new product using your PGSQL drivers, and I was just wondering whether you already support 8.3, or if not, then how soon after 8.3 goes gold do you anticipate releasing an update to make your provider compatible with it?

Many thanks.

Devart Forums

Search found 23 matches

* URGENT * SSL Documentation?!

PgsqlConnection recommendations

Problem running large scripts with PgSqlScript component

PostgreSQL 8.3 Support?

Search found 23 matches

*** URGENT *** SSL Documentation?!

PgsqlConnection recommendations

Problem running large scripts with PgSqlScript component

PostgreSQL 8.3 Support?

* URGENT * SSL Documentation?!