dbbaseclass.cpp

用ODBC写的数据库接口,支持SQlserver,mysql

//---------------------------------------------------------------------------


#pragma hdrstop

#include <vcl.h>
#include "dbbaseclass.h"
#include <string.h>
#include <stdio.h>
#include <fcntl.h>
#include <io.h>
#include <time.h>
#include <stdlib.h >
#include <windef.h>
#include <odbcinst.h>
#include <dir.h >
#include <string.h >
#include <winbase.h>
#include <sys/stat.h>

//---------------------------------------------------------------------------

#pragma package(smart_init)



void  UpperStr2(char *in,char *out)
{
    int strlength = strlen(in);
    for(int id=0;id<strlength;id++)
    {
        if(!( (in[id]>='a'&&in[id]<='z')
            ||(in[id]>='A'&&in[id]<='Z')
            ||(in[id]>='0'&&in[id]<='9')
            ||in[id]=='_') )
            break;
        if(in[id]>='a'&&in[id]<='z')
            out[id]=in[id]+'A'-'a';
        else
        {
            out[id] = in[id];
        }
    }
    out[strlength] = 0;
}

void HexToChar(char *hex,char *ch8,int length)
{
  char con17 = 0x0f;
  long i;
  char temp;

  for (i=0;i<length;i++)
  {
	 temp =  ((hex[i] >> 4) & con17) + 0x30;
	 if (temp > 0x39)	 ch8[i*2] = temp + 0x07;
		else ch8[i*2] = temp ;
	 temp = ((hex[i] & con17) + 0x30);
	 if (temp > 0x39) ch8[i*2+1] = temp + 0x07;
		else ch8[i*2+1] = temp ;
  }
}


int ReplaceValues(char *pInOutStr,char *bl,char *szCalue)
{
    char *blpos;
    char *leftstr = new char[strlen(pInOutStr)+1];
    int len,bllen = strlen(bl);
//    strcpy(leftstr,
    while(blpos = strstr(pInOutStr,bl))
    {
        len = blpos-pInOutStr;
        strcpy(leftstr,blpos+bllen);
        *blpos = 0;
        strcat(pInOutStr,szCalue);
        pInOutStr+=strlen(pInOutStr);
        strcat(pInOutStr,leftstr);
    }
    return 0;
}

int EscapeData(char *buff,char *outbuff,int len)
{
int swi;
char ch;
int i,id;
    for(id=0,i=0;id<len;id++,i++)
    {
        swi=0;
        switch(buff[id])
        {
            case '\0': ch='0'; swi=1;break;
            case '\n': ch='n'; swi=1;break;
            case '\r': ch='r'; swi=1;break;
            case '\\': ch='\\'; swi=1;break;
            case '\'': ch='\''; swi=1;break;//39
            case '\"': ch='"'; swi=1;break;
            //case ')':
            case '\032':ch='Z'; swi=1;break;
        }
        if(swi)
        {
            outbuff[i++]='\\';
            outbuff[i]=ch;
        }
        else
            outbuff[i]=buff[id];
    }
    outbuff[i]=0;
    return i;
}

CDBFaceBase::CDBFaceBase()
{
    strcpy(m_Err,"未知错误");
//    m_pSql = NULL;
//    iSqlBuffLen = 0;
    iRecordCount=-1;
    m_Driver[0] = 0;
    strcpy(m_ClassName,"");
    m_Data = NULL;
    m_ParamSql = NULL;
    m_pDataConnect = new CMyODBC;
    m_pDataSet = new CODBCSet;
//    m_Sql = NULL;
//    memset(&m_DbBatchInfo,0,sizeof(m_DbBatchInfo));
    memset (&m_WDBINFO,0,sizeof(m_WDBINFO));
//    m_adMain = new TADOConnection(NULL);
//    m_adQuery = new TADOQuery(NULL);
    strcpy(m_ServerName,"127.0.0.1");
}

CDBFaceBase::~CDBFaceBase()
{
    CloseDB();
    ClearParamSql();
    delete m_pDataConnect;
    delete m_pDataSet;
//    if(m_pSql)
 //       free(m_pSql);
 //   iSqlBuffLen = 0;
    if(m_Data)
        delete m_Data;
//    if(m_Sql)
//        delete m_Sql;
//    if(m_DbBatchInfo.fp)
//        fclose(m_DbBatchInfo.fp);
//    if(m_DbBatchInfo.pData)
//        free(m_DbBatchInfo.pData);
}

