[solved] MySQL AUTO_INCREMENT

[cacofony]

Sorry if this has been asked but I search high and low and found nothing for ZeosLib 7.2

In past versions this would work for an AutoInc field, now it returns NULL and you need to call a Refresh to show the new ID's

TZQuery.Insert;
// set your fields
TZQuery.Post;
ID := TZQuery.FieldByName('myid').AsInteger;

[miab3]

For me, it works for MySQL 5.5.35:

Code: Select all

select * from tautoinc

procedure TForm1.Button8Click(Sender: TObject);
begin
ZQuery2.Insert;
ZQuery2.FieldByName('name1').AsString:='abc'+IntToStr(random(10000));
ZQuery2.Post;
Edit2.Text := IntToStr(ZQuery2.FieldByName('id1').AsInteger);
end;

CREATE TABLE tautoinc (
  id1   Integer(11) NOT NULL AUTO_INCREMENT,
  name1 NVarChar(20) COLLATE utf8_general_ci, 
  PRIMARY KEY (
      id1
  )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE UNIQUE INDEX UK_master_autoinc 
 ON tautoinc(name1);

and ZEOS 7.2 (r3130):
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/

Michal

[cacofony]

Ok, well I probably should of done a simple test like you did.

It now works but I still think an issue exists.

If you change your ID to UNSIGNED

ALTER TABLE `tautoinc`
CHANGE COLUMN `id1` `id1` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

It will no longer work.

I actually did not use UNSIGNED on purpose just many old tables designed in the old MySQL administrator where created like this. However I looked to see if it was a good or bad thing to do, below from mySQL website.

Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.

I will see if I can find a fix but some pointers would be great as I have really delved in to the depths of ZeosDBO.

I also can provide XE6 update s for 7.2 (3130) if needed?

[cacofony]

stLongWord can be autoinc and will be if UNSIGNED integer. (MySQL 5.6) Maybe it is different in other versions.

src\dbc\ZDbcMySqlResultSet.pas

Code: Select all

constructor TZMySQLCachedResolver.Create(PlainDriver: IZMySQLPlainDriver;
  Handle: PZMySQLConnect; Statement: IZMysqlStatement; Metadata: IZResultSetMetadata);

....

    if Metadata.IsAutoIncrement(I) and
      (Metadata.GetColumnType(I) in [stByte, stSmall, stInteger, stLong, stLongWord]) then
    begin
      FAutoColumnIndex := I;
      Break;
    end;

[cacofony]

Can confirm this is fixed in latest SVN. Nice work EgonHugeist

[EgonHugeist]

Thanx for your feedback