dbsqlserver.cpp

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

bool  CDBSQLServer::BatchByFile(char *szFileName,int filetype,char *szTableName,void *callback)
{
    char szCmd[1024];
    char logfile[MAX_PATH];
    extern char g_SysDir[MAX_PATH];
    sprintf(logfile,"%s\\W_log%u.~tmp",g_SysDir,(ULONG)this);

    if(callback)
    {
        TDB_CALLBACK   mycallback = (TDB_CALLBACK)callback ;
        mycallback(0,"正在导入数据,请稍等...");
    }

    DeleteFile(logfile);
    if(filetype ==DB_FILE_TYPE_DEF)
    {
//        sprintf(szCmd,"exec master..xp_cmdshell 'bcp %s.%s.%s in \"%s\" -c -q -k -U\"%s\" -P\"%s\"'",
//            m_DBName,m_LoginUser,szTableName,szFileName,m_LoginUser,m_LoginPS);
        sprintf(szCmd,"bcp %s..%s in \"%s\" -c -q -k -S\"%s\" -U\"%s\" -P\"%s\"",
            m_DBName,szTableName,szFileName,m_ServerName,m_LoginUser,m_LoginPS);
    }
    else
    {
        char szData[128]={0};
        unsigned int nRowCount = 0;
        char *pos;
        FILE *fp = fopen(szFileName,"rb");
        if(!fp)
        {
            strcpy(m_Err,"打开文件失败!");
            WriteLog("BatchByFile 写入文件失败");
            return false;
        }
        fseek(fp,-126,SEEK_END);
        if(fread(szData,1,126,fp)!=126)
        {
            strcpy(m_Err,"文件格式错误!");
            WriteLog("BatchByFile 文件格式错误");
            return false;
        }
        fclose(fp);
        pos = strchr(szData,' ');
        if(pos)
        {
            *pos = 0;
            pos++;
            sscanf(pos,"%u",&nRowCount);
        }
        if(strcmp(szData,szTableName)!=0)
        {
            strcpy(m_Err,"需要导入的表不匹配!");
            WriteLog("BatchByFile 需要导入的表不匹配");
            return false;
        }
        if(strlen(pos)>0)
        {
            sprintf(szCmd,"bcp %s.%s.%s in \"%s\" -c -q -k -L %u -S\"%s\" -U\"%s\" -P\"%s\"",
                m_DBName,m_LoginUser,szTableName,szFileName,nRowCount,m_ServerName,m_LoginUser,m_LoginPS);
        }
        else
            sprintf(szCmd,"bcp %s.%s.%s in \"%s\" -c -q -k -S\"%s\" -U\"%s\" -P\"%s\"",
                m_DBName,m_LoginUser,szTableName,szFileName,m_ServerName,m_LoginUser,m_LoginPS);
    }

    STARTUPINFO startinfo;
    PROCESS_INFORMATION info;
    memset(&startinfo,0,sizeof(STARTUPINFO));
    memset(&info,0,sizeof(PROCESS_INFORMATION));
    startinfo.dwFlags=STARTF_USESHOWWINDOW|STARTF_USESTDHANDLES;
    startinfo.wShowWindow=SW_HIDE;
 //   WriteLog(szCmd);

    SECURITY_ATTRIBUTES lsa;
    lsa.nLength = sizeof(SECURITY_ATTRIBUTES);
    lsa.lpSecurityDescriptor = NULL;
    lsa.bInheritHandle = true;
    HANDLE fileHandle = CreateFile(logfile, GENERIC_READ|GENERIC_WRITE,0,&lsa,CREATE_ALWAYS,0,NULL);
    startinfo.hStdOutput = fileHandle;

    /*if(CreateProcess(NULL,"notepad",NULL,NULL,
        TRUE,CREATE_NEW_CONSOLE,NULL,NULL,&startinfo,&info)==0)
    {
        sprintf(m_Err,"111导入数据失败!Code=0X%X",GetLastError());
        CloseHandle(info.hThread);
        CloseHandle(info.hProcess);
        WriteLog(m_Err);
        WriteLog(szCmd);
        return false;
    }
    */
    if(CreateProcess(NULL,szCmd,NULL,NULL,
        TRUE,CREATE_NEW_CONSOLE,NULL,NULL,&startinfo,&info)==0)
    {
        sprintf(m_Err,"导入数据失败!Code=0X%X",GetLastError());
        CloseHandle(info.hThread);
        CloseHandle(info.hProcess);
        WriteLog(m_Err);
        WriteLog(szCmd);
        return false;
    }
    WaitForSingleObject(info.hProcess,INFINITE);
    DWORD ExitCode = 0;
    GetExitCodeProcess(info.hProcess,&ExitCode);
    CloseHandle(info.hThread);
    CloseHandle(info.hProcess);
    if(fileHandle!=INVALID_HANDLE_VALUE)
        CloseHandle(fileHandle);
//    unsigned int nCount;
    bool ret = CheckLog(logfile,&iRecordCount);//ExecSQLCmd(szCmd);
    DeleteFile(logfile);
    return ret;
}

