Problems writing to MySQL DB With Zeos

[Ouiji]

For whatever reason, my mind is blank and I cannot for the life of me figure out how to write data into the DB, while getting it out took me 5 seconds.

This is the code I am using to pull the data out of the database:

  zrQuery.SQL.Text := 'SELECT * FROM Event_Errors';
  zrQuery.Open();
  zrQuery.First();
  while not zrQuery.Eof do
  begin

   CurRow := grid_main.AddRow;
   Form1.grid_main.CellByName['e_ID', 'Last'].AsString        := zrQuery.FieldByName('Event_ID').AsString;
   Form1.grid_main.CellByName['e_OutPut', 'Last'].AsString    := zrQuery.FieldByName('Event_Output').AsString;
   Form1.grid_main.CellByName['e_Summary', 'Last'].AsString   := zrQuery.FieldByName('Summary').AsString;
   Form1.grid_main.CellByName['e_Severity', 'Last'].AsInteger := zrQuery.FieldByName('Severity').AsInteger;
   Form1.grid_main.CellByName['e_relations', 'Last'].AsString := zrQuery.FieldByName('Relations').AsString;
   Form1.grid_main.CellByName['e_KeyWords', 'Last'].AsString  := zrQuery.FieldByName('Keywords').AsString;
   Form1.grid_main.CellByName['e_Notes', 'Last'].AsString     := zrQuery.FieldByName('Notes').AsString;
   Form1.grid_main.BestFitRow(CurRow);

   zrQuery.Next();
  end;
  zrQuery.Close();

  sMemo1.Lines.Add('Query Complete');

This pulls the data from the table and puts it into a grid without any problems, but I cannot figure out how I would go about doing the reverse and inserting data into the table. Anyone have any ideas?

(I put this under General and not VCL because I think this is more of a lack of brain power than a question regarding zeos)

Thanks,
- ouiji

[emailx45]

Dear friend

All ACTION in database SQL (Firebird, MySql, SQL Server, Oracle, etc...) working in level of TRANSACTION.

So, YOU have open one Transaction for Insert, Update and Delete any record in table.

Example inserting data:

...
ComponentTransaction.StartTransaction;
... actions for insert data
Table1.append;
Table1.Fields[0].asString := 'text free';
Table1.Post;
ComponentTransaction.COMMIT; // for save data in database

...

Go to www.devrace.com and see the FIBPlus components (its great and fast = my preference heheehe). See the FAQs for TRANSACTION USE, etc...

[GeoWink]

You can use the Query...

with YourQuery do
begin
Insert;
FieldByName('Field1').asString := String1;
FieldByName('Field2').asString := String1;
FieldByName('Field3').asInteger := Integer1;
Post;
end;

George

[Ouiji]

