SELECT LAST_INSERT_ID () returns 0 after using the prepared statement

Question

I use MySQL and a prepared statement to insert a record BLOB(jpeg image). After executing the prepared statement, I issue SELECT LAST_INSERT_ID()and it returns 0.

In my code, I set a breakpoint after the run command and in the MySQL command window (monitor), I issue SELECT LAST_INSERT_ID()and return 0.

In the MySQL command window (monitor), I issue an SQL statement to select all identifiers, and the last (only) inserted identifier is 1 (one).

I use:

• Server Version: 5.1.46-Community MySQL Community Server (GPL)
• Visual Studio 2008, version 9.
• MySQL Connector C ++ 1.0.5

Description of my table:

mysql> describe picture_image_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| ID_Image_Data | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Image_Data    | mediumblob       | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
2 rows in set (0.19 sec)

Results using the MySQL monitor:

mysql> select ID_Image_Data
    -> from picture_image_data;
+---------------+
| ID_Image_Data |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

Prepared statement:

INSERT INTO Picture_Image_Data
(ID_Image_Data, Image_Data)
VALUES
    (?,
       ?);

, ID_Image_Data , LAST_INSERT_ID . , .

---

:

ID , - ++ std::istream *. MySQL LAST_INSERT_ID():

LAST_INSERT_ID() , AUTO_INCREMENT "" ( , NULL, 0).

LAST_INSERT_ID() 1, 0.

---

LAST_INSERT_ID?

{ , API MySQL, PHP , ).

Answer 1

, MySQL ++.

• LAST_INSERT_ID() 0, null, .
• LAST_INSERT_ID() 0, ID , .

BLOB ( JPEG) (), :

mysql> describe picture_image_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| ID_Image_Data | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Image_Data    | mediumblob       | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
mysql> PREPARE blob_stmt
    ->     FROM 'INSERT INTO picture_image_data (ID_Image_Data, Image_Data) VALUES (?, LOAD_FILE(?))';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> SET @id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = 'KY_hot_brown_picture.jpg';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE blob_stmt USING @id, @c;
Query OK, 1 row affected (0.15 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

1 row in set (0.00 sec)

SQL BLOB , API- MySQL ++.

Answer 2

LAST_INSERT_ID , auto_increment. , , , .

:

mysql> insert into test (id, name) VALUES (5, 'test 2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

:

mysql> insert into test (name) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

Answer 3

SELECT LAST_INSERT_ID() mysql, , ++.

LAST_INSERT_ID() . , ++ mysql 2 mysql.

( LAST_INSERT_ID() INSERT , SQL INSERT SELECTLAST_INSERT_ID())