Multiple Queries in Dataset

Multiple Queries in Dataset

Postby 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.
Posts: 3
Joined: Fri 17 Apr 2009 15:14

Postby 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.
Devart Team
Posts: 7888
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for PostgreSQL