Multiple insert statements with protocol 3

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
dsaracini
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Multiple insert statements with protocol 3

Post by dsaracini » Tue 10 Mar 2009 20:15

Hello,

Are there any plans to support multiple insert statements with protocol 3 in the future?

If so, please give me some idea when this might happen (next release? near future? distant future? no plans?)

If not, please give me some idea how I could accomplish the following with only 1 "round-trip" from the client to the server and using protocol 3...


insert into foo(foo_name) values ("John") returning id;
insert into foo(foo_name) values ("Tim") returning id;

Note: I have tested the above with pgAdmin III and done a network capture via WireShark and examined the packets. pgAdmin seems to definitely be using protocol 3 and all of my inserts are contained in a single packet sent to the server and all of the "returned" ids come back in a single packet also. So, it definitely seems possible. However, I've been unable to make this work with protocol 3 and pgSqlCommand.

Note2: the above does work with pgSqlCommand and pgSqlConnection and protocol 2.

Thanks in advance...

David

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

Post by Shalex » Wed 11 Mar 2009 08:03

1. Multiple insert statements can not be executed with protocol 3.0. This is a limitation of protocol 3.0, not dotConnect for PostgreSQL.

2. Please execute the PgSqlCommand.ExecuteScalar() method if you want to return the id value from the above insert statement.

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

Post by Shalex » Wed 11 Mar 2009 09:07

Supplement to the fisrt point of my previous answer:

dotConnect for PostgreSQL uses only prepared execution of queries now. Protocol 3 has implementation for prepared execution and unprepared execution. Multiple execution with protocol 3 is possible with unprepared execution only. That's why it is not allowed to use multiple execution with protocol 3 and dotConnect for PostgreSQL now. We will investigate the possibility of implementating unprepared execution with dotConnect for PostgreSQL and notify you about the results as soon as possible.

dsaracini
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Post by dsaracini » Wed 11 Mar 2009 17:04

Thank you for your efforts. I'll look forward to the reply.

dsaracini
Posts: 17
Joined: Wed 04 Mar 2009 07:50

Post by dsaracini » Thu 19 Mar 2009 17:42

May I have a status update? When will this be addressed?

Thank you.

acerbitdrain
Posts: 10
Joined: Sat 07 Feb 2009 10:09

Post by acerbitdrain » Sat 21 Mar 2009 11:45

I concur. This is a huge limitation of dotConnect for PostgreSQL.
There is a reason for a Prepare() method on the DbCommand base class. Preparing statements with parameters in many cases decreases performance as the planer has no knowledge up front of the values that it has to work with.

It would also be good to be able to execute this...

Code: Select all

command.CommandText = "CREATE TABLE test_table (test_table_id int);
INSERT INTO test_table SELECT 1::int;
- milos

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

Post by Shalex » Thu 26 Mar 2009 10:38

We cannot provide any timeframe now. You will be notified on the first available result.

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

Post by Shalex » Wed 19 Aug 2009 10:05

Unprepared execution with dotConnect for PorstgreSQL is implemented. The statement will not be prepared at the server, if the UnpreparedExecute property of the PgSqlCommand object is set to true (the default value is false). This mode allows to process multiple queries:

Code: Select all

      PgSqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "select * from \"Products\";select * from \"Orders\"";
      cmd.UnpreparedExecute = true;
It will be available in the next build of dotConnect for PostgreSQL.

acerbitdrain
Posts: 10
Joined: Sat 07 Feb 2009 10:09

Post by acerbitdrain » Mon 24 Aug 2009 12:35

This is great Shalex, however can I suggest somethings that may make this feature easier to utilize.

You'll find that most developers will use your provider with an ORM tool such as NHibernate or EF. These tools know nothing about provider specific properties when instantiating Connection or Command objects.
For example, NHibernate calls CreateConnection() on its driver class to obtain a new connection, then internally (somwhere in NHibernate code base) it calls CreateConnection().CreateCommand() to get a new command. This is where the problem is, there is no way of intercepting this to set UnpreparedExecute to true on the newly created command. This means that your PgSqlCommand class would need to be sub-classed, then the CreateCommand() method overriden, something like

Code: Select all

public new PgSqlCommand CreateCommand() {
 PgSqlCommand cmd = base.CreateCommand();
 cmd.UnpreparedExecute = true;
 return cmd;
}
I guess what I'm getting at is, could true be made the default? OR have a property in the connection string that will set this accordingly.

Regards,
Milos

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

Post by Shalex » Wed 26 Aug 2009 06:45

Thank you for your suggestions. We will implement the UnpreparedExecute parameter of the connection string in the next build.

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

Post by Shalex » Fri 04 Sep 2009 14:31

The new build of dotConnect for PostgreSQL 4.55.42 is available for download now.
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=15740 .

cjbiggs
Posts: 105
Joined: Fri 15 Jan 2010 19:56

Post by cjbiggs » Tue 27 Apr 2010 16:28

Hi Shalex,

Have this been implemented yet?

Thank you for your suggestions. We will implement the UnpreparedExecute parameter of the connection string in the next build.

Because I am being advised in this link to use Protocol=2 rather than Protocol=3 which I prefer to use.

http://www.devart.com/forums/viewtopic.php?t=17556

Is this a Protocol=3 problem or a dotConnect for Posgres Issue?

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

Post by Shalex » Wed 12 May 2010 15:42

You can use unprepared execution of Protocol 3 with NHibernate. But we implemented other approach than using additional connection string parameter that affects the PgSqlCommand object. We have added the Devart.Data.PostgreSql.NHibernate namespace with the NHibernatePgSqlConnection and NHibernatePgSqlCommand classes to the Devart.Data.PostgreSql.dll assembly. Constructor of NHibernatePgSqlCommand has the "UnpreparedExecute = true" initialization setting. So, please use the mentioned classes when you add support for dotConnect for PostgreSQL to NHibernate before recompilation its sources:

Code: Select all

using NHibernate.SqlTypes;   
using System.Data;   
namespace NHibernate.Driver   
{   
   public class DevartDataPostgreSQLDriver : ReflectionBasedDriver   
   {   
          public DevartDataPostgreSQLDriver() : base(   
           // Initializes a new instance of the DevartDataPostgreSQLDriver class.   
           "Devart.Data.PostgreSql",
           "Devart.Data.PostgreSql.NHibernate.NHibernatePgSqlConnection",
           "Devart.Data.PostgreSql.NHibernate.NHibernatePgSqlCommand"){   
          }   
  
          public override bool UseNamedPrefixInSql{   
               get { return true; }   
          }   
  
          public override bool UseNamedPrefixInParameter{   
               get { return true; }   
          }   
  
          public override string NamedPrefix{   
               get { return ":"; }   
          }
          public override bool SupportsMultipleQueries{
              get { return true; }
          }
   }   
}
This functionality is available in the current (4.90.124) build of dotConnect for PostgreSQL.

cjbiggs
Posts: 105
Joined: Fri 15 Jan 2010 19:56

Post by cjbiggs » Fri 14 May 2010 15:32

Hey Shalex,

I am not using NHibernate, I am using EF 4. So will your solution work for EF4 as well?

Thanks,

Charlie J.

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

Post by AndreyR » Wed 19 May 2010 14:37

No, this is an NHibernate-specific solution.
In Entity Framework v4 you have two options - using Protocol 2 or setting the PgSqlEntityProviderServices.UnpreparedCommandExecution property to true.

Post Reply