Batch queries
Posted: 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 :
Create some records in the lg_largeobject table :
Execute the command above at least twice, and write down the large object IDs it returns.
Now use the following code in Delphi :
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 ?
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;Code: Select all
SELECT LO_IMPORT('C:\some\path\somefile.txt');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;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 ?