Recent

Author Topic: EZDatabaseError with postgres  (Read 551 times)

Root2

  • New Member
  • *
  • Posts: 12
EZDatabaseError with postgres
« on: June 09, 2022, 10:02:19 pm »
I am trying to save data to postgres from tedit the code I am using is this:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. begin
  3.   ZQuery1.Close;
  4.   ZQuery1.SQL.Clear;
  5.   ZQuery1.SQL.Add('insert into tdatostag (dirtag,valortagint,valortagnum,nombretag,tipodato)');
  6.   ZQuery1.SQL.Add('values (:dirtag,:valortagint,:valortagnum,:nombretag,:tipodato)');
  7.   ZQuery1.Params.ParamByName('dirtag').AsString:=Edit1.Text;
  8.   ZQuery1.Params.ParamByName('valortagint').AsString:=Edit2.Text;
  9.   ZQuery1.Params.ParamByName('valortagnum').AsString:=Edit3.Text;
  10.   ZQuery1.Params.ParamByName('nombretag').AsString:=Edit4.Text;
  11.   ZQuery1.Params.ParamByName('tipodato').AsString:=Edit5.Text;
  12.   ZQuery1.ExecSQL;
  13.   ZQuery1.Open;
  14. end;  
  15.  

When compiling it does not give me any error, but when I press on the save button it gives me error:

The project has thrown an exception 'EZDatabaseError' with the message: Can not open a Resulset.

Structure of table.
dirtag      int4      not null
valortagint   int4      not null
valortagnum   numeric   not null
nombretag   bpchar   not null
tipodato           bpchar   not null

Thank you very much for your kind attention.

Regards.
« Last Edit: June 09, 2022, 10:09:18 pm by Root2 »

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 10175
  • FPC developer.
Re: EZDatabaseError with postgres
« Reply #1 on: June 09, 2022, 10:31:15 pm »
It seems to be a bug but you can work around it by using a returning statement. See

https://sourceforge.net/p/zeoslib/tickets/492/

dseligo

  • Hero Member
  • *****
  • Posts: 651
Re: EZDatabaseError with postgres
« Reply #2 on: June 10, 2022, 12:15:01 am »
Just comment out line 13:
Code: Pascal  [Select][+][-]
  1. // ZQuery1.Open;

Zvoni

  • Hero Member
  • *****
  • Posts: 1276
Re: EZDatabaseError with postgres
« Reply #3 on: June 10, 2022, 08:22:34 am »
You're trying to Open a ZQuery, which underlyings SQL-Command is an Insert.....
Now what's wrong with that?
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

gucao

  • New Member
  • *
  • Posts: 31
Re: EZDatabaseError with postgres
« Reply #4 on: June 10, 2022, 09:50:36 am »
I guess you need to return the generated data, do like this:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. begin
  3.   ZQuery1.Close;
  4.   ZQuery1.SQL.Clear;
  5.   ZQuery1.SQL.Add('insert into tdatostag (dirtag,valortagint,valortagnum,nombretag,tipodato)');
  6.   ZQuery1.SQL.Add('values (:dirtag,:valortagint,:valortagnum,:nombretag,:tipodato)');
  7.   ZQuery1.SQL.Add('RETURNING *'); //<<----add this line
  8.   ZQuery1.Params.ParamByName('dirtag').AsString:=Edit1.Text;
  9.   ZQuery1.Params.ParamByName('valortagint').AsString:=Edit2.Text;
  10.   ZQuery1.Params.ParamByName('valortagnum').AsString:=Edit3.Text;
  11.   ZQuery1.Params.ParamByName('nombretag').AsString:=Edit4.Text;
  12.   ZQuery1.Params.ParamByName('tipodato').AsString:=Edit5.Text;
  13.   //ZQuery1.ExecSQL; //<<----delete this line
  14.   ZQuery1.Open;
  15. end;
  16.  
Please do not forget commit.


Root2

  • New Member
  • *
  • Posts: 12
Re: EZDatabaseError with postgres
« Reply #5 on: June 10, 2022, 05:13:53 pm »
Thank you very much for the help, Gucao your solution works perfectly, I was also testing removing only "ZQuery1.Open;" and saved without problems, now in this part I have the following question this affects somehow to the transaction? or is it necessary to include this line, I ask this because I will have to save a lot of data in this way approximately 200 data at once which leads me to the following question this method of saving data is the most optimal to save large amounts of data? or can somehow improve this procedure.

Please excuse me if I am abusing your kindness.

Regards.

gucao

  • New Member
  • *
  • Posts: 31
Re: EZDatabaseError with postgres
« Reply #6 on: June 11, 2022, 04:43:01 am »
Quote
I was also testing removing only "ZQuery1.Open;" and saved without problems,
This means that you don't need the postgresql to return the generated data,Just do it, like dseligo's said.

Quote
this affects somehow to the transaction?
no

Quote
or is it necessary to include this line,
if you only save data to postgresql,Please delete the  "ZQuery1.Open;" and keep the  "ZQuery1.ExecSQL;" and you don't need "RETURNING *".

"RETURNING *" Is used to tell postgresql to return the generated data to you application.
« Last Edit: June 11, 2022, 04:57:48 am by gucao »

 

TinyPortal © 2005-2018