How to Discover Next AutoIncrement Number

[Marcos-N41]

Hi, Sorry my english first of all, i'm brazilian and my english is very poor lol

I Need discover what is the value of Auto Increment, i use this code in PHP:

$sqlid = "SHOW TABLE STATUS LIKE \"pira_imob\"";
$exeid = mysql_query($sqlid,$base)or die (mysql_error());
$arrid = mysql_fetch_array($exeid);
$idimo= $arrid['Auto_increment'];

I Try make something like this:

SQLstring.Add('SHOW TABLE STATUS LIKE "vendas"');
frmprincipal.Zcomprabusca.SQL.Clear;
frmprincipal.Zcomprabusca.SQL:= SQLstring;
frmprincipal.Zcomprabusca.ExecSQL;
IDa:= frmprincipal.Zcomprabusca.DbcResultSet.GetStringByName('Auto_increment');

But don't Work, show-me this exception appointing at line IDa:= :

Acess Violation at adress 0058350E in module 'soft.exe' Read of adress 0000000

Please Help-me, i need this so much!

Thanks

[mdaems]

Depends on what you need it for...
- Just to fill the number when inserting rows using a ZQuery component by using ZQuery.Append : You should not need it. Just insert all other fields. After ApplyUpdates the ID is filled.
- To know what row has last been inserted IN THE SAME CONNECTION : select last_insert_id();
- To know what the NEXT id will be? Forget it. If you need that use something you build yourself to build the autoincrement. You could go for 'select max(id) + 1 from table', but that's not safe when multiple connections can be made to the same database.

Some remark on your code :
- If you want a resultset you should use ZQuery.Open instead of ExecSQL. (that's more useful for Update statements)
- To get the result data (in case of Open) : ZQuery1.FieldByName('NAME').AsString

Mark

[Marcos-N41]

How should i use the last_insert_id(), how do i put the result in a variable?

i tryed this code: ZQuery1.FieldByName('NAME').AsString but a error message appears saying that field doesn't exists

CODE:

frmprincipal.ZID.SQL.add('SELECT last_insert_id() as IDfinal');
frmprincipal.ZID.Open;
frmprincipal.ZID.ExecSQL;
IDint:= frmprincipal.ZID.fieldbyname('IDfinal').AsInteger;

Thanks

[mdaems]

First, read http://dev.mysql.com/doc/refman/5.0/en/ ... -insert-id

Then (not tested, however!):

Code: Select all

 ZQuery1.SQL.Text := 'Select last_insert_id() as lastinsertid;';
 ZQuery1.Open;
 ShowMessage(IntToStr(ZQuery1.GetFieldByName('lastinsertid').asInteger));
 ZQuery1.Close;

Mark

[Marcos-N41]

i tryed this code:
ZQuery1.FieldByName('NAME').AsString but a error message appears saying that field doesn't exists

CODE:

frmprincipal.ZID.SQL.add('SELECT last_insert_id() as IDfinal');
frmprincipal.ZID.Open;
frmprincipal.ZID.ExecSQL;
IDint:= frmprincipal.ZID.fieldbyname('IDfinal').AsInteger;

Thanks

[mdaems]

Don't use frmprincipal.ZID.ExecSQL;
ExecSQL is used for executing statements where you don't need a resultset.

What if you remove the line?

Mark

[mdaems]

Don't forget the function only works AFTER a line is inserted!

Mark

[Marcos-N41]

Thanks for help!! Finally it's working!!

ps: I removed the line ExecSQL and worked, i had not removed it before because i hadn't understand your first answer...