actually it's a bit more simple
the first time it executes the query the AUTOINCREMENT value is not set,
so it gets 0. the second time it works correctly.
now we have two solutions :
- in the sql creation script of the person table add an
AUTOINCREMENT=1000000
- or create a configuration value for the pid so the user can configure
it to suite
their needs.
gj.
On 2010-09-29 12:57.MD, Robert Meggle wrote:
> veto! :-)
>
> Either we have a reliable PID or not. When we register a patient it
> should be give a uniqie PID, handled by sessions and db in the same way.
> This is the once and only identifier for an patient record and we should
> not make workarounds in it. All other patient demographic information
> handled different on each requirement. (complex topic, but all here who
> implemented an HIS in a real hospital know what I mean)
>
> Think also that in most cases there are existing old HIS are running and
> the data will be inserted by e.g. kettle-jobs and we are not starting to
> use it from scratch.
>
> I see just two options if we work on table layer (mysql_dump.sql):
> Either we store an dummy record in it that MySQL has a previous PK
> (maybe exactly that seems to me the issue in the different execution
> plan of several MySQL-Servers) - or remove the Autoincrement of this
> table and the scripts would take care of its unique character (worse and
> dirty solution).
>
> Alternatively to look if latest adodb has covered that issue. This is
> not a new issue on the bug reports of mysql what I can see and that is
> the reason to use adodb.
>
> Robert
>
>
> Am Mittwoch, den 29.09.2010, 16:13 +0530 schrieb Ap.Muthu:
>> If the following is executed before attempting to create a new first person
>> then the last insert id of adodb will work:
>> ALTER TABLE `care_person` AUTO_INCREMENT=9999999;
>>
>>
>>> 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
------------------------------------------------------------------------------
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
