C++通过OCCI操作Oracle数据库详解

时间:2022-07-02 17:23:04

  1.安装OCCI
  如果oracle数据库默认没有安装OCCI,可以自己从官网上下载与自己数据库版本一致的API,其中包含四个软件包:
  oracle-instantclient-sqlplus-10.2.0.5-1.i386.rpm
  oracle-instantclient-devel-10.2.0.5-1.i386.rpm
  oracle-instantclient-odbc-10.2.0.5-1.i386.rpm
  oracle-instantclient-basic-10.2.0.5-1.i386.rpm
  安装完成之后,会在/usr/lib下多个oracle 共享库文件夹,在/usr/include下多个oracle 头文件(接口)文件夹(可以将他们放到环境变量中)。我的数据库版本是10.2.0,下面以此为例。
  2.编写HelloWorld程序测试连接
  #include <iostream>
  #define LINUXOCCI //避免函数重定义错误
  #include <occi.h>
  using namespace std;
  using namespace oracle::occi;
  int main()
  {
  Environment *env=Environment::createEnvironment(Environment::DEFAULT);
  cout《"success"《endl;
  string name = "scott";
  string pass = "tiger";
  string srvName = "127.0.0.1:1522/orcl";
  try
  {
  Connection *conn = env->createConnection(name, pass);
  cout《"conn success"《endl;
  env->terminateConnection(conn);
  }
  catch(SQLException e)
  {
  cout《e.what()《endl;
  return -1;
  }
  Environment::terminateEnvironment(env);
  cout《"end!"《endl;
  return 0;
  }
  编译命令:
  g++
  test.cc -o test -I/usr/include/oracle/10.2.0.5/client -L/usr/lib/oracle/10.2.0.5/client/lib -locci -lsqlplus
  我没有将occi的路径加入到环境变量中,所以此处要显示列出目录才能通过编译,找到共享库 www.jx-jf.com
  运行。/test会报错,libocci.so找不到,解决办法很简单:将/usr/lib/oracle/…/lib下的库加入到LD_LIBRARY_PATH中就可以了。
  输出结果:
  success
  conn
  success
  end!
  注:这件不幸的事情可能只发生在我身上了,本人系统中有三个用户,其中一个是oracle,而程序是用另一个用户写的,于是编译通过了,但是运行总是报错:
  ORA-12162:
  TNS:net service name is incorrectly specified
  后来查明,这个是由于没有设置并导出ORACLE_SID.换了oracle用户试试,居然运行通过了,真的很伤心,原来那个用户没有设置Oracle环境变脸怎么能直接本地访问呢。
  3.进行一些操作,执行sql语句
  Employees.h
  /*
  * A simple OCCI test application
  * This file contains the Employees class declaration
  */
  #include <iostream>
  #include <occi.h>
  #include <iomanip>
  using namespace oracle::occi;
  using namespace std;
  class Employees {
  public:
  Employees();
  virtual ~Employees();
  void List();
  private:
  Environment *env;
  Connection *con;
  string user;
  string passwd;
  string db;
  };
  Employees::Employees()
  {
  /*
  69 * connect to the test database as the HR
  70 * sample user and use the EZCONNECT method
  71 * of specifying the connect string. Be sure
  72 * to adjust for your environment! The format
  73 * of the string is host:port/service_name
  74 */
  user = "scott";
  passwd = "tiger";
  db = "127.0.0.1:1522/orcl";
  env = Environment::createEnvironment(Environment::DEFAULT);
  try
  {
  con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
  cout 《 ex.getMessage();
  exit(EXIT_FAILURE);
  }
  }
  Employees::~Employees()
  {
  env->terminateConnection (con);
  Environment::terminateEnvironment (env);
  }
  void Employees::List()
  {
  /*
  104 * simple test method to select data from
  105 * the employees table and display the results
  106 */
  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select EMPNO, ENAME, JOB " \
  "from EMP order by EMPNO";
  try
  {
  stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
  cout 《 ex.getMessage();
  }
  if (stmt)
  {
  try
  {
  stmt->setPrefetchRowCount(32);
  rs = stmt->executeQuery();
  }
  catch (SQLException& ex)
  {
  cout 《 ex.getMessage();
  }
  if (rs)
  {
  cout 《 endl 《 setw(8) 《 left 《 "EMPNO"
  《 setw(22) 《 left 《 "ENAME"
  《 setw(27) 《 left 《 "JOB"
  《 endl;
  cout 《 setw(8) 《 left 《 "======"
  《 setw(22) 《 left 《 "===================="
  《 setw(27) 《 left 《 "========================="
  《 endl;
  while (rs->next()) {
  cout 《 setw(8) 《 left 《 rs->getInt(1)
  《 setw(22) 《 left 《 (rs->isNull(2) ? "n/a" : rs->getString(2))
  《 setw(27) 《 left 《 rs->getString(3)
  《 endl;
  }
  cout 《 endl;
  stmt->closeResultSet(rs);
  }
  con->terminateStatement(stmt);
  }
  }
  main.cc
  #include "Employees.h"
  using namespace std;
  using namespace oracle::occi;
  int main (void)
  {
  /*
  48 * create an instance of the Employees class,
  49 * invoke the List member, delete the instance,
  50 * and prompt to continue…
  51 */
  Employees *pEmployees = new Employees();
  pEmployees->List();
  delete pEmployees;
  cout 《 "ENTER to continue…";
  cin.get();
  return 0;
  }