proc_exp

最新推荐文章于 2024-01-17 10:49:44 发布

congzhi8603

最新推荐文章于 2024-01-17 10:49:44 发布

阅读量138

点赞数

proc_exp[@more@]

exp_test.pc

/* 一般头文件*/
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include

#include

/* 数据库头文件*/
#include
#include

/* 常量定义*/
/* 老程序所定义的常量*/
#define EXIT 1 /* 提示出错时是否退出 */
#define ERR 1 /* 提示结束处理过程有错误曾发生 */
#define PASS 2 /* 置执行标志为处理成功 */
#define EXITNOW 3 /* 出错时指示立即退出 */
#define NOEXIT 0 /* 出错时不要退出 */
#define COMLOG 0 /* 日志方式： 1 -- 文件 0 -- stdout */

#define MAX_BIG_GROUP 50 /* 所支持的调用大组数 */

#define IPCOUNT 6 /* 最大子进程数 */
#define CTLCOUNT 5 /* 最大sqlloader个数 */
/* 文件长度 */
#define FILEPATHMAX 128 /* 最大文件路径长度 */
#define FILENAMEMAX 128 /* 最大文件名长度 */

/* 命令或SQL语句*/
#define CMDSQLLENGTH 4000

/* 数据库环境定义*/
#define ORACLE

/* 定义数据库名，连接用户名和连接字符串*/
#define DBNAME "test" /* 数据库名 */
#define ETLUSERNAME "test" /* 用户名 */
#define ETLPASSWORD "test" /* 密码 */

#define TABLENAMEMAX 40 /* 表名最大长度 */
#define MAX_ITEMS 50 /* 表最大的可能列数*/
#define MAX_VNAME_LEN 30 /* 字段名的最大长度*/
#define MAX_INAME_LEN 60 /* 字段值的最大长度*/

/* 调度任务状态定义*/
#define STSINIT 0 /* 未执行 */
#define STSACTIVE 1 /* 正在执行 */
#define STSSUCCESS 2 /* 成功执行 */
#define STSERROR -2 /* 出错 */

/* 阀值定义，大小阀值 */
#define LOWSIDE 1 /* 检查阀值下界 */
#define UPSIDE 2 /* 检查阀值上界 */
#define BOTHSIDE 3 /* 检查阀值上下界 */

#define RETERROR -1 /* 返回失败 */
#define RETSUCCESS 0 /* 返回成功 */
#define RETWARNING 1 /* 返回警告 */

#ifndef NULL
#define NULL 0 /* 定义空*/
#endif

/* SQL语句返回值定义*/
#define SQLCODE sqlca.sqlcode /* 定义返回值，成功为0，错误<0，警告>0*/
#define SQLERRMC sqlca.sqlerrm.sqlerrmc /* SQL语句返回错误解释*/
#ifdef ORACLE
#define SQLCODEEMPTY 1403 /* SELECT、FETCH为空*/
#endif

#define SQL_STR_LEN (65535)

#undef MAX_PATH
#define MAX_PATH (256)

#define LOG_MARK __FILE__,__LINE__

#define IO_FBUF (5 * 1024 * 1024)

#define MAX_PER_FETCH_ROWS (4096)

/* for ODBC by Zhou Shui Xing*/
#define MAXCOLS 100
#define CMDLENGTH 1024
#define DATELENGTH 8
#define MAXROWS 200
#define TRYCONNECTCOUNT 20
/* 调试定义*/
#ifndef DEBUG
#define DEBUG
#endif

#define vstrcpy( a, b )
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

/* 全局变量定义*/
char g_ProgramPath[256];
bool g_IsConnected=FALSE;

/* 函数定义*/
int CheckRunning(char *szProgName);

int ConnectDatabase();
int DisConnectDatabase(bool bcommit=TRUE);

static char* GetDateTime(char* dt, int len);
static char* ReadSelectSqlFile(char* pszFileName,char* pszSelectSql);
int WriteLog(char* pszFile, int nLine, char* fmt, ...);

