Recent

Author Topic: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits  (Read 605 times)

Root2

  • New Member
  • *
  • Posts: 23
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

  • Hero Member
  • *****
  • Posts: 721
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #1 on: September 03, 2022, 12:58:00 am »
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.
Preview Lazarus 2.3.0 documentation at: https://dsiders.gitlab.io/lazdocsnext

dseligo

  • Hero Member
  • *****
  • Posts: 813
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #2 on: September 03, 2022, 09:48:17 am »
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

  • Hero Member
  • *****
  • Posts: 1527
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #3 on: September 03, 2022, 11:05:45 am »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

dseligo

  • Hero Member
  • *****
  • Posts: 813
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #4 on: September 03, 2022, 11:16:43 am »
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

  • Hero Member
  • *****
  • Posts: 1340
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #5 on: September 03, 2022, 12:14:57 pm »
Think that you're missing WITH before DELIMITER

Zvoni

  • Hero Member
  • *****
  • Posts: 1527
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #6 on: September 03, 2022, 12:17:56 pm »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Root2

  • New Member
  • *
  • Posts: 23
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #7 on: September 14, 2022, 12:15:11 am »
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

  • New Member
  • *
  • Posts: 34
Re: PostgreSQL use copy with SQLConnector Lazarus 2.2.2 Windows 10 64bits
« Reply #8 on: September 14, 2022, 04:20:22 am »
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.  

 

TinyPortal © 2005-2018