Multiple insert statements with protocol 3
Multiple insert statements with protocol 3
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
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
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.
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.
-
- Posts: 10
- Joined: Sat 07 Feb 2009 10:09
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...
- milos
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;
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:
It will be available in the next build of dotConnect for PostgreSQL.
Code: Select all
PgSqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from \"Products\";select * from \"Orders\"";
cmd.UnpreparedExecute = true;
-
- Posts: 10
- Joined: Sat 07 Feb 2009 10:09
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
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
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;
}
Regards,
Milos
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 .
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 .
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?
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?
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:
This functionality is available in the current (4.90.124) build of dotConnect for PostgreSQL.
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; }
}
}
}