使用 SQLite3 访问Blob字段

使用 SQLite3 访问Blob字段  


2012-06-13 18:47:19|  分类： 数据库 |字号 订阅
原文地址: http://blog.csdn.net/blueblood7/article/details/6576716
SQLite 是一个免费的文件型数据库。


1、 下载地址。


http://www.sqlite.org/download.html。


 


2、 编译。


C++：


建空的动态库或静态库，加入sqlite3.c，sqlite3.h和sqlite3ext.h，编译即可。


需注意的是动态库要把sqlite3.c 中的


#ifndef SQLITE_API


# define SQLITE_API


#endif


改为


#ifndef SQLITE_API


# define SQLITE_API                  __declspec(dllexport)


#endif


3、 建表。


包含字段 ID（图像ID，自增），FILENAME（图像名），DATA（图像数据）。


BOOL Ascii2Utf8(const char* pcszAscii, char* pszUtf8, int* pnUtf8Size)
{
    if (!pcszAscii || !pszUtf8 || !pnUtf8Size)
  return FALSE;
    
    int nWCharCount = MultiByteToWideChar(CP_ACP, 0, pcszAscii, -1, NULL, 0);
    wchar_t* pwszStr = new wchar_t[nWCharCount];
    MultiByteToWideChar(CP_ACP, 0, pcszAscii, -1, pwszStr, nWCharCount);
     
 int nUtf8Count = WideCharToMultiByte(CP_UTF8, 0, pwszStr, nWCharCount, NULL, 0, NULL, NULL);
 if (nUtf8Count > *pnUtf8Size)
 {
  *pnUtf8Size = nUtf8Count;
  return FALSE;
 }
    
    WideCharToMultiByte(CP_UTF8, 0, pwszStr, nWCharCount, pszUtf8, nUtf8Count, NULL, NULL);
 delete[] pwszStr;
 *pnUtf8Size = nUtf8Count;
 return TRUE;
}
BOOL Utf82Ascii(const char* pcszUtf8, char* pszAscii, int* pnAsciiSize)
{
    if (!pcszUtf8 || !pszAscii || !pnAsciiSize)
  return FALSE;
    
    int nWCharCount = MultiByteToWideChar(CP_UTF8, 0, pcszUtf8, -1, NULL, 0);
    wchar_t* pwszStr = new wchar_t[nWCharCount];
    MultiByteToWideChar(CP_UTF8, 0, pcszUtf8, -1, pwszStr, nWCharCount);
     
 int nAsciiCount = WideCharToMultiByte(CP_ACP, 0, pwszStr, nWCharCount, NULL, 0, NULL, NULL);
 if (nAsciiCount > *pnAsciiSize)
 {
  *pnAsciiSize = nAsciiCount;
  return FALSE;
 }
    
    WideCharToMultiByte(CP_ACP, 0, pwszStr, nWCharCount, pszAscii, nAsciiCount, NULL, NULL);
 delete[] pwszStr;
 *pnAsciiSize = nAsciiCount;
 return TRUE;
}
BOOL OpenDatabase(const char* pcszDBFileName, int nFlag, sqlite3** ppDb)
{
 char szUtf8DBFileName[MAX_PATH];
 int nSize = MAX_PATH;
 if (!Ascii2Utf8(pcszDBFileName, szUtf8DBFileName, &nSize))
  return FALSE;
 
 sqlite3* pDb;
 int nRtn = sqlite3_open_v2(szUtf8DBFileName, &pDb, nFlag, NULL);
 if (nRtn != SQLITE_OK)
  return FALSE;
 *ppDb = pDb;
 return TRUE;
}
void CDemoDlg::OnCreatetable() 
{
 // TODO: Add your control notification handler code here
 const char* pcszDBFileName = "d://测试中文//demo.db";
 sqlite3* pDb;
 if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, &pDb))
  return;
 
 const char* pcszSQL = "create table images(ID integer primary key, FILENAME varchar(50), DATA blob);";
 char* pszErrorInfo = NULL;
 int nRtn = sqlite3_exec(pDb, pcszSQL, NULL, NULL, &pszErrorInfo);
 if (nRtn != SQLITE_OK)
 {
  TRACE("sqlite3_exec error: %s/n", pszErrorInfo);
  sqlite3_free(pszErrorInfo);
 }
 
 sqlite3_close(pDb);
 return;
}






