转载：http://blog.csdn.net/chence19871/article/details/7645934

#include <stdio.h>
#include <windows.h>
#include <iostream>
using namespace std;

extern "C"
{
	#include "./sqlite3.h"
};

void sqlite3_exec_report(sqlite3* db,const char* sql,sqlite3_callback func_callback,void *para);
void select_v2(sqlite3 *db,const char*sql);
void outputItem(sqlite3_stmt* stat,int nColumn,char* out_pic_path);
string GetType(int t);
//回调函数
int select_items(void *para,int n_column,char **column_val,char **column_name);//查询

int main(int args,char*argv[])
{
	int result,ret;
	sqlite3* db = NULL;
	char* errmsg = NULL;
	
	char module_path[100];

	//获取当前exe路径
	DWORD dwPathLen = GetModuleFileNameA(NULL,module_path,100);
	char *p = module_path + dwPathLen;
	while (1)
	{
		char c = *p;
		if (c == '\\')
		{
			*(p+1) = '\0';
			break;
		}
		p--;
	}
	//合成数据库文件路径
	strcat_s(module_path,100,"db\\mydb.db");
	string db_path(module_path);
	//打开(创建)数据库
	result = sqlite3_open(db_path.c_str(),&db);

	//----------------------------第一张表------------------------------
	//--不包含blob数据，通过普通的方法插入
	//-- --------------------------------------------------------------
	if (result == sqlITE_OK)
	{
		//创建第一张表
		sqlite3_exec_report(db,"create table MyTable_1(ID integer autoincrement primary key,name nvarchar(30)）",NULL,NULL);
		//先清除数据
		sqlite3_exec_report(db,"delete from MyTable_1",NULL);
		//插入数据	
		const char * sqls[] = {
			"insert into MyTable_1(ID,name) values(0,'钱学森')","insert into MyTable_1(ID,name) values(1,'邓稼先')",name) values(2,'钱三强')",name) values(3,'李四光')",name) values(4,'贺才良')"
		};
		for (int i = 0; i < sizeof(sqls)/sizeof(char*); i++)
		{
			sqlite3_exec_report(db,sqls[i],NULL);
		}

		//查询插入的数据  用的是回调的方法
		sqlite3_exec_report(db,"select * from MyTable_1",select_items,NULL);
		sqlite3_close(db);
	}
	else
	{
		//打开数据库失败
		cout << sqlite3_errmsg(db) << endl;
		ret = -1;
	}

	//-----------------------------第二张表------------------------------
	//--包含blob数据，通过sqlite3_prepare，sqlite3_bind_blob，sqlite3_step
	//-- 的方法实现数据的插入
	//-- --------------------------------------------------------------
	result = sqlite3_open(module_path,&db);
	if (result==sqlITE_OK)
	{
		sqlite3_exec_report(db,"create table stu_msg(ID integer primary key autoincrement,name nvarchar(32),picture blob)",NULL);
		sqlite3_exec_report(db,"delete from stu_msg",NULL);

		sqlite3_stmt *stat;
		const char *pzTail = NULL;

		const char *sqls[] = {
			"insert into stu_msg(ID,name,picture) values(0,'华罗庚',?);","insert into stu_msg(ID,picture) values(1,'钱学森',?);"
		};
		const char *names[] = {
			"pic\\hualuogen.jpg","pic\\qianxuesen.jpg"
		};

		for (int j=0; j<sizeof(sqls)/sizeof(char*); j++)
		{
			//准备
			result = sqlite3_prepare(db,sqls[j],-1,&stat,&pzTail);

			if (!result && stat)
			{
				// 读取头像数据
				*(p+1) = '\0';//重复利用ModuleFileName
				strcat_s(module_path,names[j]);
				FILE *file = NULL;
				fopen_s(&file,"rb+");
				char* data = NULL;
				long l_file_size = 0;
				if (file)
				{
					fseek(file,SEEK_END);
					l_file_size = ftell(file);
					fseek(file,SEEK_SET);
					data = new char[l_file_size];
					fread(data,1,l_file_size,file);

					//和sql的第一个？绑定（如果有多个问号，那就要分开绑定）
					result = sqlite3_bind_blob(stat,data,NULL);

					//将数据输入数据库
					result = sqlite3_step(stat);
					cout << "Insert result:" << sqlite3_errmsg(db) << endl;
					fclose(file);
					delete [] data;
				}			
				ret = 0;
			}
			else
			{
				//准备失败
				cout << "sqlite3_prepare Failed!  " << sqlite3_errmsg(db) << endl; 
				ret = -1;
			}
			//释放stat
			sqlite3_finalize(stat);
		}
		
		//查看插入数据
		select_v2(db,"select ID,picture from stu_msg");

		//关闭数据库
		sqlite3_close(db);
	} 
	else
	{
		cout << "打开数据库" << module_path << sqlite3_errmsg(db);
		ret = -1;
	}

	//---------------------读出各记录的头像-----------------
	result = sqlite3_open(db_path.c_str(),&db);
	if (!result)
	{
		sqlite3_stmt *stat;
		const char *pzTail = NULL;
		sqlite3_prepare(db,"select * from stu_msg;",&pzTail);
		int nColumn = sqlite3_column_count(stat);
		cout << "总的列数:" << nColumn << endl;

		result = sqlite3_step(stat);//读取某一条记录
		while (result == sqlITE_ROW) /* sqlite3_step() has another row ready */
		{
			*(p+1) = '\0';
			outputItem(stat,nColumn,module_path);
			cout << endl;
			result = sqlite3_step(stat);
		}
		cout << "end..." << endl;
	}

	return ret;
}

