oracle 数据库中用Bolb保存图片
一、数据库表设置
1、创建表
create table epoliceadmin.ep_image(image_id number(10) not null primary key, image blob);
2、创建序列
create sequence emp_sequence
increment by 1-- 每次加几个
start with 1--从几开始
nomaxvalue--不设置最大值
nocycle--不循环
nocache--无缓冲区
3、创建触发器
create trigger tb_tri before insert on epoliceadmin.ep_image for each row
begin
select emp_sequence.nextval into :new.image_id from dual;
end;
VC++代码
1、在stdafx.h文件中加入
#import "c:\program files\common files\system\ado\msado15.dll"no_namespacerename("EOF","adoEOF")
2、在类头文件中加入
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
int m_filelen;
char* m_pfilebuf;
int GetMaxIDFromBlob(void);
3、初始化数据库连接
CoInitialize(NULL);
HRESULT hr = m_pConnection.CreateInstance(__uuidof(Connection));
if (FAILED(hr))
{
AfxMessageBox("Create ADO Connection Failed!");
}
try
{
hr = m_pConnection->Open("Provider=OraOLEDB.Oracle.1;Data Source=epolice", "epoliceadmin", "epoliceadmin", adModeUnknown);
if (FAILED(hr))
{
AfxMessageBox("Cannot connect to database!");
}
}
catch (_com_error e)
{
AfxMessageBox(e.ErrorMessage());
}
4、完成GetMaxIDFromBlob(void)函数
int 类名::GetMaxIDFromBlob()
{
int MaxId;
CString strSQL;
strSQL.Format("select count(*) as num, Max(image_id) as maxid from epoliceadmin.ep_image");
BSTR bstrSQL = strSQL.AllocSysString();
HRESULT hr = m_pRecordset.CreateInstance("ADODB.Recordset");
if (FAILED(hr))
{
AfxMessageBox("Create ADO Recordset Failed!");
}
try
{
hr = m_pRecordset->Open(bstrSQL, (IDispatch*)m_pConnection, adOpenDynamic, adLockOptimistic, adCmdText);
if (FAILED(hr))
{
AfxMessageBox("Open ADO Recordset Failed");
}
}
catch (_com_error e)
{
AfxMessageBox("get maxid error");
return 0;
}
_variant_t var = m_pRecordset->GetCollect("num");
var.ChangeType(VT_I4, NULL);
int num = V_I4(&var);
if (num != 0)
{
_variant_t var1 = m_pRecordset->GetCollect("maxid");
var1.ChangeType(VT_I4, NULL);
MaxId = V_I4(&var1);
}
else
{
MaxId = 0;
}
m_pRecordset->Close();
return MaxId;
}
5、插入图片到数据库
HRESULT hr = m_pRecordset.CreateInstance("ADODB.Recordset");
if (FAILED(hr))
{
AfxMessageBox("Create ADO Recordset Failed!");
}
int maxid;
maxid = GetMaxIDFromBlob();
CString strSQL;
strSQL.Format("select * from epoliceadmin.ep_image where image_id= %d", maxid);
BSTR bstrSQL = strSQL.AllocSysString();
try
{
m_pConnection->CursorLocation = adUseClient;//使用客户端游标
m_pRecordset->Open(bstrSQL, (IDispatch*)m_pConnection, adOpenDynamic, adLockOptimistic, adCmdText);
}
catch (_com_error e)
{
CString strComError;
strComError.Format("错误编号: %08lx\n错误信息: %s\n错误源: %s\n错误描述: %s",
e.Error(), // 错误编号
e.ErrorMessage(), // 错误信息
(LPCSTR) e.Source(), // 错误源
(LPCSTR) e.Description()); // 错误描述
AfxMessageBox(strComError);
}
CString imagename;
CString imagepath = _T("c:\\picture\\picture_1.jpg");
int index = imagepath.ReverseFind('\\');
imagename = imagepath.Mid(index+1);
CFile file;
if (!file.Open(imagepath, CFile::modeRead))
{
AfxMessageBox("Open file failed");
exit(0);
}
m_filelen = file.GetLength();
m_pfilebuf = new char[m_filelen + 1];
memset(m_pfilebuf, 0, m_filelen + 1);
if (NULL == m_pfilebuf)
{
printf("malloc error!");
}
if (file.Read(m_pfilebuf, m_filelen) != m_filelen)
{
printf("read file failed");
}
try
{
if (!m_pRecordset->Supports(adAddNew))
{
printf("AddNew failed");
}
m_pRecordset->Requery(adOptionUnspecified);
m_pRecordset->AddNew();//为记录集添加新的一行,更新时就会把这条新纪录放到数据库中
}
catch (_com_error e)
{
CString strComError;
strComError.Format("错误编号: %08lx\n错误信息: %s\n错误源: %s\n错误描述: %s",
e.Error(), // 错误编号
e.ErrorMessage(), // 错误信息
(LPCSTR) e.Source(), // 错误源
(LPCSTR) e.Description()); // 错误描述
AfxMessageBox(strComError);
}
try
{
int NewId = maxid + 1;
m_pRecordset->PutCollect("image_id", _variant_t((long)NewId));
//blob
char *pBuf = m_pfilebuf;
VARIANT varBLOB;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound[1];
if(pBuf)
{
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = m_filelen;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound); ///创建SAFEARRAY对象
for (long i = 0; i < (long)m_filelen; i++)
SafeArrayPutElement(psa, &i, pBuf++); ///将pBuf指向的二进制数据保存到SAFEARRAY对象psa中
varBLOB.vt = VT_ARRAY | VT_UI1; ///将varBLOB的类型设置为BYTE类型的数组
varBLOB.parray = psa; ///为varBLOB变量赋值
m_pRecordset->GetFields()->GetItem("image")->AppendChunk(varBLOB);///加入BLOB类型的数据
}
}
catch(_com_error e)
{
CString strComError;
strComError.Format("错误编号: %08lx\n错误信息: %s\n错误源: %s\n错误描述: %s",
e.Error(), // 错误编号
e.ErrorMessage(), // 错误信息
(LPCSTR) e.Source(), // 错误源
(LPCSTR) e.Description()); // 错误描述
AfxMessageBox(strComError);
}
m_pRecordset->Update();
m_pRecordset->Close();
m_pRecordset = NULL;
delete m_pfilebuf;
m_pfilebuf = NULL;
最后释放COM组件
CoUninitialize();