List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 2 1999 9:52pm
Subject:Re: mysql_insert_id()
View as plain text  
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/
Thread
mysql_insert_id()Matthias Pigulla1 Jun
  • Re: mysql_insert_id()Paul DuBois1 Jun
    • Re: mysql_insert_id()Matthias Pigulla3 Jun
      • Re: mysql_insert_id()Paul DuBois3 Jun
    • Re: mysql_insert_id()Jim Faucette3 Jun
    • Re: mysql_insert_id()Matthias Pigulla3 Jun
      • Re: mysql_insert_id()Benjamin Pflugmann3 Jun
        • Re: mysql_insert_id()Michael Widenius3 Jun
          • Re: mysql_insert_id()Paul DuBois3 Jun
    • Re: mysql_insert_id()Matthias Pigulla3 Jun
    • Re: mysql_insert_id()Matthias Pigulla3 Jun
      • Re: mysql_insert_id()Michael Widenius3 Jun