Trouble inserting multiple lines with transactions and master/detail
and mysql
lazarus / 16-Databases

----------------------------------------------------------------------
calebs · 2010-01-26 21:53

Hello all, i have tried so many times this and so many different ways
but i can't make it work anyway.
I need to insert records in a master/detail form, first in the master
table something, then use on detail table other lines using the
last_insert_id() mysql function.

I have lazarus 0.9.28.2 beta x86 with fpc that has bundled on win 7
x86,
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using
readline 5.2

Here is one version of the code:
cad is a string, idusuario an integer, label20 has an integer,
listview1 items are right
bquery is tsqlquery, transac is tsqltransaction

    cad:=cad+devolverfecha(now)+'", '+inttostr(idusuario)+' ,
'+label20.caption+', 0)';
    conexbase.bquery.InsertSQL.Add(cad);
      for nro:=0 to listview1.Items.count-1 do begin
        cad:='insert into pedidoslinea (idpedido, idarticulo,
idartprov, cant) values (';
        cad:=cad+'LAST_INSERT_ID() ,
'+listview1.Items[nro].SubItems[6]+',
'''+listview1.Items[nro].SubItems[0];
        cad:=cad+''', '+listview1.Items[nro].SubItems[2]+')';
        conexbase.bquery.InsertSQL.Add(cad);
      end;
      try
        conexbase.transac.StartTransaction;
        conexbase.bquery.Open;
        conexbase.bquery.Insert;
        conexbase.bquery.Post;
        conexbase.bquery.ApplyUpdates;
        conexbase.transac.Commit;
      except on e:exception do begin
        conexbase.transac.Rollback;
        ShowMessage('Error al ejecutar: consulta');
      end;
      end;
      conexbase.bquery.Close;

Always gave me error:
project compras.exe raised exception class 'EDatabaseError' with
message: basededatos: Error executing query; you have an error in your
sql syntax; check the manual that corresponds to your mysql server
version for the right syntax to use near 'insert into pedidos linea
(id ###(gdb unparsed remainder:...)###

I've tried also with bquery.sql.add and then bquery.execsql but
doesn't worked, if anyone asks i can paste that code here too

Thanks

----------------------------------------------------------------------
calebs · 2010-01-26 22:33

Tried this code and doesn't work either 

    cad:='insert into pedidos (fecha, idusuario, idproveedor, pasado)
values ("';
    cad:=cad+devolverfecha(now)+'", '+inttostr(idusuario)+' ,
'+label20.caption+', 0)';
    conexbase.bquery.SQL.Add(cad);
      for nro:=0 to listview1.Items.count-1 do begin
        cad:='insert into pedidoslinea (idpedido, idarticulo,
idartprov, cant) values (';
        cad:=cad+'LAST_INSERT_ID() ,
'+listview1.Items[nro].SubItems[6]+',
"'+listview1.Items[nro].SubItems[0];
        cad:=cad+'", '+listview1.Items[nro].SubItems[2]+')';
        conexbase.bquery.SQL.Add(cad);
      end;
      cad:='';
      for nro:=0 to conexbase.bquery.SQL.Count-1 do
        cad:=cad+conexbase.bquery.SQL.Strings[nro]+chr(13);
      showmessage(cad);
      try
        conexbase.transac.StartTransaction;
        conexbase.bquery.ExecSQL;
        conexbase.transac.Commit;
      except on e:exception do begin
        conexbase.transac.Rollback;
        ShowMessage('Error al ejecutar: consulta');
      end;
      end;
      conexbase.bquery.Close;

----------------------------------------------------------------------
calebs · 2010-01-26 22:56

The code generated for insert is fine, tried in phpmyadmin in a sql
window and doesn't give me any errors... doesn't work too well the
LAST_INSERT_ID() though, it inserted fine the 2 first details but the
last one doesnt... but that's another problem

START TRANSACTION;
insert into pedidos (fecha, idusuario, idproveedor, pasado) values
("2010/01/26", 1 , 1, 0);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 3, "GOAR03", 11);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 2, "222", 22);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 1, "333", 33);
COMMIT;

so it's not an SQL error, it's something in lazarus / fpc...

Any ideas?

----------------------------------------------------------------------
José Mejuto · 2010-01-27 23:03

Quote from: calebs on January 26, 2010, 11:33:35 pmTried this code and
doesn't work either 
[...]

Hello,

1) When reporting SQL problems post the SQL command as it would be
sent to the engine, this means before the Open or ExceSQL send the
Query.SQL.Text to a memo or something that allow you to copy to
clipboard and paste it here. It is clear what you try to do, but
unclear on how you are doing it.

2) Avoid as much as possible the manual insertion of fields in the SQL
string, this could be a serious security problem in your application.
Use parameters instead. Like:

SQL.Text:='Select * from Table where FieldID=:MatchID';
Params.ParamByName('MatchID').AsInteger:=10;

3) As far as I know Query can only process one sql sentence at a time,
so your code is a SQLScript but as I can't see the bQuery definition I
can not confirm.

----------------------------------------------------------------------
calebs · 2010-01-28 13:55

Hello JoshyFun.
The code you see over your post is sql code that is writed on
bquery.sql (except the start transaction and commit,  i've tried to
use transactions using the transaction component).
I'll try to clear some more.
I've droppedt 3 components in a form:    
    basededatos: TMySQL50Connection;
    transac: TSQLTransaction;
    bquery: TSQLQuery;                               
I have 2 tables: The tables will hold
Pedidos (idpedido is the primary key)
    name     type     Null     Pred              Extra  
   idpedido    int(11)    No                       auto_increment
        fecha    varchar(10) No
        idusuario    int(11)    No
        idproveedor int(11)    No 
        pasado    smallint(6)    No    0 
        obs            varchar(200) Sí    NULL
