使用 SQLite3 访问Blob字段

 

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

        

C#：

还没编译http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki中的源码，只是用了它的二进制安装包。

 

3、 建表。

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

C++：

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 hereconst 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;} 

C#：

private void CreateTable_Click(object sender, EventArgs e) { string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3"; SQLiteConnection conn = new SQLiteConnection(strDataSource); string strSQL = "create table images(ID integer primary key, FILENAME varchar(50), DATA blob);"; SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = strSQL; try { conn.Open(); int nRtn = cmd.ExecuteNonQuery(); MessageBox.Show("建表成功。"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { cmd.Dispose(); conn.Close(); } return; } 

4、 插入数据。

C++：

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 hereconst 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;} 

C#：

private void Insert_Click(object sender, EventArgs e) { string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3"; SQLiteConnection conn = new SQLiteConnection(strDataSource); string strFileName = "d://测试图片//1//ISV000005_liuyang_001.JPG"; FileStream fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read); int nFileLen = (int)fs.Length; byte[] pbyData = new byte[nFileLen]; fs.Read(pbyData, 0, nFileLen); fs.Close(); string strSQL = "insert into images values(null, :filename, :data);"; SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = strSQL; SQLiteParameter param = new SQLiteParameter("filename", DbType.String); param.Value = strFileName; cmd.Parameters.Add(param); SQLiteParameter param2 = new SQLiteParameter("data", DbType.Binary); param2.Value = pbyData; cmd.Parameters.Add(param2); try{ conn.Open(); int nRowCount = cmd.ExecuteNonQuery(); MessageBox.Show("已插入" + nRowCount + "行。"); } catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { cmd.Dispose(); conn.Close(); } } 

 

5、 查询。

C++：

void CDemoDlg::OnQuery() {// TODO: Add your control notification handler code hereconst 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;} 

C#：

private void Query_Click(object sender, EventArgs e) { string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3"; SQLiteConnection conn = new SQLiteConnection(strDataSource); string strSQL = "select * from images;"; SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = strSQL; try { conn.Open(); SQLiteDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { int nID = dataReader.GetInt32(0); string strFileName = dataReader.GetString(1); int nDataLen = (int)dataReader.GetBytes(2, 0, null, 0, 0); byte[] pbyData = new byte[nDataLen]; dataReader.GetBytes(2, 0, pbyData, 0, nDataLen); int nPos = strFileName.LastIndexOf('.'); string strNewFileName = strFileName.Substring(0, nPos); strNewFileName += "_"; strNewFileName += strFileName.Substring(nPos); FileStream fs = new FileStream(strNewFileName, FileMode.Create, FileAccess.Write); fs.Write(pbyData, 0, nDataLen); fs.Close(); } dataReader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { cmd.Dispose(); conn.Close(); } }