I have been able to make it function, but the code is about as ugly as it gets. (No reference to your posts, only to my lack of wrapping my head around how a DataSet and Query worked. But thank you very much for the input.

- ouiji

[jamiguel77]

Ouiji

contact me via skype i can help you (my skype id is: jamiguel77)

i have so so 10 years working with zeos, but why you dont use standard sql?

query1.sql.text:='insert into my table( ....)';


thanks

[Ouiji]

This is the code I came up with, it just feels dirty to me, and I need to use it too input large amounts of data, so it feels like it would be unreliable. but that could just be my lack of experience with Zeos.

var
Data:Array [1..7] of String;
mQRY:String;
begin

 sMemo1.Lines.Add('Attempting to Write Event to DB..');

 data[1] := edt_eID.Text;
 data[2] := edt_eOutp.Text;
 data[3] := edt_sum.Text;
 data[4] := edt_Sev.Text;
 data[5] := edt_rel.Text;
 data[6] := edt_kw.Text;
 data[7] := memo_Notes.Lines.Text;

 mQRY := ('INSERT INTO Event_Dev (Event_ID, Event_Output , Summary , Severity , Relations , Keywords , Notes)' +
                                                                                           ' VALUES(' + #39 + data[1] + #39 + #44 +
                                                                                                        #39 + data[2] + #39 + #44 +
                                                                                                        #39 + data[3] + #39 + #44 +
                                                                                                        #39 + data[4] + #39 + #44 +
                                                                                                        #39 + data[5] + #39 + #44 +
                                                                                                        #39 + data[6] + #39 + #44 +
(* End of Statment *)                                                                                   #39 + data[7] + #39 + ')' );

 if zCon.ExecuteDirect(mQRY) then
  sMemo1.Lines.Add('Query Success.')
   else sMemo1.Lines.Add('Query Failed.');

Does this seem like it could be workable? So far it has done the job, but it has not really been "stress tested" in a real life situation. I am anxious to hear your thoughts.

[jamiguel77]

if you work with zeos and insert data, the problem not is Zeos is the Query or delphi language.

for insert an amount of data here is the way:

http://www.electrictoolbox.com/mysql...tiple-records/

i use mysql.

or, you rename your fields same as:

field01, field02, field03.....

wsql1:='';
wsql2:='';
for i:=0 to 90 do (i suppose you have 90 fields
begin
wsql1:=wsql1+'field'+formatfloat('00',i)+',';
wsql2:=wsql2+arraydata[i]+',';
end;

query1.sql.text:='Insert into mytable ('+copy(wsql1,1,length(wsql1)-2)+') valeus ('+copy(wsql2,1,length(wsql2)-2)+')'; //for remove the last ,
query1.execute;



here too i paste some code i write on any project
(i have much data on a text file.

if openD.execute then
begin
Assignfile(f,opend.filename);
reset(f);
readln(f,l);
for i:=0 to wnofields do //extract the fieldnames (are the header of the text file)
begin
wa1[i]:=copy(l,1,pos(#9,l)-1);
delete(l,1,pos(#9,l));
end;
k:=1;
while not eof(f) do
begin
readln(f,l);
caption:=inttostr(k)+' '+l;
application.ProcessMessages;
for i:=0 to wnofields do
begin
wa2[i]:=copy(l,1,pos(#9,l)-1);
delete(l,1,pos(#9,l));
end;
zq.sql.text:='Select * from tbit where ibitidgan='+inttostr(integer(cg.Items.objects[cg.itemindex]))+' and ibitidentif='+wa2[2]+' and '+
'ibitidnomcapt='+inttostr(integer(cr.Items.objects[cr.itemindex]));
Zq.open;
if Zq.IsEmpty then
begin
zq.sql.text:='Insert into tbit (ibitidentif, ibittipo, ibitarever, ibitnocarp, ibitnofac, ibitnocenso, cbitraza, cbitclave, cbitnomtit, '+
'cbitdomtit, cbitrantit, ibitareazu, ibitresenado, dbitfecresena, dbitfechregmod, dbitfechreg, cbitcorral, '+
'cbitTIPO, fbitkgs, cbitstatus, cbitquienentrego, cbitnodict, dbitfeiny, ibitedad, ibitedad2, ibitsexo, cbitmun, '+
'ibitidentif2, ibitareteverde, cbitresultado, ibitidgan, ibitidnomcapt) values (';
wx:='';
for i:=2 to wnofields do
begin
wx:=wx+''+inttostr(i)+', ';
end;
wx:=copy(wx,1,length(wx)-2);
zq.sql.text:=zq.sql.text+wx+', idbitgan, idbitnomcap )';
for i:=2 to wnofields do
begin
if i=30 then
caption:=caption;
case wa1[i][1] of
'i': begin
if not (wa2[i]='Null') then
zq.parambyname('p'+inttostr(i)).asinteger:=strtoin t(wa2[i])
else
zq.parambyname('p'+inttostr(i)).Clear;
end;
'f': begin
if not (wa2[i]='Null') then
zq.parambyname('p'+inttostr(i)).asfloat:=strtofloa t(wa2[i])
else
zq.parambyname('p'+inttostr(i)).Clear;
end;
'c': begin
if not (wa2[i]='Null') then
zq.parambyname('p'+inttostr(i)).asstring:=wa2[i]
else
zq.parambyname('p'+inttostr(i)).Clear;
end;
'd': begin
{ showmessage('0 '+Zq.SQL.text);
clipboard.astext:=Zq.SQL.text;}
if not (wa2[i]='Null') then
begin
//14/04/2012 06:27:53 p.m.
if ShortDateFormat='M/d/yyyy' then
zq.parambyname('p'+inttostr(i)).asdatetime:=strtod atetime(copy(wa2[i],4,2)+'/'+copy(wa2[i],1,2)+'/'+copy(wa2[i],7,4)+' '+copy(wa2[i],12,13))
else
zq.parambyname('p'+inttostr(i)).asdatetime:=strtod atetime(wa2[i])
end
else
zq.parambyname('p'+inttostr(i)).Clear;
end;
else
caption:=caption;
end;
end;
// showmessage('1 '+Zq.SQL.text);
zq.parambyname('pidbitgan').asinteger:=integer(cg. Items.objects[cg.itemindex]);
zq.parambyname('pidbitnomcap').asinteger:=integer( cr.Items.objects[CnombreResena.itemindex]);
// showmessage('2 '+Zq.SQL.text);
clipboard.astext:=Zq.SQL.text;
Zq.ExecSQL;
inc(k);
end
else
begin
{ messagedlg('El arete de Siniga: '+wa2[2]+' ya esta capturado!!! con fecha: '+formatdatetime('dd/mm/yyyy',
Zq.fieldbyname('dbitfecresena').asdatetime),mterro r, [mbok],0);}
Mem.Lines.add('El arete de Siniga: '+wa2[2]+' ya esta capturado!!! con fecha: '+formatdatetime('dd/mm/yyyy',
Zq.fieldbyname('dbitfecresena').asdatetime));
end;
end;
closefile(f);


is a pleasure help try help.


thanks

[emailx45]

Try, always, show the "new sql" query in one MEMO, SHOWMESSAGE, etc.. for verify the sintax correct, ok?