How to send a number of SQL-commands to database?

[Vittorio]

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][+][-]

1. procedure TfmKPP.FormActivate(Sender: TObject);
2. begin
3.   DM.qWrite.Close; //qWrite is a TSQLQuery for writing into DB, works with TransactionWrite
4.   DM.qWrite.SQL.Clear; //Clear SQL text
5. end;
6.  
7. procedure TfmKPP.bbAddClick(Sender: TObject); //When need to add some element in TreeView TV
8. var
9.   Node : TTreeNode;
10.   NewNodeName, ParentNodeName : string;
11. begin
12.   //Creating node in TreeView  
13.   NewNodeName := IntToStr(TV.Items.Count + 1);
14.   ParentNodeName := TV.Selected.Text;
15.   Node := TV.Items.AddChild(TV.Selected, NewNodeName);
16.  
17.   //Commands for DB  
18.   DM.qWrite.SQL.Add('INSERT INTO kpp (ID, Parent_ID) VALUES (:ID, :Parent_ID);');
19.   DM.qWrite.ParamByName('ID').AsString := NewNodeName;
20.   DM.qWrite.ParamByName('Parent_ID').AsString := ParentNodeName;
21.   DM.qWrite.ExecSQL;
22. end;
23.  
24. procedure TfmKPP.bbUpdateClick(Sender: TObject); //Applying updates
25. begin
26.   DM.TransactionWrite.Commit;
27. end;
28.  

Could you please advise me how make such a thing correctly?

[devEric69]

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

Code: Pascal  [Select][+][-]

1. DM.qWrite.ParamByName('ID').AsInteger := StrToInt(NewNodeName);
2. 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]

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][+][-]

1. 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]

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][+][-]

1. procedure TfmKPP.bbUpdateClick(Sender: TObject); //Applying updates
2. begin
3.   try
4.     DM.TransactionWrite.Commit;
5.   except
6.     on e: EDatabaseError do begin
7.       ShowMessage(e.message{+ some code to display the infamous SQL. This code depends on the quey \ table object used});
8.       DM.TransactionWrite.Rollback;
9.     end;
10.   end:
11. end;

[Vittorio]

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]

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.