How to send a number of SQL-commands to database?
lazarus / 16-Databases

----------------------------------------------------------------------
Vittorio · 2020-11-25 10:08

Hi there,

I want to make a tool for editing database table. This table contains
very simple hierarchical structure - element's name and its parent's
name. The idea is to use TreeView for visual editing of structure and
button for applying updates. Thus, each time the structure changes, an
SQL-command (INSERT, DELETE, UPDATE) should be created, and then they
are all should be send to the database.

I've written code as followed (related to adding records only), but it
works only for singe SQL-command. If we try to add several instances
an error occurs (Error executing query: You have an error ir your SQL
syntax;)

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
TfmKPP.FormActivate(Sender: TObject);begin  DM.qWrite.Close; //qWrite
is a TSQLQuery for writing into DB, works with TransactionWrite 
DM.qWrite.SQL.Clear; //Clear SQL textend; procedure
TfmKPP.bbAddClick(Sender: TObject); //When need to add some element in
TreeView TVvar  Node : TTreeNode;  NewNodeName, ParentNodeName :
string;begin  //Creating node in TreeView    NewNodeName :=
IntToStr(TV.Items.Count + 1);  ParentNodeName := TV.Selected.Text; 
Node := TV.Items.AddChild(TV.Selected, NewNodeName);   //Commands for
DB    DM.qWrite.SQL.Add('INSERT INTO kpp (ID, Parent_ID) VALUES (:ID,
:Parent_ID);');  DM.qWrite.ParamByName('ID').AsString := NewNodeName; 
DM.qWrite.ParamByName('Parent_ID').AsString := ParentNodeName; 
DM.qWrite.ExecSQL;end;  procedure TfmKPP.bbUpdateClick(Sender:
TObject); //Applying updatesbegin  DM.TransactionWrite.Commit;end; 
Could you please advise me how make such a thing correctly?

----------------------------------------------------------------------
devEric69 · 2020-11-25 10:32

Hello,
To begin, I would have written...:

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
DM.qWrite.ParamByName('ID').AsInteger :=
StrToInt(NewNodeName);DM.qWrite.ParamByName('Parent_ID').AsInteger :=
StrToInt(ParentNodeName);
... as the type of their respective fields, I guess (to be rid of some
possible inserted quotes, in the final SQL statement).

----------------------------------------------------------------------
Vittorio · 2020-11-26 08:02

devEric69, thanks for your reply.

Actually, numeric or text expression is not matter for me. In fact,
both fields are VARCHAR(45) and
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 NewNodeName
:= IntToStr(TV.Items.Count + 1);was written to create an unique name
for a new node.

I just want to figure out how to write complex TSQLQuery.SQL.Text by
different procedures (relating to inserting, deleting etc.) and,
finally, send this SQL-command by another procedure.

----------------------------------------------------------------------
devEric69 · 2020-11-26 09:09

All right. But you said that you had an "Error executing query: You
have an error ir your SQL syntax;".
So, before doing more complicated things with SQL action parameterized
queries, maybe you want to understand why your current INSERT is not
correct. If yes, you could write something like:
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
TfmKPP.bbUpdateClick(Sender: TObject); //Applying updatesbegin  try  
DM.TransactionWrite.Commit;  except    on e: EDatabaseError do begin 
   ShowMessage(e.message{+ some code to display the infamous SQL. This
code depends on the quey \ table object used});     
DM.TransactionWrite.Rollback;    end;  end:end;

----------------------------------------------------------------------
Vittorio · 2020-11-26 15:40

Thank you for advice to dig into the code.

By some miracle, I found out that the root cause was connected with
the problems with TConnection to DB. I've got 1 TConnection and 3
TTransactions linked with it. One of these transactions was used to
checking time of last updates of some tables in DB. Each 2 seconds
this transaction became inactive and if I managed to press bbAdd +
bbUpdate in this 2 sec interval, everything worked as it should.

I've made special TConnection for that 2-sec-interval transaction and
problem dissapeared.

Anyway, it is not entirely clear to me how one transaction can affect
others, even with a common TConnection.

----------------------------------------------------------------------
egsuh · 2020-11-27 09:03

I have a similar structure. For me, I do not have unique key for each
node. So, after finishing changes on the TreeView, I delete all the
relevant records from DB, and save all new records.

If you have unique keys, then I think you can do all you want with two
SQL commands (assuming Firebird).

1) delete
2) update or insert

It is very intuitive .. .when you delete a node, you only have to
delete the record with the unique key of the node.

When insert, a new unique key will be given.

When you change something, like changing position of a node so that
its parent node is changed, then you need update.

Update or Insert (SQL) command will insert or update the content based
on the key.