Error Insert TSQLQuery Component (SOLVED)
lazarus / 63-Database

----------------------------------------------------------------------
Root2 · 2022-06-27 15:28

Dear community, please could you help me with an error that I get when
saving data, I am using the component "TSQLQuery" the code 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.Button4Click(Sender: TObject);begin  SQLQuery1.Close; 
SQLQuery1.Clear;  SQLQuery1.SQL.Add('insert into tdtag
(dirtag,valtagint,nametag,typedata)');  SQLQuery1.SQL.Add('values
(:dirtag,:valtagint,:nametag,:typedata)');   
SQLQuery1.ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress; 
SQLQuery1.ParamByName('valtagint').AsFloat:=PLCTagNumber1.Value;   
SQLQuery1.ParamByName('nametag').AsString:=PLCTagNumber1.Name; 
SQLQuery1.ParamByName('typedata').AsString:='I';   
SQLQuery1.Open;end; 
When I compile it doesn't give any error, but when I press on the save
button I get this message:

"the project has thrown exception epqdatabaseerror preparation query
failed, no parameter $1 line 3 values ($1,$2,$3)"

thank you very much for your time and attention.

----------------------------------------------------------------------
Root2 · 2022-06-27 17:11

I found this example:

https://lists.lazarus-ide.org/pipermail/lazarus-
es/2009-September/001358.html

I have adjusted it to my requirement, it doesn't give me any error,
but when I press the save button it does nothing, it doesn't save.
This is the code:

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.Button4Click(Sender: TObject);var  consulta: String;begin 
consulta:='insert into tdatostag
(dirtag,valortagint,nombretag,tipodato) values
(:dirtag,:valortagint,:nombretag,:tipodato)';  SQLQuery1.Close; 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(consulta);  with
SQLQuery1.Params do begin       
ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;       
ParamByName('valortagint').AsFloat:=PLCTagNumber1.Value;       
ParamByName('nombretag').AsString:=PLCTagNumber1.Name;       
ParamByName('tipodato').AsString:='F';  end;  SQLQuery1.ExecSQL;end; 
Thank you very much for your kind attention.

Best regards.

----------------------------------------------------------------------
rvk · 2022-06-27 17:48

Quote from: Root2 on June 27, 2022, 07:11:45 pmI have adjusted it to
my requirement, it doesn't give me any error, but when I press the
save button it does nothing, it doesn't save.
How do you know nothing is saved? I don't see any reading of records
here.

Also, how are you handling transaction (and what db are you using
etc).
If the transaction isn't handled it could be rolled back at the end if
your program, removing any changes you made.

----------------------------------------------------------------------
dseligo · 2022-06-27 17:58

What rvk said is correct, so check that.

I just want to say that in your first example you should have
'ExecSQL' instead of 'Open', 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.Button4Click(Sender: TObject);begin  SQLQuery1.Close; 
SQLQuery1.Clear;  SQLQuery1.SQL.Add('insert into tdtag
(dirtag,valtagint,nametag,typedata)');  SQLQuery1.SQL.Add('values
(:dirtag,:valtagint,:nametag,:typedata)');   
SQLQuery1.ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress; 
SQLQuery1.ParamByName('valtagint').AsFloat:=PLCTagNumber1.Value;   
SQLQuery1.ParamByName('nametag').AsString:=PLCTagNumber1.Name; 
SQLQuery1.ParamByName('typedata').AsString:='I';   
SQLQuery1.ExecSQL;end;

----------------------------------------------------------------------
Root2 · 2022-06-27 18:16

Hi rvk, thanks for replying.

After doing the test I checked in the database with a Select.
I am using PostgreSQL, the SQLTransacction action is caRollBack, the
lazarus version is 2.2.2.2, I also include a link to the project in
case this helps.

https://we.tl/t-oUHUuc9c2x

Best regards.

----------------------------------------------------------------------
rvk · 2022-06-27 18:24

Quote from: Root2 on June 27, 2022, 08:16:09 pmI am using PostgreSQL,
the SQLTransacction action is caRollBack,So, the default action is
rollback.

Did you commit the transaction somehow (via button, tdbnavigator) or
in code via transaction.commit?

(I did not look at your code yet)

----------------------------------------------------------------------
Root2 · 2022-06-27 18:36

dseligo, thanks for replying, I tried that and I get this error: "no
parameter $1 values($1,$2,$3,$4)", although when reviewing more in
detail the query I found a small error related to the number of
columns, I corrected the error however it still gives error, anyway I
would like to make the insert work with the second example as that
will allow me to insert several records to the same fields which is
what I want to do.

----------------------------------------------------------------------
Root2 · 2022-06-27 18:39

I think I misunderstood, but I don't think I'm using commit anywhere.

I modified the default action, I set it to caCommit, now the message I
get is:
"Cannot open a non-select statement"

Regards.

----------------------------------------------------------------------
Root2 · 2022-06-27 19:30

it works, it was only to modify it to be commit, I didn't save
correctly, I checked again and it worked, thank you very much.
Now I have a question about this mode, can I save several records at
the same time with this mode using the following code?

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.Button4Click(Sender: TObject);var  consulta: String;begin 
consulta:='insert into tdatostag
(dirtag,valortagint,nombretag,tipodato) values
(:dirtag,:valortagint,:nombretag,:tipodato)';  SQLQuery1.Close; 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(consulta);  with
SQLQuery1.Params do begin       
ParamByName('dirtag').AsInteger:=PLCTagNumber1.MemAddress;       
ParamByName('valortagint').AsFloat:=PLCTagNumber1.Value;       
ParamByName('nombretag').AsString:=PLCTagNumber1.Name;       
ParamByName('tipodato').AsString:='F';        
ParamByName('dirtag').AsInteger:=PLCTagNumber2.MemAddress;       
ParamByName('valortagint').AsFloat:=PLCTagNumber2.Value;       
ParamByName('nombretag').AsString:=PLCTagNumber2.Name;       
ParamByName('tipodato').AsString:='I';  end;  SQLQuery1.ExecSQL;end; 
all are the same data types.

Thank you very much.

----------------------------------------------------------------------
rvk · 2022-06-27 19:38

Quote from: Root2 on June 27, 2022, 09:30:43 pmit works, it was only
to modify it to be commit, I didn't save correctly, I checked again
and it worked, thank you very much.
Now I have a question about this mode, can I save several records at
the same time with this mode using the following code?
No, but you can do it like this: (each insert needs it's own ExecSQL)

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.Button4Click(Sender: TObject);var  consulta: String;begin 
consulta:='insert into tdatostag
(dirtag,valortagint,nombretag,tipodato) values
(:dirtag,:valortagint,:nombretag,:tipodato)';  SQLQuery1.Close; 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(consulta);  
SQLQuery1.ParamByName('dirtag').AsInteger :=
PLCTagNumber1.MemAddress; 
SQLQuery1.ParamByName('valortagint').AsFloat := PLCTagNumber1.Value; 
SQLQuery1.ParamByName('nombretag').AsString := PLCTagNumber1.Name; 
SQLQuery1.ParamByName('tipodato').AsString := 'F'; 
SQLQuery1.ExecSQL;   SQLQuery1.ParamByName('dirtag').AsInteger :=
PLCTagNumber2.MemAddress; 
SQLQuery1.ParamByName('valortagint').AsFloat := PLCTagNumber2.Value; 
SQLQuery1.ParamByName('nombretag').AsString := PLCTagNumber2.Name; 
SQLQuery1.ParamByName('tipodato').AsString := 'I'; 
SQLQuery1.ExecSQL; end; 
(I don't like the with-statement that much but you could leave it in
if you like it more)
You can ommit the extra .Params. because TSQLQuery also has the
ParamByName function.

----------------------------------------------------------------------
Root2 · 2022-06-27 20:32

WOW¡, thank you very much, it works perfectly, now I just need to add
the other records to start testing, thanks again.