PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits

[Root2]

dear community, a cordial greeting to all, after trying to execute without success by batch the postgres copy command I decided to try with lazarus, for that I am using a SQLConnector to execute this command, but I am not getting to do it, it seems that I am missing something else, this is the code that I am using.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   consulta: String;
4. begin
5.   begin
6.  
7.   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM 'C:\Borrame\satosap.csv' DELIMITER ',' CSV HEADER';
8.   SQLQuery1.Close;
9.   SQLQuery1.SQL.Clear;
10.   SQLQuery1.SQL.Add(consulta);
11.   SQLQuery1.ExecSQL;
12.  
13. end;
14. end.    

when executing it I get this error.

Code: Pascal  [Select][+][-]

1. Compilar proyecto, Objetivo: project1.exe: Código de salida 1, Errores: 1
2. unit1.pas(42,173) Fatal: Syntax error, ";" expected but "identifier C" found

it seems that it recognized that it is not sql so it gives me an error.

Maybe my approach is wrong?

Thank you for your kind attention.

[dsiders]

dear community, a cordial greeting to all, after trying to execute without success by batch the postgres copy command I decided to try with lazarus, for that I am using a SQLConnector to execute this command, but I am not getting to do it, it seems that I am missing something else, this is the code that I am using.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   consulta: String;
4. begin
5.   begin
6.  
7.   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM 'C:\Borrame\satosap.csv' DELIMITER ',' CSV HEADER';
8.   SQLQuery1.Close;
9.   SQLQuery1.SQL.Clear;
10.   SQLQuery1.SQL.Add(consulta);
11.   SQLQuery1.ExecSQL;
12.  
13. end;
14. end.    

when executing it I get this error.

Code: Pascal  [Select][+][-]

1. Compilar proyecto, Objetivo: project1.exe: Código de salida 1, Errores: 1
2. unit1.pas(42,173) Fatal: Syntax error, ";" expected but "identifier C" found

it seems that it recognized that it is not sql so it gives me an error.

Maybe my approach is wrong?

Thank you for your kind attention.

Your statement in consulta is not properly quoted.

Use QuotedStr(): https://www.freepascal.org/docs-html/rtl/sysutils/quotedstr.html.
For UTF-8, use: https://dsiders.gitlab.io/lazdocsnext/lazutils/lazutf8/utf8quotedstr.html.

[dseligo]

As dsiders said, but you can also do it like this (use two quote signs, so you escape quote sign):

Code: Pascal  [Select][+][-]

1.   consulta:='COPY recetasv1(fecha, hora, sapreceta, lote, orden, coordenada, sapinsumo, volumen, tiempo, temperatura, velagitador, velancla, velobular, velsilverson) FROM ''C:\Borrame\satosap.csv'' DELIMITER '','' CSV HEADER';

[Zvoni]

What i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database.

1) open CSV for reading
2) read first line
3) if you know the first line are the column headers, ignore that line, or use column headers as field names later
4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts
5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4
6) encase everything inside a transaction
Done

At a guess: a first draft for something like this maybe 10 minutes

[dseligo]

What i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database.

1) open CSV for reading
2) read first line
3) if you know the first line are the column headers, ignore that line, or use column headers as field names later
4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts
5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4
6) encase everything inside a transaction
Done

At a guess: a first draft for something like this maybe 10 minutes

But it may get complicated. For instance, values could be quoted and they must be quoted if value contain delimiter.
And if OP had some CSV generated by Postgres, you could expect that they took care of processing CSV, so OP doesn't have to worry about header line, delimiters, quotes and similar.

[zeljko]

Think that you're missing WITH before DELIMITER

[Zvoni]

What i don‘t understand: independent of ready-to-use code for processing CSV‘s, a CSV is the easiest file to process „manually“ in regards to import it to a database.

1) open CSV for reading
2) read first line
3) if you know the first line are the column headers, ignore that line, or use column headers as field names later
4) if delimiter is comma you read each following line as is, otherwise replace the delimiter with comma. Take care for delimiters within quoted parts
5) construct INSERT-command from the columnheaders and the VALUES-part from the lines you read in 4
6) encase everything inside a transaction
Done

At a guess: a first draft for something like this maybe 10 minutes

But it may get complicated. For instance, values could be quoted and they must be quoted if value contain delimiter.
And if OP had some CSV generated by Postgres, you could expect that they took care of processing CSV, so OP doesn't have to worry about header line, delimiters, quotes and similar.

My post was more in regards to his other thread inquiring about processing CSV, which didn’t come to a conclusion.
Looks like he‘s trying other ways, and i just pointed out, that CSV is the easiest for „manual“ processing

[Root2]

thanks for the help, it no longer gives error but it is not executing the command, I thought that the command was wrong so I executed it separately and it worked without problems, but when I execute it from lazarus it does nothing.

Thanks.

[gucao]

please try it

Code: Pascal  [Select][+][-]

1.   with SQLQuery1 do
2.   begin
3.     //create recetasv1
4.     Active:=False;
5.     SQL.Clear;
6.     SQL.Add('CREATE TABLE public.recetasv1 ');
7.     SQL.Add('(                             ');
8.     SQL.Add('    fecha character varying,  ');
9.     SQL.Add('    hora character varying    ');
10.     SQL.Add(');                            ');
11.     SQL.Add('ALTER TABLE IF EXISTS public.recetasv1 ');
12.     SQL.Add('    OWNER to "Administrator";');
13.     ExecSQL;
14.     //insert data to recetasv1
15.     Active:=False;
16.     SQL.Clear;
17.     SQL.Add('Insert Into recetasv1(fecha,hora) values(''A1'',''A2''),(''B1'',''B2''),(''C1'',''C2'');');
18.     ExecSQL;
19.     //copy to file
20.     Active:=False;
21.     SQL.Clear;
22.     SQL.Add('COPY recetasv1(fecha, hora) to ''D:\satosap.csv'' DELIMITER '','' CSV HEADER;');
23.     ExecSQL;
24.     //create recetasv2
25.     Active:=False;
26.     SQL.Clear;
27.     SQL.Add('CREATE TABLE public.recetasv2 ');
28.     SQL.Add('(                             ');
29.     SQL.Add('    fecha character varying,  ');
30.     SQL.Add('    hora character varying    ');
31.     SQL.Add(');                            ');
32.     SQL.Add('ALTER TABLE IF EXISTS public.recetasv2 ');
33.     SQL.Add('    OWNER to "Administrator";');
34.     ExecSQL;
35.     //copy from file
36.     Active:=False;
37.     SQL.Clear;
38.     SQL.Add('COPY recetasv2(fecha, hora) from ''D:\satosap.csv'' DELIMITER '','' CSV HEADER;');
39.     ExecSQL;
40.     //check recetasv2
41.     Active:=False;
42.     SQL.Clear;
43.     SQL.Add('Select * from recetasv2');
44.     Open;
45.     ShowMessage(IntToStr(RecordCount)); //is 3
46.     First;
47.     ShowMessage(FieldByName('hora').AsString); //is A2
48.   end;  
49.