📄 unloadfun.ec
字号:
#include <stdio.h>
EXEC SQL include sqlca;
EXEC SQL include sqlda;
EXEC SQL include sqltypes;
EXEC SQL include decimal;
EXEC SQL include datetime;
#define FILENUM 6000000
#define FLDSIZE 40
#define _DEBUG_0225 1
#undef _DEBUG_0225
/*static void prtlongsql(char *sSql);*/
/*static void rtrim(char* str);*/
/* This program handles ONLY the following data types:
*
* SMALLFLOAT FLOAT MONEY
* DECIMAL CHAR VARCHAR
* SMALLINT INT DATE
*
* It does not handle any other SQL data type, nor does it
* handle nulls. (Adding such support is easy.)
*/
int has_trans; /* True if DB has transactions. */
/*main()
{
EXEC SQL CONNECT TO 'reserve';
has_trans = ( sqlca.sqlwarn.sqlwarn1 == 'W' );
if(FunUnload("select * from end_trans","cust.unl","w"))
printf("error");
}*/
/**************************************************************************
FunctionName : int fun_unload_data(nargs)
Description : 和4gl进行连接的函数
InputParameter : 传入参数个数
OutputParameter : 处理是否成功标志(0:成功,-1:失败),处理记录数
Created : 2005/07/07
Author : 周锦标
Last modified :
Version :
Updated :
Updated by :
**************************************************************************/
int fun_unload_data(nargs)
int nargs;
{
EXEC SQL BEGIN DECLARE SECTION;
char SelectSql[2048];/*存放需要进行传出的sql语句*/
EXEC SQL END DECLARE SECTION;
char L_FilePath[256];/*存放传出文件的路径和文件名称*/
char L_FileName[FLDSIZE+1];
char L_OpenType[2];/*文件打开的方式*/
int L_total;
int l_ret=0;
if (nargs!=5)/*如果传参不是5个就报错误*/
{
retquote("");
retint(0);
retint(-1);
return(3);
}
has_trans = ( sqlca.sqlwarn.sqlwarn1 == 'W' );
/*获取4gl调用函数传来的参数*/
memset(&L_OpenType,'\0',sizeof(L_OpenType));
memset(&L_FileName,'\0',sizeof(L_FileName));
memset(&L_FilePath,'\0',sizeof(L_FilePath));
memset(&SelectSql,'\0',sizeof(SelectSql));
popvchar(L_OpenType,2);
rtrim(L_OpenType);
popint(&L_total);
popquote(L_FileName,41);
rtrim(L_FileName);
popquote(L_FilePath,256);
rtrim(L_FilePath);
popquote(SelectSql,2048);
rtrim(SelectSql);
if(FunUnload(SelectSql,L_FilePath,L_FileName,L_OpenType,&L_total))
{
retquote(L_FileName);
retint(0);
retint(-1);
return(3);
}
retquote(L_FileName);
retint(L_total);
retint(0);
return(3);
}
/**************************************************************************
FunctionName : FunUnload (SelectSql, FilePath,FileName,opentype,L_total)
Description : 进行处理下载数据的函数
InputParameter : 处理sql,数据写入文件存放路径,数据写入文件名称,文件打开方式,处理记录数
OutputParameter : 处理是否成功标志(0:成功,-1:失败)
Created : 2005/07/07
Author : 周锦标
Last modified :
Version :
Updated :
Updated by :
**************************************************************************/
int FunUnload(SelectSql, FilePath,FileName,opentype,L_total)
EXEC SQL BEGIN DECLARE SECTION;
char *SelectSql;
EXEC SQL END DECLARE SECTION;
char *FileName;
char *FilePath;
char *opentype;
int *L_total;
{
register int pos;
register char *cp;
register int len;
register int i;
int valdate;
static int NameNum;
double valdec;
char field[FLDSIZE+1];
char *fieldp, *fieldq;
struct sqlda *udesc;
struct sqlvar_struct *col;
char delim = '|';
char *buffer = NULL;
char *malloc();
char FileDir[320];
FILE *unlfile;
#ifdef _DEBUG_0225
prtlongsql(SelectSql);
#endif
EXEC SQL prepare usqlobj from :SelectSql;
if ( sqlca.sqlcode != 0 )
return -1;
EXEC SQL describe usqlobj into udesc;
if ( sqlca.sqlcode != 0 )
return -1;
if (*L_total==0)
{
NameNum = 0;
memset(&FileDir,'\0',sizeof(FileDir));
strcpy(FileDir,FilePath);
strcat(FileDir,FileName);
unlfile = fopen(FileDir,opentype);
if ( unlfile == NULL )
return -1 ;
}
/* Step 1: analyze udesc to determine type and memory requirements
* of each item in the select list. rtypalign() returns a
* pointer to the next appropriate boundary (starting at
* pos) for the indicated data type.
*/
pos = 0;
/*L_total = 0;*/
for (col = udesc->sqlvar, i = 0; i < udesc->sqld; col++, i++)
{
switch(col->sqltype)
{
/*case SQLDTIME:
col->sqltype = CDTIMETYPE;
break;*/
case SQLDATE:
col->sqltype = CDATETYPE;
break;
case SQLSMINT:
/*col->sqltype = CSHORTTYPE;
break;*/
case SQLSERIAL:
case SQLINT:
col->sqltype = CINTTYPE;
break;
case SQLSMFLOAT:
col->sqltype = CFLOATTYPE;
break;
case SQLFLOAT:
col->sqltype = CDOUBLETYPE;
break;
case SQLMONEY:
case SQLDECIMAL:
col->sqltype = CDECIMALTYPE;
break;
case SQLCHAR:
col->sqltype = CCHARTYPE;
break;
case SQLVCHAR:
col->sqltype = CVCHARTYPE;
break;
default:
/* The program does not handle INTEGER,
* SMALL INTEGER, DATE, SERIAL or other
* data types. Do nothing if we see
* an unsupported type.
*/
return -1;
}
col->sqllen = rtypmsize(col->sqltype,col->sqllen);
pos = rtypalign( pos, col->sqltype) + col->sqllen;
col->sqlind = NULL;
}
/* Step 2: Allocate memory to receive a row of the table returned
* by the SELECT statement. The variable pos has an integer
* value equal to the number of bytes occupied by the row.
*/
buffer = malloc(pos);
if ( buffer == NULL )
{
fclose(unlfile);
return -1;
}
/* Step 3: Set pointers in the allocated memory to receive each
* item in the select list.
*/
cp = buffer;
for (col = udesc->sqlvar, i = 0; i < udesc->sqld; col++, i++)
{
cp = (char *) rtypalign( (long) cp, col->sqltype);
col->sqldata = cp;
cp += col->sqllen;
}
/* Step 4: Fetch each row of the query, convert to ASCII format, and
* write to the output file.
*/
EXEC SQL declare usqlcurs cursor for usqlobj;
EXEC SQL open usqlcurs;
EXEC SQL fetch usqlcurs using descriptor udesc;
while (sqlca.sqlcode == 0 )
{
for (col=udesc->sqlvar, i = 0; i < udesc->sqld; col++, i++)
{
byfill(field,FLDSIZE+1,0);
fieldp = field;
switch (col->sqltype)
{
/*case CDTIMETYPE:
dttoasc((dtime_t *) col->sqldata,field);
dttofmtasc((dtime_t *) col->sqldata,field,FLDSIZE,"%Y-%m-%d %H:%M:%S");
break;*/
case CDATETYPE:
valdate = *((int *) (col->sqldata));
rfmtdate(valdate, "yyyy/mm/dd", field);
break;
case CSHORTTYPE:
sprintf(field,"%d", *((int *) (col->sqldata)));
break;
case CINTTYPE:
sprintf(field,"%ld", *((long *) (col->sqldata)));
break;
case CFLOATTYPE:
sprintf(field,"%.2lf",(double) *((float *) (col->sqldata)));
break;
case CDOUBLETYPE:
sprintf(field,"%.2lf",*((double *) (col->sqldata)));
break;
case CDECIMALTYPE:
dectoasc( (dec_t *) col->sqldata, field, FLDSIZE,-1);
/*rstod(field, &valdec);
sprintf(field,"%.2lf", valdec);*/
break;
case CCHARTYPE:
fieldp = col->sqldata;
break;
case CVCHARTYPE:
fieldp = col->sqldata;
break;
default:
/* Usupported data type. */
break;
}
len = stleng(fieldp);
cp = fieldp + len - 1;
while ( len > 1 && *cp == ' ' )
{
*cp = '\0';
len--, cp--;
}
if((risnull(CCHARTYPE,(char *)fieldp)==0)&&(strcmp(fieldp,"-nan(7ffffffffffff)")!=0)&&((strcmp(fieldp," ")!=0)))
fputs(fieldp,unlfile);
putc(delim,unlfile);
}
putc('\n',unlfile);
if ( (++*L_total % 10000) == 1)
{
printf("%c[24;1H",27);
printf("已经传出了%d条记录,请等待......",*L_total);
}
if (((*L_total % FILENUM) == 1)&&(*L_total>1))
{
fclose(unlfile);
/*system();*/
if(FunNewFileName(FileDir,FilePath,FileName,&NameNum)<0)
return(-1);
unlfile = fopen(FileDir,opentype);
if ( unlfile == NULL )
return -1 ;
}
EXEC SQL fetch usqlcurs using descriptor udesc;
}
fclose(unlfile);
free(buffer);
if ( sqlca.sqlcode && sqlca.sqlcode != SQLNOTFOUND )
{
EXEC SQL close usqlcurs;
EXEC SQL free usqlcurs;
EXEC SQL free usqlobj;
return -1;
}
EXEC SQL close usqlcurs;
EXEC SQL free usqlcurs;
EXEC SQL free usqlobj;
/*printf("%c[24;1H",27);
printf("总共传出了%d条记录,请按任意键返回",L_total);
getchar();*/
return 0;
}
int FunNewFileName(FileDir,FilePath,FileName,PNum)
char *FileDir;
char *FilePath;
char *FileName;
int *PNum;
{
char L_tmpStr[10];
char L_Name[FLDSIZE+1];
int L_i,L_j;
int L_length;
memset(&L_tmpStr,'\0',sizeof(L_tmpStr));
memset(&L_Name,'\0',sizeof(L_Name));
strcpy(FileDir,FilePath);
++*PNum;
sprintf(L_tmpStr,"%d",*PNum);
if(*PNum==1)
{
strcpy(L_Name,FileName);
strcat(L_Name,"_");
}
else if(*PNum>1)
{ L_length = strlen(FileName);
L_length = L_length - 1;
for(L_i=L_length;L_i>1;L_i--)
{
if(FileName[L_i]=='_')
break;
}
rsetnull(CCHARTYPE,(char*)&L_Name);
for(L_j=0;L_j<=L_i;L_j++)
L_Name[L_j]=FileName[L_j];
}
strcat(L_Name,L_tmpStr);
rtrim(L_Name);
strcpy(FileName,L_Name);
strcat(FileDir,FileName);
return(0);
}
/**************************************************************************
FunctionName : void prtlongsql(char *sSql)
Description : 打印长字符串
InputParameter : 需要处理字符串的指针
OutputParameter : 在屏幕上显示
Created : 2004/12/27
Author : 周锦标
Last modified :
Version :
Updated :
Updated by :
**************************************************************************/
/*void prtlongsql(char *sSql)
{
register int i=0,j=0;
char str[81]="";
int len=strlen(sSql);
printf("\nSql:\n");
while(len>0)
{
strncpy(str,&sSql[i],80);
str[80]='\0';
printf("%s\n",str);
j=strlen(str);
len=len-j;
i=i+j;
}
}*/
/**************************************************************************
FunctionName : void rtrim(char* str)
Description : 去除右边的空格
InputParameter : 需要处理字符串的指针
OutputParameter : 处理后的字符串
Created : 2004/12/27
Author : 周锦标
Last modified :
Version :
Updated :
Updated by :
**************************************************************************/
/*void rtrim(char* str)
{
register short i;
for (i=strlen(str)-1;i>=0;i--)
if (!isspace(str[i]))
break;
str[++i]='\0';
}*/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -