Page 1 of 1

Batch queries

Posted: Fri 02 Feb 2018 08:45
by apriem
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)
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);
  pgConnection: TPgConnection;
  pgq: TPgQuery;

  pgConnection := TPgConnection.Create(Nil);

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

    pgq := TPgQuery.Create(nil);
      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.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;




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 ?

Re: Batch queries

Posted: Fri 02 Feb 2018 11:50
by azyk
Thank you for the information. We reproduced the mentioned error and are investigating it now. We will let you know the results.

Re: Batch queries

Posted: Mon 12 Feb 2018 13:40
by apriem
Any progress on this ? I'd really appreciate a fix for this :)

Re: Batch queries

Posted: Tue 13 Feb 2018 09:09
by azyk
The investigation of the issue is in progress. We will immediately let you know about the results.

Re: Batch queries

Posted: Wed 28 Feb 2018 11:47
by azyk
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: and provide us with PgDAC license number, as well as the email address where the night build can be sent to.

Re: Batch queries

Posted: Wed 07 Mar 2018 14:12
by apriem
Thanks !

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