Pedidoslinea (idlinea is PK and idpedido is FK referencing idpedido
from pedidos)
        Name     Type      Null     Pred              Extra
   idlinea    int(11)     No                       auto_increment
        idpedido    int(11)     No
   idarticulo    int(11)     No
   idartprov    varchar(20) No    0
   cant            int(11)     No
   obs            varchar(200)Sí    NULL 
The code is stored in the query this way:
conexbase.bquery.SQL.Add(cad)
and the code inserted is:
insert into pedidos (fecha, idusuario, idproveedor, pasado) values
("2010/01/26", 1 , 1, 0);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 3, "GOAR03", 11);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 2, "222", 22);
insert into pedidoslinea (idpedido, idarticulo, idartprov, cant)
values (LAST_INSERT_ID() , 1, "333", 33);

as you see i've tried to use exeqsql and the insert method but none of
it worked.
I want to use transactions to mantain integrity, do you know any other
way to insert rows in a master/detail tables using transaction?
Any example would be useful, can't find anything anywhere.
Thanks

----------------------------------------------------------------------
Wodzu · 2010-01-28 14:51

Hi celebs.

The problem is that you are not giving anything that can be easly
readable :|
Try to format your code next time.

If engine says that the query is bad, than most probably the query is
bad
As JoshyFun mentioned, you should check the produced result.
On what basis you are saying that the insert query is fine?
Check it on the server side(!) What I mean, is that you should run
some profiler on the server while executing your query. You will pick
up the executed query and analyze it.
Now you are not doing it, am I right? Unfortunately I do not remember
how it was done in MySQL since last time I've used it two years ago
but I am pretty sure that this is possible and I've done that that
time.

----------------------------------------------------------------------
calebs · 2010-01-28 15:05

Hi wodzu.
  I've pasted in the firsts posts the code, tell me what is that you
don't understand of it... basically the code only makes sql orders
like:
insert into pedidos (fecha, idusuario, idproveedor, pasado) values
("2010/01/26", 1 , 1, 0);

And yes, like i've said before, tried the querys on a sql window in
phpmyadmin just like the program sends them and they worked, so i
asume i'm using wrong the components.
Do you have a piece of the code that you've used to insert data in
master/detail in mysql?
Thanks

----------------------------------------------------------------------
calebs · 2010-01-28 23:27

I've solved it!
Here is the code, that worked, maybe some other could use it

    conexbase.bquery.SQL.Clear;
    conexbase.bquery.SQL.Text:='SET autocommit=0;';
    conexbase.bquery.ExecSQL;
    conexbase.bquery.SQL.Text:='START TRANSACTION;';
    conexbase.bquery.ExecSQL;
    cad:='insert into pedidos (fecha, idusuario, idproveedor, pasado)
values ("';
    cad:=cad+devolverfecha(now)+'", '+inttostr(idusuario)+' ,
'+label20.caption+', 0);';
    conexbase.bquery.SQL.Text:=cad;
    conexbase.bquery.ExecSQL;
    conexbase.bquery.SQL.Text:='select LAST_INSERT_ID() as ultimo';
    conexbase.bquery.Open;
    cad2:=conexbase.bquery.FieldByName('ultimo').AsString;
    conexbase.bquery.Close;
      for nro:=0 to listview1.Items.count-1 do begin
        cad:='insert into pedidoslinea (idpedido, idarticulo,
idartprov, cant) values (';
        cad:=cad+cad2+' , '+listview1.Items[nro].SubItems[6]+',
"'+listview1.Items[nro].SubItems[0];
        cad:=cad+'", '+listview1.Items[nro].SubItems[2]+');';
        conexbase.bquery.SQL.Text:=cad;
        conexbase.bquery.ExecSQL;
      end;
      conexbase.bquery.SQL.Text:='COMMIT;';
      conexbase.bquery.ExecSQL;
      conexbase.bquery.SQL.Text:='SET autocommit=1;';
      conexbase.bquery.ExecSQL;
      conexbase.bquery.Close;

----------------------------------------------------------------------
Wodzu · 2010-01-29 09:22

Hi celebs.

I understand the code, but I think we didn't understand each other.
What you did (when you were saying about using PHP My Admin) was a
running a query prepared by you in that SQL window.
You prepared the query and you run it but this is different from what
you should do in such case.
Maybe I was not clear enough, sorry for that.

When the query was run by your application it was passed to the SQL
engine and it has been saved in some sort of a log. (In this case,
query was prepared by your application.) I proposed you to browse that
log, find the query and then re-run.
You see the difference?

To answer your question about having the code. I don't have it right
now but there is an easier way of achiving a master detail relation.

Have you ever tried to link two TSQLQuery objects togheter?

Basicaly you need to have 2 TSQLQuery and 1 TDatasource, you connect
queries trough the datasource. You must use parameters (but you should
use it anyway). In detail query you create a parameter which is a
master query primary key.
When you connect queries, everything works automaticaly.

I can make some example project for you but I don't have MySQL now.

----------------------------------------------------------------------
huwmungous · 2010-02-08 15:40

Hi Calebs.

One of the replies to your posts advises you to use parameters in your
queries.  That is good advice for several reasons, one of which might
have been the underlying cause of your original problem - namely, that
if you use parameters, then the whole issue of quoting (") your values
becomes simpler.

There is another good reason to do with performance of your database
server.  Generally, a parameterised query can be prepared once on the
server and then re-used.  The way you do it the server will have to
prepare each query separately (because they are all different)

There is also a nasty security issue which is referred to in this
thread.  I dont believe it affects your example, as it happens, but
you might want to Google 'SQL Insertion' just to be aware.

Hope that is helpful.