国产精品高清网站,亚洲欧美中日韩,国产精品一区二区欧美

sqlite讀取,存儲二進制數據（轉載）

SQLite存取二進制數據
(http://hi.baidu.com/ejoywx/blog/item/4d7b418c8677cc00b31bbae9.html)
http://blog.sina.com.cn/s/blog_60f8483a0100ydaw.html

#include <stdio.h>
2

#include <memory.h>
3

#include <sqlite3.h>
4

int main(int argc,char *argv[])
6

{
7

sqlite3* pDB=NULL;
8

char* pErrMsg;
9

if(SQLITE_OK==sqlite3_open("./mydb.db",&pDB))
11

{
12

sqlite3_exec(pDB,"create table bList(fileName varchar(16) primary key, binData blob);",NULL,NULL,&pErrMsg);
13

{
14

char buffer[65535];
15

int iLen=0;
16

sqlite3_stmt* stmt;
17

{//存儲二進制數據
19

unsigned char hex[16]={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
20

sqlite3_prepare(pDB,"insert into bList values ('hex',?);",-1,&stmt,NULL);
21

sqlite3_bind_blob(stmt,1,hex,16,NULL);
22

sqlite3_step(stmt);
23

}
24

stmt=NULL;
26

{//存儲exe文件
27

memset(buffer,0,65535);
28

{
29

FILE* fp=fopen(argv[0],"rb");
30

iLen=fread(buffer,1,65535,fp);
31

fclose(fp);
32

}
33

sqlite3_prepare(pDB,"insert into bList values ('me.exe',?);",-1,&stmt,NULL);
34

sqlite3_bind_blob(stmt,1,buffer,iLen,NULL);
35

sqlite3_step(stmt);
36

}
37

stmt=NULL;
39

{//存儲普通c文檔
40

memset(buffer,0,65535);
41

{
42

FILE* fp=fopen("../main.c","rb");
43

iLen=fread(buffer,1,65535,fp);
44

fclose(fp);
45

}
46

sqlite3_prepare(pDB,"insert into bList values ('main.txt',?);",-1,&stmt,NULL);
47

sqlite3_bind_blob(stmt,1,buffer,iLen,NULL);
48

sqlite3_step(stmt);
49

}
50

stmt=NULL;
51

{//從數據庫中讀取txt文件數據
52

char *data=NULL;
53

memset(buffer,0,65535);
54

sqlite3_prepare(pDB, "select binData from bList where fileName='main.txt';", -1, &stmt, 0);
55

sqlite3_step(stmt);
56

data= (char *)sqlite3_column_blob(stmt,0);//得到紀錄中的BLOB字段
57

iLen= sqlite3_column_bytes(stmt, 0);//得到字段中數據的長度
58

memmove(buffer,data,iLen);
59

printf("%s\n",buffer);
60

}
61

}
62

sqlite3_close(pDB);
63

}
64

{
66http://blog.csdn.net/chence19871/article/details/7645934

#include <stdio.h>
2

#include <windows.h>
3

#include <iostream>
4

using namespace std;
5

extern "C"
7

{
8

#include "./sqlite3.h"
9

};
10

void sqlite3_exec_report(sqlite3* db, const char* sql, sqlite3_callback func_callback, void *para);
12

void select_v2(sqlite3 *db, const char*sql);
13

void outputItem(sqlite3_stmt* stat, int nColumn, char* out_pic_path);
14

string GetType(int t);
15

//回調函數
16

int select_items(void *para, int n_column, char **column_val, char **column_name);//查詢
17

int main(int args, char*argv[])
19

{
20

int result, ret;
21

sqlite3* db = NULL;
22

char* errmsg = NULL;
23

char module_path[100];
25

//獲取當前exe路徑
27

DWORD dwPathLen = GetModuleFileNameA(NULL, module_path, 100);
28

char *p = module_path + dwPathLen;
29

while (1)
30

{
31

char c = *p;
32

if (c == '\\')
33

{
34

*(p+1) = '\0';
35

break;
36

}
37

p--;
38

}
39

//合成數據庫文件路徑
40

strcat_s(module_path, 100, "db\\mydb.db");
41

string db_path(module_path);
42

//打開(創建)數據庫
43

result = sqlite3_open(db_path.c_str(), &db);
44

//----------------------------第一張表------------------------------
46

//--不包含blob數據，通過普通的方法插入
47

//-- --------------------------------------------------------------
48

if (result == SQLITE_OK)
49

{
50

//創建第一張表
51

sqlite3_exec_report(db, "create table MyTable_1(ID integer autoincrement primary key, name nvarchar(30)）", NULL, NULL);
52

//先清除數據
53

sqlite3_exec_report(db, "delete from MyTable_1", NULL, NULL);
54

//插入數據
55

const char * sqls[] = {
56

"insert into MyTable_1(ID,name) values(0, '錢學森')",
57

"insert into MyTable_1(ID,name) values(1, '鄧稼先')",
58

"insert into MyTable_1(ID,name) values(2, '錢三強')",
59

"insert into MyTable_1(ID,name) values(3, '李四光')",
60

"insert into MyTable_1(ID,name) values(4, '賀才良')"
61

};
62

for (int i = 0; i < sizeof(sqls)/sizeof(char*); i++)
63

{
64

sqlite3_exec_report(db, sqls[i], NULL, NULL);
65

}
66

//查詢插入的數據用的是回調的方法
68

sqlite3_exec_report(db, "select * from MyTable_1", select_items, NULL);
69

sqlite3_close(db);
70

}
71

else
72

{
73

//打開數據庫失敗
74

cout << sqlite3_errmsg(db) << endl;
75

ret = -1;
76

}
77

//-----------------------------第二張表------------------------------
79

//--包含blob數據，通過sqlite3_prepare，sqlite3_bind_blob，sqlite3_step
80

//-- 的方法實現數據的插入
81

//-- --------------------------------------------------------------
82

result = sqlite3_open(module_path, &db);
83

if (result==SQLITE_OK)
84

{
85

sqlite3_exec_report(db, "create table stu_msg(ID integer primary key autoincrement, name nvarchar(32), picture blob)", NULL, NULL);
86

sqlite3_exec_report(db, "delete from stu_msg", NULL, NULL);
87

sqlite3_stmt *stat;
89

const char *pzTail = NULL;
90

const char *sqls[] = {
92

"insert into stu_msg(ID, name, picture) values(0, '華羅庚', ?);",
93

"insert into stu_msg(ID, name, picture) values(1, '錢學森', ?);"
94

};
95

const char *names[] = {
96

"pic\\hualuogen.jpg",
97

"pic\\qianxuesen.jpg"
98

};
99

100

for (int j=0; j<sizeof(sqls)/sizeof(char*); j++)
101

{
102

//準備
103

result = sqlite3_prepare(db, sqls[j], -1, &stat, &pzTail);
104

105

if (!result && stat)
106

{
107

// 讀取頭像數據
108

*(p+1) = '\0';//重復利用ModuleFileName
109

strcat_s(module_path, 100, names[j]);
110

FILE *file = NULL;
111

fopen_s(&file, module_path, "rb+");
112

char* data = NULL;
113

long l_file_size = 0;
114

if (file)
115

{
116

fseek(file, 0, SEEK_END);
117

l_file_size = ftell(file);
118

fseek(file, 0, SEEK_SET);
119

data = new char[l_file_size];
120

fread(data, 1, l_file_size, file);
121

122

//和sql的第一個？綁定（如果有多個問號，那就要分開綁定）
123

result = sqlite3_bind_blob(stat, 1, data, l_file_size, NULL);
124

125

//將數據輸入數據庫
126

result = sqlite3_step(stat);
127

cout << "Insert result:" << sqlite3_errmsg(db) << endl;
128

fclose(file);
129

delete [] data;
130

}
131

ret = 0;
132

}
133

else
134

{
135

//準備失敗
136

cout << "sqlite3_prepare failed! " << sqlite3_errmsg(db) << endl;
137

ret = -1;
138

}
139

//釋放stat
140

sqlite3_finalize(stat);
141

}
142

143

//查看插入數據
144

select_v2(db, "select ID, name, picture from stu_msg");
145

146

//關閉數據庫
147

sqlite3_close(db);
148

}
149

else
150

{
151

cout << "打開數據庫" << module_path << sqlite3_errmsg(db);
152

ret = -1;
153

}
154

155

//---------------------讀出各記錄的頭像-----------------
156

result = sqlite3_open(db_path.c_str(), &db);
157

if (!result)
158

{
159

sqlite3_stmt *stat;
160

const char *pzTail = NULL;
161

sqlite3_prepare(db, "select * from stu_msg;", -1, &stat, &pzTail);
162

int nColumn = sqlite3_column_count(stat);
163

cout << "總的列數:" << nColumn << endl;
164

165

result = sqlite3_step(stat);//讀取某一條記錄
166

while (result == SQLITE_ROW) /* sqlite3_step() has another row ready */
167

{
168

*(p+1) = '\0';
169

outputItem(stat, nColumn, module_path);
170

cout << endl;
171

result = sqlite3_step(stat);
172

}
173

cout << "end

" << endl;
174

}
175

176

return ret;
177

}
178

179

//輸出一條記錄
180

void outputItem(sqlite3_stmt* stat, int nColumn, char* out_pic_path)
181

{
182

int m = -1;
183

for (int k=0; k< nColumn; k++)
184

{
185

string s;
186

int colType = sqlite3_column_type(stat, k);
187

if (colType == 3)//text
188

{
189

m = k; //得到名字
190

}
191

s = GetType(colType);
192

cout << "column " << k << ":" << s.c_str() << endl;
193

194

switch(colType){
195

case 1: cout << "Value: " << sqlite3_column_int(stat, k) << endl;break;
196

case 2: cout << "Value: " << sqlite3_column_int(stat, k) << endl; break;
197

case 3: cout << "Value: " << sqlite3_column_text(stat, k) << endl; break;
198

case 5: cout << "Value: " << "內容為空" << endl; break;
199

case 4: cout << "Value: " << "二進制數據" << endl;
200

const void* bi = sqlite3_column_blob(stat, k);
201

int size = sqlite3_column_bytes(stat,k);
202

char *name = NULL;
203

if (m == -1)
204

{
205

name = "xxx";
206

}
207

else
208

{
209

name = (char*)sqlite3_column_text(stat, m);
210

}
211

strcat_s(out_pic_path, 100, "out_pic\\");
212

strcat_s(out_pic_path, 100, (char*)name);
213

strcat_s(out_pic_path, 100, ".jpg");
214

FILE *f = NULL;
215

216

fopen_s(&f, out_pic_path, "wb+");
217

if (f)
218

{
219

fwrite(bi, 1, size, f);
220

fclose(f);
221

}
222

break;
223

}
224

}
225

226

}
227

228

//獲取類型
229

string GetType(int t)
230

{
231

string s;
232

switch (t)
233

{
234

case 1: s = "SQLITE_INTEGER";break;
235

case 2: s = "SQLITE_FLOATE"; break;
236

case 3: s = "SQLITE_TEXT"; break;
237

case 4: s = "SQLITE_BLOB"; break;
238

case 5: s = "SQLITE_NULL"; break;
239

}
240

return s;
241

}
242

243

//執行sql語句并報告執行結果
244

void sqlite3_exec_report(sqlite3* db, const char* sql, sqlite3_callback func_callback, void *para)
245

{
246

char* errmsg;
247

int result = sqlite3_exec(db, sql, func_callback, para, &errmsg);
248

cout << endl << endl;
249

if(result != SQLITE_OK)
250

{
251

cout << "error code： " << result << endl
252

<< "error： " << errmsg << endl;
253

}
254

else
255

cout << "SQL execute succeed: " << sql << endl;
256

}
257

258

//一條記錄調用一次回調函數
259

int select_items(void *para, int n_column, char **column_val, char **column_name)
260

{
261

int i;
262

cout << "contain " << n_column << " columns" << endl;
263

for (i = 0; i<n_column; i++)
264

{
265

cout << "column" << i << ": " << column_name[i] << endl
266

<< "values: " << column_val[i] << endl;
267

}
268

return 0;
269

}
270

271

//另一種查詢方式
272

void select_v2(sqlite3 *db, const char*sql)
273

{
274

#if 1
275

276

char *errmsg = NULL;
277

char** dbResult = NULL;
278

int nRow, nColumn;
279

int result = sqlite3_get_table( db, sql, &dbResult, &nRow, &nColumn, &errmsg );
280

281

//查詢成功
282

int index = nColumn; //dbResult 前面第一行數據是字段名稱，從 nColumn 索引開始才是真正的數據
283

284

for( int i = 0; i < nRow ; i++ )
285

{
286

cout << "第" << i+1 << "條記錄" << endl;
287

for(int j = 0 ; j < nColumn; j++ )
288

{
289

cout << "字段名：" << dbResult[j] << " "
290

<< "字段值：" << dbResult[index] << endl;
291

++index; // dbResult 的字段值是連續的，從第0索引到第 nColumn - 1索引都是字段名稱，從第 nColumn 索引開始，后面都是字段值，它把一個二維的表（傳統的行列表示法）用一個扁平的形式來表示
292

}
293

cout << "------------" << endl;
294

}
295

296

sqlite3_free_table(dbResult);//釋放查詢空間
297

#endif
298

}

puts("Press any key to exit

");
67

getchar();
68

}
69

return 0;
70

}

posted on 2013-03-24 16:06 陳志遠閱讀(9818) 評論(1) 編輯收藏引用

# re: sqlite讀取,存儲二進制數據（轉載） 2016-05-24 10:52 塵曦

隨心動

sqlite讀取,存儲二進制數據（轉載）

評論

導航

統計

常用鏈接

留言簿

隨筆檔案

文章分類

Othor's blog

搜索

最新評論

閱讀排行榜

評論排行榜

只有注冊用戶登錄后才能發表評論。
【推薦】100%開源！大型工業跨平臺軟件C++源碼提供，建模，組態！



網站導航: 博客園 IT新聞 BlogJava 博問 Chat2DB 管理