sybaseproxy.c

在UNIX下使用DBLIB连接SYBASE数据库有重连

#include "stdio.h"
#include "string.h"
#include <sybfront.h>
#include "sybdb.h"
#include <syberror.h>
#include <sybdbex.h>
#include <stdlib.h>
#include <time.h>
#include "jhinilib.h"       // Jihang in libjhinilib.a
#define  ERR_CH    stderr   // Sybase error messages
#define  IMAGESIZE "6000000"
#define  MAX_COLUMN    10    //程序中可以处理的总列数
#define  MAX_ALTCOLUMN 10    //程序中可以处理的计算列的总列
#define  MAX_COLSIZE   255   //程序中可以处理的一列的最大长度

//for sybase values
char RowBuffer[MAX_COLUMN][MAX_COLSIZE];
char AltBuffer[MAX_ALTCOLUMN][MAX_COLSIZE];
char S_DbName[255] , S_ServerName[255] , S_UserName[255] , S_PassWord[255]  ;
//char S_DbName2[255] , S_ServerName2[255] , S_UserName2[255] , S_PassWord2[255]  ;
int  S_DbNumber;
void error PROTOTYPE((char *, char *));       /* print error message and die *///DBPROCESS    *dbproc;            /* connection to sybase for update, select, insert command. */
LOGINREC     *login;               /* login information.      */
DBTYPEINFO   *typeinfo;
DBBINARY     *image;
DBNUMERIC    *image1;
BYTE         *ret_p ;
RETCODE      result_code;
long         length;


char         sqlcmd[255];
char         tabular_tab[11][128];


//for system time and printf
FILE *fdbfdsyb1 ;
char S_Debug[255];
char ServerTime[8];
//for  system start...................
int IsdigitOrChar ( char digit )
{
 int  res ;

 res = 0 ;
 if ( ( digit >= 0x30 ) && ( digit <= 0x39 ) )  res = 1 ;
 if ( ( digit >= 0x41 ) && ( digit <= 0x5A ) )  res = 2 ;
 if ( ( digit >= 0x61 ) && ( digit <= 0x7A ) )  res = 3 ;

 return ( res ) ;
}
char  GetLocalDataTime()
{
        struct tm *ltm;
        long   ret_time;

        time(&ret_time);
        ltm=localtime(&ret_time);
        sprintf(ServerTime,"%02d:%02d:%02d->" , ltm->tm_hour, ltm->tm_min, ltm->tm_sec );
        ServerTime[8];
        return(ServerTime);
}

int  PrintfTraceFiles()
{
        char debugdev[255];
        memset ( debugdev , 0x0 , 255 ) ;
        sprintf( debugdev , "%s" , S_Debug ) ;
        fdbfdsyb1 = fopen( debugdev , "w" ) ;
        return(0);

}

int ParseSybaseMessage(char *mesg,int mtextlen)
{
 char tmpstr[128];
 int  p,i,f;

 i=p=0;
 f=1;
 memset(tmpstr,0,128);
 memset(tabular_tab,0,11*128);
 while (( mesg[i] != '\0') && ( i<= mtextlen ))
 {
   if ( (mesg[i] != ',') && (mesg[i] != '\0') )
   {
       tmpstr[p] = mesg[i];
       p++;
   }
   else
   {
       strcpy(tabular_tab[f-1],tmpstr);
       memset(tmpstr,0,128);
       f++;
       p=0;
   }
   i++;
 }
 strcpy(tabular_tab[f-1], tmpstr);
 return i;
}

//for sybase start...................

int msg_handler(dbproc, msgno, msgstate, severity, msgtext,
                srvname, procname, line)
DBPROCESS      *dbproc;
DBINT           msgno;
int             msgstate;
int             severity;
char           *msgtext;
char           *srvname;
char           *procname;
DBUSMALLINT     line;
{
    if (msgno != 5701)
    {
        fprintf (ERR_CH, "Msg %ld, Level %d, State %d\n",msgno, severity, msgstate);
        if (strlen(srvname) > 0)
            fprintf (ERR_CH, "Server '%s', ", srvname);
        if (strlen(procname) > 0)
            fprintf (ERR_CH, "Procedure '%s', ", procname);
        if (line > 0)
            fprintf (ERR_CH, "Line %d", line);
        fprintf (ERR_CH, "\n\t%s\n", msgtext);
    }
    return(0);
}
int err_handler(dbproc, severity, dberr, oserr, dberrstr, oserrstr)
DBPROCESS      *dbproc;
int             severity;
int             dberr;
int             oserr;
char           *dberrstr;
char           *oserrstr;
{
    if ((dbproc == (DBPROCESS *)NULL) || (DBDEAD(dbproc)))
        return(INT_CANCEL);
    else
    {
        fprintf (ERR_CH, "DB-Library error:\n\t%s\n", dberrstr);
        if (oserr != DBNOERR)
            fprintf (ERR_CH, "Operating-system error:\n\t%s\n", oserrstr);
        return(INT_CANCEL);
    }
}