bool  CDBFaceBase::CreateDSN (char * szAttributes)
{
char *startpos,*pos;
    startpos=szAttributes;
    while(1)
    {
        pos=strstr(startpos,"\\0");
        if(pos)
        {
            pos[0]=0;
            pos++;
            for(int id=0;id<(int)strlen(pos);id++)
                pos[id]=pos[id+1];
        }
        else
            break;
        startpos=pos;
    }
    if(SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,m_Driver,szAttributes)!=1)
        return false;
    return true;
}

bool  CDBFaceBase::CreateDB (char * szDBName)
{
char cmd[256];
    sprintf(cmd,"Drop DATABASE %s",szDBName);
    ExecSQLCmd(cmd);
//        return false;

    sprintf(cmd,"CREATE DATABASE %s;USE %s",
           szDBName,szDBName);
    return ExecSQL(cmd,NULL);
}


bool  CDBFaceBase::ConnectDB (char *szhostserver,char *szUser,char *szPassWord,char *szDBName)
{
    char szConStr[1024]={0};
    char sztmp[56]={0};
    //无DSN连接
    sprintf(szConStr,"DRIVER=%s",m_Driver);
    if(szhostserver)
    {
        sprintf(sztmp,";SERVER=%s",szhostserver);
        strcat(szConStr,sztmp);
        strcpy(m_ServerName,szhostserver);
    }
    if(szUser)
    {
        sprintf(sztmp,";UID=%s",szUser);
        strcat(szConStr,sztmp);
        strcpy(m_LoginUser,szUser);
    }
    if(szPassWord)
    {
        sprintf(sztmp,";PWD=%s",szPassWord);
        strcat(szConStr,sztmp);
        strcpy(m_LoginPS,szPassWord);
    }
    if(szDBName)
    {
        sprintf(sztmp,";DATABASE=%s",szDBName);
        strcat(szConStr,sztmp);
        strcpy(m_DBName,szDBName);
    }
    bool ret = ConnectDBByStr(szConStr);
    if(!ret)
    {
        strcpy(m_Err,m_pDataConnect->GetErr());
        WriteLog(m_Err);
    }
//    sprintf(szConStr,"use %s",szDBName);
//    ExecSQLCmd(szConStr);
    return ret;
}

bool  CDBFaceBase::ConnectDBByStr (char *szConnectStr)
{
    bool ret = m_pDataConnect->ConnectDB(szConnectStr);
    if(!ret)
        strcpy(m_Err,m_pDataConnect->GetErr());
    return ret;
    
//    SQLDriverConnect(
/*    AnsiString  sConnStr;
    sConnStr ="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source="
            + ExtractFilePath(Application->ExeName) + "sfInfo.dat" +";"
            + "Mode=Share Deny None;Extended Properties=\"\";"
            + "Persist Security Info=True;Jet OLEDB:System database=\"\";"
            + "Jet OLEDB:Registry Path=\"\";Jet OLEDB:Database Password=\"lxl_xxx\";"
            + "Jet OLEDB:Engine Type=5;";*/
/*
    if (m_adMain->Connected)
        m_adMain->Connected = false;
    m_adMain->ConnectionString = szConnectStr;
    try
    {
        m_adMain->LoginPrompt = false;
        m_adMain->Connected = true;
        m_adMain->KeepConnection = true;
        m_adQuery->Connection = m_adMain;

    }
    catch (Exception &exception)
    {
        strncpy(m_Err,exception.Message.c_str(),sizeof(m_Err)-1);
//        ShowError(Handle, exception.HelpContext, "程序初始化错误",
//            "打开本地数据库失败", exception.Message, false);
//        Application->Terminate();
        return false;
    }
    return m_adMain->Connected;*/
}

bool  CDBFaceBase::UseDB (char *szDBName)
{
    char sql[560];
    sprintf(sql,"use %s",szDBName);
    strcpy(m_DBName,szDBName);
    return ExecSQLCmd(sql);
}

bool  CDBFaceBase::CloseDB ()
{
    //if (m_adMain->Connected)
    //    m_adMain->Connected = false;
    m_pDataConnect->DisConnect();
    return true;
}

bool  CDBFaceBase::GetAllDB (char * szDB,int iSize)
{
    return false;
}


bool  CDBFaceBase::GetAllUser (DB_USER_INFO * szUser,int iInNum,int *iOutNum)
{
    return false;
}

bool  CDBFaceBase::SetUserRole (DB_USER_INFO *useinfo)
{
    return false;
}

bool  CDBFaceBase::DeleteUser (DB_USER_INFO *szUser)
{
    return false;
}

/*bool  CDBFaceBase::DeleteUser (char *username)
{
    return false;
}
*/