//输出一条记录
void outputItem(sqlite3_stmt* stat,char* out_pic_path)
{
	int m = -1;
	for (int k=0; k< nColumn; k++)
	{
		string s;
		int colType = sqlite3_column_type(stat,k);
		if (colType == 3)//text
		{
			m = k; //得到名字
		}
		s = GetType(colType);
		cout << "column " << k << ":" << s.c_str() << endl;

		switch(colType){
		case 1: cout << "Value: " << sqlite3_column_int(stat,k) << endl;break;
		case 2: cout << "Value: " << sqlite3_column_int(stat,k) << endl; break;
		case 3: cout << "Value: " << sqlite3_column_text(stat,k) << endl; break;		
		case 5: cout << "Value: " << "内容为空" << endl; break;
		case 4: cout << "Value: " << "二进制数据" << endl; 		
			const void* bi = sqlite3_column_blob(stat,k);
			int size = sqlite3_column_bytes(stat,k);
			char *name = NULL;
			if (m == -1)
			{
				name = "xxx";
			} 
			else
			{
				name = (char*)sqlite3_column_text(stat,m);
			}
			strcat_s(out_pic_path,"out_pic\\");
			strcat_s(out_pic_path,(char*)name);
			strcat_s(out_pic_path,".jpg");
			FILE *f = NULL;

			fopen_s(&f,out_pic_path,"wb+");
			if (f)
			{
				fwrite(bi,size,f);
				fclose(f);
			}
			break;
		}
	}
	
}

//获取类型
string GetType(int t)
{
	string s;
	switch (t)
	{
	case 1: s = "sqlITE_INTEGER";break;
	case 2: s = "sqlITE_FLOATE"; break;
	case 3: s = "sqlITE_TEXT"; break;
	case 4: s = "sqlITE_BLOB"; break;
	case 5: s = "sqlITE_NULL"; break;
	}
	return s;
}

//执行sql语句并报告执行结果
void sqlite3_exec_report(sqlite3* db,void *para)
{
	char* errmsg;
	int result = sqlite3_exec(db,sql,func_callback,para,&errmsg);
	cout << endl << endl;
	if(result != sqlITE_OK)
	{
		cout << "error code： " << result << endl
			<< "error： " << errmsg << endl;
	}
	else 
		cout << "sql execute succeed: " << sql << endl;
}

//一条记录调用一次回调函数
int select_items(void *para,char **column_name)
{
	int i;
	cout << "contain " << n_column << " columns" << endl;
	for (i = 0; i<n_column; i++)
	{
		cout << "column" << i << ": " << column_name[i] << endl
		     << "values: " << column_val[i] << endl;
	}
	return 0;
}

//另一种查询方式
void select_v2(sqlite3 *db,const char*sql)
{
#if 1
	
	char *errmsg = NULL;
	char** dbResult = NULL;
	int nRow,nColumn;
	int result = sqlite3_get_table( db,&dbResult,&nRow,&nColumn,&errmsg );

	//查询成功
	int index = nColumn; //dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据

	for( int i = 0; i < nRow ; i++ )
	{
		cout << "第" << i+1 << "条记录" << endl; 
		for(int j = 0 ; j < nColumn; j++ )
		{
			cout << "字段名：" << dbResult[j] << "  "
				<< "字段值：" << dbResult[index] << endl;
			++index; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
		}
		cout << "------------" << endl;
	}

	sqlite3_free_table(dbResult);//释放查询空间
#endif
}