SQLDA * AllocSelectDp(char * pszSelectSql);
int ExportData(char* pszSelectSql, char* pszDataDir, char* pszDelimiter);

void Killed(int no);

/* 必须包含这两句*/
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;

/*
export FILE_MAX_KB=500000
export SEQ_NUM_WIDTH=3
export SEQ_NUM_FIRST=1
export FNAME="${f1}_${f2}_${f3}_${f4}_${f5}_"
export FEXT_TMP="tmp"
export FEXT="dat"
export EXPPATH=.
export LOGSTR="${loghwfbi}"
export SQLFILE="${infile}"
*/
int FILE_MAX_KB=500000;
int SEQ_NUM_WIDTH=3;
int SEQ_NUM_FIRST=1;
char FNAME[256];
char FEXT_TMP[16];
char FEXT[16];
char EXPPATH[256];
char LOGSTR[128];
char SQLFILE[256];
int DISPLAY_SQL=0;

/**********************************************************************
函数名: main
功能描述：调度主程序，根据ETL的调度表中的任务，进行相应的调度
参数说明：
argc: 输入参数的个数
argv：输入参数项
返回值：
成功：ERROR_SUCCESS
*********************************************************************/
int main(int argc,char *argv[])
{
time_t starttime;
time(&starttime);

int i,ret_val;
ret_val = RETERROR;

/*定位程序所在目录（绝对路径）*/
char runpath[256];
getcwd(runpath,255);

strcpy(g_ProgramPath,argv[0]);
i=strlen(g_ProgramPath);
while(g_ProgramPath[i]!='/' && i>=0)
{
g_ProgramPath[i]=0;
i--;
}
if(i>0)
chdir(g_ProgramPath);
getcwd(g_ProgramPath,255);
/*恢复原起始目录*/
chdir(runpath);

/* 防止程序重入*/
if ((i=CheckRunning(argv[0])) != RETSUCCESS)
{
return i;
}

/*注册消息，当进程被强行杀出时，能做清理*/
signal(SIGTERM,Killed);

/*读取环境变量*/
if ( strcmp(getenv("FILE_MAX_KB" ),"")!=0) FILE_MAX_KB=atoi(getenv("FILE_MAX_KB" ));
if ( strcmp(getenv("SEQ_NUM_WIDTH"),"")!=0) SEQ_NUM_WIDTH=atoi(getenv("SEQ_NUM_WIDTH"));
if ( strcmp(getenv("SEQ_NUM_FIRST"),"")!=0) SEQ_NUM_FIRST=atoi(getenv("SEQ_NUM_FIRST"));
if ( strcmp(getenv("FNAME" ),"")!=0) strcpy( FNAME,getenv("FNAME" ));
if ( strcmp(getenv("FEXT_TMP"),"")!=0) strcpy(FEXT_TMP,getenv("FEXT_TMP"));
if ( strcmp(getenv("FEXT" ),"")!=0) strcpy( FEXT,getenv("FEXT" ));
if ( strcmp(getenv("EXPPATH" ),"")!=0) strcpy(EXPPATH ,getenv("EXPPATH" ));
if ( strcmp(getenv("LOGSTR" ),"")!=0) strcpy( LOGSTR,getenv("LOGSTR" ));
if ( strcmp(getenv("SQLFILE" ),"")!=0) strcpy( SQLFILE,getenv("SQLFILE" ));

if ( strcmp(getenv("DISPLAY_SQL"),"")!=0) DISPLAY_SQL=atoi(getenv("DISPLAY_SQL"));

/*这里应该对这些变量做些检查*/

/*
printf("FILE_MAX_KB=%dn",FILE_MAX_KB);
printf("SEQ_NUM_WIDTH=%dn",SEQ_NUM_WIDTH);
printf("SEQ_NUM_FIRST=%0*dn",SEQ_NUM_WIDTH,SEQ_NUM_FIRST);
printf("FNAME =%sn",FNAME );
printf("FEXT_TMP=%sn",FEXT_TMP);
printf("FEXT =%sn",FEXT );
printf("EXPPATH =%sn",EXPPATH );
printf("LOGSTR =%sn",LOGSTR );
printf("SQLFILE =%sn",SQLFILE );
*/
printf("准备连接数据库n");
if (!ConnectDatabase())
{
printf("line%d:连接数据库失败!n", __LINE__);
return RETERROR;
}
printf("连接数据库成功n");

char pszSelectSql[CMDSQLLENGTH];
char pszDataDir[128];
char pszDelimiter[10];

ret_val=RETERROR;
if(ReadSelectSqlFile(SQLFILE,pszSelectSql))
{
sprintf(pszDataDir,"%s",EXPPATH);
sprintf(pszDelimiter,"|");

ret_val=ExportData(pszSelectSql, pszDataDir, pszDelimiter);
}
DisConnectDatabase();

return (ret_val);
}
/********************************************************
函数名: CheckRunning
功能描述：防止程序重入。建立锁文件，直到进程结束时自动释放，如果加锁失败则意味已有程序在运行。
参数说明：无
返回值：程序已经运行，返回RETERROR，否则返回RETSUCCESS
*********************************************************/
int CheckRunning(char *szProgName)
{
if (1) return RETSUCCESS;

char lockfile[256];
int i=strlen(szProgName);
while(i>=0 && szProgName[i]!='/')
i--;
i++;
strcpy(lockfile,szProgName+i);
strcat(lockfile,".lock");
int fd=open(lockfile,O_RDWR|O_CREAT,S_IRUSR|S_IWUSR);
if(fd==-1)
{
printf("打开锁文件出错(进程号%d)：%sn",getpid(),strerror(errno));
return RETERROR;
}
struct flock lock;
lock.l_type = F_WRLCK;
lock.l_start = 0;
lock.l_whence = SEEK_SET;
lock.l_len = 0;
if(fcntl(fd,F_SETLK,&lock)==-1)
{
printf("锁文件加锁失败(进程号%d)：%sn",getpid(),strerror(errno));
return RETWARNING;
}
char buf[256];
memset(buf,0,256);
sprintf(buf,"进程号%dn",getpid());
write(fd,buf,strlen(buf));

return RETSUCCESS;
}

