求救？！！使用ADO来遍历数据库的时候在100万条记录正常，但200万以上总是失败！

我用ADO+VC做了一个系统，需要将表中所有记录写成文件！所以用SELECT * FORM
得到recordset进行处理但我发现当记录数很巨大时！等待很慢!但还可以用但超过200万
条数据后就catch到错误！数据库连接不上！我该怎么办！是不是要设置某些参数，还望高手指点一二！

12 个解决方案

#1

可能需要设置数据库的timeout等参数的值，你应该到相应的数据库版去问一下，SQL SERVER版的高手很多，也很热情。

#2

网络速度慢了

#3

好的谢谢 jnxulei(石头)

#4

关注！

#5

#6

#7

不会吧，贴代码看看

我做个一个480万多的，没有出现异常啊

#8

我建议改造数据库
怎么会把这么多记录放在一张表里

#9

好的我把它贴出来！！！

//对数据库进行操作
CString   sqlStr;
char *p;
_RecordsetPtr m_pRecordset;
_variant_t RecordsAffected;
_variant_t vIndex ;
_variant_t temp_var;
_bstr_t temp_str;
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
    //校验invoice_index的数据
sqlStr.Format("SELECT * FROM INVOICE_INDEX WHERE TABLE_NAME='%s'",tablename);
    m_pRecordset->Open((_bstr_t)sqlStr,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
if(m_pRecordset->adoEOF)
{
m_pRecordset->Close();
AfxMessageBox("找不到该条记录!");
g_CheckOK=-1;
_endthread();
return;

}
else
{
//serial_begin
vIndex=(long)2;
temp_str=m_pRecordset->GetCollect(vIndex);
p=_com_util::ConvertBSTRToString(temp_str);
serial_begin=p;
delete p;
        //data_num
vIndex=(long)4;
temp_var=m_pRecordset->GetCollect(vIndex);
data_num=(long)temp_var;
//data_time
vIndex=(long)5;
temp_var=m_pRecordset->GetCollect(vIndex);
        data_time=(COleDateTime)temp_var;
//data_t=(COleDateTime)temp_var;
data_strTime=data_time.Format("%Y-%m-%d %H:%M:%S");
//data_key
vIndex=(long)9;
temp_str=m_pRecordset->GetCollect(vIndex);
p=_com_util::ConvertBSTRToString(temp_str);
data_key=p;
delete p;
//data_mac
vIndex=(long)10;
temp_str=m_pRecordset->GetCollect(vIndex);
p=_com_util::ConvertBSTRToString(temp_str);
data_mac=p;
delete p;
//TRACE("serial_begin=%s\n",serial_begin);
//TRACE("data_key=%s\n",data_key);
//TRACE("data_num=%d\n",data_num);
//TRACE("data_strTime=%s\n",data_strTime);
    //TRACE("data_mac=%s\n",data_mac);
m_pRecordset->Close();

//获得数据密钥
//先解码
memset(encdeckey,0,128);
memcpy(encdeckey,data_key,128);
memset(deckey,0,128);
rv = INFO_Base64Decode(encdeckey,deckey,&deckeylen);
if (rv != 0)
{
g_CheckOK=-1;
_endthread();
return;
}
//TRACE("deckey=%s\n",deckey);
memset(key,0,128);
rv = INFO_Decrypt((char*)backupkey,CGetLength(backupkey),(char*)deckey,deckeylen,(char*)key,&keylen,3);
if (rv != 0)
{
g_CheckOK=-1;
_endthread();
return;
}
//TRACE("key=%s\n",key);
//对mac进行校验
//生成校验码
memset(macStr,0,128);
memset(mac,0,128);
memset(pemMac,0,128);
wsprintf((char *)macStr,"%s,%s,%s",serial_begin,key,data_strTime);
//TRACE("macStr=%s\n",macStr);
//对MAC数据校验码进行摘要
rv = INFO_GenerateMac((char*)key,CGetLength(key),(char*)macStr,CGetLength(macStr),mac,&encDataLen,11);
if (rv != 0)
{
printf("INFO_Encrypt error ....");
g_CheckOK=-1;
_endthread();
return ;
}
rv = INFO_Base64Encode((unsigned char*)mac,encDataLen,(char*)pemMac);
if (rv != 0)
{
printf("INFO_Base64Encode error ....");
g_CheckOK=-1;
_endthread();
return ;
}
//TRACE("pemMac=%s\n",pemMac);
if (pemMac!=data_mac)
{
AfxMessageBox("invoice_index校验失败!");
g_CheckOK=-1;
_endthread();
return;
}
else
{
                 //AfxMessageBox("invoice_index校验成功!");
//以下校验发票数据
sqlStr.Format("SELECT * FROM INVOICE_%s ORDER BY INVOICE_SN  ",postfixname);
                 //TRACE("sqlStr=%s\n",sqlStr);
m_pRecordset->Open((_bstr_t)sqlStr,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
if(m_pRecordset->adoEOF)
{
m_pRecordset->Close();
AfxMessageBox("找不到该条记录!");
g_CheckOK=-1;
_endthread();
return;
}
else
{
m_pRecordset->MoveFirst();
//发票密码
CString       sn;
CString       invoice_pass;
char pemdecPass[128];
unsigned char decPass[128];
unsigned long decPasslen;
char  password[128];
int passwordlen;
                     g_CheckNum=0;
while (!m_pRecordset->adoEOF)
{
vIndex=(long)2;
                       temp_str=m_pRecordset->GetCollect(vIndex);
//sn
p=_com_util::ConvertBSTRToString(temp_str);
sn=p;
delete p;
vIndex=(long)3;
temp_str=m_pRecordset->GetCollect(vIndex);
//invoice_pass
p=_com_util::ConvertBSTRToString(temp_str);
invoice_pass=p;
delete p;
//data_mac
vIndex=(long)6;
temp_str=m_pRecordset->GetCollect(vIndex);
p=_com_util::ConvertBSTRToString(temp_str);
data_mac=p;
delete p;

//TRACE("sn=%s\n",sn);
//TRACE("invoice_pass=%s\n",invoice_pass);
//TRACE("data_mac=%s\n",data_mac);
//对发票密码进行解码
memset(pemdecPass,0,128);
memcpy(pemdecPass,invoice_pass,128);
memset(decPass,0,128);
rv = INFO_Base64Decode(pemdecPass,decPass,&decPasslen);
if (rv != 0)
{
g_CheckOK=-1;
m_pRecordset->Close();
_endthread();
return;
}
memset(password,0,128);
rv = INFO_Decrypt((char*)key,CGetLength(key),(char*)decPass,decPasslen,(char*)password,&passwordlen,3);
if (rv != 0)
{
g_CheckOK=-1;
m_pRecordset->Close();
_endthread();
return;
}
//TRACE("password=%s\n",password);

    //生成校验码
memset(macStr,0,128);
memset(mac,0,128);
memset(pemMac,0,128);
wsprintf((char *)macStr,"%s,%s,%d",sn,password,0);
                        //TRACE("macStr=%s\n",macStr);
//对MAC数据校验码进行摘要
rv = INFO_GenerateMac((char*)key,CGetLength(key),(char*)macStr,CGetLength(macStr),mac,&encDataLen,11);
if (rv != 0)
{
printf("INFO_Encrypt error ....");
g_CheckOK=-1;
m_pRecordset->Close();
_endthread();
return ;
}
rv = INFO_Base64Encode((unsigned char*)mac,encDataLen,(char*)pemMac);
if (rv != 0)
{
printf("INFO_Base64Encode error ....");
g_CheckOK=-1;
m_pRecordset->Close();
_endthread();
return ;
}
//TRACE("pemMac=%s\n",pemMac);

if (pemMac!=data_mac)
{
g_CheckOK=-1;
m_pRecordset->Close();
                                AfxMessageBox("数据校验失败!");
//_endthread();
return;
}
g_CheckOK++;
g_CheckNum++;
                        m_pRecordset->MoveNext();
}
m_pRecordset->Close();
}
}

}
_endthread();
}//try
catch(_com_error e)///捕捉异常
{
AfxMessageBox("读取数据库失败!CHECKOUT");///显示错误信息
        _endthread();
}
_endthread();
return;

#10

你是在服务器端，还是在客户端打开记录集
m_pRecordset->CursorLocation = adUseClient;
m_pRecordset->CursorLocation = adUseServer;
前者纪录在服务器缓存，后者在客户机缓存
对于数据量比较大的记录集
通常不一次就缓存所有数据，而是设置一个CacheSize
每次取n个，分页进行

#11

m_pRecordset->CursorLocation = adUseServer
我的是在服务器端运行！

#12

最后问题还是没有解决不过还是谢谢大家的热心帮助！

#1