ADO往SQLSERVER插入BLOB字段,超过60M就异常,求救!!!!

[VcandI]

用ADO往SQLSERVER插入BLOB字段,超过60M,插入后读ActualSize正常,用pMySet->Update()后,过好长时间出现runtime error,退出.还看到内存随着AppedChuck()的次数在迅速消耗,是不是SQL SERVER 需特别设置?请高手诊断,开方!!!

[VcandI]

每次插入一祯,每祯15M,插入代码如下:

void COracToSqlSock::LobToDb(DATA_ITEMHD *buf)
{
VARIANT varChunk;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound[1];


BYTE *pVal;
int wide=buf->wide;
CString strSegName;
strncpy(strSegName.GetBuffer(wide),(char*)buf->GetDataPtr(),wide);


DebugPrint(" 插入BLOB字段: %s \n",strSegName);

DATA_ITEMHD *p=(DATA_ITEMHD *)buf->GetNextPtr();
if(p->type!=GDD_BLOB) return;
wide=p->wide;
if(wide>ChunkSize)
{
DebugPrint("ChunkSize 太小,缓冲不够!需%d字节.\n",wide);
return;
}
if(wide<=0)
return;

pVal=(BYTE*)p->GetDataPtr();
// g_pRecordset->Fields->GetItem(_bstr_t(strSegName))->put_Attributes(adFldLong);
// while(1)
{
rgsabound[0].cElements =wide;
rgsabound[0].lLbound = 0;

///创建SAFEARRAY对象
psa = SafeArrayCreate(VT_UI1,1,rgsabound);

for(long index=0;index<wide;index++)
{
if(FAILED(SafeArrayPutElement(psa,&index,pVal+index)))
DebugPrint("SAFEARRAY错误!\n");
}

//为varChunk变量赋值
varChunk.vt = VT_ARRAY|VT_UI1;
varChunk.parray = psa;

//加入BLOB类型的数据
try{
// for(int i=0;i<4 && strSegName=="DAT" ;i++)//测试用 6,93M
{
g_pRecordset->Fields->GetItem(_bstr_t(strSegName))->AppendChunk(varChunk);
long l=g_pRecordset->Fields->GetItem(_bstr_t(strSegName))->ActualSize;
DebugPrint("ActualSize=%d\n",l);
}
}
catch (_com_error *e)
{
DebugPrint(e->ErrorMessage());
}
::VariantClear(&varChunk);
::SafeArrayDestroyData( psa);
}
m_strLastSeg=strSegName;
}

[快乐鹦鹉]

代码看看。
数据库没有什么问题吧

[VcandI]

单独写了个测试程序,丢开网络传输,在本机上试,BLOB字段超过60M后,同样内存占用很大,机器运行奇慢,执行到Update()处,就出现异常.是不是SQL SERVER 对BLOB支持不好?因为连到本机的ACCESS数据库,400M的数据都插进去了,当然插入时机器反应慢,但不致于象SQL SERVER一样慢和插不了,内存也不见猛耗.盼有过此方面经验的同仁相助!!!!!!!!

[快乐鹦鹉]

sql server中，并未见有blob类型的字段阿

[VcandI]

text和image型即是.

[VcandI]

大家都未碰到这个问题吗?自己顶

[newzk]

我只知道7.0的要把数据库的大文本限制环境变量改成你要的值，不然会出错。

[VcandI]

你指的是 max text吗?其默认为65535,没改能过40M,早超过它了,后改为2G,最大值,同样不行

[VcandI]

难道没有人用ADO插入TEXT或IMAGE字段超过60M的吗?能否帖出代码和服务器的设置?在这上面已耗2-3周了,实在不能再拖了!!!

[jazy]

我的代码差不多，可以一次插入60m
我的连接打开时用的adLockBatchOptimistic，然后不是用Update而是UpdateBatch，就这里跟你不一样。

[balloy]

如果数据不是非常保密的话，可以考虑存为文件啊。我们做上传都是这样的。
当然，这个就要看你的具体应用了。

[VcandI]

仍然无人再复,失望...................................

[masterz]

keyword: UPDATETEXT
Recordset With/Without AppendChunk is unusable to store large binary data to MS SQL (>10th of MB). We have to use native MS SQL methods - UPDATETEXT command.
I used the same code with Recordset to store extra upload field - client IP, description, ... As a result of it I have UploadID - primary key of row, which has zero length of binary field named 'Data'


'Code 6
'Set block size to 1MB
Const BlockSize = &H100000

'Process source data using blocks
Dim BlockCounter, HexDataBlock
For BlockCounter = 0 To Form("DBFile").Length Step BlockSize
'Get a part of source data as a Hex string
HexDataBlock = Form("DBFile").HexString(BlockCounter, BlockSize)

'Add a part of source data to the field using SQL UPDATETEXT command
SQL = "DECLARE @dataptr binary(16)" & vbCrLf
SQL = SQL & "SELECT @dataptr = TEXTPTR(Data)"
SQL = SQL & " FROM Upload WHERE UploadID=" & UploadID & vbCrLf
SQL = SQL & "UPDATETEXT Upload.Data @dataptr " & BlockCounter & " NULL 0x" & HexDataBlock
'Execute prepared SQL command
Conn.Execute SQL, 0, adCmdText + adExecuteNoRecords
Next 'BlockCounter

And how this code works? For example 100MB file. Uploaded data are stored on the disk. We add another fields of upload record to the database (some bytes/kB).
Then we process source binary data using blocks. HexString reads 1MB of source data from the disk, converts this data to HexString (+4MB - Unicode HexString), creates UPDATETEXT SQL (+4MB) and sends this SQL command to MS SQL. MS SQL gets this string (+10MB ADO +2MB in SQL) converts it to binary data (+1MB) and the data are stored to the database file.
This code takes constant amount of memory - the amount of memory depends on source block size. For 1MB block - VBS code takes 8MB of memory; ADO Execute method takes 10MB (I do not know why such big amount).

Although this code takes a big amount of processor time, I test it to store files with up to 512MB of size.
http://www.pstruh.cz/help/scptutl/pa28.htm

[VcandI]

谢各位,看来与sql server有关,还与SQL服务器的内存等配置有关,在本机上只运行这个用户进程,最大存到120M,问题虽未根本解决,但只能到此,散分.