mysql_insert_id() issue with MySQL 5.0.67 + Windows + VC++ 2008

Question

Hello!

On the manual page; http://dev.mysql.com/doc/refman/5.0/en/mysql-insert-id.html It is said that "Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement."

The problem is that it doesn't really work for me after an update.

The real need is that I'm trying to update a row in a table (updated with a "WHERE" conditions) and then, if a row indeed was updated, get it's full details. So first I UPDATE, then I need it's id to get it's details with a SELECT statement.

Here's an example code which shows the problem:

#define _WIN32_WINNT 0x0400
#define WIN32_LEAN_AND_MEAN
#include <windows.h>

#include <stdio.h>
#include <conio.h>

#include <winsock.h>

#include "C:/Program Files (x86)/MySQL/MySQL Server 5.0/include/mysql.h"
#pragma comment(lib, "C:/Program Files (x86)/MySQL/MySQL Server 5.0/lib/opt/libmysql.lib")

void main()
{
 MYSQL *conn = mysql_init(NULL);
 if(conn == NULL)
  throw "error";
 if(mysql_real_connect(
  conn,
  "127.0.0.1",
  "user", "passwd",
  NULL,
  0,
  NULL,
  0) != conn)
  throw "error";

 int nRet = 0;

 char szInsert[] = "INSERT INTO db_transation_test.tbl_transactions SET amount=71, ses=72";
 nRet = mysql_real_query(conn, szInsert, sizeof(szInsert));
 if(nRet != 0)
 {
  printf("%s\n", mysql_error(conn));
  throw;
 }
 my_ulonglong inserted_id = mysql_insert_id(conn);

 ///////// the real issue of my post starts here /////////

 char szUpdateTemplate[] = "UPDATE db_transation_test.tbl_transactions SET amount=123 WHERE id_transaction=%lld";
 char szUpdate[1024];
 nRet = sprintf_s(szUpdate, sizeof(szUpdate), szUpdateTemplate, inserted_id);
 nRet = mysql_real_query(conn, szUpdate, nRet);
 if(nRet != 0)
 {
  printf("%s\n", mysql_error(conn));
  throw;
 }
 my_ulonglong updated_id = mysql_insert_id(conn); // returns ZERO instead of 'inserted_id' :(

 _getch();
}

How do I make this mysql_insert_id() function work in that case, or alternatively how do I UPDATE and SELECT in one statament?

Note that I've INSERTed a row in this example so it'll make some row for you, so you can see..

Thank you!

EDIT: Check out my latest reply to this question. I gave a much more clear description.

Answer 1

Poni i think this is your function:

http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html

Answer 2

mysql_insert_id will only return an id on an update statement if the update statement modifies the auto incrementable value. Presumably, you have the id if you're already doing an update based on it, so just do the select based on id after you've updated.

Edit: I'm not suggesting that you modify the id column on update, I'm just saying that's the only case where mysql_insert_id will return a value for an update query.

Response to comment: then what you should be doing is the select first to get the id of the row by login/password, then doing your update by the id of the row from the result of the select

Answer 3

Your UPDATE statement does not cause the generation of an autoincremented id, so calling mysql_insert_id after you update makes no sense.

As the docs say, mysql_insert_id() works on inserts statements that cause an auto incremented value to be generated, or an update/insert statement that calls `LAST_INSERT_ID(expr)^ directly.

You've already fetched the inserted_id right after your insert statement, you don't need to fetch it again.

Answer 4

Your UPDATE statement does not cause the generation of an autoincremented id, so calling mysql_insert_id after you update makes no sense.

As the docs say, mysql_insert_id() works on inserts statements that cause an auto incremented value to be generated, or an update/insert statement that calls `LAST_INSERT_ID(expr)^ directly.

You've already fetched the inserted_id right after your insert statement, you don't need to fetch it again.

Edit, You are apparently asking about something else.

You have something like UPDATE db_transation_test.tbl_transactions SET amount=123 WHERE username=someone;

and you want to fetch the primary key of whatever row that that query updated ? Is that your actual question ?

If so. No. Run a separate select query to fetch that ID.

Answer 5

To clarify, forget about the code I've originally posted - here's what I need in simpler way:

First, I UPDATE: UPDATE db_test.tbl_test SET owner_id=55 WHERE login='user_name' AND passwd='password' AND owner_id=-1

Then I check if any row was updated, doing so with mysql_affected_rows(). If it's one (can't be more because field "login" is unique) then I want to get this row's id, instead of just doing another SELECT.

What's the logic behind this? It's going to be a multi-player database store, and there are several servers. I need to enforce somehow that a player will be able to login only once at a time. This is done by locking it's account; "owner_id" of "-1" indicates a free-to-play account, while if it's not then that player is already playing.

Hope it's a bit more clear now.