CODEWITHSUNDEEP
c++mysql
Mendes
Mendes

Reputation: 18521

C++ MySql get last inserted id

I´m having the following code that insert a row into mysql database and return the inserted id:

    /*
     * Execute the statement
     */
    std::string sql = "INSERT INTO TABLE (A, B, C) VALUES (1, 2, 3)";

    sql::Statement *stmt;
    stmt = connection->createStatement();
    stmt->execute(sql);

    /*
     * Get the returned id
     */
    stmt = connection->createStatement();
    sql::ResultSet *res = stmt->executeQuery("SELECT @@identity AS id");
    res->next();
    model.modelId = res->getInt64("id");

My questions are:

Do I really need to call connection->createStatement() again ? I think this may overload the code, as I need to call twice the database.

Is there a way to optimize this code ?

Is there other way to get the last inserted id ?

Thanks for helping.

Upvotes: 1

Views: 3816

Answers (1)

Sheraz Nadeem
Sheraz Nadeem

Reputation: 311

Just for the reference, @@identity is MSSQL-specific system function, that also returns last-insert id but for MSSQL, not for MySQL.

To get last-insert id in MySQL, since you have asked specifically for MySQL, you need to change your SELECT statement to following:

SELECT LAST_INSERT_ID() AS id;

Also, since stmt->execute() and stmt->executeQuery() methods take String, as input argument, I am sure that you don't need to connection->createStatement() again. Just to confirm, I just googled it and found this link.

Please note that this answer is specifically for MySQL, as this question is about MySQL. Hope it helps.

Upvotes: 4

Related Questions