OCI编程自学与总结(二)

#include <oci.h> #include <iostream> #include <string> #include <string.h> #include <stdlib.h> using namespace std; struct result { int id; char name[20]; char date[20]; result() { id = 0; memset(name, '/0', sizeof(name)); memset(date, '/0', sizeof(date)); } }; int main() { // 初始化OCI环境句柄指针,对应的句柄类型OCI__HTYPE_ENV OCIEnv *envhpp = NULL; // 初始化服务器句柄,对应的句柄类型OCI_HTYPE_SERVER OCIServer *servhpp = NULL; // 用于捕获OCI错误信息,对应的句柄类型OCI_HTYPE_ERROR OCIError *errhpp = NULL; // 初始化用户连接句柄,对应的句柄类型OCI_HTYPE_SESSION OCISession *usrhpp = NULL; // 初始化服务上下文句柄,对应的句柄类型OCI_HTYPE_SVCCTX OCISvcCtx *svchpp = NULL; // 初始化句子句柄(创建一个会话),对应的句柄类型OCI_HTYPE_STMT OCIStmt *stmthpp = NULL; string sid="orcl"; // 创建或初始化OCI环境,并设置环境句柄。 sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL); if (swResult != OCI_SUCCESS && swResult != OCI_SUCCESS_WITH_INFO) { cout << "Oracle environment initialization error!" << endl; exit(1); } cout << "Oracle environment initialization success!" << endl; // 产生一个指定环境的错误句柄 OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); // 产生一个指定环境的服务句柄 OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0); // 连接|断开服务器 多用户方式连接(创建一个存取路径到数据源) if (OCIServerAttach(servhpp, errhpp, (text *)sid.c_str(), strlen(sid.c_str()), 0) != OCI_SUCCESS) { int errcno; char errbuf[512]={'/0'}; sb4 errcode; // 返回一个错误指针和一个OCI错误代码 OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR); errcno = errcode; cout << "Oracle server attach error:" << errbuf << endl; OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV); OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER); OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR); exit(1); } cout << "Oracle server attach success!"<< endl; /*****************连接数据库****************/ string user = "tzos"; string pas = "tzos"; errhpp = NULL; // 产生一个指定环境的错误句柄 (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); // 产生一个指定环境的服务上下文句柄 (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0); // 为指定的句柄及描述符设置特定的属性(为服务句柄指定服务上下文) (void) OCIAttrSet((dvoid *)svchpp, OCI_HTYPE_SVCCTX, (dvoid *)servhpp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhpp); // 产生一个指定环境的用户连接句柄 (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0); // 为用户连接句柄设置登录名及密码 (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp); (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pas.c_str(), (ub4)strlen(pas.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp); // 认证用户再建立一个会话连接 if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS) { int errcno; char errbuf[512]={'/0'}; sb4 errcode; // 返回一个错误指针和一个OCI错误代码 OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR); errcno = errcode; cout << "User session error:" << errbuf << endl; OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR); OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION); OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX); exit(1); } cout << "user session success!" << endl; (void) OCIAttrSet((dvoid *)svchpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)usrhpp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhpp); /***************执行SQL语句******************/ errhpp = NULL; // 创建一个句子句柄 if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS) { cout << "Create STMT error !" << endl; exit(1); } cout << "Create stmt success !" << endl; // 产生一个指定环境的错误句柄 // ???为什么如果不加这句就会出现指定SQL语句时就会出现错误呢？ OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); // 指定会话的SQL执行语句 char sql[255] = {'/0'}; sprintf(sql, "%s", "SELECT ID,NAME,/ to_char(DATE, 'yyyy-mm-dd hh24:MI:SS') as DATE FROM STUDENT "); if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS) { cout << "Create prepare error!" << sql << endl; exit(1); } cout << "Create prepare success!" << endl; /********* 绑定参数 ***********/ // 申请绑定字段的句柄 OCIDefine *bhp1 = NULL; OCIDefine *bhp2 = NULL; OCIDefine *bhp3 = NULL; OCIDefine *bhp4 = NULL; OCIDefine *bhp5 = NULL; OCIDefine *bhp6 = NULL; OCIDefine *bhp7 = NULL; OCIDefine *bhp8 = NULL; struct result gather; // 指定提取数据长度 ub2 datalen = 0; // 定义指示器变量,用于取可能存在空值的字段 char isnul[6] = {'/0'}; // 定义输出变量, OCIDefineByPos(stmthpp, &bhp1, errhpp, 1, (dvoid *)&gather. id, sizeof(int), SQLT_INT,NULL, &datalen, NULL, OCI_DEFAULT); OCIDefineByPos(stmthpp, &bhp2, errhpp, 2, (dvoid *)&gather.name, sizeof(gather.name), SQLT_CHR, NULL, &datalen, NULL, OCI_DEFAULT); OCIDefineByPos(stmthpp, &bhp3, errhpp, 3, (dvoid *)&gather.date, sizeof(gather.date), SQLT_STR, NULL, &datalen, NULL, OCI_DEFAULT); // 获得SQL语句类型,判断会话执行的SQL语句时什么类型，值为OCI_STMT_XXX类常量 ub2 stmt_type; OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, errhpp); //执行SQL语句 OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)(stmt_type==OCI_STMT_SELECT?1:0), (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT); // 提取信息 int rows_fetched; do { cerr << gather. id << ","; cerr << gather.name<< ","; cerr << gather.date<< "./n"; memset(gather.end, '/0', sizeof(gather.date)); } while(OCIStmtFetch2(stmthpp, errhpp, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT) != OCI_NO_DATA); // 获得记录条数 OCIAttrGet((CONST void *)stmthpp, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched), OCI_ATTR_ROW_COUNT, errhpp); cout << "/n总共记录数:" << rows_fetched << endl; OCILogoff(svchpp, errhpp); OCIServerDetach(servhpp, errhpp, OCI_DEFAULT); OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT); OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX); OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER); OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR); return 0; }