扫描文件夹将获取文件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); }