RowsAfffected with RETURNING (Postgres)

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
bursch
Posts: 5
Joined: Tue 25 Sep 2018 07:45

RowsAfffected with RETURNING (Postgres)

Post by bursch » Mon 20 Jan 2020 17:02

Hi,

i have a problem with the RowsAffected-Property in combination with RETURNING under Postgres.

Step 1:

Code: Select all

	
	UniQuery1.SQL.Text := 'CREATE TEMPORARY TABLE Test (k serial primary key, v integer)';
	UniQuery1.ExecSQL;
Step 2:

Code: Select all

	
	UniQuery1.SQL.Text := 'INSERT INTO Test (k, v) VALUES (DEFAULT, 1) RETURNING k';
	UniQuery1.ExecSQL;
UniQuery1.RowsAffected is -1 and UniQuery1.FieldByName('k').AsInteger is 1.

But UniQuery1.RowsAffected has to be 1.

In pgAdmin the result is correct so it must be a problem with you components.

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: RowsAfffected with RETURNING (Postgres)

Post by FCS » Mon 20 Jan 2020 19:51

Hello,

Try use:

UniQuery1.Open;

As I remember the ExecSQL does not return dataset.

Regards
Michal

bursch
Posts: 5
Joined: Tue 25 Sep 2018 07:45

Re: RowsAfffected with RETURNING (Postgres)

Post by bursch » Tue 21 Jan 2020 07:31

Hi,

i've already tried it with the Open-Command. The result is the same.

The Fieldvalue is correct but the RowsAffected stays -1.

As a workaround i can use the RecordCount-Value. Maybe that's the solution.

FCS
Posts: 166
Joined: Sat 23 Feb 2013 18:46

Re: RowsAfffected with RETURNING (Postgres)

Post by FCS » Tue 21 Jan 2020 09:00

Hello,

As a workaround you may try something like this:

UQ.SQL.Clear;
UQ.SQL.Add('WITH rek AS (');
UQ.SQL.Add('INSERT INTO Test (k, v) VALUES (DEFAULT, 1) RETURNING k');
UQ.SQL.Add(') SELECT * FROM rek;');
UQ.Open;

However In documentation is written:

Check RowsAffected to determine how many rows were inserted, updated, or deleted during the last query
operation. I f RowsAffected is -1, the query has not inserted, updated, or deleted any rows.


Regards
Michal

bursch
Posts: 5
Joined: Tue 25 Sep 2018 07:45

Re: RowsAfffected with RETURNING (Postgres)

Post by bursch » Tue 21 Jan 2020 09:09

Hi,

i've checked the table already. The record is in the table.

The documentation or the RowsAffected is the problem.

Thanks for your help.

Post Reply