void error(s1, s2)        //* print error message and die
char *s1, *s2;
{
    fprintf (ERR_CH, s1, s2);
    fprintf (ERR_CH, "\n");
    dbexit();
    exit(ERREXIT);
}

//*Init Sybase DB Messages..
int InitSybase(char *SybaseIniFileName , char *Field_Name)
{
  FILE *fp;
  int res=0;

  fp=(FILE * )JhOpenProfile(SybaseIniFileName);

  memset ( S_ServerName , 0x00 , 254 ) ;
  memset ( S_DbName     , 0x00 , 254 ) ;
  memset ( S_UserName   , 0x00 , 254 ) ;
  memset ( S_PassWord   , 0x00 , 254 ) ;
  memset ( S_Debug      , 0x00 , 254 ) ;
  JhReadProfileInt   ( fp , "SYB_DB_NUM" , "SYBDBNUM" , &S_DbNumber , 1 ) ;
  JhReadProfileString( fp , Field_Name , "DBSERVER" , S_ServerName , "SYBASE11" ) ;
  JhReadProfileString( fp , Field_Name , "Database" , S_DbName     , "db160"    ) ;
  JhReadProfileString( fp , Field_Name , "User"     , S_UserName   , "dbusr"    ) ;
  JhReadProfileString( fp , Field_Name , "Password" , S_PassWord   , "dbusrpwd" ) ;
  JhReadProfileString( fp , Field_Name , "DEBUG"    , S_Debug      ,  "/dev/tty12"   ) ;
  JhCloseProfile(fp);
  if (dbinit() == FAIL)
    {
        fprintf (ERR_CH,"Can't initialize SYBASE..dblib...\n");
        fclose(fdbfdsyb1);
        exit(ERREXIT);
    }
  //PrintfTraceFiles();
 // printf("S_DbNumber=%d\r\n",S_DbNumber);
  return ( S_DbNumber ) ;
}

DBPROCESS * LoginSybase(char * username , char * password , char * servername , char * databaseName)
{
DBPROCESS *re_login_dbproc ;
LOGINREC  *login_ptr;
DBPROCESS *conn_ptr;

  dberrhandle(err_handler);
 // dbmsghandle(msg_handler);

  login_ptr=dblogin();
  DBSETLUSER(login_ptr,username);
  DBSETLPWD (login_ptr,password);

  dbsetlogintime( 1 ) ; 
  re_login_dbproc = dbopen(login_ptr,servername);
  dbloginfree(login_ptr);

  if ( re_login_dbproc == NULL )
  {
      fprintf("fdbfdsyb1,SYBASE:%s Connect To SQL Server %s Fail !\r\n" , (char *)GetLocalDataTime(), servername ) ;
      dbexit();
      return(NULL);
  }

  if ( dbuse(re_login_dbproc,databaseName) == FAIL )
  {
  dbclose( re_login_dbproc ) ;
  fprintf(fdbfdsyb1,"SYBASE:%s Database %s not in SQL Server\r\n",(char *)GetLocalDataTime(),S_DbName);
  dbexit();
  return(NULL);
  }
  fprintf(fdbfdsyb1,"SYBASE:%s Connect SYBASE SQL Server %s OK \r\n",(char *)GetLocalDataTime(),servername ) ;
 return(re_login_dbproc);
}

DBPROCESS  OpenSybaseDB()
{
  DBPROCESS *  in_dbproc;
   while(1){   	
    in_dbproc = ( DBPROCESS * ) LoginSybase( S_UserName , S_PassWord , S_ServerName , S_DbName ) ;
    if ( in_dbproc == NULL )
       {
        fprintf(fdbfdsyb1,"SYBASE:%s After 10 seconds Will Retry Login SYBASE...\r\n",GetLocalDataTime()) ;
        dbexit();
        sleep( 10 ) ;
        continue ;
       }//if
    else
        break;
    }//while
    dbsetopt(in_dbproc, DBTEXTSIZE, IMAGESIZE, -1);
    dbsettimeout(in_dbproc , 6 ) ;    
    fprintf(fdbfdsyb1, "SYBASE:%s Login Server %s Database %s OK\r\n" , GetLocalDataTime(),S_ServerName , S_DbName  ) ;

    return(*in_dbproc);
}

