dbsqlserver.cpp

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

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


#pragma hdrstop

#include "dbsqlserver.h"
#include <stdio.h>
//---------------------------------------------------------------------------

#pragma package(smart_init)
CDBSQLServer::CDBSQLServer()
{
    strcpy(m_ClassName,STR_SQLSERVER);
    strcpy(m_Driver,"SQL Server"); //初始值
    m_WDBINFO.bSupportTrans = true;
    m_WDBINFO.bSupportLockOne = true;
    m_WDBINFO.bSupportView = true;
    m_WDBINFO.bEscape = false;
}

CDBSQLServer::~CDBSQLServer()
{
 
}

//AnsiString   ConnString="Provider=SQLOLEDB.1;Persist   Security   Info=False;"
//    "User   ID=sa;Initial   Catalog=tempdb;Data   Source=Server   name";//?
//    Microsoft OLE db provider for SQL Server

//采用父类的连接
//bool  CDBSQLServer::ConnectDB (char *szhostserver,char *szUser,char *szPassWord,char *szDBName)
//{
    //通过OLE
/*    strcpy(szConStr,"Provider=SQLOLEDB.1");
    if(szhostserver)
    {
        sprintf(sztmp,";SERVER=%s",szhostserver);
        strcat(szConStr,sztmp);
    }
    if(szUser)
    {
        sprintf(sztmp,";UID=%s",szUser);
        strcat(szConStr,sztmp);
    }
    if(szPassWord)
    {
        sprintf(sztmp,";PWD=%s",szPassWord);
        strcat(szConStr,sztmp);
    }
    if(szDBName)
    {
        sprintf(sztmp,";DATABASE=%s",szDBName);
        strcat(szConStr,sztmp);
    }
    return ConnectDBByStr(szConStr);*/
//}


bool  CDBSQLServer::SetUserRole (DB_USER_INFO *useinfo)
{
char cmd[512],str[120];
    if(!useinfo)
        return false;
//    int len = strlen(useinfo->szDBName);


    sprintf(cmd,"sp_droprolemember 'db_owner','%s'",useinfo->szUser);
    ExecSQLCmd(cmd);

    sprintf(cmd,"sp_dropuser %s",useinfo->szUser);
    ExecSQLCmd(cmd);

    sprintf(cmd,"sp_droplogin %s",useinfo->szUser);
    ExecSQLCmd(cmd);

    if(strlen(useinfo->szDBName)<=0)
        sprintf(cmd,"sp_addlogin @loginame = '%s',@passwd ='%s',@defdb='master' ",
            useinfo->szUser,useinfo->szPassword);
    else
        sprintf(cmd,"sp_addlogin @loginame = '%s',@passwd ='%s',@defdb='%s' ",
            useinfo->szUser,useinfo->szPassword,
            useinfo->szDBName);
//[ , [ @passwd = ] 'password' ]
//[ , [ @defdb = ] 'database' ]
//[ , [ @deflanguage = ] 'language' ]
//[ , [ @sid = ] sid ]
//[ , [ @encryptopt = ] 'encryption_option' ]

/*    if(userinfo->szDBName&&userinfo->szDBName[0])
        sprintf(cmd,"grant all privileges ON %s.* ",userinfo->szDBName);
    else
        sprintf(cmd,"grant all privileges ON *.* ");
    if(userinfo->szUser[0])
        sprintf(str," TO '%s'",userinfo->szUser);
    else
        sprintf(str," TO ''");
    strcat(cmd,str);
    if(userinfo->szHost[0])
        sprintf(str,"@'%s' ",userinfo->szHost);
    else
        sprintf(str,"@'%%' ");
    sprintf(cmd,"grant all privileges to %s ",userinfo->szUser);
    strcat(cmd,str);
    sprintf(str," identified by '%s' with grant option",userinfo->szPassword);
    strcat(cmd,str);*/

    ExecSQLCmd(cmd);
    sprintf(cmd,"sp_adduser %s",useinfo->szUser);
    ExecSQLCmd(cmd);
    sprintf(cmd,"sp_grantdbaccess %s",useinfo->szUser);
    ExecSQLCmd(cmd);
    if(useinfo->role== DB_USER_ROLE_ALL)
    {
        sprintf(cmd,"sp_addsrvrolemember '%s','sysadmin'",useinfo->szUser);
        ExecSQL(cmd,NULL);
    }
    sprintf(cmd,"sp_addrolemember 'db_owner','%s'",useinfo->szUser);

    return ExecSQL(cmd,NULL);
//    sprintf(cmd,"grant all to %s as db_owner",useinfo->szUser);
//    return ExecSQLCmd(cmd);
}

