dbase.c

dos下pos机程序 连接sybase数据库 使用Microsoft c7 编译

/* ******************************************
   qtsylib.c     
   前台收银程序的 DB-Library 函数库
 ****************************************** */
#define DBMSDOS
#include <sqlfront.h>
#include <sqldb.h>
#include <dos.h>
#include <bios.h>
#include <io.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <conio.h>
#include <math.h>
#include <time.h>
#include <fcntl.h>
#include <ctype.h>
#include <stdarg.h>
#include "direct.h"
//
DBPROCESS  *dbproc,*dbproc_thk; // 与SQL Server的联结
RETCODE    result_code;         // SQL命令执行的结果代码

int __sqlerrormark=0;
extern int MsgBoxEx(char *,int);

typedef struct
{
  char FieldName[11];
  char DataType[2];
  int  Size;
  int  Dec;
  int  xOff;
} TTable;

int sql_connectdb(char *user,char *pass,char *server,int flag);
//  SQL的错误处理函数
int err_handler(DBPROCESS *dbproc,int severity,int dberr,int oserr,
		char *dberrstr,char *oserrstr)
{
  struct dosdate_t date;
  struct dostime_t time;
  FILE *f_errinfo;

  _dos_getdate(&date);
  _dos_gettime(&time);

  f_errinfo=fopen("errinfo.txt","a");
  fprintf(f_errinfo,"%d-%d-%d %02d:%02d\n",
	   date.year,date.month,date.day,time.hour,time.minute);
  if ((dbproc == NULL) || (DBDEAD(dbproc)))
  {
   if(!__sqlerrormark)
   {
    MsgBoxEx("网络故障,请重新连接数据库.",1);
    __sqlerrormark=1;
   }
   return INT_CANCEL;  //INT_EXIT;
  }
  else
  { fprintf(f_errinfo,"DB-LIBRARY error:\n\t%s\n", dberrstr);
    if (oserr != DBNOERR)
	  fprintf(f_errinfo,"Operating-system error:\n\t%s\n", oserrstr);
    return INT_CANCEL;
  }
  fclose(f_errinfo);
}

// SQL的消息处理函数
int msg_handler(DBPROCESS *dbproc,DBINT msgno,int msgstate, int severity,
		char *msgtext)
{
  char    cmd[300];
  struct dosdate_t date;
  struct dostime_t time;
  FILE *f_errinfo;

  _dos_getdate(&date);
  _dos_gettime(&time);

  f_errinfo=fopen("errinfo.txt","a");
  fprintf(f_errinfo,"%d-%d-%d %02d:%02d\n",
	  date.year,date.month,date.day,time.hour,time.minute);
  fprintf(f_errinfo,"SQL Server message %ld, state %d, severity %d:\n\t%s\n",
	  msgno, msgstate, severity, msgtext);
  dbstrcpy(dbproc,0,-1,cmd);
  fprintf(f_errinfo,"%s\n",cmd);
  fclose(f_errinfo);
  return 0;
}

//联结SQL Server(用户名,密码,服务器名)
int sql_connectdb(char *user,char *pass,char *server,int flag)
{
  LOGINREC        *login;

  //安装错误处理程序
  dberrhandle(err_handler);
  dbmsghandle(msg_handler);

  login = dblogin();
  DBSETLUSER(login,user);
  DBSETLPWD(login,pass);
  DBSETLAPP(login,"QTSY");
  dbsetlogintime(60);

  if(!flag)
   {
     dbproc=dbopen(login, "Server_main");    //打开服务器
     if(dbproc==NULL) return 0;
     dbuse(dbproc,"ytposdb");
   }
  else if(flag==1)
	{
	 dbproc_thk = dbopen(login, server);
	 if (dbproc_thk == NULL) return 0;
	 dbuse(dbproc_thk,"ytposdb");
	}
  return 1;
}
//
void sql_close(void)
{
 dbexit();
}

void CopyStr(char *d,char *s,int nfrom,int nlen,int Blank)
{
 int i,j;
 j = nfrom;
 for(i=0;i<nlen;i++)
  d[i]=s[j++];
 d[i]='\0';
 for(i=nlen;i>=0&&Blank&&(d[i]=='\0'||d[i]==' ');i--)
  d[i]='\0';
}
void StrToLower(char *s)
{
  int i;
  for(i=0;i<(int)strlen(s);i++)
   s[i] = (char)tolower(s[i]);
}
void ClearBuffer(char *s,int rLen)
{
  int i;
  for(i=0;i<rLen;i++)
   s[i]='\0';
}

