可以进行select操作,DDL操作,绑定变量没有,如果要方便使用,还需要再封装 #ifndef _DBCONN_H #define _DBCONN_H #include <stdio.h> #include <string.h> #include <ctype.h> #include <oci.h> enum DBFieldType { FT_NULL, FT_BIT, FT_CHAR, FT_STRING, FT_WSTRING, //Wide String for unicode. FT_BLOB, FT_TIMESTAMP, FT_DATE, FT_TIME, FT_DATETIME, FT_FLOAT, FT_DOUBLE, FT_INTEGER, FT_SMALLINT, FT_WORD, FT_BOOLEAN }; class dbconn { public: dbconn(); ~dbconn(); int connect(char *ServerAddr,char *vsp,char *user,char *passwd); int disconnect(); int initial(); int execute(char *query); int getFieldsInfo(char *para); int Rtrim(char *para); int ConvertFeild(char *src,int srctype,char *dst,int length); int dbconn::getFields(char *para); public: OCIEnv *myenvhp; /* the environment handle */ OCIError *myerrhp; /* the error handle */ OCIServer *mysrvhp; /* the server handle */ OCISession *myusrhp; /* user session handle */ OCISvcCtx *mysvchp; /* the service handle */ OCIStmt *mystmthp; /* the statment handle */ OCITrans *mytxnhp; /* the transaction handle */ char *stord_sql_stmt; int fieldCount; //列数 int iRecord; char ValueList[1000][512]; }; #endif ----------- #include <stdlib.h> #include "dbconn.h" //OCIAttrSet(m_hStmt,OCI_HTYPE_STMT,(void *)&ORA_MAX_FETCH_COUNT,sizeof(ub4), OCI_ATTR_PREFETCH_ROWS,m_hErr) const ub4 ORA_MAX_FETCH_COUNT = 1000; //ServerAddr ip //vsp dbname int dbconn::connect(char *ServerAddr,char *vsp,char *user,char *passwd) { char connect_str[1024] = {0}; sword status; try { OCIEnvCreate(&myenvhp, OCI_DEFAULT|OCI_THREADED, (dvoid *)0, 0, 0, 0, (size_t)0, (dvoid **)0); OCIInitialize(OCI_THREADED|OCI_OBJECT, NULL, NULL, NULL, NULL); OCIEnvInit(&myenvhp, OCI_DEFAULT, 0, NULL); OCIHandleAlloc((dvoid*)myenvhp, (dvoid**)&myerrhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid**) 0); OCIHandleAlloc((dvoid *)myenvhp, (dvoid**)&mystmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid**)0); sprintf(connect_str, "(DESCRIPTION= (ADDRESS= (PROTOCOL= TCP)(HOST= %s)(PORT= 1521))(CONNECT_DATA= (SERVICE_NAME= %s)))",ServerAddr,vsp); OCIHandleAlloc((dvoid*)myenvhp, (dvoid**)&mysrvhp, OCI_HTYPE_SERVER, (size_t)0, (dvoid**) 0); OCIHandleAlloc((dvoid*)myenvhp, (dvoid**)&mysvchp, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid**) 0); status = OCIServerAttach(mysrvhp, myerrhp, (text*)connect_str, strlen(connect_str), OCI_DEFAULT); if (status != OCI_SUCCESS) { sb4 errcodep; char errbuf[1024] = {0}; OCIErrorGet(myerrhp, (ub4)1, NULL, &errcodep, (text*)errbuf, (ub4)sizeof(errbuf), (ub4)OCI_HTYPE_ERROR); printf("OCIServerAttach error[%s]!/n",errbuf); OCIHandleFree(mysrvhp, OCI_HTYPE_SERVER); return -2; } OCIAttrSet((dvoid*)mysvchp, OCI_HTYPE_SVCCTX, (dvoid*)mysrvhp, (ub4)0, OCI_ATTR_SERVER, myerrhp); OCIHandleAlloc((dvoid*)myenvhp, (dvoid**)&myusrhp, OCI_HTYPE_SESSION, (size_t)0, (dvoid**) 0); OCIAttrSet((dvoid*)myusrhp, OCI_HTYPE_SESSION, (dvoid*)user, (ub4)strlen(user), OCI_ATTR_USERNAME, myerrhp); OCIAttrSet((dvoid*)myusrhp, OCI_HTYPE_SESSION, (dvoid*)passwd, (ub4)strlen(passwd), OCI_ATTR_PASSWORD, myerrhp); status = OCISessionBegin(mysvchp, myerrhp, myusrhp, OCI_CRED_RDBMS, OCI_DEFAULT); if (status != OCI_SUCCESS) { sb4 errcodep; char errbuf[1024] = {0}; OCIErrorGet(myerrhp, (ub4)1, NULL, &errcodep, (text*)errbuf, (ub4)sizeof(errbuf), (ub4)OCI_HTYPE_ERROR); printf("OCISessionBegin error[%s]!/n",errbuf); printf("OCISessionBegin failed!/n"); OCIServerDetach(mysrvhp, myerrhp, OCI_DEFAULT); OCIHandleFree(myusrhp, OCI_HTYPE_SESSION); OCIHandleFree(mysvchp, OCI_HTYPE_SVCCTX); OCIHandleFree(mysrvhp, OCI_HTYPE_SERVER); return -1; } OCIAttrSet((dvoid*)mysvchp, OCI_HTYPE_SVCCTX, (dvoid*)myusrhp, (ub4)0, OCI_ATTR_SESSION, myerrhp); OCIHandleAlloc((dvoid *)myenvhp, (dvoid**)&mytxnhp, OCI_HTYPE_TRANS, (size_t)0, (dvoid**)0); OCIAttrSet((dvoid*)mysvchp, OCI_HTYPE_SVCCTX, (dvoid*)mytxnhp, (ub4)0, OCI_ATTR_TRANS, myerrhp); } catch(...) { printf("catch a error!/n"); } return 0; } int dbconn::disconnect() { sword status; try { status = OCISessionEnd(mysvchp, myerrhp, myusrhp, OCI_DEFAULT); if (status != OCI_SUCCESS) { printf("DBCON_ERROR_DISCONNECTING failed!/n"); } OCIServerDetach(mysrvhp, myerrhp, OCI_DEFAULT); OCIHandleFree(mytxnhp, OCI_HTYPE_TRANS); OCIHandleFree(myusrhp, OCI_HTYPE_SESSION); OCIHandleFree(mysrvhp, OCI_HTYPE_SERVER); OCIHandleFree(mysvchp, OCI_HTYPE_SVCCTX); OCIHandleFree(myerrhp, OCI_HTYPE_ERROR); OCIHandleFree(myenvhp, OCI_HTYPE_ENV); myerrhp = NULL; myenvhp = NULL; mytxnhp = NULL; mysrvhp = NULL; myusrhp = NULL; mysvchp = NULL; } catch(...) { printf("DBCON_ERROR_DISCONNECTING failed/n"); } return 0; } dbconn::dbconn() { myenvhp = NULL; myerrhp = NULL; mysrvhp = NULL; myusrhp = NULL; mysvchp = NULL; mystmthp = NULL; mytxnhp = NULL; stord_sql_stmt = NULL; } dbconn::~dbconn() { } int dbconn::execute(char *query) { sword status; ub2 stType; ub4 parmub4; if (stord_sql_stmt) { free(stord_sql_stmt); stord_sql_stmt = NULL; } stord_sql_stmt = (char*)malloc(strlen(query)+16); memset(stord_sql_stmt, 0, strlen(query)+16); strcpy(stord_sql_stmt, query); try { status = OCIStmtPrepare(mystmthp, myerrhp, (text*)stord_sql_stmt, (ub4)strlen(stord_sql_stmt), OCI_NTV_SYNTAX, OCI_DEFAULT); if (status != OCI_SUCCESS) { free(stord_sql_stmt); stord_sql_stmt = NULL; printf("DBCON_QUERY_ERROR failed/n"); return -1; } OCIAttrGet((dvoid*)mystmthp, OCI_HTYPE_STMT, (dvoid*)&stType, (size_t *)0, OCI_ATTR_STMT_TYPE, myerrhp); if (stType == OCI_STMT_SELECT) { //设置一次执行取数据的行数 OCIAttrSet(mystmthp,OCI_HTYPE_STMT,(void *)&ORA_MAX_FETCH_COUNT,sizeof(ub4), OCI_ATTR_PREFETCH_ROWS,myerrhp); status = OCIStmtExecute(mysvchp, mystmthp, myerrhp, 0, 0, NULL, NULL, OCI_DEFAULT); if (status != OCI_SUCCESS) { free(stord_sql_stmt); stord_sql_stmt = NULL; printf("DBCON_QUERY_ERROR failed!/n"); return -1; } OCIAttrGet((dvoid*)mystmthp, OCI_HTYPE_STMT, (dvoid*)&parmub4, (size_t *)0, OCI_ATTR_PARAM_COUNT, myerrhp); fieldCount = parmub4; // getFieldsInfo(""); getFields(""); } else { status = OCIStmtExecute(mysvchp, mystmthp, myerrhp, 1, 0, NULL, NULL, OCI_DEFAULT); if (status != OCI_SUCCESS) { free(stord_sql_stmt); stord_sql_stmt = NULL; printf("OCIStmtExecute no select !/n"); // return -2; } status = OCITransCommit(mysvchp, myerrhp, OCI_DEFAULT); if (status != OCI_SUCCESS) { printf("OCITransCommit error !/n"); return -3; } } } catch(...) { printf("An exception was thrown./n"); } free(stord_sql_stmt); stord_sql_stmt = NULL; return 0; } int dbconn::ConvertFeild(char *src,int srctype,char *dst,int length) { int iRet = 0; int result = 0; sb2 year; ub1 month,day,hour,min,sec; switch(srctype) { case FT_STRING: strcpy(dst,src); break; case FT_INTEGER: OCINumberToInt(myerrhp, (OCINumber*)src, (uword)sizeof(int), OCI_NUMBER_UNSIGNED, (dvoid*)&result); sprintf(dst,"%d",result); break; case FT_DATE: OCIDateGetDate((OCIDate*)src, &year, &month, &day); OCIDateGetTime((OCIDate*)src, &hour, &min, &sec); sprintf(dst,"%d-%d-%d %d:%d:%d",year,month,day,hour,min,sec); break; default: iRet = -1; break; } return iRet; } int dbconn::Rtrim(char *sz) { int j = 0; int ilen=strlen(sz); for(j=ilen-1;j>0;j--) { if(sz[j]==0x20 || sz[j]== 0x11) { sz[j] = '/0'; }else { break; } } return 0; } int dbconn::getFieldsInfo(char *para) { int i = 0; int status = 0; OCIParam *mypard = NULL; ub2 dtype; ub2 col_len ; char *fieldValue[200]; ub2 fieldType[200] = {0}; int fieldlen[200] = {0}; OCIDefine *dfnhp[200]; ub2 external_type; char acNode[513] = {0}; if(fieldCount > 200) { return -1; } for (i=0; i< fieldCount; i++) { OCIParamGet((dvoid*)mystmthp, OCI_HTYPE_STMT, myerrhp, (dvoid**)&mypard, (ub4)i+1); // OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid**)&colname, &colname_len, OCI_ATTR_NAME, myerrhp); // strncpy(fieldName, (char*)colname, colname_len); OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid**)&dtype, 0, OCI_ATTR_DATA_TYPE, myerrhp); switch (dtype) { case 1: //VARCHAR2, NVARCHAR2 fieldType = FT_STRING; external_type = SQLT_CHR; break; case 2: //NUMBER fieldType = FT_INTEGER; external_type = SQLT_VNU; break; case 11: //ROWID fieldType = FT_STRING; external_type = SQLT_STR; break; case 12: //DATE fieldType = FT_DATE; external_type = SQLT_ODT; break; case 96: //CHAR, NCHAR fieldType = FT_STRING; external_type = SQLT_STR; break; default: continue; } OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid*)&col_len, 0, OCI_ATTR_DATA_SIZE, myerrhp); col_len += 2; fieldValue = (char *)malloc(sizeof(char)*col_len); memset(fieldValue,0x00,sizeof(char)*col_len); fieldlen = col_len; status = OCIDefineByPos(mystmthp, &dfnhp, myerrhp, i+1, (dvoid*)fieldValue, (sb4)col_len, external_type, 0, 0, 0, OCI_DEFAULT); if (status != OCI_SUCCESS) { printf("select error!/n"); return -1; } } iRecord = 0; for (int ilRec = 0;ilRec < 1000;++ilRec) { memset(ValueList[ilRec],0x00,512);; status = OCIStmtFetch(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT); if (status == OCI_NO_DATA) { printf("no find data!/n"); break; } for(i = 0;i < fieldCount;++i) { fieldValue[fieldlen-1] = '/0'; if(ConvertFeild(fieldValue,fieldType,acNode,512) < 0) { continue; } Rtrim(acNode); strcat(ValueList[ilRec],acNode); strcat(ValueList[ilRec],"/t"); if(strlen(ValueList[ilRec]) > 4484) { break; } } ValueList[ilRec][strlen(ValueList[ilRec])-1] = '/0'; ++iRecord; printf("get msg is[%s][%s][%d]/n",acNode,ValueList[ilRec],strlen(ValueList[ilRec])); } for(i = 0;i < fieldCount;++i) { free(fieldValue); fieldValue = NULL; } return 0; } int dbconn::getFields(char *para) { int i = 0; int status = 0; OCIParam *mypard = NULL; ub2 dtype; ub2 col_len ; char *fieldValue[200]; ub2 fieldType[200] = {0}; int fieldlen[200] = {0}; OCIDefine *dfnhp[200]; ub2 external_type; char acNode[513] = {0}; if(fieldCount > 200) { return -1; } for (i=0; i< fieldCount; i++) { OCIParamGet((dvoid*)mystmthp, OCI_HTYPE_STMT, myerrhp, (dvoid**)&mypard, (ub4)i+1); // OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid**)&colname, &colname_len, OCI_ATTR_NAME, myerrhp); // strncpy(fieldName, (char*)colname, colname_len); OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid**)&dtype, 0, OCI_ATTR_DATA_TYPE, myerrhp); switch (dtype) { case 1: //VARCHAR2, NVARCHAR2 fieldType = FT_STRING; external_type = SQLT_CHR; break; case 2: //NUMBER fieldType = FT_INTEGER; external_type = SQLT_VNU; break; case 11: //ROWID fieldType = FT_STRING; external_type = SQLT_STR; break; case 12: //DATE fieldType = FT_DATE; external_type = SQLT_ODT; break; case 96: //CHAR, NCHAR fieldType = FT_STRING; external_type = SQLT_STR; break; default: continue; } // get the length of the column OCIAttrGet((dvoid*)mypard, OCI_DTYPE_PARAM, (dvoid*)&col_len, 0, OCI_ATTR_DATA_SIZE, myerrhp); col_len += 2; fieldValue = (char *)malloc(sizeof(char)*col_len); memset(fieldValue,0x00,sizeof(char)*col_len); fieldlen = col_len; // bind the column with the value status = OCIDefineByPos(mystmthp, &dfnhp, myerrhp, i+1, (dvoid*)fieldValue, (sb4)col_len, external_type, 0, 0, 0, OCI_DEFAULT); if (status != OCI_SUCCESS) { printf("select error!/n"); return -1; } } iRecord = 0; i = 0; while (1) { //get the next row status = OCIStmtFetch(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT); if (status == OCI_NO_DATA) { printf("no find data!/n"); break; } i++; } printf("i = %d/n", i); for(i = 0;i < fieldCount;++i) { free(fieldValue); fieldValue = NULL; } return 0; } --------- #include "dbconn.h" #if defined(WIN32) #pragma comment(lib,"oci.lib") #endif int main() { dbconn conn; conn.connect("10.41.24.200", "orcl", "user", "psw"); conn.execute("select * from t_name"); conn.disconnect(); return 0; }