RowsAfffected with RETURNING (Postgres)

[bursch]

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]

Hello,

Try use:

UniQuery1.Open;

As I remember the ExecSQL does not return dataset.

Regards
Michal

[bursch]

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]

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]

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.