why sometimes does 'last insert id' return 0

[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

[EgonHugeist]

Which compiler, database and which protocol? An example to support you would be nice!
My crystal balls don't tell anything here..

[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.

[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

[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?

[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?

[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

[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;