Re: [lazarus] SQLdb : get last_inserted_id
Sorry if I bother you... but I still have to understand: - if the component don't allows to do that kind of query (to get a last_insert_id) - if i'm doing something wrong - if there is a bug somewere Also, now I have a new problem... A big query I do (the smaller ones works...) works only the first time I do it. The second time, the program goes in segmentation fault if the query.ParseSQL is false. If ParseSQL is true, i get a sql error, but copying and pasting the same query into a mysql client the query works perfectly (it's the same I did the first time and worked). What can I do? _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
John ha scritto: Alvise Nicoletti wrote: Hi... this is the complete code I'm using to extract the last_insert_id from the table TABLE1, but it still don't works. Infact I get always 0 from the variable id_ingresso, pheraphs in the database the row is correctly inserted. Hi Alvise, I haven't gone through you example completely, but try doing the same thing by generating the sql text on the fly instead of using parameters. I was unable to get anything to work using parameters when I last tried a few months ago. cheers, John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives I tryed but that wasn't the problem... With delphi+zeoslib the (nearly) same code works, so I suppose I found some kind of bug... How can I debug this? I tryed, as you see in the code, to handle the transaction like if it's only one block of operations... ... Maybe there is an implicit commit after the query execution that I don't know how to remove? _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
Op dinsdag 16-10-2007 om 10:45 uur [tijdzone +0200], schreef Alvise Nicoletti: John ha scritto: Alvise Nicoletti wrote: Hi... this is the complete code I'm using to extract the last_insert_id from the table TABLE1, but it still don't works. Infact I get always 0 from the variable id_ingresso, pheraphs in the database the row is correctly inserted. Hi Alvise, I haven't gone through you example completely, but try doing the same thing by generating the sql text on the fly instead of using parameters. I was unable to get anything to work using parameters when I last tried a few months ago. cheers, John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives I tryed but that wasn't the problem... With delphi+zeoslib the (nearly) same code works, so I suppose I found some kind of bug... How can I debug this? Compile fcl-db with debuginfo. If you use Lazarus, you can simply add fpcsrc/packages/fcl-db/sqldb and fpcsrc/packages/fcl-db/sqldb/mysql to your project-sources path. Or you can do a 'make clean all OPT='-gl' install' in fpcsrc/packages/fcl-db. But then you have to take care that the generated .ppu files are installed into the right paths. (On windows this is mostly not the case) I tryed, as you see in the code, to handle the transaction like if it's only one block of operations... ... Maybe there is an implicit commit after the query execution that I don't know how to remove? I haven't debugged your code yet, but sqldb doesn't support transactions at all for mysql (As mysql 5 doesn't do so either) So it doesn't do any commits. As you've set parsesql to false, it also doesn't do any extra queries, do I think it should work. For a real solution you should have take a look at http://www.freepascal.org/mantis/view.php?id=9758 Joost _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
Joost van der Sluis ha scritto: Op dinsdag 16-10-2007 om 10:45 uur [tijdzone +0200], schreef Alvise Nicoletti: John ha scritto: Alvise Nicoletti wrote: Hi... this is the complete code I'm using to extract the last_insert_id from the table TABLE1, but it still don't works. Infact I get always 0 from the variable id_ingresso, pheraphs in the database the row is correctly inserted. Hi Alvise, I haven't gone through you example completely, but try doing the same thing by generating the sql text on the fly instead of using parameters. I was unable to get anything to work using parameters when I last tried a few months ago. cheers, John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives I tryed but that wasn't the problem... With delphi+zeoslib the (nearly) same code works, so I suppose I found some kind of bug... How can I debug this? Compile fcl-db with debuginfo. If you use Lazarus, you can simply add fpcsrc/packages/fcl-db/sqldb and fpcsrc/packages/fcl-db/sqldb/mysql to your project-sources path. Or you can do a 'make clean all OPT='-gl' install' in fpcsrc/packages/fcl-db. But then you have to take care that the generated .ppu files are installed into the right paths. (On windows this is mostly not the case) Sorry but debug is a little hard for me... the program is a linux service and I never debugged it, when I have a problem I just put some text-file-logging around in the code. I tryed, as you see in the code, to handle the transaction like if it's only one block of operations... ... Maybe there is an implicit commit after the query execution that I don't know how to remove? I haven't debugged your code yet, but sqldb doesn't support transactions at all for mysql (As mysql 5 doesn't do so either) So it doesn't do any commits. As you've set parsesql to false, it also doesn't do any extra queries, do I think it should work. For a real solution you should have take a look at http://www.freepascal.org/mantis/view.php?id=9758 Ok, so the auto-commit can't be the problem cause I'm using mysql 4.0.20. I read the bugtracker at the link... Just for completion, I write you that the linux-service I'm doing have several threads inside it, however actually the class it's using it it's not used by a thread but by a class (a tcp/ip device that generates query events when send/receive something). Just to say... actually with your component I'm doing 7 queryes each one of 5-10 rows involving 5 to 8 tables in 37 milliseconds (amazing). And the mysql server is not localhost. So it seems to work VERY well; I just have a problem with that damn last_insert_id. If you have other ideas (after you look at the code) or if you need some more code, just write me. Thanks again, Alvise Joost _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
Hi... this is the complete code I'm using to extract the last_insert_id
from the table TABLE1, but it still don't works.
Infact I get always 0 from the variable id_ingresso, pheraphs in the
database the row is correctly inserted.
Maybe it's a bug of the component?
This is the structure of the table:
CREATE TABLE `TABLE1`
(
`PK_TABLE1` integer (11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`FIELD1`varchar (20),
`FIELD2`varchar (20),
`FIELD3`datetime NOT NULL,
PRIMARY KEY (`PK_TABLE1`)
) TYPE=MyISAM
This is the code:
procedure TPassaggioGestito.RegistraPassaggioSulDB(Connessione:
TSQLConnection);
var
Qry: TSQLQuery;
Trans: TSQLTransaction;
begin
//parametri db per query
Trans:=TSQLTransaction.Create(nil);
Qry:=TSQLQuery.Create(nil);
try
Connessione.Transaction := Trans;
Qry.DataBase := Connessione;
Qry.Transaction := Trans;
Connessione.StartTransaction;
Qry.ParseSQL := true;
Qry.ReadOnly := false;
Qry.Active := false;
Qry.SQL.Text := 'INSERT INTO TABLE1 '+
'(FIELD1, FIELD2, FIELD3) '+
'VALUES '+
'(:FIELD1, :FIELD2, :FIELD3) ';
Qry.Params.ParamByName('FIELD1').AsString := self.field1;
Qry.Params.ParamByName('FIELD2').AsString := self.field2;
Qry.Params.ParamByName('FIELD3').AsDateTime := self.field3;
try
Qry.ExecSQL;
except
Log.Scrivi(0,'Errore SQL in passaggigestiti: SQL =
'+qry.SQL.Text+'');
end;
//la query viene ora usata in lettura
Qry.Active := false;
qry.ParseSQL := false;
qry.ReadOnly := true;
//leggo il pk_ingresso appena inserito
Qry.SQL.Text := 'SELECT LAST_INSERT_ID() as PRIMARY_KEY';
try
Qry.open;
self.id_ingresso := Qry.FieldByName('PRIMARY_KEY').AsInteger;
Qry.close;
except
Log.Scrivi(0,'Errore SQL in passaggigestiti: SQL =
'+Qry.SQL.Text+'');
self.id_ingresso := -1;
end;
finally
Connessione.EndTransaction;
Qry.Free;
Trans.Free;
end;
end;
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
Alvise Nicoletti wrote: Hi... this is the complete code I'm using to extract the last_insert_id from the table TABLE1, but it still don't works. Infact I get always 0 from the variable id_ingresso, pheraphs in the database the row is correctly inserted. Hi Alvise, I haven't gone through you example completely, but try doing the same thing by generating the sql text on the fly instead of using parameters. I was unable to get anything to work using parameters when I last tried a few months ago. cheers, John _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
[lazarus] SQLdb : get last_inserted_id
Hi... I'm having some problems to get the last_insert_id after a insert
query...
I'm getting always 0, the problem (I suppose) is that I have to do
everything in the same transaction...
I paste you some code here to help me to understand the problem
(id_ingresso is the variable I get always = 0):
procedure TMyClass.InsertIntotheDB(Connection: TSQLConnection); // i
pass the connection from another class (to be sure it's open and alive)
var
Qry, QryRead: TSQLQuery;
Trans: TSQLTransaction;
begin
//parameters
Trans:=TSQLTransaction.Create(nil);
Qry:=TSQLQuery.Create(nil);
QryRead:=TSQLQuery.Create(nil);
try
Connessione.Transaction := Trans;
Qry.DataBase := Connessione;
Qry.Transaction := Trans;
QryRead.DataBase := Connessione;
QryRead.Transaction := Trans;
Qry.ParseSQL := true;
Qry.ReadOnly := false;
QryRead.ParseSQL := false;
QryRead.ReadOnly := true;
Qry.Active := false;
Qry.SQL.Text := 'INSERT INTO ECCETERA';
Qry.Params.ParamByName('PK_parameter').AsInteger := self.id_parameter;
try
Qry.ExecSQL;
except
Log.Scrivi(0,'Error'+QryRead.SQL.Text+'');
end;
QryRead.Active := false;
QryRead.SQL.Text := 'SELECT LAST_INSERT_ID() as PK_INGRESSO';
try
QryRead.open;
self.id_ingresso := QryRead.FieldByName('PK_parameter').AsInteger;
QryRead.close;
except
Log.Scrivi(0,'Error'+QryRead.SQL.Text+'');
self.id_ingresso := -1;
end;
Log.Scrivi(6, 'Logica Passaggio registrato sul DB. Pk_ingresso =
'+IntToStr(self.id_ingresso));
finally
Qry.Free;
QryRead.Free;
Trans.Free;
end;
end;
end.
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
Op vrijdag 12-10-2007 om 10:39 uur [tijdzone +0200], schreef Alvise
Nicoletti:
Qry.Active := false;
I don't see anything strange. It should work, except that the following
query is incorrect (incomplete).
Qry.SQL.Text := 'INSERT INTO ECCETERA';
And it also has no parameters. Maybe there's an error on your log?
Qry.Params.ParamByName('PK_parameter').AsInteger := self.id_parameter;
try
Qry.ExecSQL;
Joost.
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
The query was incomplete cause I censored it... just cause the mailing
list is public and sometimes googling I found pieces of our customers
stuff.
I mailed you in private the complete code.
Thank you.
Op vrijdag 12-10-2007 om 10:39 uur [tijdzone +0200], schreef Alvise
Nicoletti:
Qry.Active := false;
I don't see anything strange. It should work, except that the following
query is incorrect (incomplete).
Qry.SQL.Text := 'INSERT INTO ECCETERA';
And it also has no parameters. Maybe there's an error on your log?
Qry.Params.ParamByName('PK_parameter').AsInteger := self.id_parameter;
try
Qry.ExecSQL;
Joost.
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
No, in the original code it's correct.
However: I'm still having problems, someone can show me some code about
how to extract a last_insert_id from a mysql database after a insert,
with SQLdb libs?
On 12/10/2007, Alvise Nicoletti [EMAIL PROTECTED] wrote:
---8---
QryRead.Active := false;
QryRead.SQL.Text := 'SELECT LAST_INSERT_ID() as PK_INGRESSO';
try
QryRead.open;
self.id_ingresso := QryRead.FieldByName('PK_parameter').AsInteger;
must this not be:
self.id_ingresso := QryRead.FieldByName('PK_INGRESSO').AsInteger;?
henry
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] SQLdb : get last_inserted_id
On 12/10/2007, Alvise Nicoletti [EMAIL PROTECTED] wrote:
---8---
QryRead.Active := false;
QryRead.SQL.Text := 'SELECT LAST_INSERT_ID() as PK_INGRESSO';
try
QryRead.open;
self.id_ingresso := QryRead.FieldByName('PK_parameter').AsInteger;
must this not be:
self.id_ingresso := QryRead.FieldByName('PK_INGRESSO').AsInteger;?
henry
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
