VC用ADO链接sqlserver数据库问题

[yuerkewanghupo4]

#pragma comment(lib,"ws2_32.lib")
#include "stdafx.h"
#include "public.h"
#include "zip.h"
#include "unzip.h"
#include "ftp.h"
#ifdef _ATL_STATIC_REGISTRY
#include <statreg.h>
#include <statreg.cpp>

#include <stdlib.h>
#include <stdio.h>
#include <io.h>
#include <iostream>

#endif

#include <atlimpl.cpp>
#include <string>
using namespace std;

string Rename_file_card(string StartTime,string CheckinID,const char* date,string EndTime,string db_name);
void create_bcp(string StartTime,string filecard_information,string content_front,string UnitCode,string ClientId,string FileSize,string content_back,string original_name);
string bcpname;
HZIP hz;
int num;
string zipname;

void createxmlfile(string bcp,int line)
{
}

bool Rename_File(string ClientId,string FileName,string FileSize,const char* date,string db_name)
{
string sql="select StartTime,EndTime,CardOwner,Sex,Certificate_type,Certificate_Code,UnitCode,ComputerIP,CheckinID,AreaCode,HyperID,NCardNO from ";
string tmp="Provider=SQLOLEDB; Data Source=192.168.70.21;Initial Catalog="+ db_name +"; Integrated Security=SSPI";
_bstr_t strConnect=tmp.c_str();
::CoInitialize(NULL);
_ConnectionPtr m_pConnection;
HRESULT hr=m_pConnection.CreateInstance(_uuidof(Connection));
if(FAILED(hr))
{
_com_error e(hr);
WriteLog("Rename_File: %s",e.ErrorMessage());
return false;
}
m_pConnection->Open(strConnect,"","",adModeUnknown);
string customer_table="NSMC_log_Customer";
customer_table+=date;
sql+=customer_table + " where ClientID=";
sql+="\'";
sql+=ClientId;
sql+="\'";
_bstr_t strSQL(sql.c_str());
_RecordsetPtr m_pRecordset("ADODB.Recordset");
string original_name;
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open(strSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
while(!m_pRecordset->EndOfFile)
{
string NewFileName;
string CardOwner,Sex,Certificate_type,Certificate_code,cStartTime,cEndTime,StartTime,EndTime,UnitCode,ComputerIP,CheckinID,AreaCode,HyperID,NCardNO;

try
{
Sex=(char*)(_bstr_t)m_pRecordset->GetCollect("Sex");
}
catch(_com_error e)
{
Sex="";
}

try
{
Certificate_type=(char*)(_bstr_t)m_pRecordset->GetCollect("Certificate_type");
}
catch(_com_error e)
{
Certificate_type="";
}

try
{
cStartTime=(char*)(_bstr_t)m_pRecordset->GetCollect("StartTime");
StartTime=ConvertTime((char*)cStartTime.c_str());
}
catch(_com_error e)
{
StartTime="";
}
try
{
UnitCode=(char*)(_bstr_t)m_pRecordset->GetCollect("UnitCode");
}
catch(_com_error e)
{
UnitCode="";
}

try
{
cEndTime=(char*)(_bstr_t)m_pRecordset->GetCollect("EndTime");
EndTime=ConvertTime((char*)cEndTime.c_str());
}
catch(_com_error e)
{
EndTime=StartTime;
}

try
{
Certificate_code=(char*)(_bstr_t)m_pRecordset->GetCollect("Certificate_code");
}
catch(_com_error e)
{
Certificate_code="";
}

try
{
CheckinID=(char*)(_bstr_t)m_pRecordset->GetCollect("CheckinID");
CheckinID=CheckinID.substr(1,CheckinID.size()-2);
}
catch(_com_error e)
{
CheckinID="";
}

NewFileName="C:\\Users\\Administrator\\Desktop\\photo\\"+StartTime+"_"+ClientId+".jpeg";

string buff=FileName;
string buff2;
int i;
i=buff.find_last_of('\\');
buff2=buff.substr(0,i);
original_name=buff.substr(i+1);
buff=buff.substr(i);
i=buff2.find_last_of('\\');
buff=buff2.substr(i)+buff;
buff2=buff2.substr(0,i);

if(buff2.find_last_of('\\')==string::npos)
{
FileName="\\\\tsclient\\M\\Zetronic\\Snap\\"+FileName;
}
else
{
i=buff2.find_last_of('\\');
buff=buff2.substr(i)+buff;
FileName="\\\\tsclient\\M\\Zetronic\\Snap"+buff;
}

if(_access(FileName.c_str(),0)!=-1)
{

FILE* infile;
FILE* outfile;
infile=fopen(FileName.c_str(),"rb");
if(infile==NULL)
{
WriteLog("打开上网时照片失败");
return false;
}
outfile=fopen(NewFileName.c_str(),"wb");
if(outfile==NULL)
{
WriteLog("打开失败");
return false;
}
char* buff=new char[1024];
memset(buff,0,1024);
while(fread(buff,1024,1,infile))
{
fwrite(buff,1024,1,outfile);
memset(buff,0,1024);
}
fclose(infile);
fclose(outfile);
delete [] buff;

string NewFileName2=StartTime+"_"+ClientId+".jpeg";

ZipAdd(hz,NewFileName2.c_str(), NewFileName.c_str());
DeleteFile(NewFileName.c_str());

string filecard_information;
filecard_information=Rename_file_card(StartTime,CheckinID,date,EndTime,db_name);

string first_Time;
if(NCardNO.empty()||HyperID.empty())
{
first_Time="";
}
else
{
first_Time=first_netplay(HyperID,NCardNO);
}

string content_front;
content_front=AreaCode+"\t"+first_Time+"\t";
content_front+=StartTime+"\t";
content_front+="\t";
content_front+="120";
content_front+="\t";

string content_back;
content_back=CardOwner+"\t";
content_back+=Sex+"\t"+Certificate_type+"\t"+Certificate_code+"\t"+ComputerIP+"\t";
num++;
create_bcp(StartTime,content_front,filecard_information,UnitCode,ClientId,FileSize,content_back,original_name);
}
break;
}

}
catch(_com_error e)
{
WriteLog("Rename_Filed %s",e.ErrorMessage());
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
return false;
}
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
return true;

}

string Rename_file_card(string StartTime,string CheckinID,const char* date,string EndTime,string db_name)
{

}

void create_bcp(string StartTime,string content_front,string filecard_information,string UnitCode,string ClientId,string FileSize,string content_back,string original_name)
{


}

void connect_sqlserver(time_t ltime)
{

struct tm* ptm;
ptm = localtime(<ime);
char buff[32];
string db_name="NSMCLOG";
sprintf(buff, "%4d%.2d%",ptm->tm_year + 1900, ptm->tm_mon + 1);
WriteLog("上传月份%s",buff);
db_name+=buff;
string tmp="Provider=SQLOLEDB; Data Source=192.168.0.25;Initial Catalog="+ db_name +"; Integrated Security=SSPI";
_bstr_t strConnect=tmp.c_str();

::CoInitialize(NULL);
_ConnectionPtr m_pConnection;
HRESULT hr=m_pConnection.CreateInstance(_uuidof(Connection));
char date[32];
ptm = localtime(<ime);
sprintf(date, "%4d%.2d%.2d",ptm->tm_year + 1900, ptm->tm_mon + 1, ptm->tm_mday);
if(FAILED(hr))
{
_com_error e(hr);
WriteLog("conect_sqlserver: %s",e.ErrorMessage());
}
m_pConnection->Open(strConnect,"","",adModeUnknown);

if(num!=0)
{
int k;
k=bcpname.find_last_of('\\');
string bcpname2=bcpname.substr(k+1);
ZipAdd(hz,bcpname2.c_str(),bcpname.c_str());
createxmlfile(bcpname2,num);
ZipAdd(hz,"GAB_ZIP_INDEX.xml","GAB_ZIP_INDEX.xml");
DeleteFile("GAB_ZIP_INDEX.xml");
DeleteFile(bcpname.c_str());
CloseZip(hz);
WriteLog("%s",zipname.c_str());
transfile(zipname.c_str());
DeleteFile(zipname.c_str());
num=0;
}
if(_access(zipname.c_str(),0)!=-1)
{
CloseZip(hz);
DeleteFile(zipname.c_str());
}
bcpname="C:\\Users\\Administrator\\Desktop\\photo\\"+getbcpname();
WriteLog("上传日期 %s",date);

_RecordsetPtr m_pRecordset("ADODB.Recordset");

string tb_name="NSMC_log_ClientCamera";
tb_name+=date;
string sql="select ClientID,FileName,FileSize from "+tb_name;
_bstr_t strSQL(sql.c_str());
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open(strSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
zipname=bcpname.substr(0,bcpname.size()-4)+".zip";
hz = CreateZip(zipname.c_str(),0);
while(!m_pRecordset->EndOfFile)
{
string ClientId;
string FileName;
string FileSize;
try
{
ClientId=(char*)(_bstr_t)m_pRecordset->GetCollect("ClientID");
FileName=(char*)(_bstr_t)m_pRecordset->GetCollect("FileName");
FileSize=(char*)(_bstr_t)m_pRecordset->GetCollect("FileSize");
}
catch(_com_error e)
{
m_pRecordset->MoveNext();
continue;
}
ClientId=ClientId.substr(1,ClientId.size()-2);
Rename_File(ClientId,FileName,FileSize,date,db_name);
m_pRecordset->MoveNext();
}
}
catch(_com_error e)
{
WriteLog("connect_sqlserver try:%s",e.ErrorMessage());
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
return;
}
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
return;
}