DBPROCESS  ReOpenSybaseDB(DBPROCESS * closeproc )
{
         DBPROCESS *  in_dbproc;
         int i;

   if( closeproc != NULL )
    CloseSybaseDB(closeproc);

    for(i=0;i<=2;i++) {
    in_dbproc = ( DBPROCESS * ) LoginSybase( S_UserName , S_PassWord , S_ServerName , S_DbName ) ;
    if ( in_dbproc == NULL )
       {
        fprintf(fdbfdsyb1,"SYBASE:%s After 1 seconds Will Retry Login SYBASE...\r\n",GetLocalDataTime()) ;
        dbexit();
        sleep( 1 ) ;
       }//if
   else
   break;
   }//for

    dbsetopt(in_dbproc, DBTEXTSIZE, IMAGESIZE, -1);
    dbsettimeout(in_dbproc , 6 ) ;
    fprintf(fdbfdsyb1, "SYBASE:%s Login Server %s Database %s OK\r\n" , GetLocalDataTime(),S_ServerName , S_DbName  ) ;

    return(*in_dbproc);
}
int ExecCommit( DBPROCESS *  dbproc)
{
          char         sqlcmd[255];
          dbfreebuf(dbproc);
          strcpy(sqlcmd,"commit transcation");
        dbcmd(dbproc,sqlcmd);
        dbsqlexec(dbproc);
    return(1);
}

int ExecRollback( DBPROCESS *  dbproc)
{   char         sqlcmd[255];
          dbfreebuf(dbproc);
          strcpy(sqlcmd,"rollback transcation");
        dbcmd(dbproc,sqlcmd);
        dbsqlexec(dbproc);

    return(1);
}

int ExecUpdate( DBPROCESS *  dbproc, char *sqlcmd )
{
          int Successed = 1 ;
        //  dbfreebuf(dbproc);
    dbcmd(dbproc, sqlcmd);

    if(dbsqlexec(dbproc)==FAIL)
          Successed = 0 ;
    if ( DBDEAD ( dbproc ) ){
       Successed = 0 ;
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Update SQL Server Dead\r\n",GetLocalDataTime()) ;
       }
      fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
    if ( Successed == 0 ){
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Update Data From SQL Server Fail...\r\n",GetLocalDataTime()) ;
       return(-1);
       }
       else{
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Update Data From SYBASE Server OK...\r\n",GetLocalDataTime()) ;
       return(1);
      }
}

int ExecInsert( DBPROCESS *  dbproc, char *sqlcmd )
{
          int Successed = 1 ;
          dbfreebuf(dbproc);
    dbcmd(dbproc, sqlcmd);
    if ( dbsqlexec(dbproc)==FAIL)
           Successed = 0 ;
    if ( DBDEAD ( dbproc ) ){
        Successed = 0 ;
        fprintf(fdbfdsyb1,"SYBASE:%s Exec Insert SQL Server Dead\r\n",GetLocalDataTime()) ;
       }
       fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
    if ( Successed == 0 ){
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Insert Data From SQL Server Fail...\r\n",GetLocalDataTime()) ;
       return(-1);
       }
       else{
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Insert Data From SYBASE Server OK...\r\n",GetLocalDataTime()) ;
       return(1);
      }
        }

int ExecDelete( DBPROCESS *  dbproc, char *sqlcmd )
{
    int Successed = 1 ;
    dbfreebuf(dbproc);
    dbcmd(dbproc, sqlcmd);
    if ( dbsqlexec(dbproc)==FAIL)
           Successed = 0 ;
    
    if ( DBDEAD ( dbproc ) ){
        Successed = 0 ;
       fprintf(fdbfdsyb1,"SYBASE:%s Exec Delete SQL Server Dead\r\n",GetLocalDataTime()) ;
       }
       fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
    if ( Successed == 0 ){
    	 fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;       
       return(-1);
       }
       else{
        fprintf(fdbfdsyb1,"SYBASE:%s Exec Delete Data From SYBASE Server OK...\r\n",GetLocalDataTime()) ;
       return(1);
      }
        }

