Devart Forums

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.

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.