how dotConnect for PostgreSQL work with ibatis.net

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
ithunter
Posts: 2
Joined: Mon 05 Jul 2010 10:29

how dotConnect for PostgreSQL work with ibatis.net

Post by ithunter » Mon 05 Jul 2010 10:37

i use dotConnect for PostgreSQL in ibatis.net
ibatis.net don't use command prepare method default but
dotConnect for PostgreSQL , PgSqlCommand's property UnpreparedExecute is false default.

so when i execute batch sql will throw exception

batch sql like this:
INSERT INTO "Frm_Dept"("DeptName","ParentId")VALUES('dept1',1);
INSERT INTO "Frm_Dept"("DeptName","ParentId")VALUES('dept2',2);

exception:

Devart.Data.PostgreSql.PgSqlException: cannot insert multiple commands into a prepared statement
at Devart.Data.PostgreSql.v.c(r A_0)
at Devart.Data.PostgreSql.r.af()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at IBatisNet.DataMapper.Commands.DbCommandDecorator.System.Data.IDbCommand.ExecuteNonQuery()

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

Post by Shalex » Tue 06 Jul 2010 15:23

We will implement the Unprepared Execute parameter of the connection string. I will notify you when this parameter is available.

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

Post by Shalex » Wed 07 Jul 2010 13:30

The Unprepared Execute (UnpreparedExecute) connection string parameter is implemented. I will post here when the corresponding build is available.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 29 Jul 2010 13:16

We've released the 4.95.152 build where this functionality is implemented. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information on improvements and fixes available in the 4.95.152 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18591

bozr
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Post by bozr » Thu 29 Jul 2010 15:54

I'm confused ...

Using the following code and build 4.95.152, I still get the "cannot insert multiple commands into a prepared statement" exception.
Is this supposed to work now?

Code: Select all

DbProviderFactory providerFactory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = providerFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=test;User Id=me;Unprepared Execute=true;Unicode=true;Password=secret";
connection.Open();

DbCommand command = connection.CreateCommand();
command.CommandText = @"INSERT INTO test_table(intval,strval) VALUES(1,'test1');
                        INSERT INTO test_table(intval,strval) VALUES(2,'test2');";
command.ExecuteNonQuery();

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 30 Jul 2010 17:28

The PgSqlCommand class is designed for executing single SQL commands only. For multiple commands execution, please use the PgSqlScript component instead.

bozr
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Post by bozr » Mon 02 Aug 2010 09:36

Ah ok, so it's not in a usable state for my use case scenario, since I use the DataProviderFactory to obtain a DbCommand. There's no equivalent of PgSqlScript available in ADO.NET.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 04 Aug 2010 08:35

Sorry for the previous post, the multiple commands execution should work with the 'Unprepared Execute' parameter set to true. We've reproduced the problem in case of creating the connection via DbProviderFactory. We will investigate it and inform you about the results. At the moment, a possible workaround is to create the PgSqlConnection object directly:

Code: Select all

PgSqlConnection connection = new PgSqlConnection(); 
connection.ConnectionString = connectionString; 
connection.Open(); 

DbCommand command = connection.CreateCommand(); 
command.CommandText = @"INSERT INTO test_table(intval,strval) VALUES(1,'test1'); 
                        INSERT INTO test_table(intval,strval) VALUES(2,'test2');"; 
command.ExecuteNonQuery();

Post Reply