使用SQLite数据库存取mp3和图片等二进制数据

源代码说话吧：

CString sql1 = "create table if not exists picture(id integer,pic blob)";
CString sql2 = L"insert into picture(id,pic)values(:id,:pic)";
CString sql3 = L"select * from picture";
pDB->BeginTrans();
pDB->Execute(_bstr_t(sql1));
pDB->CommitTrans();

FILE *fp;
LONG lfileSize = 0;
byte* picBuffer;
fp = fopen("./src.wma","rb");
if(NULL != fp)
{
fseek(fp,SEEK_END);
lfileSize = ftell(fp);
fseek(fp,SEEK_SET);
picBuffer = new byte[lfileSize];
size_t sz = fread(picBuffer,sizeof(byte),lfileSize,fp);
fclose(fp);
}

CComSafeArray<byte> *pcsfa;
CComSafeArrayBound bound[1];
bound[0].SetCount(lfileSize);
bound[0].SetLowerBound(0);
pcsfa = new CComSafeArray<byte>(bound,1);
for(LONG i = 0; i <(LONG)lfileSize; i++)
{
HRESULT hr = pcsfa->SetAt(i,picBuffer[i]);
}

_variant_t variant;
variant.vt = VT_ARRAY | VT_UI1;
variant.parray = pcsfa->m_psa;

ISDKCommandPtr pCommand;
pCommand = pDB->CreateCommand();
pCommand->PrepareCommand(_bstr_t(sql2));
pCommand->BindParaByIndex(0,_variant_t(::GetCurrentThreadId()));
pCommand->BindParaByIndex(1,variant);
pCommand->Execute();

ISDKResultSetPtr pResultSet;
pResultSet = pDB->Execute(_bstr_t(sql3));
cout << pResultSet->GetFieldCount() << endl;
cout << pResultSet->GetFieldName(1) << endl;

while(pResultSet->IsValidRow())
{
_variant_t val;
val = pResultSet->GetFieldValueByIndex(0);
int nId = val.lVal;
VariantClear(&val);

val = pResultSet->GetFieldValueByName(_bstr_t(L"pic"));
LONG lfileSize2 = val.parray->rgsabound->cElements;
byte* buf = new byte[lfileSize2];
if(val.vt == (VT_UI1|VT_ARRAY))
{
for(LONG index = 0; index < lfileSize2; index++)
{
::SafeArrayGetElement(val.parray,&index,buf+index);
}
}
FILE *fp2;
fp2 = fopen("dest.wma","wb");
if(NULL != fp2)
{
size_t ret = fwrite(buf,lfileSize2,fp2);
fclose(fp2);
}
delete[] buf;
cout << endl;
VariantClear(&val);

pResultSet->Next();
}
delete[] picBuffer;

下面的是原始的API接口存取图片的示例代码：

sqlite3 *db;
sqlite3_stmt *stat,*stat2;
char *zErrMsg = 0;
FILE *fp = NULL;
long filesize = 0;
char * ffile = NULL;
char *buf = NULL;

sqlite3_open("pic.db",&db);
if(db == NULL)
{
return -1;
}

fp = fopen("inpic.jpg","rb");
if(fp != NULL)
{
fseek(fp,SEEK_END);
filesize = ftell(fp);
fseek(fp,SEEK_SET);

ffile = new char[filesize];
size_t sz = fread(ffile,sizeof(char),filesize,fp);

fclose(fp);
}

sqlite3_exec(db,"create table pic (fliename varchar(128) unique,pic blob);",&zErrMsg);
sqlite3_prepare(db,"insert into pic values ('inpic.jpg',?)",-1,&stat,0);
sqlite3_bind_blob(stat,1,ffile,NULL);
sqlite3_step(stat);

sqlite3_prepare(db,"select * from pic",&stat2,0);
sqlite3_step(stat2);
const void * picData = sqlite3_column_blob(stat2,1);
int size = sqlite3_column_bytes(stat2,1);
buf = new char[size];
sprintf(buf,"%s",picData);

FILE*fp2;
fp2 = fopen("outpic.jpg","wb");
if(fp2 != NULL)
{
size_t ret = fwrite(picData,size,fp2);
fclose(fp2);
}

delete[] ffile;sqlite3_finalize(stat);sqlite3_finalize(stat2);sqlite3_close(db);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!