How can I re-use a MySQL++ query object to call multiple stored procedures?

#include <iostream>
#include <mysql++.h>
using namespace std;

int main() {
    // Get database access parameters from command line
    const char* db = "enet", *server = "192.168.1.108", *user = "root", *pass =
            "123456";
    // Connect to the sample database.
    mysqlpp::Connection conn(false);
    conn.set_option(new mysqlpp::MultiStatementsOption(true));
    if (conn.connect(db, server, user, pass)) {
        mysqlpp::Query query = conn.query();
        query << "call CreateTable('test1', 'generic', 0, 1, 2, 3,4,5,6,7,8,9,10,NOW());";
        query.execute();
        query.reset();

        query << "call CreateTable('test2', 'generic', 0, 1, 2, 3,4,5,6,7,8,9,10,NOW());";
        query.execute();
        query.reset();

        return 0;
    } else {
        cerr << "DB connection failed: " << conn.error() << endl;
        return 1;
    }

    return 0;
}

I want to use mysql++ query to execute procedure "CreateTable" many times, and i reset the query at last, but no matter how, just the first query works, the last does not, my problem is that: how to make all of queries work?

-- create table --
delimiter $$
drop procedure if exists CreateTable $$
create procedure CreateTable(
    IN tableName VARCHAR(20), 
    IN dbName VARCHAR(20),
    IN INT_RegDevID INTEGER,
    IN Dec_Long DECIMAL(24,16),
    IN Dec_Lat DECIMAL(24,16),
    IN Dec_Height DECIMAL(10,6),
    IN Dec_Direction DECIMAL(10,6),
    IN AverageSpeed DECIMAL(10,6),
    IN Dec_Base VARCHAR(10),
    IN MCC INTEGER,
    IN MNC INTEGER,
    IN LAC INTEGER,
    IN CI INTEGER,
    IN Dec_LocaDate TIMESTAMP)
-- --------------------------------------------------开发者_运维知识库-----------------------------
-- -------------------------------------------------------------------------------
begin
    -- the test variable
    -- Warning: the encoding can result many problem!!!
    declare varTableName VARCHAR(32) default NULL;
    set @varTableName = NULL;
    set @table_prefix = "posinfo_";
    set @table_params = "(
          `Int_LocaID` int(11) NOT NULL auto_increment,
          `INT_RegDevID` int(11) NOT NULL default '0',
          `Dec_Long` decimal(24,16) NOT NULL default '0.0000000000000000',
          `Dec_Lat` decimal(24,16) NOT NULL default '0.0000000000000000',
          `Dec_Height` decimal(10,6) NOT NULL default '0.000000',
          `Dec_Direction` decimal(10,6) NOT NULL default '0.000000',
          `Dec_ MaxSpeed` decimal(10,6) NOT NULL default '0.000000',
          `Dec_ MinSpeed` decimal(10,6) NOT NULL default '0.000000',
          `AverageSpeed` decimal(10,6) NOT NULL default '0.000000',
          `Var_PosInfo` varchar(50) character set latin1 NOT NULL default '',
          `Var_Remark` varchar(200) character set latin1 NOT NULL default '',
          `Date_LocaDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `Dec_Base` varchar(10) character set latin1 NOT NULL,
          `MCC` int(11) NOT NULL COMMENT '',
          `MNC` int(11) NOT NULL COMMENT '',
          `LAC` int(11) NOT NULL COMMENT '',
          `CI` int(11) NOT NULL COMMENT '',
          PRIMARY KEY  (`Int_LocaID`)
        ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk;";
    set @varCreate = CONCAT("create table ", dbName,".",@table_prefix, tableName, @table_params);
    -- the insert operation
    set @insertOperation = CONCAT("insert into ", dbName,".",@table_prefix, tableName,
        "(INT_RegDevID,Dec_Long,Dec_Lat,Dec_Height,Dec_Direction,AverageSpeed,
         Dec_Base,MCC,MNC,LAC,CI,Date_LocaDate) values(",INT_RegDevID,",",Dec_Long,
         ",",Dec_Lat,",",Dec_Height,",",Dec_Direction,",",AverageSpeed,",",Dec_Base,
         ",",MCC,",",MNC,",",LAC,",",CI,",NOW())"); 
    -- find the target table
    -- Look care about the "' '" !
    set @getTargetTable = CONCAT("select TABLE_NAME into @varTableName from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='",
        dbName, "' and TABLE_NAME='", @table_prefix, tableName,"'");

    -- -------------------------------------------------------------------------------
    -- -------------------------------------------------------------------------------
    PREPARE getTargetTable from @getTargetTable;
    execute getTargetTable;
    select @varTableName;
    set varTableName = @varTableName;

    if varTableName is NULL then
        -- create new table
        PREPARE newTable 
        from @varCreate;
        execute newTable;
        -- do insert operation
        PREPARE insertOperation
        from @insertOperation;
        execute insertOperation;

    else
        -- do insert operation
        PREPARE insertOperation
        from @insertOperation;
        execute insertOperation;
    end if;

end $$
delimiter ;

above, are the procedure.

There are several bugs here:

1. You've turned off exceptions (conn(false)) but you're also not checking return values for error codes. Your second execute() call is failing, but without asking the Query object why, you're running blind.

   Instead of adding error checking to all MySQL++ calls, though, I think it's cleaner to allow MySQL++ to throw exceptions (conn()) and wrap the whole thing in a try block.

2. You don't need the MultiStatementsOption to do what you're asking the way you currently show. You have two separate statements here, not one compound statement. That in combination with the semicolons may be confusing MySQL, which is why the second call fails.

   The mysql command line tool demands semicolons to terminate SQL statements, but when using a database API like MySQL++, they're only necessary to separate multiple statements.

   You can either combine both CREATE statements into a single string (and one execute()) or you can drop the semicolons and the MultiStatementsOption.

3. The reset() calls between queries haven't been necessary since MySQL++ 2.x. The only reason the method is still available is that it's necessary if you want to reuse a Query object that had been used for template queries; they're the only type that still don't auto-reset, for fairly obvious reasons.