bool  CDBFaceBase::ExecSQLCmd(char * szSQL)
{
    bool bRet = m_pDataConnect->ExeSqlDirect(szSQL);
    if(!bRet)
    {
        snprintf(m_Err,sizeof(m_Err),"%s",m_pDataConnect->GetErr());
        if(strstr(szSQL,"drop")!=szSQL)
        {
            WriteLog(m_Err);
            WriteLog(szSQL);
        }
    }
    return bRet;
/*        
    bool iRet = true;
    UpperStr(szSQL,szSQL);
    //m_adQuery->Enter();
//    try
//    {
        try
        {
            m_adQuery->Close();
            m_adQuery->SQL->Clear();
            //m_adQuery->SQL->Text = szSQL;

            m_adQuery->SQL->Add(szSQL);

            iRet = m_adQuery->ExecSQL();
        }
        catch (Exception &exception)
        {
            strncpy(m_Err,exception.Message.c_str(),sizeof(m_Err)-1);
            //ShowError(NULL, exception.HelpContext, "数据库操作出错",
            //    "执行SQL语句异常", exception.Message, false);
            iRet = false;
        }
//    }
//    __finally
//    {
        //m_adQuery->Leave();
//    }
    return iRet;*/
}

bool  CDBFaceBase::ExecSQL(char * szSQL,void *callback)
{
char c,*strstart,*pos,*exesql;
//    if(!mDatabase->Connected&&!->Active)
//        return false;
    strstart=szSQL;
    bool ignoeflag = false;

    TDB_CALLBACK DB_CallBack = (TDB_CALLBACK)callback;
    int sqllen = strlen(szSQL);
//    int currpos = 0;

    while(strstart[0])
    {
        while(strstart[0]==' '||strstart[0]=='\n'||strstart[0]=='\r')
            strstart++;

        //忽略空格和注释
        while(1)
        {
            if(strstart[0]=='#')  //注释
            {
                while(strstart[0]&&(strstart[0]!='\n'&&strstart[0]!='\r'))
                    strstart++;
                while(strstart[0]==' '||strstart[0]=='\n'||strstart[0]=='\r') //忽略空
                    strstart++;
            }
            else
                break;
        }
        pos=strstart;
        while(pos[0])
        {
            if(*pos=='\'' && *(pos-1)!='\\' && pos!=strstart)
                ignoeflag=!ignoeflag;
            if(!ignoeflag && pos[0]==';') //取得一条语句
                break;
            pos++;
        }
        if(strstart==pos)      //无语句
        {
            if(!pos[0])       //如果字符串结束
                break;
            pos++;
            continue;
        }
        exesql=new char[pos-strstart+1];
        if(!exesql)
            return 0;
        memset(exesql,0,pos-strstart+1);
        memcpy(exesql,strstart,pos-strstart);
        c=pos[0];
        if(DB_CallBack)
            DB_CallBack((pos-szSQL)*100/sqllen,exesql);
        if(!ExecSQLCmd(exesql))
        {
            delete exesql;
            return false;
        }
        if(*pos)
            pos++;
        delete exesql;
        if(!pos[0]||!c) //如果字符串结束
            break;
        strstart=pos;
    }
    if(DB_CallBack)
        DB_CallBack(100,"end");
    return true;
}

bool  CDBFaceBase::ExecSQLFile(char * szSQLFile,void *callback)
{
struct stat statbuf;
char *buff;
    stat(szSQLFile,&statbuf);
    FILE *fp=fopen(szSQLFile,"rb");
    if(fp==NULL)
    {
        strcpy(m_Err,"不能打开初始化数据库的执行文件!");
        WriteLog(szSQLFile);
        return false;
    }
    buff=new char[statbuf.st_size+1];
    memset(buff,0,statbuf.st_size+1);
    fread(buff,statbuf.st_size,1,fp);
    fclose(fp);
    if(!ExecSQL(buff,callback))
    {
//        strcpy(ErrInfo,"执行初始化数据库文件失败!请检查SQL脚本!");
        return false;
    }
    delete buff;
    return true;
//    return false;
}

void  CDBFaceBase::ClearParamSql()
{
    PARAM_SQL *tmp = m_ParamSql;
    while(tmp)
    {
        m_ParamSql = tmp->pNext;
        if(tmp->pData)
            delete tmp->pData;
        delete tmp;
        tmp = m_ParamSql;
    }
}

void  CDBFaceBase::AddSQL(char *sql)
{
    ClearParamSql();

    char szSQL[2048];
    strncpy(szSQL,sql,2047);
    char *pos = szSQL;//strchr(szSQL,':');
    char *pose;
    bool noParam;
    PARAM_SQL *tmp = NULL;
    while(pos[0] )
    {
        if(pos[0]=='\'')
        {
            pos++;
            while(pos[0]&&pos[0]!='\'')
                pos++;
            if(pos[0]=='\'')
                pos++;
        }
        else if(pos[0]==':')
        {
            pose  = pos;