扫描文件夹将获取文件oci数据入库
1、句柄层次：
Environment Handle 环境句柄
Statement Handle 表达句柄
Error Handle 错误句柄
Service Context Handle 服务上下文句柄
Define Handle 查询输出定位句柄
Bind Handle 输入输出绑定变量句柄
Server Handle 服务器句柄
User Session Handle 用户会话句柄
Transaction Handle 事务句柄
2、连接 ORACLE数据库流程
OCI 连接过程比较复杂，除了分配设置各个基本句柄外，还要明确彼此之间的联系，大致流程如下：
创建环境句柄： OCIEnvCreate(&envhp, …);
创建一个指定环境的错误句柄： OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,…);
创建一个指定环境的服务句柄： OCIHandleAlloc((dvoid *)envhp, (dvoid **)&servhp,…);
建立到数据源的访问路径： OCIServerAttach(servhpp, errhpp,…);
创建一个指定环境的服务上下文句柄： (void) OCIHandleAlloc((dvoid *)envhpp,…);
为指定的句柄及描述符设置特定的属性： (void) OCIAttrSet((dvoid *)svchpp,…);
创建一个指定环境的用户连接句柄： (void) OCIHandleAlloc((dvoid *)envhpp,…);
为用户连接句柄设置登录名及密码： (void) OCIAttrSet((dvoid *)usrhpp,…);
认证用户建立一个会话连接： OCISessionBegin(svchpp, errhpp,…);
创建一个句子句柄： OCIHandleAlloc((dvoid *)envhpp,…);s
准备 SQL 语句： OCIStmtPrepare(stmthpp, errhpp,…);
绑定输入变量： OCIBindByPos(stmtp &hBind, errhp,…);
绑定输出变量： OCIDefineByPos(stmthpp, &bhp1, errhpp,…);
获得 SQL 语句类型： OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT,…);
执行 SQL 语句： OCIStmtExecute(svchpp, stmthpp,…);
释放一个会话： OCISessionRelease();
删除到数据源的访问： OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);
释放句柄： OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
OCI简单示例：
#include "list_process.h" #include<stdio.h> #include<stdlib.h> #include<string.h> #include<oci.h> #include <fcntl.h> #include <unistd.h> #include <string.h> #include <sys/types.h> #include <sys/stat.h> #include <dirent.h> #include <syslog.h> #include <sys/wait.h>
#define NAMESIZE 512 //文件夹名字 #define MAX_FID 65535 #define OCIREADSIZE 228*1000 //每次读取1000条话单 #define ROW 1000 static OCIEnv *p_env; // 环境句柄 static OCIError *p_err; //错误句柄 static OCISvcCtx *p_svc; //服务上下文句柄 static OCIStmt *p_sql; //语句句柄
//绑定句柄 static OCIDefine *p_dfn = (OCIDefine*)0; static OCIBind *p_bnd1 = (OCIBind*)0; static OCIBind *p_bnd2 = (OCIBind*)0; static OCIBind *p_bnd3 = (OCIBind*)0; static OCIBind *p_bnd4 = (OCIBind*)0; static OCIBind *p_bnd5 = (OCIBind*)0; static OCIBind *p_bnd6 = (OCIBind*)0; static OCIBind *p_bnd7 = (OCIBind*)0; static OCIBind *p_bnd8 = (OCIBind*)0; static OCIBind *p_bnd10 = (OCIBind*)0;
//入参定义 char mdn_in1[ROW]; char mdn_in2[ROW][21]; char mdn_in3[ROW][21]; char mdn_in4[ROW][13]; char mdn_in5[ROW][13]; long int mdn_in6[ROW]; long int mdn_in7[ROW]; long int mdn_in8[ROW]; char mdn_in9[ROW][21]; char mdn_in10[ROW][21];
void connectOra(); void ReadFileAndInsertIn(char *rPath); void realse(); void bindingDate(struct detail_list *mylist); void FindFileAndDo(const char *path); void DealWithChild(); void CreateDaemon();
int main() { openlog( "daemon.c:info", LOG_PID, LOG_DAEMON ); //打开log CreateDaemon(); DealWithChild(); closelog; //关闭log return 0; }
void CreateDaemon() { pid_t pid; pid = fork(); //守护进程 if( pid < 0 ) { syslog( LOG_INFO,"error!"); exit(1); }else if( pid > 0 ) { exit(0); //主进程自然消亡 } setsid(); chdir("./"); umask(0); int i; for( i = 0; i < MAX_FID; i++ ) { close(i); } }
void DealWithChild() { char *path = "./file"; FindFileAndDo(path); }
//获得文件夹下的文件名 void FindFileAndDo(const char *path) { DIR *dir ; char *com; while(1) { dir = opendir(path); char spath[NAMESIZE]; //文件路径 struct dirent *dRent = NULL; int flag = 0;
if( dir == NULL ) { continue; }
//读文件夹内容 while( (dRent = readdir(dir)) != NULL ) { if(strcmp(dRent->d_name,".") == 0 || strcmp(dRent->d_name,"..") == 0 || strcmp(dRent->d_name,"~temp") == 0) continue; memset(spath, 0, sizeof(spath)); strcpy(spath, path); strcat(spath,"/"); strcat(spath,dRent->d_name); //获得文件路径 flag++; /*处理文件*/ connectOra(); //连接数据 ReadFileAndInsertIn(spath); //插入数据 realse(); //释放句柄 syslog( LOG_INFO,"%s\n", spath); break; }
//移除文件 if(flag > 0) { char com[512] = "mv "; strcat(com, spath); strcat(com, " ./used"); syslog( LOG_INFO,"%s\n",com); system(com); //执行命令 } sleep(5); } closedir(dir); }
//打开文件入库 void ReadFileAndInsertIn(char *rPath) { //声明区 int fpR; int readbytes = OCIREADSIZE; char buf[OCIREADSIZE+1]; struct detail_list mylist[ROW];
//打开话单文件 if( (fpR = open(rPath,O_RDONLY)) < 0 ) { syslog( LOG_INFO,"open rPath error\n"); exit(1); }
//每次读取1000条数据 while(readbytes == OCIREADSIZE ) { memset( buf, 0, sizeof(buf) ); if( (readbytes=read(fpR, buf, OCIREADSIZE)) < 0) { syslog( LOG_INFO,"read file error\n"); close(fpR); exit(1); } buf[OCIREADSIZE] = '\0'; if(readbytes != 0) { //将读出的内容格式为detail_list类型结构体 memcpy(mylist, buf, OCIREADSIZE); //1000条数据放入结构体 //邦定数据,执行插入 bindingDate(mylist); } } //关闭数据库 syslog( LOG_INFO,"the insert operation completed:\n"); OCILogoff(p_svc, p_err); close(fpR); } //数据绑定 void bindingDate(struct detail_list *mylist) { int i; for(i = 0; i < ROW ; i++) { syslog( LOG_INFO,"%d\n", sizeof(mylist[i])); mdn_in1[i] = mylist[i].listType; strcpy(mdn_in2[i], mylist[i].dialClientNumber); strcpy(mdn_in3[i], mylist[i].calledClientNumber); strcpy(mdn_in4[i], mylist[i].responseDate); strcpy(mdn_in5[i], mylist[i].listEndDate); mdn_in6[i] = mylist[i].talkTime; mdn_in7[i] = mylist[i].inRelayGroupId; mdn_in8[i] = mylist[i].outRelayGroupId; strcpy(mdn_in9[i], mylist[i].connectNumber); strcpy(mdn_in10[i], mylist[i].billingNumber); } \*绑定数据*\ OCIBindByPos(p_sql, &p_bnd1, p_err, 1, //绑定的位置 (ub1*)mdn_in1, sizeof(mdn_in1[0]), SQLT_INT,0,0,0,0,0,OCI_DEFAULT);
OCIBindByPos(p_sql, &p_bnd2, p_err, 2, //绑定的位置 (ub1*)mdn_in2, sizeof(mdn_in2[0]), SQLT_STR,0,0,0,0,0,OCI_DEFAULT);
OCIBindByPos(p_sql, &p_bnd3, p_err, 3, //绑定的位置 (ub1*)mdn_in3, sizeof(mdn_in3[0]), SQLT_STR,0,0,0,0,0,OCI_DEFAULT);
。。。略。。
//数组绑定 OCIBindArrayOfStruct(p_bnd1, p_err, (ub4)sizeof(mdn_in1[0]), (ub4)0,(ub4)0,(ub4)0); //数组绑定 OCIBindArrayOfStruct(p_bnd2, p_err, (ub4)sizeof(mdn_in2[0]), (ub4)0,(ub4)0,(ub4)0); //数组绑定 OCIBindArrayOfStruct(p_bnd3, p_err, (ub4)sizeof(mdn_in3[0]), (ub4)0,(ub4)0,(ub4)0); 。。。略。。。
//--------------处理数据，insert数据库-------------------- OCIStmtExecute(p_svc, p_sql, p_err, (ub4)ROW, (ub4)0, (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT); }
//连接数据库 void connectOra() { int rc; char errbuf[100]; int errcode; char mysql[200];
//创建OCI环境 rc = OCIEnvCreate((OCIEnv **)&p_env, //oci环境句柄 OCI_DEFAULT, //初始化默认模式 (dvoid *)0, (dvoid *(*)(dvoid *, size_t))0, (dvoid *(*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0 );
//申请或释放句柄 rc = OCIHandleAlloc((dvoid *)p_env, //新申请句柄的父句柄 (dvoid **)&p_err, //申请的错误句柄 OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
rc = OCIHandleAlloc((dvoid *)p_env, (dvoid **)&p_svc, //申请的服务上下文句柄 OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0);
//连接服务器，单用户连接 /*Connect to database server*/ rc = OCILogon(p_env, p_err, &p_svc, "scott", 5, "oracle", 6, "orcl", 4);
//测试是否成功 if(rc != 0) { OCIErrorGet((dvoid *)p_err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
syslog( LOG_INFO,"- %.*s", 512, errbuf); exit(8); } else { syslog( LOG_INFO,"Connect to orcl successful!\n"); }
//申请句柄 /*Allocate and prepare SQL statement*/ rc = OCIHandleAlloc((dvoid *)p_env, (dvoid **)&p_sql, //语句句柄 OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
/*Set mysql statement*/ strcpy(mysql, "insert into detail_list(listType, dialClientNumber, calledClientNumber, responseDate, listEndDate,talkTime, inRelayGroupId, outRelayGroupId, connectNumber,billingNumber) values(:1, :2, :3, :4, :5, :6, :7, :8,:9,:10)");
//准备SQL语句，语句邦定 rc = OCIStmtPrepare(p_sql, //sql语句句柄 p_err, mysql, (ub4)strlen(mysql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); }
//释放句柄 void realse() { int rc; //释放句柄 rc = OCIHandleFree((dvoid *)p_sql, OCI_HTYPE_STMT); rc = OCIHandleFree((dvoid *)p_svc, OCI_HTYPE_SVCCTX); rc = OCIHandleFree((dvoid *)p_err, OCI_HTYPE_ERROR); }
