Zeos SQL (mysql - mariadb)

[douglas.cast]

OK guys, before some research and no lucky, I cant find what i need, so i came back hier.

I'm doing a simple project inside of Lazarus and my project is just some inserts and selects from the database, but i'm confused with my research and all the forum information that I've found.

I'm my database i have 3 tables where I need to insert the values at same transaction, usualy for this case I can use this SQL code:

Code: MySQL  [Select][+][-]

1. START TRANSACTION
2. INSERT INTO users (id, name) VALUES (id, 'some complete name');
3. SELECT LAST_INSERT_ID() FROM users INTO @id;
4. INSERT INTO table2 (id, value) VALUES (@id, 'value');
5. INSERT INTO table3 (id, value) VALUES (@id, 'another value');
6. COMMIT;
7.  

But my problem is that i cant find a way to setup this SQL inside of Lazarus and Zeos to get the same result, i've tryied the TZQuery with some sintax that i've found over the internet (something like this);

Code: MySQL  [Select][+][-]

1. START TRANSACTION;
2. INSERT INTO users VALUES (name :name);
3. SELECT LAST_INSERT_ID() INTO @id;
4. INSERT INTO table3 (id, value)VALUES (@id, value :value);
5. INSERT INTO table3 (id, value)VALUES (@id, avalue :avalue);
6. COMMIT;
7.  

I've got a problem with the : if I try to activate my connection talbe I got a SQL error and 'name NULL' instead of something else. but I've started all of them on the 'before open event' in my table (not sure because my dbe fiels are empty when i try actvate):

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

1.  table.ParamByName('name').AsString:=main_form.dbeditName.Text;
2.  

I also tryied another code with some sql statements under the TZConnection but something is messed up and i got another weird error, this code select the last inserted value then update it and create a blank inser, if I close and run the application again the code select the first register and update it.

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

1.         datamodule.zconnection.AutoCommit:=True;
2.         datamodule.zconnection.StartTransaction;
3.                 try
4.                         datamodule.table.Insert;
5.                         datamodule.table.FieldByName('name').AsString:=dbeditName.Text;
6.                         datamodule.table.Post;
7.                         datamodule.zconnection.Commit;
8.                 except
9.                         datamodule.zconnection.Rollback;
10.                 end;
11.  

I'm not sure if this last method work with my triple table and no ideia how to select the user id to insert into the other tables (maybe a var can help, but not sure yet).

So someone can give a giant and huge and and make a favor and try to explain how I can use the sql language under the tzquery?

Has i said I've made a research but cant find any awser that can help me.

DBEdits and some DBLookUpComboBox.

Sorry to annoy and also sorry my bad english, thanks very much.

[douglas.cast]

Now i know were I can't find awsers 

Anyway I solved my problem before some real long research:

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

1. try
2. btn.Enabled:=False;
3. with zconnection do begin
4. zconnection.AutoCommit:=True;
5. zconnection.StartTransaction;
6. tbl.SQL.Clear;
7. tbl.SQL.Text:='INSERT INTO tbl (fields) VALUES ('+pascalFields+')';
8. tbl.ExecSQL;
9. tbl.Close;
10.  
11. tbl.SQL.Clear;
12. tbl.SQL.Text:='SELECT LAST_INSERT_ID() AS iddb';
13. tbl.Open;
14. variable:=tbl.FieldByName('iddb').AsInteger;
15. tbl.ExecSQL;
16.  
17. try
18. zconnection.Commit;
19. except
20. zconnection.Rollback;
21. ShowMessage('Describe error');
22. end;
23. pascalFields.Clear;
24. end;
25. except
26. ShowMessage('Describe connection error');
27. end;
28. btn.Enabled:=True;
29. end;
30.  

[mangakissa]

Correct. I was just looking for you, but you placed the answer already.
Only you don't need a close and a clear.

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

1. var lastinsertedid : integer;
2. begin
3.   try
4.     ZConnection1.StartTransaction;
5.     ZQuery1.SQL.Add('INSERT INTO forum (naam) VALUES (:name)');
6.     zQuery1.ParamByName('name').AsString := 'john';
7.     zQuery1.ExecSQL;
8.     zquery1.SQL.Text  := 'SELECT last_insert_id() AS id';
9.     zquery1.Active  := true;
10.     lastinsertedid := zQuery1.fieldbyname('id').AsInteger;
11.     zquery1.Active  := false;
12.     zquery1.SQL.Text := 'INSERT INTO subforum (forum, plaats) VALUES (:id, :place)';
13.     zquery1.parambyname('id').asInteger := lastinsertedid;
14.     zquery1.parambyname('place').asString := 'Some place';
15.     zQuery1.ExecSQL;
16.     ZConnection1.commit;
17.   except
18.     ZConnection1.Rollback;
19.   end;
20.  
21.  

[miab3]

One way already described but you can also do it differently (with datasets):

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3. zquery1.close;
4. zquery1.SQL.Text:='select id, name from users';
5. zquery2.close;
6. zquery2.SQL.Text:='select id, value from table2';
7. zquery3.close;
8. zquery3.SQL.Text:='select id, value from table3';
9. zquery4.close;
10. zquery4.SQL.Text:='select last_insert_id() from users';
11. zquery1.open;
12. zquery2.open;
13. zquery3.open;
14. end;

and

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

1. procedure TForm1.Button2Click(Sender: TObject);
2. var vid:integer;
3. begin
4. zconnection1.AutoCommit:=True;
5. zconnection1.StartTransaction;
6. try
7. zquery1.Insert;
8. zquery1.FieldByName('name').AsString:=EditName.Text;
9. zquery1.Post;
10. zquery4.Open;
11. vid:=zquery4.fields[0].AsInteger;
12. zquery4.Close;
13. zquery2.Insert;
14. zquery2.FieldByName('id').value:=vid;
15. zquery2.FieldByName('value').AsString:=EditValue.Text;
16. zquery2.Post;
17. zquery3.Insert;
18. zquery3.FieldByName('id').value:=vid;
19. zquery3.FieldByName('value').AsString:=EditAnotherValue.Text;
20. zquery3.Post;
21. zconnection1.Commit;
22. except
23. zconnection1.Rollback;
24. end;
25. end;

Michal

[mangakissa]

It's an option, but very expensive.

[douglas.cast]

Ty for all replies, but I still with my code, it's alread typed ha ha

Just wondering, before expend some time with interface I just got a new problem, how can I place a item index number from a combobox into a str query, my db uses field uses tinyint like default data type.

Something like:

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

1. tbl.SQL.Text:='INSERT INTO tbl (fields) VALUES ('+QuotedStr(pascalStrField)+' , '+(pascalIntegerField+1)+' , '+pascalDateField+' ) ';
2.  

[mangakissa]

Works with parameters. Look at my example. A combobox is nothing more then a TEdit with an popup function with extra items.

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

1.   tbl.SQL.text := 'INSERT INTO tbl (field) VALUES (:item1)':
2.   tbl.parambyname.asstring := combobox1.text;
3.  

Is there a reason for not using DBComponents?

[douglas.cast]

Works with parameters. Look at my example. A combobox is nothing more then a TEdit with an popup function with extra items.

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

1.   tbl.SQL.text := 'INSERT INTO tbl (field) VALUES (:item1)':
2.   tbl.parambyname.asstring := combobox1.text;
3.  

Is there a reason for not using DBComponents?

I used to play a real long time ago with PHP, so I was thinking in write a commando in one line like I used to do, but I've alread typed everything like Lazarus need it, using parameters and field names, once again thanks for all the replys.