/********************************************************
函数名: ConnectDatabase
功能描述：连接数据库
参数说明：无
返回值：连接数据库成功返回TRUE，失败返回FALSE
*********************************************************/
int ConnectDatabase()
{
EXEC SQL BEGIN DECLARE SECTION;
char szDatabase[30];
char szUser[30];
char szPassword[100];
EXEC SQL END DECLARE SECTION;

/* sprintf(szDatabase, DBNAME);
sprintf(szUser, ETLUSERNAME);
*/ sprintf(szPassword, LOGSTR);

if(g_IsConnected) DisConnectDatabase();

int num=0;
for(num=0;num {
// EXEC SQL CONNECT :szUser IDENTIFIED BY :szPassword USING :szDatabase;
EXEC SQL CONNECT :szPassword;
if (SQLCODE)
{
printf("line%d:连接数据库%s失败，SQL错误号[%d][%s]n",__LINE__, szPassword, SQLCODE, SQLERRMC);
sleep(10);
continue;
}
g_IsConnected=TRUE;
break;
}
return g_IsConnected;
}

/********************************************************
函数名: DisConnectDatabase
功能描述：断开与数据库的连接
参数说明：无
返回值：断开数据库成功返回TRUE，失败返回FALSE
*********************************************************/
int DisConnectDatabase(bool bcommit)
{
if(bcommit)
EXEC SQL COMMIT RELEASE;
else
EXEC SQL ROLLBACK RELEASE;
if (SQLCODE)
{
printf("line%d:断开数据库连接失败，SQL错误号[%d][%s]n",__LINE__, SQLCODE, SQLERRMC);
return FALSE;
}
g_IsConnected=FALSE;
return TRUE;
}
static char* GetDateTime(char* dt, int len)
{
time_t caltime;
struct tm* ptime = NULL;

if ((NULL == dt) || (len < 19))
{
return NULL;
}

time(&caltime);
ptime = localtime(&caltime);

sprintf(dt, "%04d-%02d-%02d %02d:%02d:%02d", 1900 + ptime->tm_year, ptime->tm_mon + 1, ptime->tm_mday, ptime->tm_hour, ptime->tm_min,
ptime->tm_sec);

return dt;
}

int WriteLog(char* pszFile, int nLine, char* fmt, ...)
{
va_list elem_list;
pid_t pid;
char szDateTime[20];

GetDateTime(szDateTime, 20);
pid = getpid();

fprintf(stdout, "[%s] pid[%d] file[%s] line[%d] ", szDateTime, pid, pszFile, nLine);

va_start(elem_list, fmt);
vfprintf(stdout, fmt, elem_list);
va_end(elem_list);

fflush(stdout);

return 0;
}

SQLDA * AllocSelectDp(char * pszSelectSql)
{
SQLDA * select_dp = NULL;
int i = 0;
int j = 0;
int null_ok = 0;
int precision = 0;
int scale = 0;
int nFields = 1024; /*定义最大字段数*/
size_t nPerFetchCnts = MAX_PER_FETCH_ROWS;

EXEC SQL BEGIN DECLARE SECTION;
varchar szSqlStmt[CMDSQLLENGTH+2];
EXEC SQL END DECLARE SECTION;

sprintf((char*)szSqlStmt.arr,"%s",pszSelectSql);
szSqlStmt.len=strlen(pszSelectSql);
szSqlStmt.arr[szSqlStmt.len]='';

/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.

外部类型 CODE C 语言类型
VARCHAR2 1 char[n]
NUMBER 2 char[n] ( n <= 22)
INTEGER 3 int
FLOAT 4 float
STRING 5 char[n+1]
VARNUM 6 char[n] (n <= 22)
DECIMAL 7 float
LONG 8 char[n]
VARCHAR 9 char[n+2]
ROWID 11 char[n]
DATE 12 char[n]
VARRAW 15 char[n]
RAW 23 unsigned char[n]
LONG RAW 24 unsigned char[n]
UNSIGNED 68 unsigned int
DISPLAY 91 char[n]
LONG VARCHAR 94 char[n+4]
LONG VARRAW 95 unsigned char[n+4]
CHAR 96 char[n]
CHARF 96 char[n]
CHARZ 97 char[n+1]
*/

EXEC SQL WHENEVER SQLERROR GOTO sqlerr;
EXEC SQL PREPARE S FROM :szSqlStmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN))== NULL )
{
WriteLog(LOG_MARK, "Cannot allocate memory for select descriptor.errmsg[%s]n", strerror(errno));
return NULL;
}