bool  CDBSQLServer::FetchToFile(char *sql,char *pathname,char *szTable,void *callback)
{
//        "create table #wstt(re varchar(8000)) "
//            "insert into #wstt exec master..xp_cmdshell 'bcp         '"
//        "select * from #wstt where re like '已复制了%'"
    extern char g_SysDir[MAX_PATH];
    char logfile[MAX_PATH];
    sprintf(logfile,"%s\\W_log%u.~tmp",g_SysDir,(ULONG)this);

    char szCmd[2048];
    if(callback)
    {
        TDB_CALLBACK   mycallback = (TDB_CALLBACK)callback ;
        mycallback(0,"正在导出数据,请稍等...");
    }

    sprintf(szCmd,"bcp \"%s\" queryout \"%s\" -c -q -k -E -S\"%s\" -U\"%s\" -P\"%s\" ",
        sql,pathname,m_ServerName,m_LoginUser,m_LoginPS);
    STARTUPINFO startinfo;
    PROCESS_INFORMATION info;
    memset(&startinfo,0,sizeof(STARTUPINFO));
    memset(&info,0,sizeof(PROCESS_INFORMATION));

    startinfo.dwFlags=STARTF_USESHOWWINDOW|STARTF_USESTDHANDLES;
    startinfo.wShowWindow=SW_HIDE;

    SECURITY_ATTRIBUTES lsa;
    memset(&lsa,0,sizeof(SECURITY_ATTRIBUTES));
    lsa.nLength = sizeof(SECURITY_ATTRIBUTES);
    lsa.lpSecurityDescriptor = NULL;
    lsa.bInheritHandle = true;
    //HANDLE hReadPipe,hWritePipe;
    //HANDLE fileHandle = INVALID_HANDLE_VALUE;
    //创建管道
/*    if(CreatePipe(&hReadPipe,&hWritePipe,&lsa,0))
    {
        //Sleep(2000);
    }
    else
    {
        hWritePipe = INVALID_HANDLE_VALUE;
//        return;
    }*/
    HANDLE fileHandle = CreateFile(logfile, GENERIC_READ|GENERIC_WRITE,0,&lsa,CREATE_ALWAYS,0,NULL);

    startinfo.hStdOutput = fileHandle;
    //startinfo.hStdError  = fileHandle;
    
    if(CreateProcess(NULL,szCmd,NULL,NULL,
        TRUE,CREATE_NEW_CONSOLE,NULL,NULL,&startinfo,&info)==0)
    {
        strcpy(m_Err,"导入数据失败!");
        CloseHandle(info.hThread);
        CloseHandle(info.hProcess);
        return false;
    }

/*
    if(hWritePipe!=INVALID_HANDLE_VALUE)
    {
        DWORD bytesRead;
        char szData[4096];
        char *finspos;
        while(true)
        {
            bytesRead = 0;
            if(!PeekNamedPipe(hReadPipe,szData,1,&bytesRead,NULL,NULL)) break;
            if(bytesRead)
            {
                memset(szData,0,sizeof(szData));
                if(!ReadFile(hReadPipe,szData,4096,&bytesRead,NULL)) break;
                szData[bytesRead] = 0;
                //Memo1->Lines->Add(readBuf);
            }
            else
            {
                if(WaitForSingleObject(pi.hProcess,0)==WAIT_OBJECT_0) break;
                Sleep(1000);
            }
        }
        CloseHandle(hReadPipe);
    }
    else*/
        WaitForSingleObject(info.hProcess,INFINITE);
    CloseHandle(info.hThread);
    CloseHandle(info.hProcess);
    if(fileHandle!=INVALID_HANDLE_VALUE)
        CloseHandle(fileHandle);

    //    unsigned int nCount;
    bool ret = CheckLog(logfile,&iRecordCount);//ExecSQLCmd(szCmd);
    if(ret)
    {
        char szData[130]={0};
        sprintf(szData,"\n\n%s %u",szTable,iRecordCount);

        FILE *fp = fopen(pathname,"r+");
        if(!fp)
        {
            strcpy(m_Err,"打开文件失败!");
            return false;
        }
        fseek(fp,0,SEEK_END);
        fwrite(szData,1,128,fp);
        fclose(fp);
    }
    DeleteFile(logfile);
    return ret;
}