//从服务器读取数据到表TableName中
int GetDataFromServer(const char *TableName,
		      const char *STableName,const char *Order)
{
  FILE *fp,*fpt;
  int i,hLen,rLen,FieldCount=0;
  char _DbfHead[2048];
  char buffer[2048],_pFor[256],_Size[2],_Dec[2];
  unsigned char a[4];
  DBCHAR  tmpbuf[1000];
  long int m,n,RecordCount=0,RCount;
  TTable TStr[100];

  if(dbproc==NULL||DBDEAD(dbproc)) return 0;

  //读取表的结构
  fp = fopen(TableName,"rb");
  if(fp)
  {
    fseek(fp,4,0);             //记录数
    fread(&a,4,1,fp);
    RCount = a[0]+a[1]*0x100+a[2]*0x100*0x100;

    fseek(fp,8,0);             //库结构说明长度
    fread(&a,2,1,fp);
    hLen = a[0]+a[1]*0x100+1;

    fseek(fp,10,0);             //记录长度
    fread(&a,2,1,fp);
    rLen = a[0]+a[1]*0x100;


    FieldCount = hLen/32-1; //字段数
    //printf("%d  %d\n",hLen,FieldCount);

    //读取库结构
    fseek(fp,0,0);
    fread(&_DbfHead,hLen,1,fp);

    for(i=0;i<FieldCount;i++)
    {
     fseek(fp,32+i*32,0);      //读取记录描述
     fread(&buffer,32,1,fp);
     CopyStr(TStr[i].FieldName,buffer,0,10,1);

     StrToLower(TStr[i].FieldName);

     CopyStr(TStr[i].DataType, buffer,11,1,1);
     CopyStr(_Size,buffer,16,1,0);
     CopyStr(_Dec,buffer,17,1,0);
     TStr[i].Size=(int)_Size[0];
     //printf("%10s,%2d ",TStr[i].FieldName,_Size[0]);
     TStr[i].Dec=(int)_Dec[0];
     if(i)
      TStr[i].xOff=TStr[i-1].xOff+TStr[i-1].Size;
     else
      TStr[i].xOff=0;
    }
   fclose(fp);
  }
  else
   return 0;
  //getchar();
  //return 0;
  //构造查询语句
  strcpy(buffer,"");
  for(i=0;i<FieldCount;i++)
  {
    switch(TStr[i].DataType[0])
    {
     case 'C':if(TStr[i].Size==20&&strcmp(TStr[i].FieldName,"lbmc")) //日期类型
	       sprintf(_pFor,"isnull(CONVERT(char(11),%s,102),'')+isnull(CONVERT(char(9),%s,8),'')",
			     TStr[i].FieldName,TStr[i].FieldName);
	      else
	       sprintf(_pFor,"isnull(CONVERT(char(%d),%s),'')",
			     TStr[i].Size,TStr[i].FieldName);
	      break;
     case 'N':sprintf(_pFor,"isnull(convert(char(%d),convert(money,%s)),'')",
			     TStr[i].Size,TStr[i].FieldName);
	      break;
    }
    if(i)
     strcat(buffer,"+\n");
    strcat(buffer,_pFor);
  }

  dbfreebuf(dbproc);
  dbfcmd(dbproc,"SELECT %s\n",buffer);
  dbfcmd(dbproc,"FROM %s\n",STableName);
  if(Order[0]!='\0')
   dbfcmd(dbproc,"ORDER BY %s",Order);
  dbsqlexec(dbproc);
  while((result_code=dbresults(dbproc))!= NO_MORE_RESULTS)
   if (result_code == SUCCEED)
    {
      fpt = fopen("temp.dat","wb+");
      dbbind(dbproc, 1, NTBSTRINGBIND, (DBINT)0,tmpbuf);
      while(dbnextrow(dbproc)!=NO_MORE_ROWS)
      {
       RecordCount++;
       fwrite(&tmpbuf,rLen,1,fpt);
      }
      fclose(fpt);
      //设置记录数
      i=4;
      n=RecordCount;
      while(n&&i<8)
      {
	m=n%256;
	_DbfHead[i++]=(char)m;
	n/=256;
      }
      fpt = fopen("temp.dat","rb");
      fp = fopen(TableName,"wb+");
      fwrite(&_DbfHead,hLen,1,fp); //写入表头
      while(fread(&buffer,rLen,1,fpt))
       fwrite(&buffer,rLen,1,fp);
      fclose(fpt);
      fclose(fp);
    }
  fclose(fp);
  return 1;
}
//检索数据
int dbf_tm(const char *tm_str)
{
  FILE *fp;
  int i,hLen,rLen,FieldCount=0,k;
  char buffer[2048],_Size[2],_Dec[2],_buf[80];
  unsigned char a[4];
  long int RCount,m,n=1;
  TTable TStr[100];
  int xOff,fLen;
  //读取表的结构
  fp = fopen("jxc_spb.dbf","rb");
  if(fp)
  {
    fseek(fp,4,0);             //记录数
    fread(&a,4,1,fp);
    RCount = a[0]+a[1]*0x100+a[2]*0x100*0x100;

    fseek(fp,8,0);             //库结构说明长度
    fread(&a,2,1,fp);
    hLen = a[0]+a[1]*0x100+1;

    fseek(fp,10,0);             //记录长度
    fread(&a,2,1,fp);
    rLen = a[0]+a[1]*0x100;

    FieldCount = hLen/32-1; //字段数

    for(i=0;i<FieldCount;i++)
    {
     fseek(fp,32+i*32,0);      //读取记录描述
     fread(&buffer,32,1,fp);
     CopyStr(TStr[i].FieldName,buffer,0,10,1);

     StrToLower(TStr[i].FieldName);

     CopyStr(TStr[i].DataType, buffer,11,1,1);
     CopyStr(_Size,buffer,16,1,0);
     CopyStr(_Dec,buffer,17,1,0);
     TStr[i].Size=(int)_Size[0];
     TStr[i].Dec=(int)_Dec[0];
     if(i)
      TStr[i].xOff=TStr[i-1].xOff+TStr[i-1].Size;
     else
      TStr[i].xOff=0;
     if(!strcmp(TStr[i].FieldName,"spbm"))
     {
      xOff = TStr[i].xOff;
      fLen = TStr[i].Size;
     }
    }
  // fseek(fp,hLen,0);             //记录数据开始
   RCount--;
   n = 0;
   while(n<=RCount)
   {
    m = (n+RCount)/2;
    fseek(fp,m*rLen+hLen+xOff,0);
    fread(&_buf,fLen,1,fp);  //读取记录
    _buf[fLen]='\0';
    k = strcmp(tm_str,_buf);
    if(k==0)
    {
     fseek(fp,m*rLen+hLen,0);
     fread(&buffer,rLen,1,fp);  //读取记录
     for(i=0;i<FieldCount;i++)
     {
      CopyStr(_buf,buffer,TStr[i].xOff,TStr[i].Size,0);
      printf("%10s:%s\n",TStr[i].FieldName,_buf);
     }
     getchar();
     break;
    }
    else if(k>0)
	  n=m+1;
	 else
	  RCount=m-1;
   }
   fclose(fp);
   return 1;
  }
  return 0;
}

int sql_begin(void)
{
  if((dbproc==NULL)||(DBDEAD(dbproc))) return 0;

  dbfreebuf(dbproc);
  dbcmd(dbproc,"begin transaction ");
  dbsqlexec(dbproc);
  while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
  {
   if (result_code==FAIL) return 0;
  }
  return 1;
}

int sql_commit(void)
{
  if((dbproc==NULL)||(DBDEAD(dbproc))) return 0;

  dbfreebuf(dbproc);
  dbcmd(dbproc,"commit transaction ");
  dbsqlexec(dbproc);
  while((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
  {
   if (result_code==FAIL) return 0;
  }
  return 1;
}

int sql_rollback(void)
{
  if((dbproc==NULL)||(DBDEAD(dbproc))) return 0;

  dbfreebuf(dbproc);
  dbcmd(dbproc,"rollback transaction ");
  dbsqlexec(dbproc);
  while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS)
  {
   if (result_code==FAIL) return 0;
  }
  return 1;
}