At 4:12 PM -0500 6/2/99, Matthias Pigulla wrote:
>Paul DuBois wrote:
>> Yes, it should remain visible. The 0 return should happen only if you
>> haven't generated an AUTO_INCREMENT value during the course of the
>> connection yet.
>
>Well, please try running the following:
>
>-8<- C code -
>#include <stdio.h>
>#include "mysql.h"
>
>#define DBHOST "WHEREVER"
>#define DBUSER "WHATEVER"
>#define DBPASSWD "WHATEVER"
>#define DBDB "WHATEVER"
>
>int main(void)
>{
> MYSQL con;
>
> mysql_init(&con);
> mysql_real_connect(&con, DBHOST, DBUSER, DBPASSWD, DBDB, 0, NULL,
>0);
>
> mysql_query(&con, "insert into a (val) values('test')");
> printf("last insert id is %d\n", mysql_insert_id(&con));
> mysql_query(&con, "insert into b (id,val)
>values(last_insert_id(),'test')");
> printf("last insert id is %d\n", mysql_insert_id(&con));
>
> mysql_close(&con);
>
> return 0;
>}
>-8<- C code -
>
>The table definitions:
>-8<- SQL -
>CREATE TABLE a (
> id tinyint not null auto_increment,
> val varchar(10) not null,
> PRIMARY KEY(id)
>);
>CREATE TABLE b (
> id tinyint not null,
> val varchar(10) not null,
> PRIMARY KEY(id)
>);
>-8<- SQL -
>
>On my system (with local server, ie 3.22.22) this prints "last insert id
>is 1\nlast insert id is 0", but I expected it to print id 1 twice.
>
>Can anyone confirm my observations?
Yes, I can. Although initially on my system the program prints "0" twice.
That's because, to be safe, you should cast the value of mysql_insert_id()
to a safe value. It returns a my_ulonglong value, which doesn't print
correctly on some systems. I used:
> printf("last insert id is %lu\n", (unsigned long) mysql_insert_id(&con));
^^^ ^^^^^^^^^^^^^^^
Even with that correction, the second instance of mysql_insert_id()
seems to return 0. Note however, that the correct value *is* being
inserted into table b.
It seems that accessing last_insert_id() in a SQL statement somehow
messes up the C API mysql_insert_id() function. I don't see anything in the
manual that would lead me to expect this...
Executing the equivalent statements in SQL doesn't seem to have the same
problem:
mysql> insert into a (val) values('test');
Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> insert into b (id,val) values(last_insert_id(),'test');
Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
