quanzxx.sqc

本人写的在AIX下的用SQLC开发的处理DB2数据库中业务的程序

/*-------------------------------------------------------
** 程序名:quanzxx.sqc
** 程序员:
** 修改时间:
** 程序功能:
** 
-------------------------------------------------------*/


EXEC SQL INCLUDE SQLCA;

int main(int argc,char *argv[])
{
   time_t t_bgn,t_end;
   int t_use,t_hour,t_min,t_sec;
   int icntfetch,icntinsert,icntupdate,icnterror,iret,iper;
   FILE *fp;
   char logfile[30],showmsg[80];
   char xwdm1[9],xwdm2[9],xwdm3[9],xwdm4[9];

   EXEC SQL BEGIN DECLARE SECTION;
     /* 连接数据库所用的字段 */
     char  m_dbs[11];
     char  m_user[11];
     char  m_password[11];
     char  m_branch_code[4]; 

     /*KS.currency*/  
     char      m_currency_type[3];
     char      m_currency_name[11];
     double    m_forex_buy_price;
     double    m_forex_sell_price;
     double    m_oof_buy_price;
     char      m_unit_name[11];
     char      m_set_emp[7];
     char      m_set_date[9];
     char      m_set_time[9];

     char      m_hbdm[2];
     char      m_hbmc[7];
     double    m_xhmr;
     double    m_xhmc;
     double    m_xcmr;
     char      m_dw[5];
     char      m_zgdm[4];
     char      m_bdsj[17];
     char      m_qssrbz[2];

   EXEC SQL END   DECLARE SECTION;
       
   EXEC SQL WHENEVER NOT FOUND  CONTINUE;
   EXEC SQL WHENEVER SQLWARNING CONTINUE;
   EXEC SQL WHENEVER SQLERROR   CONTINUE;
       

   if ( argc != 5 && argc != 4 )
    { printf("ERROR:Parameter is less than 3!\n");
      printf("Usage: %s dbs user password schema\n",argv[0]);
      return 1;
    }

  strcpy(m_dbs,argv[1]);
  strcpy(m_user,argv[2]);
  strcpy(m_password,argv[3]);

  if (argc == 5)
     strcpy(m_branch_code,argv[4]);
  else 
    strcpy(m_branch_code," ");
  
  icntfetch=0;
  icntinsert=0;
  icntupdate=0;
  icnterror=0;
  iret=0;
  iper=0;


  strcpy(logfile,"data/");
  strcat(logfile,argv[0]);
  /*strcat(logfile,".txt");*/	

    if ( ( fp=fopen(logfile,"w+")) == NULL )
   { printf("Error:Open log file %s error!\n",logfile);
     return 2;
   }

  strcpy(showmsg,"货币代码(hbdm)<-(currency)");
  printf("----------------%s:转换开始--------------\n",showmsg);
  t_bgn=time(NULL);
  EXEC SQL CONNECT TO :m_dbs USER :m_user USING :m_password;
  if ( sqlca.sqlcode != 0 )
   { printf("ERROR:Fail to connect to %s!\n",m_dbs);
     return 3;
   }

 
        
   EXEC SQL DECLARE c1 CURSOR WITH HOLD FOR
   	select CURRENCY_TYPE,
              CURRENCY_NAME,
              FOREX_BUY_PRICE,
              FOREX_SELL_PRICE,
              OOF_BUY_PRICE,
              UNIT_NAME,
              SET_EMP,
              SET_DATE,
              SET_TIME

	from KS.CURRENCY
       order by currency_type ;
                       
   EXEC SQL OPEN c1;
   while(1)
   {
   EXEC SQL FETCH c1 INTO   :m_currency_type,
                            :m_currency_name,
                            :m_forex_buy_price,
                            :m_forex_sell_price,
                            :m_oof_buy_price,
                            :m_unit_name,
                            :m_set_emp,
                            :m_set_date,
                            :m_set_time;

   if (SQLCODE) break;
   
      icntfetch++;

	memset(m_hbdm  ,0x0,sizeof(m_hbdm  ));
	memset(m_hbmc  ,0x0,sizeof(m_hbmc  ));
        m_xhmr=0;
        m_xhmc=0;
        m_xcmr=0;
	memset(m_dw  ,0x0,sizeof(m_dw  ));
        memset(m_zgdm  ,0x0,sizeof(m_zgdm  ));
        memset(m_bdsj  ,0x0,sizeof(m_bdsj  ));
        memset(m_qssrbz  ,0x0,sizeof(m_qssrbz  ));
        
        
       /*以下进行数据转换赋值*/ 
       strncpy(m_hbdm,m_currency_type+1,1);
       strncpy(m_hbmc,m_currency_name,6); 
       m_xhmr=m_forex_buy_price;     
       m_xhmc=m_forex_sell_price;
       m_xcmr=m_oof_buy_price;

       strncpy(m_dw,m_unit_name,4); 
       strncpy(m_hbmc,m_currency_name,6); 
       strncpy(m_zgdm,m_set_emp+3,3); 
	sprintf(m_bdsj,"%8s%8s",m_set_date,m_set_time);
	sprintf(m_qssrbz,"%1d",icntfetch);       		

	fprintf(fp,"%s~",m_hbdm      );
	fprintf(fp,"%s~",m_hbmc      );
	fprintf(fp,"%.4f ~",m_xhmr      );
	fprintf(fp,"%.4f ~",m_xhmc      );
	fprintf(fp,"%.4f ~",m_xcmr      );
	fprintf(fp,"%s~",m_dw        );
	fprintf(fp,"%s~",m_zgdm       );
	fprintf(fp,"%s~",m_bdsj      );
	fprintf(fp,"%s",m_qssrbz      );
	fprintf(fp,"%c%c", 0x0D,0x0A   );
                            
      icntinsert++;         
                            
      if ( icntfetch % COMMITROWS == 0 )
       { if ( iper == 0 )   
           printf("%s: %d OK\n",showmsg,icntfetch);
         else               
           printf("%s: %d %d%% FAILED!\n",showmsg,icntfetch,iper);
         iper=0;            
       }

   }
   EXEC SQL CLOSE c1;
   fclose(fp);

   EXEC SQL CONNECT RESET;
   printf("*************%s:数据转换结束***************\n",showmsg);
   printf("共从旧表中取出%d条记录,共生成%d条新记录,有%d条错误!\n",
        icntfetch,icntinsert,icnterror);
   t_end=time(NULL);
   t_use=t_end - t_bgn;
   t_sec=t_use % 60;
   t_min=t_use / 60;
   t_min=t_min % 60;
   t_hour=t_use/ 3600;
/*   printf("处理共花时[%02d:%02d:%02d],每秒处理%d条记录\n",
           t_hour,t_min,t_sec,icntfetch/t_use);
*/   exit(0);
}