List:MySQL++« Previous MessageNext Message »
From:Guru Clint Date:August 15 2008 7:03pm
Subject:insert_id always 0 when using stored procedures
View as plain text  
So, I'm using stored procedures to create new records in my tables.

It's just a regular SP with input arguments and an INSERT statement.
All working very well.

Now I wanted to get the ID of the new record (auto increment key), but it always returns
0.
So I tried calling the stored procedure with the MySQL command line, then doing a

SELECT LAST_INSERT_ID()

and that works like a charm.

But it doesn't want to work with mysql++

I have MultiStatementsOption turned on for the connection, and am executing it as follows:

       try{
            sqlResult = sqlQuery.store();

            m_uliLastID =
sqlQuery.insert_id()

   
         if (bResultExpected)
                if
(!sqlResult.num_rows())
                {
               
    MESSAGEBOX(MB_TYPE_INFO, "Nothing found");
               
    bResult = false;
                }

            /* retreive all results,
otherwise the next query will fail */
            for (int i = 1;
sqlQuery.more_results(); ++i) {
               
mysqlpp::StoreQueryResult sqlResultDump =
 sqlQuery.store_next();
            }

        }catch(mysqlpp::Exception &ex){
            MESSAGEBOX(MB_TYPE_CRITICAL,
ex.what());
            return false;
        }


m_uliLastID however, is always 0

I tried different things, like using a SimpleResult with the execute() method, but doesn't
work
I'm using the same connection (opens when the app starts, closes when it's killed)

So what am I doing wrong here?
I can solve this by putting the SELECT statement after the INSERT in the SP, but that
should be the last option as mysql++ provides a method for it...


thx for the help :)



      
Thread
insert_id always 0 when using stored proceduresGuru Clint17 Aug
  • Re: insert_id always 0 when using stored proceduresWarren Young18 Aug