bool  CDBSQLServer::LockTables(char *tables)
{
    char *posb,*pose;
    char tablename[120];
    char role[24],sql[256];
    bool ret= true;
    posb = tables;
    while(posb && posb[0])
    {
        pose = strchr(posb,' ');
        if(!pose)
        {
            ret = false;
            break;
        }

        memset(tablename,0,sizeof(tablename));
        if(pose-posb>sizeof(tablename)-1)
        {
            ret = false;
            break;
        }
        memcpy(tablename,posb,pose-posb);
        pose++;
        posb = pose;

        pose = strchr(posb,',');
        memset(role,0,sizeof(role));
        if(pose)
        {
            if(pose-posb>sizeof(role)-1)
            {
                ret = false;
                break;
            }
            memcpy(role,posb,pose-posb);
            pose++;
        }
        else
        {
            if(strlen(posb)<sizeof(role)-1)
                strcpy(role,posb);
        }
        if(strcmp(role,"write")==0)
            sprintf(sql,"select * from %s with (TABLOCKX) where 1=2",tablename);
        else
            sprintf(sql,"select * from %s with (HOLDLOCK)where 1=2",tablename); //共享锁
        if(!ExecSQLCmd(sql))
        {
            ret = false;
            break;
        }
        posb = pose;
    }

    return ret;
}

bool  CDBSQLServer::UnlockTables(char *tables)
{
    return true;
}


bool  CDBSQLServer::BatchAdd(HANDLE hdBatchHandle,char *szValues,int type,int ValueLen,bool endline)
{
    DB_BATCH_INFO *pDbBatchInfo = (DB_BATCH_INFO *)hdBatchHandle;
//    if(pDbBatchInfo->pData==NULL)
//    {
//        WriteLog("begin new batch");
//        pDbBatchInfo->pData = (char *)realloc(NULL,1000);
//    }
    //return true;
    if(szValues && ValueLen>0)
    {
        if(ValueLen>409600)
        {
            sprintf(m_Err,"值长度太长!LEN=%d",ValueLen);
            WriteLog(m_Err);
            return false;
        }
        if(type==AS_BLOB)
        {
            pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+ValueLen*2+3);
            HexToChar(szValues,pDbBatchInfo->pData+pDbBatchInfo->nDataLen,ValueLen);
            pDbBatchInfo->nDataLen += (ValueLen*2);
        }
        else
        {
            //pDbBatchInfo->nDataLen+=(ValueLen+1);
            while(ValueLen>0 && (szValues[ValueLen-1]==' ' ||szValues[ValueLen-1]=='\0'))
                ValueLen--;
            pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+ValueLen+3);
            memcpy(pDbBatchInfo->pData+pDbBatchInfo->nDataLen,szValues,ValueLen);
            pDbBatchInfo->nDataLen += ValueLen;
        }
    }
    else
        pDbBatchInfo->pData = (char *)realloc(pDbBatchInfo->pData,pDbBatchInfo->nDataLen+3);
    if(endline)
    {
        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X0D;
        pDbBatchInfo->nDataLen++;
        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X0A;
        pDbBatchInfo->nDataLen++;
    }
    else
    {
        pDbBatchInfo->pData[pDbBatchInfo->nDataLen] = 0X09;
        pDbBatchInfo->nDataLen++;
    }
    if(pDbBatchInfo->nDataLen>409600)
    {
        int writesize = fwrite(pDbBatchInfo->pData,pDbBatchInfo->nDataLen,1,pDbBatchInfo->fp);
        if(writesize!=1)
        {
            strcpy(m_Err,"写入文件失败!");
            return false;
        }
        free(pDbBatchInfo->pData);
        pDbBatchInfo->nDataLen = 0;
        pDbBatchInfo->pData = 0;
//        WriteLog("write file batch end ");
    }
    return true;
}

