Hi,
I just tested it out here as well:
mysql> CREATE TABLE `care_person` ( `pid` int(11) unsigned NOT NULL
AUTO_INCREMENT, `name` varchar(60) NOT NULL, PRIMARY KEY (`pid`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000,
'Donald');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 10000001 |
+------------------+
1 row in set (0.00 sec)
So we have a different behaviour under windows and linux. But I would no
like to have such autoincrement decrements (AI - 1) in the code
(readability, maintainability, reliability.. in case that this will be
fixed by mysql later it would be a real ugly workaround what might bring
working HIS outside in real trouble by updating mysql).
Before doing that, I would suggest to create an dummy record in
care_person. (if adodb does not support here any suggestion on that
issue)
Robert
Am Mittwoch, den 29.09.2010, 15:49 +0530 schrieb Ap.Muthu:
> Hi Robert,
>
> I have tested it to be a MySQL issue and not a ADOdb issue.
> On execution of the said statements, I get the Last Insert ID as 0.
> So our care2x code seems to be inserting this kind of insert sql.
> Possibly if we altered the AUTO_INCREMENT value on the table to be
> (10000000 - 1)
> and then performed a normal SQL insert without the
> AUTO_INCREMENT field "pid", we might be able avoid such issues.
>
> CREATE TABLE `care_person` (
> `pid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
> `name` VARCHAR(60) NOT NULL,
> PRIMARY KEY (`pid`)
> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
>
> The attached pre-test image is the situation just after table creation.
>
> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000, 'Robert');
>
> SELECT LAST_INSERT_ID();
>
>
> After execution of the said 3 sqls, the backup schema of the table is:
>
> CREATE TABLE `care_person` (
> `pid` int(11) unsigned NOT NULL auto_increment,
> `name` varchar(60) NOT NULL,
> PRIMARY KEY (`pid`)
> ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8;
>
> The attached mess image is taken after all the above SQLs have been
> executed.
>
> Regards,
> Ap.Muthu
>
> > Sorry, I do not get the point. When you do that then you will have the
> > query result of LAST_INSERT_ID() 10000001....
> >
> > There is on table definition an autoincrement and you give in your
> > insert statement an fixed value to it? The result is always strange
> > (guess it will be 10000001 instead of 10000000 - not tested so
> > far...why?)
> >
> >
> > Do do you want to make me busy? :-)
> >
> > Robert
> >
> >
> > Am Mittwoch, den 29.09.2010, 15:12 +0530 schrieb Ap.Muthu:
> >> Hi Robert,
> >>
> >> Try to make the table without any records and the default AUTO_INCREMENT
> >> to
> >> start with should be 1 (if the parameter AUTO_INCREMENT=1is omitted).
> >> Then make an insert with a pid = 10000000 in the insert statement and
> >> check
> >> the Last insert ID():
> >>
> >> CREATE TABLE `care_person` (
> >> `pid` int(11) unsigned NOT NULL AUTO_INCREMENT,
> >> `name` varchar(60) NOT NULL,
> >> PRIMARY KEY (`pid`)
> >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> >>
> >> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000, 'Robert');
> >>
> >> SELECT LAST_INSERT_ID();
> >>
> >> Regards,
> >> Ap.Muthu
> >>
> >>
> >> > ...I tested out with mysql client and standard call, and it worked
> >> > perfectly:
> >> >
> >> > mysql> create database dropme;
> >> > Query OK, 1 row affected (0.02 sec)
> >> > mysql> \u dropme
> >> > Database changed
> >> > mysql> CREATE TABLE `care_person` ( `pid` int(11) unsigned NOT NULL
> >> > AUTO_INCREMENT, `name` varchar(60) NOT NULL, PRIMARY KEY (`pid`) )
> >> > ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8;
> >> > Query OK, 0 rows affected (0.06 sec)
> >> > mysql> insert into care_person (name) VALUES ('Donald');
> >> > Query OK, 1 row affected (0.00 sec)
> >> >
> >> > mysql> SELECT LAST_INSERT_ID();
> >> > +------------------+
> >> > | LAST_INSERT_ID() |
> >> > +------------------+
> >> > | 10000001 |
> >> > +------------------+
> >> > 1 row in set (0.00 sec)
> >> >
> >> > That would mean as logic consequence: MySQL does not have that issue on
> >> > my ubuntu here. But the behaviour of adodb method Insert_ID() had
> >> > reproducibly that issue on the same ubuntu. Is there any reason not to
> >> > try with latest adbodb layer if that strange behaviour could be covered
> >> > with it?
> >> >
> >> > Robert
> >>
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Start uncovering the many advantages of virtual appliances
> >> and start using them to simplify application deployment and
> >> accelerate your shift to cloud computing.
> >> http://p.sf.net/sfu/novell-sfdev2dev
> >> _______________________________________________
> >> Care2002-developers mailing list
> >> Care2002-developers@lists.sourceforge.net
> >> https://lists.sourceforge.net/lists/listinfo/care2002-developers
> >
> > --
> > --
> > --------------------------------------------
> > CARE2X - free Integ Hospital Info System
> > https://sourceforge.net/projects/care2002/
> > http://www.care2x.org
> >
> >
> > ------------------------------------------------------------------------------
> > Start uncovering the many advantages of virtual appliances
> > and start using them to simplify application deployment and
> > accelerate your shift to cloud computing.
> > http://p.sf.net/sfu/novell-sfdev2dev
> > _______________________________________________
> > Care2002-developers mailing list
> > Care2002-developers@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/care2002-developers
> >
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________ Care2002-developers mailing
> list Care2002-developers@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/care2002-developers
--
--
--------------------------------------------
CARE2X - free Integ Hospital Info System
https://sourceforge.net/projects/care2002/
http://www.care2x.org
------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Care2002-developers mailing list
Care2002-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/care2002-developers
