Page 1 of 1
why sometimes does 'last insert id' return 0
Posted: 23.01.2016, 20:31
by top1
As the above question
Why sometimes does last insert id retuning 0
After i call another procedure to insert , its works again after i fire the another insert by another procedure
I'm creating tzquery object every time when procedure runs with the same connection
Re: why sometimes does 'last insert id' return 0
Posted: 24.01.2016, 09:01
by EgonHugeist
Which compiler, database and which protocol? An example to support you would be nice!
My crystal balls don't tell anything here..
Re: why sometimes does 'last insert id' return 0
Posted: 24.01.2016, 19:44
by top1
I'm using Delphi7 as compiler with MariaDB-10 protocal and MariaDB-10 in xampp as sql server , here below is my sql text
Code: Select all
SELF_QUERY.Close;
SELF_QUERY.SQL.Clear;
SELF_QUERY.SQL.Add('insert into xxx (uid, itemid, C0, tempo, DATE_RECEIVE, DATE_END) values (' + QuotedStr(inttostr(Client[i].uid)) + ',' + QuotedStr(inttostr(ITEM_TYPEID)) + ', ' + QuotedStr(inttostr(QTY)) + ' , ' + QuotedStr(IntToStr(Temp)) + ' , ' + QuotedStr(FormatDateTime('yyyy-mm-dd tt',Now())) +' , ' + QuotedStr(FormatDateTime('yyyy-mm-dd tt',DateEnd)) +') ');
SELF_QUERY.ExecSQL;
Code: Select all
// get last insert id
SELF_QUERY.SQL.Clear;
SELF_QUERY.SQL.Text := 'SELECT LAST_INSERT_ID()';
SELF_QUERY.Open;
ITEM_IDI := SELF_QUERY.FieldByName('LAST_INSERT_ID()').AsInteger;
I always use the new TZQuery with the same connections
I got my data inserted but sometimes the LAST_INSERT_ID is zero for awhile.
Re: why sometimes does 'last insert id' return 0
Posted: 08.02.2016, 02:34
by top1
I faced this problem again and again
i have to truncate table every time for get last last_insert_id works again
Edited...
when AutoIncreasement reached to 59 it will be return 0 at 60
and come back work again at 100
and will be 0 again at 600
every time i try it will be like this
pictures are below
1.jpg
2.jpg
Re: why sometimes does 'last insert id' return 0
Posted: 08.02.2016, 17:48
by EgonHugeist
Don't know why your making it so complicated.
If you're using the TZQuery for the fetches and you insert a row ... zeos automalicaly returns the last inserted id into the field.
Is there a spezial reason whay you want to do everthing manually?
In addition stop working with pascal quoted strings, this is not supported by MySQL this way. Use Parameters instead. And zeos is doing the escape stuff for your. Just look at:
https://xkcd.com/327/ funny isn't it?
Re: why sometimes does 'last insert id' return 0
Posted: 08.02.2016, 18:02
by EgonHugeist
I DON'T KNOW what you're doing, dude.
This way nobody can help you. Note Last_Inserted_id depends to last update smt. Are you using a pure sequential code or do you play with threads oslt
Did you read my last post? Why did you drop your last post?
Re: why sometimes does 'last insert id' return 0
Posted: 08.02.2016, 18:50
by top1
Do you have a example page please ?
I'm quite confused about the component i must use.
ps. i'm out of thread for awhile call direct from socket.read
Re: why sometimes does 'last insert id' return 0
Posted: 09.02.2016, 18:27
by top1
finally after 2 day , I nailed it by below code
Code: Select all
with zqry1 do begin
sql.Clear;
SQL.Add('select idi,uid,itemid from py_itens where 1=1');
Open;
Insert;
fieldbyname('uid').AsInteger := 1;
fieldbyname('itemid').AsInteger := 188905095;
Post;
chklst1.Items.Add( IntToStr( fieldbyname('idi').AsInteger )) ;
end;