bool  CDBSQLServer::BatchEnd(HANDLE hdBatchHandle,void *callback)
{
    DB_BATCH_INFO *pDbBatchInfo = (DB_BATCH_INFO *)hdBatchHandle;
    if(pDbBatchInfo->pData)
    {
        int writesize = fwrite(pDbBatchInfo->pData,pDbBatchInfo->nDataLen,1,pDbBatchInfo->fp);
        if(writesize!=1)
        {
            strcpy(m_Err,"写入文件失败!");
            WriteLog("write file batch end 写入文件失败");
            return false;
        }
        free(pDbBatchInfo->pData);
        pDbBatchInfo->nDataLen = 0;
        pDbBatchInfo->pData = 0;
//        WriteLog("write file batch end 2");
    }
    fclose(pDbBatchInfo->fp);
    pDbBatchInfo->fp = NULL;
/*    char szCmd[320];
    sprintf(szCmd,"bcp %s.%s.%s in \"%s\" -c -q -k -U\"%s\" -P\"%s\"",
        m_DBName,m_LoginUser,pDbBatchInfo->TableName,pDbBatchInfo->tmpFile,m_LoginUser,m_LoginPS);
    STARTUPINFO startinfo;
    PROCESS_INFORMATION info;
    memset(&startinfo,0,sizeof(STARTUPINFO));
    memset(&info,0,sizeof(PROCESS_INFORMATION));
    startinfo.dwFlags=STARTF_USESHOWWINDOW;
    startinfo.wShowWindow=SW_HIDE;
 //   WriteLog(szCmd);
    if(CreateProcess(NULL,szCmd,NULL,NULL,
        false,CREATE_NEW_CONSOLE,NULL,NULL,&startinfo,&info)==0)
    {
        strcpy(m_Err,"导入数据失败!");
        CloseHandle(info.hThread);
        CloseHandle(info.hProcess);
        return false;
    }
    WaitForSingleObject(info.hProcess,INFINITE);
    DWORD ExitCode = 0;
    GetExitCodeProcess(info.hProcess,&ExitCode);
    CloseHandle(info.hThread);
    CloseHandle(info.hProcess);
    //DeleteFile(pDbBatchInfo->tmpFile);
//    char info[120];
    sprintf(szCmd,"bcp EXIT %X",ExitCode);
    WriteLog(szCmd);
//    if(ExitCode==0)
//        return true;
//    else
//        return false;*/
    return BatchByFile(pDbBatchInfo->tmpFile,DB_FILE_TYPE_DEF,pDbBatchInfo->TableName,callback);
}

bool CDBSQLServer::CheckLog(char *szLogFile,unsigned int *nRecCount)
{
    char szData[1024];
    char *finspos;
    FILE *fp = fopen(szLogFile,"r");
    if(!fp)
    {
        strcpy(m_Err,"未知错误");
        return false;
    }
    fseek(fp,0,SEEK_END);
    DWORD dwFileSize = ftell(fp);
    if(dwFileSize<=0)
    {
        fclose(fp);
        return true;
    }
    fseek(fp,0,SEEK_SET);
    while(!feof(fp))
    {
        fgets(szData, sizeof(szData)-1, fp);
        if(strstr(szData,"Error")==szData)
        {
            fclose(fp);
            WriteLog(szData);
            strncpy(m_Err,szData,sizeof(m_Err)-1);
            return false;
        }
        else
        {
            finspos = strstr(szData,"已复制了");
            if(finspos!=0)
            {
                finspos+=9;
                sscanf(finspos,"%u",nRecCount);
            }
        }
    }
    fclose(fp);
    return true;
}