如何重用MySQL++查询对象来调用多个存储过程？

Question

#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;
}

我想要多次使用mysql++ query来执行过程"CreateTable“，最后我重置了查询，但是无论如何，只有第一个查询工作，最后一个查询不工作，我的问题是:如何让所有的查询都工作？

-- 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 ;

上面是程序。

Answer 1

这里有几个bug：

1. 您已经关闭了异常(conn(false))，但是您也没有检查错误代码的返回值。您的第二个execute()调用失败了，但是没有询问Query对象的原因，您就是在盲目地运行。

不过，我认为允许MySQL++抛出异常(conn())并将整个过程包装在一个try块中会更简洁，而不是对所有的try调用都添加错误检查。

• 您不需要MultiStatementsOption按照您当前显示的方式来执行您所要求的操作。这里有两个单独的语句，而不是一个复合语句。再加上分号可能会让MySQL感到困惑，这就是第二个调用失败的原因。

mysql命令行工具要求使用分号来终止SQL语句，但是在使用像MySQL++这样的数据库应用编程接口时，分号只需要分隔多个语句。

您可以将这两个CREATE语句组合成一个字符串(和一个execute())，也可以删除分号，这样从MySQL++ 2.x开始就不需要在查询之间调用MultiStatementsOption.

• The reset()了。该方法仍然可用的唯一原因是，如果您希望重用已用于模板查询的Query对象，则该方法是必要的；由于相当明显的原因，它们是唯一仍不能自动重置的类型。