ITPub博客

首页 > 数据库 > 数据库开发技术 > 调用mysql

调用mysql

原创 数据库开发技术 作者:liiinuuux 时间:2016-09-26 15:18:06 0 删除 编辑
链接选项:-lmysqlcppconn

点击(此处)折叠或打开

  1. #include <mysql_driver.h>
  2. #include <mysql_connection.h>
  3. #include <mysql_error.h>

  4. int main()
  5. {
  6.     sql::mysql::MySQL_Driver* driver;
  7.     sql::Connection* con;

  8.     driver = sql::mysql::get_mysql_driver_instance();
  9.     con = driver->connect("tcp://127.0.0.1:1101", "root", "oracle");
  10.     if(con->isValid()) cout << "ok" << endl;
  11.     else con->reconnect();
  12.     delete con;
  13. }

  14. con->isValid() 测试链接
  15. con->reconnect() 重新连接
  16. 必须释放con对象

获取查询结果

点击(此处)折叠或打开

  1. #include <stdio.h>
  2. #include <iostream>
  3. #include <string>
  4. #include <vector>
  5. #include <mysql_driver.h>
  6. #include <mysql_connection.h>
  7. #include <mysql_error.h>
  8. #include <cppconn/driver.h>
  9. #include <cppconn/exception.h>
  10. #include <cppconn/resultset.h>
  11. #include <cppconn/statement.h>
  12. #include <cppconn/prepared_statement.h>
  13. using namespace std;
  14. int main()
  15. {
  16.     sql::mysql::MySQL_Driver *driver;
  17.     sql::Connection* conn;
  18.     sql::Statement * stmt;
  19.     sql::ResultSet * rest;

  20.     driver = sql::mysql::get_mysql_driver_instance();
  21.     conn = driver->connect("tcp://127.0.0.1:1101", "root", "oracle");
  22.     cout << "connect ok" << endl << endl;

  23.     stmt = conn->createStatement();
  24.     stmt->execute("use test"); // 也可以先conn->setSchema(xxxx);
  25.     stmt->execute("drop table t");
  26.     stmt->execute("create table t(id int, name varchar(10))");
  27.     stmt->execute("insert into t values (1, 'xxx')");
  28.     stmt->execute("insert into t values (2, 'yyy')");
  29.     stmt->execute("insert into t values (3, 'zzz')");
  30.     stmt->execute("commit");
  31.     rest = stmt->executeQuery("select * from t");

  32.     sql::ResultSetMetaData* md = rest->getMetaData();
  33.     for(int i=0; i<md->getColumnCount(); i++) printf("%10s", md->getColumnLabel(i+1).c_str());
  34.     cout << endl;
  35.     while (rest->next()) printf("%10d%10s\n", rest->getInt(1), rest->getString(2).c_str());

  36.     delete rest;
  37.     delete stmt;
  38.     delete conn;
  39. }

  40. 每次执行查询都要释放其产生的resultset

使用绑定变量

点击(此处)折叠或打开

  1. int main()
  2. {
  3.     sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
  4.     sql::Connection* conn = driver->connect("tcp://192.168.1.201:1101", "root", "oracle");
  5.     sql::Statement * stmt = conn->createStatement();
  6.     stmt->execute("use test");
  7.     stmt->execute("drop table t");
  8.     stmt->execute("create table t(id int, name varchar(10))");
  9.     delete stmt;

  10.     char name[5];
  11.     memset(name, '0', 5);
  12.     sql::PreparedStatement* pst1 = conn->prepareStatement("insert into t values (?, ?)");
  13.     sql::PreparedStatement* pst2 = conn->prepareStatement("select * from t where id = ?");
  14.     sql::ResultSet* rest = NULL;
  15.     for(int i=0; i<100; i++)
  16.     {
  17.         sprintf(name, "%d", i);
  18.         pst1->setInt(1, i);
  19.         pst1->setString(2, string(name));
  20.         pst1->executeUpdate();
  21.     }
  22.     for(int i=0; i<100; i++)
  23.     {
  24.         pst2->setInt(1, i);
  25.         rest = pst2->executeQuery();
  26.         while (rest->next()) printf("%10d%10s\n", rest->getInt(1), rest->getString(2).c_str());
  27.         delete rest;
  28.     }
  29.     delete pst1;
  30.     delete pst2;
  31.     delete rest;
  32.     delete conn;
  33. }

异常处理

点击(此处)折叠或打开

  1. try {
  2.   ...
  3. } catch (sql::SQLException &e) {
  4.     cout << "# ERR: " << e.what();
  5.     cout << " (MySQL error code: " << e.getErrorCode();
  6.     cout << ", SQLState: " << e.getSQLState() << " )" << endl;
  7. }

调用存储过程

点击(此处)折叠或打开

  1. DELIMITER //
  2. create procedure add_t (in iid int, in iname varchar(10)) begin insert into t values (iid, iname); end;
  3. create procedure get_t_name (in iid int, out oname varchar(10)) begin select max(name) into oname from t where id = iid; end;
  4. create procedure get_t_id (in iname varchar(10), out oid int) begin select max(id) into oid from t where name = iname; end;
  5. create procedure get_t_cnt (out cnt int) begin select count(*) into cnt from t; end;
  6. create procedure get_t ()
  7. begin
  8. select id from t;
  9. select name from t;
  10. end;
  11. //
  12. DELIMITER ;

调用

点击(此处)折叠或打开

  1. stmt->execute("call add_t(1, 'asd')");

获取存储过程的out参数

点击(此处)折叠或打开

  1. stmt->execute("call get_t_name(1, @name)");
  2. std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("select @name as _name"));
  3. while (res->next()) cout << "name = " << res->getString("_name") << endl;

  4. stmt->execute("call get_t_id('asd', @id)");
  5. res.reset(stmt->executeQuery("select @id as _id"));
  6. while (res->next()) cout << "id = " << res->getInt("_id") << endl;

  7. stmt->execute("call get_t_cnt(@cnt)");
  8. res.reset(stmt->executeQuery("select @cnt as _cnt"));
  9. while (res->next()) cout << "count = " << res->getInt("_cnt") << endl;

获取存储过程输出的结果集

点击(此处)折叠或打开

  1. stmt->execute("call get_t");
  2. std::auto_ptr<sql::ResultSet> res;
  3. do {
  4.   res.reset(stmt->getResultSet());
  5.   while (res->next()) cout << "id: " << res->getInt("id") << " name: " << res->getString("name") << endl;
  6. } while (stmt->getMoreResults());

一个stmt有多个结果集时用stmt->getMoreResults()查看是否还有结果集











来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-2125604/,如需转载,请注明出处,否则将追究法律责任。

下一篇: perl文件操作
请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    309725