Devart Forums

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...

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.

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)

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

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 ?

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

Great, thanks !

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.

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

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

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.

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.