Zeos SQL (mysql - mariadb)
lazarus / 16-Databases

----------------------------------------------------------------------
douglas.cast · 2016-05-06 21:44

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i START
TRANSACTIONINSERT INTO users (id, name) VALUES (id, 'some complete
name');SELECT LAST_INSERT_ID() FROM users INTO @id;INSERT INTO table2
(id, value) VALUES (@id, 'value');INSERT INTO table3 (id, value)
VALUES (@id, 'another value');COMMIT; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i START
TRANSACTION;INSERT INTO users VALUES (name :name);SELECT
LAST_INSERT_ID() INTO @id;INSERT INTO table3 (id, value)VALUES (@id,
value :value);INSERT INTO table3 (id, value)VALUES (@id, avalue
:avalue);COMMIT; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 table.ParamByName('name').AsString:=main_form.dbeditName.Text; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        
datamodule.zconnection.AutoCommit:=True;       
datamodule.zconnection.StartTransaction;                try          
            datamodule.table.Insert;                       
datamodule.table.FieldByName('name').AsString:=dbeditName.Text;      
                datamodule.table.Post;                       
datamodule.zconnection.Commit;                except                 
     datamodule.zconnection.Rollback;                end; 
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 · 2016-05-09 12:58

Now i know were I can't find awsers 

Anyway I solved my problem before some real long research:

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
trybtn.Enabled:=False;with zconnection do beginzconnection.AutoCommit:
=True;zconnection.StartTransaction;tbl.SQL.Clear;tbl.SQL.Text:='INSERT
INTO tbl (fields) VALUES ('+pascalFields+')';tbl.ExecSQL;tbl.Close; tb
l.SQL.Clear;tbl.SQL.Text:='SELECT LAST_INSERT_ID() AS iddb';tbl.Open;v
ariable:=tbl.FieldByName('iddb').AsInteger;tbl.ExecSQL; tryzconnection
.Commit;exceptzconnection.Rollback;ShowMessage('Describe
error');end;pascalFields.Clear;end;exceptShowMessage('Describe
connection error');end;btn.Enabled:=True;end;

----------------------------------------------------------------------
mangakissa · 2016-05-09 13:24

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var
lastinsertedid : integer;begin  try    ZConnection1.StartTransaction; 
  ZQuery1.SQL.Add('INSERT INTO forum (naam) VALUES (:name)');   
zQuery1.ParamByName('name').AsString := 'john';    zQuery1.ExecSQL;  
zquery1.SQL.Text  := 'SELECT last_insert_id() AS id';   
zquery1.Active  := true;    lastinsertedid :=
zQuery1.fieldbyname('id').AsInteger;    zquery1.Active  := false;   
zquery1.SQL.Text := 'INSERT INTO subforum (forum, plaats) VALUES (:id,
:place)';    zquery1.parambyname('id').asInteger := lastinsertedid;  
zquery1.parambyname('place').asString := 'Some place';   
zQuery1.ExecSQL;    ZConnection1.commit;  except   
ZConnection1.Rollback;  end;

----------------------------------------------------------------------
miab3 · 2016-05-09 15:31

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

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.Button1Click(Sender:
TObject);beginzquery1.close;zquery1.SQL.Text:='select id, name from
users';zquery2.close;zquery2.SQL.Text:='select id, value from
table2';zquery3.close;zquery3.SQL.Text:='select id, value from
table3';zquery4.close;zquery4.SQL.Text:='select last_insert_id() from
users';zquery1.open;zquery2.open;zquery3.open;end;
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 procedure
TForm1.Button2Click(Sender: TObject);var vid:integer;beginzconnection1
.AutoCommit:=True;zconnection1.StartTransaction;tryzquery1.Insert;zque
ry1.FieldByName('name').AsString:=EditName.Text;zquery1.Post;zquery4.O
pen;vid:=zquery4.fields[0].AsInteger;zquery4.Close;zquery2.Insert;zque
ry2.FieldByName('id').value:=vid;zquery2.FieldByName('value').AsString
:=EditValue.Text;zquery2.Post;zquery3.Insert;zquery3.FieldByName('id')
.value:=vid;zquery3.FieldByName('value').AsString:=EditAnotherValue.Te
xt;zquery3.Post;zconnection1.Commit;exceptzconnection1.Rollback;end;en
d;
Michal

----------------------------------------------------------------------
mangakissa · 2016-05-10 06:49

It's an option, but very expensive.

----------------------------------------------------------------------
douglas.cast · 2016-05-11 16:18

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
tbl.SQL.Text:='INSERT INTO tbl (fields) VALUES
('+QuotedStr(pascalStrField)+' , '+(pascalIntegerField+1)+' ,
'+pascalDateField+' ) ';

----------------------------------------------------------------------
mangakissa · 2016-05-12 08:06

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
tbl.SQL.text := 'INSERT INTO tbl (field) VALUES (:item1)': 
tbl.parambyname.asstring := combobox1.text; Is there a reason for not
using DBComponents?

----------------------------------------------------------------------
douglas.cast · 2016-05-16 01:27

Quote from: mangakissa on May 12, 2016, 10:06:13 amWorks with
parameters. Look at my example. A combobox is nothing more then a
TEdit with an popup function with extra items.
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  
tbl.SQL.text := 'INSERT INTO tbl (field) VALUES (:item1)': 
tbl.parambyname.asstring := combobox1.text; 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.