4、 插入数据。
BYTE* GetFileData(const char* pcszFileName, int* pnFileLen)
{
 FILE* fp = fopen(pcszFileName, "rb");
 if (!fp)
  return NULL;
 
 fseek(fp, 0, SEEK_END);
 int nFileLen = ftell(fp);
 fseek(fp, 0, SEEK_SET);
 
 BYTE* pbyData = new BYTE[nFileLen];
 if (!pbyData)
 {
  fclose(fp);
  return NULL;
 }
 fread(pbyData, nFileLen, 1, fp);
 fclose(fp);
 if (pnFileLen)
  *pnFileLen = nFileLen;
 
 return pbyData;
}
void FreeFileData(void* pData)
{
 delete[] pData;
}
void CDemoDlg::OnInsert() 
{
 // TODO: Add your control notification handler code here
 const char* pcszDBFileName = "d://测试中文//demo.db";
 sqlite3* pDb;
 if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READWRITE, &pDb))
  return;
 
 const char* pcszFileName = "d://测试图片//图片1.jpg";
 int nFileLen;
 BYTE* pbyData = GetFileData(pcszFileName, &nFileLen);
 if (!pbyData)
  return;
 
 char szSQL[100];
 sprintf(szSQL, "insert into images values(NULL, '%s', ?);", pcszFileName);
 char szUtf8SQL[200];
 int nSize = 200;
 if (!Ascii2Utf8(szSQL, szUtf8SQL, &nSize))
 {
  FreeFileData(pbyData);
  return;
 }
 
 sqlite3_stmt* pStmt = NULL;
 int nRtn = sqlite3_prepare_v2(pDb, szUtf8SQL, -1, &pStmt, NULL);
 if (nRtn == SQLITE_OK)
 {
  nRtn = sqlite3_bind_blob(pStmt, 1, pbyData, nFileLen, NULL);
  if (nRtn == SQLITE_OK)
  {
   nRtn = sqlite3_step(pStmt);
   if (nRtn == SQLITE_DONE)
   {
    TRACE("insert succ!/n");
   }
  }
  sqlite3_finalize(pStmt);
 }
 
 FreeFileData(pbyData);
 sqlite3_close(pDb);
 return;
}




5、 查询。
void CDemoDlg::OnQuery() 
{
 // TODO: Add your control notification handler code here
 const char* pcszDBFileName = "d://测试中文//demo.db";
 sqlite3* pDb;
 if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READONLY, &pDb))
  return;
 
 const char* pcszSQL = "select * from images;";
 
 sqlite3_stmt* pStmt = NULL;
 int nRtn = sqlite3_prepare_v2(pDb, pcszSQL, -1, &pStmt, NULL);
 if (nRtn == SQLITE_OK)
 {
  nRtn = sqlite3_step(pStmt);
  while (nRtn == SQLITE_ROW)
  {   
   int nID = sqlite3_column_int(pStmt, 0);
   const char* pcszUtf8FileName = (const char*)sqlite3_column_text(pStmt, 1);
   char szFileName[MAX_PATH];
   int nSize = MAX_PATH;
   Utf82Ascii(pcszUtf8FileName, szFileName, &nSize);
   const BYTE* pbyData = (const BYTE*)sqlite3_column_blob(pStmt, 2);
   int nDataLen = sqlite3_column_bytes(pStmt, 2);
   TRACE("id=%d, filename=%s, data len=%d/n", nID, szFileName, nDataLen);
   
   char szNewFileName[MAX_PATH];
   strcpy(szNewFileName, szFileName);
   char* pszExtension = strrchr(szNewFileName, '.');
   *pszExtension = 0;
   strcat(szNewFileName, "__");
   pszExtension = strrchr(szFileName, '.');
   strcat(szNewFileName, pszExtension);
   FILE* fp = fopen(szNewFileName, "wb");
   if (fp)
   {
    fwrite(pbyData, nDataLen, 1, fp);
    fclose(fp);
   }
   nRtn = sqlite3_step(pStmt);
  }
  sqlite3_finalize(pStmt);
 }
 sqlite3_close(pDb);
 return; 
}