select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F )
{
sqlclu( select_dp );
return NULL;
}

if (select_dp->F < 0)
{/*MAX_ITEMS比sql语句中字段数少，select_dp->F为实际字段数的负数,根据这个值重新初始化select_dp*/
nFields = -(select_dp->F);
sqlclu( select_dp );
if ((select_dp = sqlald (nFields, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
{
WriteLog(LOG_MARK, "Cannot allocate memory for descriptor.errmsg[%s]n", strerror(errno));
return NULL;
}
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

/*为每列指示器份配nPerFetchCnts个空间(每次fetch取nPerFetchCnts条记录)*/
for (i = 0; i < select_dp->N; i++)
{
select_dp->I[i] = (short *) malloc(sizeof(short) * nPerFetchCnts);
}

for (i = 0; i < select_dp->F; i++)
{
sqlnul ((unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok);

// if (select_dp->T[i]==2)
// {
// SQLNumberPrecV6(0,(unsigned int *)&(select_dp->L[i]), &precision,&scale);
// if (precision == 0) precision = 40;
// printf("%4d;%4d;%4d;%4d---%.*sn",select_dp->T[i],select_dp->L[i],precision,scale,select_dp->C[i],select_dp->S[i]);
// }
// else
// printf("%4d;%4d---%.*sn",select_dp->T[i],select_dp->L[i],select_dp->C[i],select_dp->S[i]);

switch (select_dp->T[i])
{
case 1 :
select_dp->L[i] += 2;
break;
case 2 : /*NUMBER 类型*/
SQLNumberPrecV6(0,(unsigned int *)&(select_dp->L[i]), &precision,&scale);
if (precision == 0) precision = 40;
select_dp->L[i] = precision + 2;
if (scale < 0) select_dp->L[i] += -scale;
break;
case 8 : /* LONG 类型*/
select_dp->L[i] = 240;
break;
case 11 : /* ROWID 类型 */
select_dp->L[i] = 18;
break;
case 12 : /* DATE 类型*/
select_dp->L[i] = 9;
break;
case 23 : /* RAW 类型 */
break;
case 24 : /* LONG RAW 类型 */
select_dp->L[i] = 240;
break;
default:
select_dp->L[i] += 2;
break;
}

/*将字段类型设为STRING 5 char[n+1]*/
select_dp->T[i] = 5;
/*每个字段分配nPerFetchCnts个指定长度的空间(每次fetch取nPerFetchCnts条记录)*/
select_dp->V[i] = (char *)malloc(( select_dp->L[i]) * nPerFetchCnts );
}

EXEC SQL OPEN C;
return select_dp;

sqlerr:
WriteLog(LOG_MARK, "分配 AllocSelectDp 错误[%d][%s]n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return NULL;
}

int ExportData(char* pszSelectSql, char* pszDataDir, char* pszDelimiter)
{
SQLDA* select_dp = NULL;
size_t nLastFetchCount = 0;
size_t nRowCounts = 0;
short ind_value = 0;
char* char_ptr = NULL;
char* pszFieldOut = NULL;
char szDataFile_ok[MAX_PATH];
char szDataFile_tmp[MAX_PATH];
int file_count = 0;
size_t file_max_linecount = 100000;
size_t file_linecount = 0;
int i = 0;
int j = 0;
int nStatus = 0;

/*把输出字段合并为行*/
char * pszLineBuf,*pszFieldPos;
int linesize=0;

/*io缓存，设置为 5 MB(IO_FBUF常量)*/
char* pszIOBuf;
FILE* pfDatafile;

EXEC SQL BEGIN DECLARE SECTION;
int nPerFetchCnts;
EXEC SQL END DECLARE SECTION;
nPerFetchCnts = MAX_PER_FETCH_ROWS;

/*分配 select descriptor*/
select_dp = AllocSelectDp(pszSelectSql);
if ( NULL == select_dp )
{
return RETERROR;
}

/*计算行宽度，并分配行缓存*/
for (i = 0; i < select_dp->F; i++)
{
linesize += select_dp->L[i]-2;
}
file_max_linecount = ((float)FILE_MAX_KB)/(linesize+1)*1024-1;
printf("行缓存长度%d,文件记录限制%d行。n",linesize,file_max_linecount);
pszLineBuf = (char*)malloc(linesize+1);
if ( NULL == pszLineBuf )
{
WriteLog(LOG_MARK, "无法分配行缓存. errmsg[%s]n", strerror(errno));
return RETERROR;
}

/*分配 io 缓存*/
pszIOBuf = (char*)malloc(IO_FBUF);
if ( NULL == pszIOBuf )
{
WriteLog(LOG_MARK, "无法分配内存. errmsg[%s]n", strerror(errno));
return RETERROR;
}

/*创建数据文件，并关联io缓存*/
file_count=0;
sprintf(szDataFile_tmp, "%s/%s%0*d.%s", pszDataDir,FNAME,SEQ_NUM_WIDTH,SEQ_NUM_FIRST+file_count,FEXT_TMP);
file_count+=1;

pfDatafile = fopen64(szDataFile_tmp, "w+");
if ( NULL == pfDatafile )
{
WriteLog(LOG_MARK, "打开数据文件[%s]，错误[%s]n", szDataFile_tmp, strerror(errno));
return RETERROR;
}
nStatus = setvbuf(pfDatafile, pszIOBuf, _IOFBF, IO_FBUF);
if ( 0 != nStatus )
{
WriteLog(LOG_MARK, "设置io缓存错误[%s]n", strerror(errno));
return RETERROR;
}

/*导出数据*/
file_linecount = file_max_linecount;

WriteLog(LOG_MARK,"开始导出数据.n");
for ( nLastFetchCount = 0;;nLastFetchCount = sqlca.sqlerrd[2] )
{
/*一次fetch多条记录,每列为一个数组*/
EXEC SQL FOR nPerFetchCnts FETCH C USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-nLastFetchCount; j++ )
{
nRowCounts++;
if ( nRowCounts%1000000 == 0 )
{
WriteLog(LOG_MARK, "已经抽取记录数[%ld]n", nRowCounts);
}

if(file_linecount<=0)
{/*数据文件大小已达到限制值*/
/*关闭数据文件*/
fclose(pfDatafile);

/*创建新的数据文件*/
sprintf(szDataFile_tmp, "%s/%s%0*d.%s", pszDataDir,FNAME,SEQ_NUM_WIDTH,SEQ_NUM_FIRST+file_count,FEXT_TMP);
file_count+=1;

file_linecount = file_max_linecount;
}
file_linecount--;

memset(pszLineBuf,' ',sizeof(pszLineBuf));
pszLineBuf[sizeof(pszLineBuf)-1]='';
pszFieldPos = pszLineBuf;
/*将字段格式化为定长行（将来须考虑替换字段中的回车和换行符）*/
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
pszFieldOut = select_dp->V[i] + (j*select_dp->L[i]);
sprintf(pszFieldPos,"%-*s",select_dp->L[i]-2,pszFieldOut);
pszFieldPos += select_dp->L[i]-2;
}
*pszFieldPos = '';
fprintf(pfDatafile,"%sn",pszLineBuf);
}
if ( sqlca.sqlcode > 0 )
{
break;
}
}

/*关闭数据文件*/
fclose(pfDatafile);

/*释放缓存*/
free(pszLineBuf);
free(pszIOBuf);

sqlclu(select_dp);

EXEC SQL CLOSE C;

/*将数据文件改为正式名*/
char szCmd[1024];
while(file_count>0)
{
file_count--;
sprintf(szDataFile_tmp, "%s/%s%0*d.%s", pszDataDir,FNAME,SEQ_NUM_WIDTH,SEQ_NUM_FIRST+file_count,FEXT_TMP);
sprintf(szDataFile_ok, "%s/%s%0*d.%s", pszDataDir,FNAME,SEQ_NUM_WIDTH,SEQ_NUM_FIRST+file_count,FEXT);
sprintf(szCmd,"mv %s %s",szDataFile_tmp,szDataFile_ok);
if(system(szCmd))
{
WriteLog(LOG_MARK, "文件%s改名失败!n", szDataFile_tmp);
return RETERROR;
}
}

sqlerr:

if ( 1403 == sqlca.sqlcode || -1403 == sqlca.sqlcode || 0 == sqlca.sqlcode )
{
WriteLog(LOG_MARK, "抽取完成，记录数[%ld]n", nRowCounts);
return RETSUCCESS;
}

WriteLog(LOG_MARK, "导出数据错误[%d][%s]n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return sqlca.sqlcode;
}

void Killed(int no)
{
int nSqlCode = 0;

WriteLog(LOG_MARK, "被强行中止%sn", " ");

nSqlCode = DisConnectDatabase();
if ( 0 != nSqlCode )
{
WriteLog(LOG_MARK, "断开数据库失败错误号[%d]n", nSqlCode);
}
exit(RETERROR);
}

static char* ReadSelectSqlFile(char* pszFileName,char* pszSelectSql)
{
char tempStr[1024+1];
FILE *intsqlfile=NULL;
size_t len;

char tempSqlStr[3*CMDSQLLENGTH]; /*考虑文件中有注释需去掉*/

memset(tempStr, 0, sizeof(tempStr));
memset(pszSelectSql, 0, sizeof(pszSelectSql));
memset(tempSqlStr, 0, sizeof(tempSqlStr));

intsqlfile=fopen(pszFileName, "r");
if (NULL == intsqlfile )
{
WriteLog(LOG_MARK, "打开sql文件[%s]，错误[%s]n", pszFileName, strerror(errno));
return NULL;
}
while(NULL!=fgets(tempStr,1024,intsqlfile))
{
strcat(tempSqlStr,tempStr);
}
fclose(intsqlfile);

/*删除SQL语句结尾的分号*/
int i=0,j=0,k=0;
for(i=strlen(tempSqlStr)-1;i>=0;i--)
{
if(tempSqlStr[i]==';')
{
tempSqlStr[i]=' ';
break;
}else if(tempSqlStr[i]>32)
{
break;
}
}
/*屏蔽原SQL文件中的set命令*/
for(i=0;i {
if(tempSqlStr[i]==' '||tempSqlStr[i]==9)
{
continue;
}
else if((tempSqlStr[i]=='s'||tempSqlStr[i]=='S')
&&(tempSqlStr[i+1]=='e'||tempSqlStr[i+1]=='E')
&&(tempSqlStr[i+2]=='t'||tempSqlStr[i+2]=='T')
&&(tempSqlStr[i+3]==' '||tempSqlStr[i+3]==9)
)
{
tempSqlStr[i]='-';
tempSqlStr[i+1]='-';
i+=4;
}
/*本行非set命令,跳到本行结尾*/
for(j=i;j {
if(tempSqlStr[j]=='n')
{
/*printf("跳过字符数%dn",j-i);*/
break;
}
}
i=j;
}

/*排除注释(没有考虑注释符号发生在字符串中)，复制到pszSelectSql*/
i=0;
j=0;
k=0;

for(i=0;i {
if(tempSqlStr[i]=='/' && tempSqlStr[i+1]=='*')
{
/*跳到下一个反注释后*/
for(j=i+2;j {
if(tempSqlStr[j]=='*' && tempSqlStr[j+1]=='/')
{
i=j+1;
/*插入一个空格*/
pszSelectSql[k]=' ';
k+=1;
break;
}
}
continue;
}if(tempSqlStr[i]=='-' && tempSqlStr[i+1]=='-')
{
/*跳到下一行*/
for(j=i+2;j {
if(tempSqlStr[j]=='n')
{
i=j-1; /*以便下一循环插入回车符*/
break;
}
}
continue;
}
pszSelectSql[k]=tempSqlStr[i];
k+=1;
}
pszSelectSql[k]='';

/*排除行尾空格*/
strcpy(tempSqlStr,pszSelectSql);
k=0;
for(i=0;i {
if(tempSqlStr[i]==' ')
{
for(j=i+1;j {
if(tempSqlStr[j]!=' ')
{
if(tempSqlStr[j]=='n')
{
i=j-1;
}else{
/*补传这部分空格(j之前),j处的字符在下一循环检查*/
for(;i {
pszSelectSql[k]=tempSqlStr[i];
k+=1;
}
i-=1;
}
break;
}
}
continue;
}
pszSelectSql[k]=tempSqlStr[i];
k+=1;
}
pszSelectSql[k]='';

if(DISPLAY_SQL) printf("n%sn",pszSelectSql);

return pszSelectSql;
}

make.sh
pcfile_name=$1
if [ ! -f ${pcfile_name}.pc ];then
echo "not found pc file ${pcfile_name}.pc"
exit 12
fi
rm ./${pcfile_name} ./${pcfile_name}.cpp ./${pcfile_name}.lis

proc code=cpp sqlcheck=full userid=test/test@test INCLUDE=/usr/include iname=${pcfile_name}.pc oname=${pcfile_name}.cpp

[ $? -ne 0 ] && exit 12
cc -c -I. -I${ORACLE_HOME}/precomp/public ${pcfile_name}.cpp

[ $? -ne 0 ] && exit 12
cc -g -o ${pcfile_name} ${pcfile_name}.o -I. -L${ORACLE_HOME}/lib32 -lclntsh `cat ${ORACLE_HOME}/lib32/sysliblist` -lm -lpthread

exit

rm ./expdata ./main.c ./main.lis

proc sqlcheck=full userid=test/test@test INCLUDE=/usr/include iname=main.pc

cc -q32 -o expdata main.c -g -I${ORACLE_HOME}/precomp/public -L${ORACLE_HOME}/lib32 `cat ${ORACLE_HOME}/lib32/ldflags` `cat ${ORACLE_HOME}/lib32/sysliblist` -lm -lpthread -lclntsh

#cc -q32 -DDEBUG -o expdata main.c -g -I${ORACLE_HOME}/precomp/public -L${ORACLE_HOME}/lib32 `cat ${ORACLE_HOME}/lib32/ldflags` `cat ${ORACLE_HOME}/lib32/sysliblist` -lm -lpthread -lclntsh

#[ $? -ne 0 ] && exit 12
#mv ${pcfile_name}.c ${pcfile_name}.cpp
#cc -c -DDEBUG -qcpluscmt -I. -I${ORACLE_HOME}/precomp/public -I${ORACLE_HOME}/rdbms/public -I${ORACLE_HOME}/rdbms/demo -I${ORACLE_HOME}/plsql/public -I${ORACLE_HOME}/network/public ${pcfile_name}.cpp

exp_hxl_sql.sh
#!/usr/bin/sh
######################################################################
######################################################################
# 参数 1 = 用户名
# 参数 2 = 表名
# 参数 3 = where子句(可选)

. ${HOME}/.testconfig
loghw_hxl=test/test#@test

if [ $# -ne 3 ];then
echo "Usage: exp_sql.sh 用户名文件名 SQL"
exit -10
fi

userid=$1
data_path="/test/test/test/"
filename=${2}_
seq_width=3
size_limit=500000
field_dmt=" "
sql_smt="$3"

#根据用户名获得登录串
eval logstr=${log${userid}}
if [ -z "${logstr}" ];then
logstr="${loghwfbi}"
fi

infile=/tmp/exp_sqlfile$$.sql

echo "$sql_smt" > ${infile}

exp_pro_path="/test/test/test/proc" #`dirname $0`

if ! [ -f ${infile} ];then
echo "找不到接口查询语句文件:${infile}"
exit -10
fi

if [ ${size_limit} -lt 1000 ];then
size_limit=30000
fi
export FILE_MAX_KB=${size_limit}
export SEQ_NUM_WIDTH=${seq_width}
export SEQ_NUM_FIRST=1
export FNAME="${filename}"
export FEXT_TMP="ttt"
export FEXT="dat"
export EXPPATH="${data_path}"
export LOGSTR="${logstr}"
export SQLFILE="${infile}"
export DISPLAY_SQL=1
export FIELD_DELIMITER="${field_dmt}" #字段分隔符，如果为空则定长输出，否则以此分隔符分隔
export DATE_FORMAT="YYYYMMDD"

#删除符合本次输出格式的文件
if [ -f ${EXPPATH}/${FNAME}*.${FEXT_TMP} ];then
rm -f ${EXPPATH}/${FNAME}*.${FEXT_TMP}
fi
if [ -f ${EXPPATH}/${FNAME}*.${FEXT} ];then
rm -f ${EXPPATH}/${FNAME}*.${FEXT}
fi

${exp_pro_path}/exp_ora

sqlretval=$?

if [ ${sqlretval} -ne 0 ] ; then
echo "导出数据报错,错误代码:${sqlretval}!!"
exit ${sqlretval}
fi
rm ${infile}

1.编译
make.sh exp_test.pc
2.导出数据
exp_hxl_sql.sh "hw_hxl" "aa" "select * from hw_hxl.tp_hxl_08_kb_user_cp"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/77311/viewspace-1020399/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/77311/viewspace-1020399/