int ExecProcOrSelect( DBPROCESS *  dbproc, char *sqlcmd,  char *ReBuf)
{
    int i = 0;
    int count = 0 ;
    int Successed = 1 ;
    char TempBuf[256];

    dbfreebuf(dbproc);
    dbcmd(dbproc, sqlcmd);
 if( dbsqlexec(dbproc)!=SUCCEED ){
     fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
     fprintf(fdbfdsyb1,"SYBASE:%s Exec Select Data From SQL Server Fail...\r\n",GetLocalDataTime()) ;
     return(-1);
    }
    memset( RowBuffer , 0x0 , MAX_COLUMN*MAX_COLSIZE );
    memset( AltBuffer , 0x0 , MAX_ALTCOLUMN*MAX_COLSIZE );
    while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
               {
                  if (result_code == SUCCEED)
                   {
                  for (  i=0;i<dbnumcols(dbproc);i++ )//取得的总列
                       dbbind_ps(dbproc,i+1,NTBSTRINGBIND,0,RowBuffer[i],typeinfo);
                 // for (  i=0;i<dbnumalts(dbproc,1);i++)//取得计算列的总列
                 //      dbaltbind(dbproc,1,i+1,NTBSTRINGBIND,0,AltBuffer[i]);
              while ((result_code=dbnextrow(dbproc)) != NO_MORE_ROWS)
                     {
                        if (result_code==FAIL) break;

                        if ( DBDEAD ( dbproc )||(result_code==FAIL) ){
                            Successed = 0 ;
                            fprintf(fdbfdsyb1,"SYBASE:%s Exec Select SQL Server Dead\r\n",GetLocalDataTime()) ;
                            break;
                            }
                         if ( result_code!=REG_ROW ){
                                    for(i=0;i<dbnumalts(dbproc,result_code);i++){
                                          //printf("111111REG_ROW %s:%s\r\n",dbprtype(dbaltop(dbproc,result_code,i+1)),AltBuffer[i]);
                                   }
                            }
                         else{
                               for (  i=0;i<dbnumcols(dbproc);i++ ){
                                    memset  ( TempBuf, 0x0, 256 );
                                    strncpy ( TempBuf, RowBuffer[i], strlen(RowBuffer[i]) ) ;
                                    strncat ( TempBuf, ",", 1 );
                                    if ( (strlen(ReBuf) + strlen(TempBuf)) >4093 ){
                                    	    dbcancel(dbproc);
                                    	    strncat ( ReBuf, "#,", 2 );
                                          ReBuf[strlen(ReBuf)]='\0';
                                          Successed = 1;
                                          break;
                                      }
                                    TempBuf[strlen(TempBuf)]='\0';;
                                    strncat(ReBuf,TempBuf,strlen(TempBuf));
                                   }
                                    strncat ( ReBuf, "#,", 2 );
                                    ReBuf[strlen(ReBuf)]='\0';
                                }
                              count ++ ;
                        // if ( count>9 ){
                           if ( count<0 ){//
                                ReBuf[strlen(ReBuf)]='\0';
                              // dbcanquery(dbproc);
                              // dbcanquery()清除dbproc中未处理的行数据
                                dbcancel(dbproc);
                                // dbcancel()取消当前批命令中所有命令及所有正在执行但尚未完成的结果
                                }
                                Successed = 1;
                      }//while 2
                }//if
             if( strlen(ReBuf)<=0 ){   
                 strncpy ( ReBuf, "NULL,", 5 );
                 ReBuf[strlen(ReBuf)]='\0';
                 Successed = 1;     
               }
      }//while 1

if ( Successed == 0 ){
     fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
     fprintf(fdbfdsyb1,"SYBASE:%s Exec Select Data From SQL Server Fail...\r\n",GetLocalDataTime()) ;
     return(-1);
     }
     else{
     fprintf(fdbfdsyb1,"SYBASE:%s Exec %s\r\n",GetLocalDataTime(),sqlcmd) ;
     fprintf(fdbfdsyb1,"SYBASE:%s SQL  Result=%s  Len=%d\r\n",GetLocalDataTime(),ReBuf,strlen(ReBuf));
     fprintf(fdbfdsyb1,"SYBASE:%s Exec Reutrn Rows=%d\r\n",GetLocalDataTime(),count) ;
     fprintf(fdbfdsyb1,"SYBASE:%s Exec Select Data From SYBASE Server OK...\r\n",GetLocalDataTime()) ;
     return(1);
     }
}

int CloseSybaseDB(  DBPROCESS *   dbproc )
{
      dbclose ( dbproc );
      dbexit();
      return(1);
}