ADOQuery操作oracle的blob字段问题
我有一表
create table t_blob(id int,type varchar(20),content blob)
在实现图片，文档等数据的存取中遇到了如下问题：
1、blob写入
据查资料，oracle写入blob必须分两步，先插入空的再更新。
String sql= "INSERT INTO T_BLOB VALUES(:ID,:TYPE,EMPTY_BLOB()) ";
ADOQuery1-> Close();
ADOQuery1-> SQL-> Clear();
ADOQuery1-> SQL-> Add(sql);
ADOQuery1-> Parameters-> ParamByName( "ID ")-> Value=1;
ADOQuery1-> Parameters-> ParamByName( "TYPE ")-> Value= "jpg ";
ADOQuery1-> ExecSQL();
ADOQuery1-> Close();
TMemoryStream* ms=new TMemoryStream();
Image1-> Picture-> Graphic-> SaveToStream(ms);
ms-> Seek(0,soFromBeginning);
sql= "Update T_BLOB set CONTENT=:CONTENT where ID=1 ";
ADOQuery1-> SQL-> Clear();
ADOQuery1-> SQL-> Add(sql);
ADOQuery1-> Parameters-> ParamByName( "CONTENT ")-> LoadFromStream(ms,ftBlob);
ADOQuery1-> ExecSQL();
ADOQuery1-> Close();
delete ms;
按照上述方法可以成功执行，但是我通过DBImage控件关联发现图片并没有存入，因为DBImage上没有图片，通过BDE 方式查询得到的stream-> size为0。
不只为何没能插入？
2、读取
我用BDE方式的Query查询含有blob字段记录，执行query-> Open没有问题
但是用ADOQuery执行包含有blob字段的查询就提示 不支持的数据类型，不知何故？
代码如下：
String sql= "select CONTENT from T_BLOB where ID=1 ";
ADOQuery1-> Close();
ADOQuery1-> SQL-> Clear();
ADOQuery1-> SQL-> Add(sql);
ADOQuery1-> Prepared=true;
ADOQuery1-> Open();//执行到这句就提示不支持的数据类型
TStream* ms =new TMemoryStream();
ms=ADOQuery1-> CreateBlobStream(ADOQuery1-> FieldByName( "CONTENT "),bmRead);
if(ms-> Size> 0)
{
Image2-> Picture-> Graphic-> LoadFromStream(ms);
}
ADOQuery1-> Close();
delete ms;
主要是因为我必须用ADO,所以这些问题还必须解决，有哪位高手知道情不吝赐教！
[解决办法]
用TADOTable应该会影响效率吧?
我每次可能只查询一条记录，而且blob的数据很大，一般10MB的数量级
谁有什么好的办法，请出招啊
如果数据量大最好不要用ado
直接用 oci吧 效率高得多
[解决办法]
// insert blob field
TMemoryStream* pMS = new TMemoryStream;
// pMS Load File
....
....
pMS-> Seek(0,soFromBeginning);
// ADOQuery1 SQL TEXT = "insert into imagetab (id,image) values (:id,:image); "
ADOQuery1-> Parameters-> ParamByName( "id ")-> Value = // enter your id
ADOQuery1-> Parameters-> ParamByName( "image ")-> LoadFromStream(pMS, ftBlob);
ADOQuery1-> ExecSQL();
------解决方案--------------------
// 做了两个我的通用函数
bool __fastcall TMainForm::LoadBlobFromFile(TDataSet *DataSet, AnsiString FieldName,
AnsiString FileName)
{
bool Result = FileExists(FileName);
if (Result && DataSet)
if (!DataSet-> Eof || DataSet-> State == dsInsert)
if (DataSet-> FindField(FieldName))
{
bool IsEdit = false;
if (!(DataSet-> State == dsEdit || DataSet-> State == dsInsert))
{
IsEdit = true;
DataSet-> Edit();
}
try
{
TBlobField *tmpField = (TBlobField *)DataSet-> FieldByName(FieldName);
tmpField-> Clear();
tmpField-> LoadFromFile(FileName);
if (IsEdit) DataSet-> Post();
}
catch (...)
{
Result = false;
if (IsEdit)
{
try
{
DataSet-> Cancel();
}
catch (...) {}
}
}
}
return Result;
}
//---------------------------------------
bool __fastcall TMainForm::SaveBlobToFile(TDataSet *DataSet, AnsiString FieldName,
AnsiString FileName)
{
bool Result = false;
TStream *Stream;
if (DataSet)
if (!DataSet-> Eof)
if (DataSet-> FindField(FieldName))
{
TMemoryStream *tmpStream = new TMemoryStream();
Stream = DataSet-> CreateBlobStream(DataSet-> FieldByName(FieldName),bmRead);
if (Stream-> Size > 0)
{
tmpStream-> Clear();
tmpStream-> CopyFrom(Stream,Stream-> Size);
if (FileExists(FileName + ".bak ")) DeleteFile(FileName + ".bak ");
if (FileExists(FileName)) RenameFile(FileName, FileName + ".bak ");
AnsiString FilePath = ExtractFilePath(FileName);
if (FilePath.LastDelimiter( "\\ "))
FilePath.SetLength(FilePath.Length()-1);
if (!DirectoryExists(FilePath))
CreateDir(FilePath);
tmpStream-> SaveToFile(FileName);
if (FileExists(FileName + ".bak "))
if (FileExists(FileName)) DeleteFile(FileName + ".bak ");
else RenameFile(FileName + ".bak ", FileName);
//else RenameFile(FileName + ".bak ", ExtractFileName(FileName));
Result = true;
}
}
return (Result && FileExists(FileName));
}
//---------------------------------------
i
[解决办法]
// 添加图片路径到数据库
if( FileExists(strFileName) )
{
int age = FileAge(strFileName);
AnsiString strSql = "select * from TmcDev where DevID= ";
strSql = strSql + " ' "+strFileName.SubString(strFileName.Length()-13,10)+ " ' ";
ModifyADOQuery-> Close();
ModifyADOQuery-> SQL-> Clear();
ModifyADOQuery-> SQL-> Add( strSql );
ModifyADOQuery-> Open();
ModifyADOQuery-> Edit();
TBlobField *pField = (TBlobField *)ModifyADOQuery-> FieldByName( "Image ");
TADOBlobStream *pMem = new TADOBlobStream(pField, bmWrite);
pMem-> Seek(0, soFromBeginning);
TFileStream *fStream=new TFileStream(strFileName,fmOpenRead);
fStream-> Seek(0, soFromBeginning);
pMem-> CopyFrom(fStream,fStream-> Size);
delete fStream; fStream = NULL;
delete pMem; pMem = NULL;
ModifyADOQuery-> FieldByName( "ImageRcvDt ")-> AsString = FileDateToDateTime(age).DateTimeString();
ModifyADOQuery-> Post();
ModifyADOQuery-> Close();
}
