Page 1 of 1
[solved] MySQL AUTO_INCREMENT
Posted: 04.06.2014, 10:03
by 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;
Re: MySQL AUTO_INCREMENT
Posted: 04.06.2014, 11:22
by 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
Re: MySQL AUTO_INCREMENT
Posted: 04.06.2014, 14:14
by 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?
Re: MySQL AUTO_INCREMENT
Posted: 04.06.2014, 14:44
by 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;
Re: MySQL AUTO_INCREMENT
Posted: 16.06.2014, 14:16
by cacofony
Can confirm this is fixed in latest SVN. Nice work EgonHugeist
Re: MySQL AUTO_INCREMENT
Posted: 27.06.2014, 22:14
by EgonHugeist
Thanx for your feedback