Batch queries

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

Batch queries

Post by apriem » Fri 02 Feb 2018 08:45

We are using batch queries to import large amounts of data. In a specific situation there are many large BLOBS stored in the pg_largeobject table. We need to import a large number of records, with some of the data coming from an external source, and some of the data coming from already stored data in pg_largeobject.

This leads to "syntax errors" when executing the batch queries through TPgQuery.

I have prepared a scenario for you to test this :

Create a table :

Code: Select all

CREATE TABLE public.testx
(
  x_id serial,
  x_bin bytea,
  x_thumb bytea,
  CONSTRAINT testx_pkey PRIMARY KEY (x_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.testx
  OWNER TO postgres;
Create some records in the lg_largeobject table :

Code: Select all

SELECT LO_IMPORT('C:\some\path\somefile.txt');
Execute the command above at least twice, and write down the large object IDs it returns.

Now use the following code in Delphi :

Code: Select all

procedure TZZ_Main_Form.Info1Click(Sender: TObject);
var
  pgConnection: TPgConnection;
  pgq: TPgQuery;
begin

  pgConnection := TPgConnection.Create(Nil);
  try

    pgConnection.Server := 'localhost';
    pgConnection.Database := 'test';
    pgConnection.Username := 'postgres';
    pgConnection.Password := 'postgres';
    pgConnection.Options.UseUnicode := True;
    pgConnection.Connected := True;

    pgq := TPgQuery.Create(nil);
    try
      pgq.Connection := pgConnection;
      pgq.sql.text := 'INSERT INTO TESTX (X_ID, X_BIN, X_THUMB) VALUES (:X_ID, LO_GET(:X_BIN), LO_GET(:X_THUMB));';
      pgq.params.ValueCount := 2;
      pgq.params[0].DataType := ftInteger;
      pgq.params[1].DataType := ftInteger;
      pgq.params[2].DataType := ftInteger;
      pgq.prepare();

      pgq.Params[0][0].Value := 1;
      pgq.Params[1][0].Value := 8859775;
      pgq.Params[2][0].Value := 8859778;

      pgq.Params[0][1].Value := 2;
      pgq.Params[1][1].Value := 8859775;
      pgq.Params[2][1].Value := 8859778;

      pgq.execute(2);

    finally
      pgq.Free;
    end;

  finally
    pgConnection.Free();
  end;

end;
Executing this code will trigger an error saying : syntax error at or near ";".

I narrowed it down to the LO_GET() function being used with an SQL parameter. I have tried several different ways of doing this (with and without specifically specifying the parameter data types, for example). I cannot get it to work.
I'd really like to be able to use this construction, since it will save a lot of time for this import. I cannot use PgLoader in this case.

Can you help me with this situation ?

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

Re: Batch queries

Post by azyk » Fri 02 Feb 2018 11:50

Thank you for the information. We reproduced the mentioned error and are investigating it now. We will let you know the results.

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

Re: Batch queries

Post by apriem » Mon 12 Feb 2018 13:40

Any progress on this ? I'd really appreciate a fix for this :)

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

Re: Batch queries

Post by azyk » Tue 13 Feb 2018 09:09

The investigation of the issue is in progress. We will immediately let you know about the results.

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

Re: Batch queries

Post by azyk » Wed 28 Feb 2018 11:47

We fixed the issue. This fix will be included in the PgDAC next build.

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, as well as the email address where the night build can be sent to.

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

Re: Batch queries

Post by apriem » Wed 07 Mar 2018 14:12

Thanks !

I will test it as soon as the new official build becomes available.

Post Reply