EZDatabaseError with postgres
lazarus / 16-Databases

----------------------------------------------------------------------
Root2 · 2022-06-09 20:02

I am trying to save data to postgres from tedit the code I am using is
this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button3Click(Sender: TObject);begin  ZQuery1.Close; 
ZQuery1.SQL.Clear;  ZQuery1.SQL.Add('insert into tdatostag
(dirtag,valortagint,valortagnum,nombretag,tipodato)'); 
ZQuery1.SQL.Add('values
(:dirtag,:valortagint,:valortagnum,:nombretag,:tipodato)'); 
ZQuery1.Params.ParamByName('dirtag').AsString:=Edit1.Text; 
ZQuery1.Params.ParamByName('valortagint').AsString:=Edit2.Text; 
ZQuery1.Params.ParamByName('valortagnum').AsString:=Edit3.Text; 
ZQuery1.Params.ParamByName('nombretag').AsString:=Edit4.Text; 
ZQuery1.Params.ParamByName('tipodato').AsString:=Edit5.Text; 
ZQuery1.ExecSQL;  ZQuery1.Open;end; 
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.

----------------------------------------------------------------------
marcov · 2022-06-09 20:31

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 · 2022-06-09 22:15

Just comment out line 13:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //
ZQuery1.Open;

----------------------------------------------------------------------
Zvoni · 2022-06-10 06:22

You're trying to Open a ZQuery, which underlyings SQL-Command is an
Insert.....
Now what's wrong with that?

----------------------------------------------------------------------
gucao · 2022-06-10 07:50

I guess you need to return the generated data, do like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button3Click(Sender: TObject);begin  ZQuery1.Close; 
ZQuery1.SQL.Clear;  ZQuery1.SQL.Add('insert into tdatostag
(dirtag,valortagint,valortagnum,nombretag,tipodato)'); 
ZQuery1.SQL.Add('values
(:dirtag,:valortagint,:valortagnum,:nombretag,:tipodato)'); 
ZQuery1.SQL.Add('RETURNING *'); //<<----add this line 
ZQuery1.Params.ParamByName('dirtag').AsString:=Edit1.Text; 
ZQuery1.Params.ParamByName('valortagint').AsString:=Edit2.Text; 
ZQuery1.Params.ParamByName('valortagnum').AsString:=Edit3.Text; 
ZQuery1.Params.ParamByName('nombretag').AsString:=Edit4.Text; 
ZQuery1.Params.ParamByName('tipodato').AsString:=Edit5.Text; 
//ZQuery1.ExecSQL; //<<----delete this line  ZQuery1.Open;end;  Please
do not forget commit.

----------------------------------------------------------------------
Root2 · 2022-06-10 15:13

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 · 2022-06-11 02:43

QuoteI 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.

Quotethis 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.