void CServiceModule::OperateSqlserver()
{
char date[32];
time_t nowtime;
time(&nowtime);
nowtime=nowtime-86400;
struct tm* ptm;
ptm = localtime(&nowtime);
sprintf(date, "%4d%.2d%.2d",ptm->tm_year + 1900, ptm->tm_mon + 1, ptm->tm_mday);
WriteLog("date: %s",date);
string sdate = date;
connect_sqlserver(nowtime);
while(1)
{
deletelog();
Sleep(1000*60*60*1);
char date2[32];
time_t nowtime2;
time(&nowtime2);
nowtime2=nowtime2-86400;
struct tm* ptm2;
ptm2 = localtime(&nowtime2);
sprintf(date2, "%4d%.2d%.2d",ptm2->tm_year + 1900, ptm2->tm_mon + 1, ptm2->tm_mday);
WriteLog("date: %s",date2);
string sdate2 = date2;
if(sdate!=sdate2)
{
Sleep(1000*60*60*2);
connect_sqlserver(nowtime2);
sdate=sdate2;
}

}
}

代码如上，由于篇幅限制，只把连接数据库和处理表里面数据的代码拿出来了，其他的一些实现代码我删除了，这个循环查询对方的数据库表里面的数据，可以跑两天，，查询两天的数据，每天查询耗时1小时左右，第三天进入循环3分钟后，没有查出任何数据，退出connect_sqlserver，查看日志文件，没有任何错误信息，求大神们指教，给点建议，只要建议合理就给分。

[oyljerry]

查看sql语句查出什么数据没

[yuerkewanghupo4]

对方以天为单位建立表，一天一张表，这个程序运行起来，会先取昨天的表的数据，然后通过循环，实现隔天同步数据，现在的问题是，我程序跑起来，取了两天的数据之后，第三天进入了connetc_sqlserver函数三分钟时间然后退出这个函数，一点数据没有取出来，前两天的取数据每次大约花费1小时，还有一次这个程序同步了一个星期的数据，然后不同步了，他是进入了connnect_sqlserver这个同步的接口，大约三分钟，没有返回任何数据，我打的日志没有反应到任何错误。我想知道是我的程序的问题，还是其他问题

[boylafong]

查询不到数据？

[yuerkewanghupo4]

有哪位大神知道原因啊，求指导啊，我不是研究vc的，大神们看看这程序问题出在哪了

[yuerkewanghupo4]

大神们，程序现在跑了7天了，每天也都取出数据了，不知道啥时候还会出现进入输出库取不出数据的情况，大神们讲解下吧

[风--还在吹]

你取数据的时候对面是否也在同时操作相应表？