Using "RETURNING" in a batch query

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Using "RETURNING" in a batch query

Post by apriem » Wed 11 Apr 2018 13:09

I am using your batch queries, since you solved a bug in this in PgDac 5.1.5. It is working nicely, but I am having trouble with a scenario.

In some scenarios, I want to use batch queries for INSERTS and UPDATES. And after the queries have finished, I want some fields in the hit rows to be returned.

So for example, my INSERT query will be like 'INSERT INTO XTABLE (A, B) VALUES (:A, :B) RETURNING A, B;'.

When I call myQuery.Execute(10), I get an Access Violation. When I leave out the 'RETURNING' part, it works like a charm.

Is using RETURNING not supported by batch queries ? Do I need to call some other method than Execute() ? I could not find anything about this in the documentation...

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Thu 12 Apr 2018 08:28

We cannot reproduce the specified problem with RETURNING clause in batch operations. Please compose a small test project to demonstrate it and send us via the contact form at our site:
https://www.devart.com/company/contactform.html. In the sample, also include the script for creating the XTABLE table.

apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Re: Using "RETURNING" in a batch query

Post by apriem » Thu 12 Apr 2018 14:16

Hmm, I cannot reproduce the AV in a simple test project. However, I still have an issue. Calling Execute(2) inserts the rows into the database, that's fine. But... The query object does not return any rows. RecordCount is zero after calling Execute(2).

I think I need to call some other method instead of Execute() to get any returned rows (just like with non-batch queries). Is that correct ? Open() does not seem to support any parameters for batch operations.

I used the following code (just create a simple table 'xtable' with two varchar fields)

Code: Select all

  x := TPgQuery.Create(Nil);
  x.connection := datacon; // Some TPgConnection to some PostgreSQL database
  x.sql.text := 'insert into xtable (a, b) values (:a, :b) returning a, b;';
  x.Params.ValueCount := 2;
  x.parambyname('a')[0].asstring := 'a1';
  x.parambyname('b')[0].asstring := 'b1';
  x.parambyname('a')[1].asstring := 'a2';
  x.parambyname('b')[1].asstring := 'b2';
  x.execute(2);
  
  // x.recordcount will be zero here !

  x.free;

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Fri 13 Apr 2018 08:59

The INSERT SQL command only inserts rows to the table on the server. Therefore, the TPgQuery.Execute call for such an SQL query does not return the inserted rows to the dataset. This is correct behavior. More details about INSERT SQL command in PostgreSQL documentation: https://www.postgresql.org/docs/current ... nsert.html

Batch operations in PgDAC are designed not for fetching data, but for insering and modifying data:
https://www.devart.com/pgdac/docs/batchops.htm . The SELECT SQL command should be used to retrive rows from the table on the server. More details about SELECT: https://www.postgresql.org/docs/current ... elect.html

apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Re: Using "RETURNING" in a batch query

Post by apriem » Fri 13 Apr 2018 09:07

That's not entirely true...

If you create a TPgQuery with an SQL text like "Insert Into XTable (A, B) Values (:A, :B) RETURNING A, B" and if you call OPEN() on that TPgQuery, it definitely returns the A and B fields for the new records. It adds the rows on the server, and then returns the desired fields. You can then navigate through those records, just like a SELECT query.

This can be used to (for example) return SERIAL values that have been added.

I actually use this functionality in PgDac.

My question is : This works perfectly for normal (non-batch) queries. You have to call Open() instead of ExecSQL(). For batch-queries, I think there is only the Execute(nr) method, no Open() method there.

Am I explaining myself clearly, of do we misunderstand each other ?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Fri 13 Apr 2018 12:01

At present we are investigating the possibility of returning rows with fields from RETURNING for batch queries. We will let you know the results.

apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Re: Using "RETURNING" in a batch query

Post by apriem » Fri 13 Apr 2018 13:04

Great, thanks !

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Thu 19 Apr 2018 10:03

We implemented rows returning in TPgQuery dataset when exectuing INSERT ... RETURNING batch operations. This feature will be included in the next build of PgDAC.

If you want to get it before the official release, use the contact form at our site: https://www.devart.com/company/contactform.html and provide us with PgDAC license number and the email address where to send a night build.

apriem
Posts: 20
Joined: Mon 06 Oct 2014 07:26

Re: Using "RETURNING" in a batch query

Post by apriem » Mon 23 Apr 2018 08:34

Did you implement it for UPDATE ... RETURNING as well ? I'll need it for UPDATE as well as INSERT.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Wed 25 Apr 2018 08:08

We added the implementation of UPDATE ... RETURNING batch operations to our roadmap.

tristan
Posts: 2
Joined: Thu 29 Nov 2018 06:31

Re: Using "RETURNING" in a batch query

Post by tristan » Thu 29 Nov 2018 06:44

Hi, thanks for the information above, it helped me get this working using "RETURNING".

In some of the queries I wanted to batch, I required an "ON CONFLICT" clause also.

I observed "ON CONFLICT" resulted in a syntax error during runtime, whenever I ran as a batch query.
Where of course exactly the same query (with the "ON CONFLICT") ran fine when NOT done as a batch. And ran fine as batch with the "ON CONFLICT" omitted.

Has anyone else had this problem? I assume has just not been catered for somewhere in the implementation.

Just wanted to see if this was a known problem. (Or an an error on my part). And if is a problem, are there plans for a fix to be implemented in the near future?

Thanks!

Additional info:
- using: Delphi XE6 and pgdac build 5.2.7
- version PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

I will hopefully compose test project soon if i get time.
Last edited by tristan on Fri 07 Dec 2018 00:05, edited 1 time in total.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using "RETURNING" in a batch query

Post by azyk » Thu 29 Nov 2018 10:58

Please compose a small test project. In this project, demonstrate how you are using ON CONFLICT with PgDAC Batch Operations. Include the script for creating a test table to the test project. Then use the contact form at our site: https://www.devart.com/company/contactform.html and send us this project. Also specify the exact version of PostgreSQL you are using.

Post Reply