bool  CDBSQLServer::Backup(char *pathname,char *db,void *callback)
{
    char sql[1024];
    if(callback)
    {
        TDB_CALLBACK   mycallback = (TDB_CALLBACK)callback ;
        mycallback(0,"正在备份数据请稍等...");
    }
//    sprintf(sql,"sp_addumpdevice 'disk','%s','%s'",m_DBName,pathname);
    //DATABASE processctrol to disk='%s'",pathname);
//    if(!ExecSQLCmd(sql))
//        return false;
    if(db)
        strcpy(m_DBName,db);
    sprintf(sql,"dump tran %s with truncate_only",m_DBName);
    ExecSQLCmd(sql);
    sprintf(sql,"backup DATABASE %s to DISK='%s' ",m_DBName,pathname);
    return ExecSQLCmd(sql);
}

bool  CDBSQLServer::Restore(char *pathname,char *dbname,void *callback)
{
    char sql[1024];
    if(callback)
    {
        TDB_CALLBACK   mycallback = (TDB_CALLBACK)callback ;
        mycallback(0,"正在恢复数据请稍等...");
    }
//    sprintf(sql,"sp_detach_db %s",m_DBName);
//    ExecSQLCmd(sql);
    if(dbname)
        strcpy(m_DBName,dbname);

    ExecSQLCmd("use   master");
    sprintf(sql,"Declare   @tblConnectedUsers   Table   (SPID   int)\n\
  Declare   @vcSQLText   varchar(200),@iSPID   int,@dbName   varchar(20)\n\
  set   @dbName='%s'\n\
  Insert   into     @tblConnectedUsers\n\
  Select   p.spid\n\
  from   master.dbo.sysprocesses   p   (nolock)\n\
  join   master..sysdatabases   d   (nolock)   on   p.dbid   =   d.dbid\n\
  Where   d.[name]   =   @dbName\n\
  While   1   =   1\n\
  Begin\n\
  Select   top   1   @iSPID   =   SPID\n\
  From     @tblConnectedUsers\n\
  Where   SPID   >   IsNull(@iSPID,   0)\n\
  order   by   SPID   asc\n\
  If   @@RowCount   =   0\n\
  Break\n\
  Set   @vcSQLText   =   'Kill   '   +   Convert(varchar(10),   @iSPID)\n\
  Exec(   @vcSQLText   )\n\
  End",m_DBName);
 //   if(!ExecSQLCmd(sql))
 //       return false;

//    sprintf(sql,"drop DATABASE %s ",m_DBName);
//    ExecSQLCmd(sql);
    sprintf(sql,"restore DATABASE %s from disk='%s' WITH RECOVERY ",m_DBName,pathname);
    bool ret = ExecSQLCmd(sql);

//    sprintf(sql,"dump tran %s with truncate_only",m_DBName);
//    ExecSQLCmd(sql);
//    Commit();
//    sprintf(sql,"use %s",m_DBName);
//    ExecSQLCmd(sql);
    int ntimes = 0;
    bool sret = ConnectDB(m_ServerName,m_LoginUser,m_LoginPS,m_DBName);
    if(!sret && ntimes<15)
    {
        Sleep(1000);
        ntimes++;
        sret = ConnectDB(m_ServerName,m_LoginUser,m_LoginPS,m_DBName);
    }
    if(sret)
    {
        sprintf(sql,"sp_configure 'allow updates',1;reconfigure with override;"
            "update master.dbo.sysdatabases set status=16 where name = '%s';sp_configure 'allow updates',0;reconfigure with override",m_DBName);
        ExecSQL(sql,NULL);

        WriteLog("to repaire user!\n");
        RepairUser(dbname);
    }
    else
        WriteLog("reconncect error!\n");
    return ret;
}

bool CDBSQLServer::RepairUser(char *dbname)
{
    char sql[560];
    sprintf(sql,"use %s",dbname);
    ExecSQLCmd(sql);
    sprintf(sql,"SELECT a.name,a.SID FROM MASTER.dbo.syslogins a,%s.dbo.sysusers b "
        "where a.SID!=b.SID and a.name=b.name",dbname);
    if(Query(sql))
    {
        char szUserName[10][56]={0};
        int id;
        for(id=0;id<RecordCount() && id<10;id++)
        {
            strcpy(szUserName[id],GetValueByIdx(0));
            Next();
        }
        id = 0;
        while(id<10 && szUserName[id][0]!='\0')
        {
            sprintf(sql,"sp_change_users_login @Action='update_one',"
                    "@UserNamePattern='%s',@LoginName='%s'",szUserName[id],szUserName[id]);
            WriteLog("to repair---");
            WriteLog(sql);
            ExecSQLCmd(sql);
            id++;
        }
    }
    return true;
}