Multiple Queries in Dataset

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 3
Joined: Fri 17 Apr 2009 15:14

Multiple Queries in Dataset

Post by landsharkdaddy » Fri 08 May 2009 17:43

I have a typed dataset and I need to execute and update statement and then execute a SELCT statement with the same id so my dataset can get the ID of the record that was updated or inserted.

For SQL the query is as below:

@"UPDATE [dbo].[Testing] SET [Testing] = @Testing, [Testing1] = @Testing1, [Testing2] = @Testing2, [Testing3] = @Testing3 WHERE (([ID] = @Original_ID) AND ((@IsNull_Testing = 1 AND [Testing] IS NULL) OR ([Testing] = @Original_Testing)) AND ((@IsNull_Testing1 = 1 AND [Testing1] IS NULL) OR ([Testing1] = @Original_Testing1)) AND ((@IsNull_Testing2 = 1 AND [Testing2] IS NULL) OR ([Testing2] = @Original_Testing2)) AND ((@IsNull_Testing3 = 1 AND [Testing3] IS NULL) OR ([Testing3] = @Original_Testing3)));
SELECT ID, Testing, Testing1, Testing2, Testing3 FROM Testing WHERE (ID = @ID)";

I need to produce the same thing for a PostgreSQL database. This is my attempt at that and I get the error "cannot insert multiple commands into a prepared statement"

this._adapter.UpdateCommand.CommandText = @"UPDATE lanemanager.reservations SET type = :type, startdate = :startdate, enddate = :enddate, allday = :allday, subject = :subject, location = :location, description = :description, status = :status, label = :label,
resourceid = :resourceid, reminderinfo = :reminderinfo, recurrenceinfo = :recurrenceinfo, customfield1 = :customfield1 WHERE (uniqueid = :Original_uniqueid);
SELECT uniqueid, type, startdate, enddate, allday, subject, location, description, status, label, resourceid, reminderinfo, recurrenceinfo, customfield1 FROM lanemanager.reservations
WHERE uniqueid = :Original_uniqueid;";

Any help that you can give me on this would be most appreciated.

Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 13 May 2009 16:04

dotConnect for PostgreSQL uses only prepared execution of queries now. Protocol 3.0 has implementation for prepared execution and unprepared execution. Multiple execution with the protocol 3.0 is possible with unprepared execution only. That's why it is not allowed to use multiple execution with protocol 3.0 and dotConnect for PostgreSQL now.

But you can use protocol 2.0 which allows multiple execution. Starting with PostgreSQL server 7.4, the new protocol version (3.0) is introduced. It is used by default by servers 7.4 and higher. However, sometimes you might require to use the older protocol (2.0), for example, when executing batch statements. In this case, set PgSqlConnectionStringBuilder.Protocol property to Ver20 to force usage of the protocol version 2.0.

If you use the Devart typed DataSet, the "With refresh SQL" options can be set in Table Adapter Editor when using Devart DataSet Wizard. It includes the RETURNING clause into the update commands. Maybe it will help you to exclude several statements from your query if you don't have the possibility to use the 2.0 protocol.

Post Reply