sqlite3test.cpp

1.编译色情sqlite源代码为dll；2.运用sqlite3数据库存储二进制数据到数据库

/*
#include <stdio.h>
#include <StdAfx.h>
#include <stdlib.h>
#include "sqlite3.h"
#include <afxwin.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}

int main(int argc, char **argv)
{
	/*
#if 1
	//回调函数
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;	
	HINSTANCE hd;
	hd = ::LoadLibrary("sqlite3.dll");
	if (NULL == hd)
	{
		exit(1);
	}
	
	typedef int (*sqlite3_open)( const char *filename, sqlite3 **ppDb );
	typedef int (*sqlite3_close)(sqlite3 *);
	typedef const char *(*sqlite3_errmsg)(sqlite3*);
	typedef int (*sqlite3_exec)( sqlite3*, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg );
	typedef void (*sqlite3_free)(void*);
	
	sqlite3_open psqlite3_open = (sqlite3_open)GetProcAddress(hd,"sqlite3_open");
	sqlite3_close psqlite3_close = (sqlite3_close)GetProcAddress(hd,"sqlite3_close");
	sqlite3_errmsg psqlite3_errmsg = (sqlite3_errmsg)GetProcAddress(hd,"sqlite3_errmsg");
	sqlite3_exec psqlite3_exec = (sqlite3_exec)GetProcAddress(hd,"sqlite3_exec");
	sqlite3_free psqlite3_free = (sqlite3_free)GetProcAddress(hd,"sqlite3_free");
	
	rc = psqlite3_open("test.db", &db);
	if( rc ){
		fprintf(stderr, "Can't open database: %s\n", psqlite3_errmsg(db));
		psqlite3_close(db);
		exit(1);
	}
	rc = psqlite3_exec(db, "select * from gg", callback, 0, &zErrMsg);
	rc = psqlite3_exec(db, "insert into gg values(22,33)", NULL, 0, &zErrMsg);
	if( rc!=SQLITE_OK )
	{
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		psqlite3_free(zErrMsg);
	}
	psqlite3_close(db);
	if (hd)
	{
		::FreeLibrary(hd);
	}
	
#else

	//没有回调函数
	sqlite3 *db;
	char *zErrMsg = 0;
	int nrow = 0, ncolumn = 0;  //查询语句返回的结果集的行数，列数
	char ** azResult;   //用于保存查询结果集数据
	HINSTANCE hd;
	
	
	hd = ::LoadLibrary("sqlite3.dll");
	if (NULL == hd)
	{
		exit(1);
	}
	
	typedef int (*sqlite3_open)( const char *filename, sqlite3 **ppDb );
	typedef int (*sqlite3_close)(sqlite3 *);
	typedef const char *(*sqlite3_errmsg)(sqlite3*);
	typedef int (*sqlite3_get_table)( sqlite3*, const char *sql, char ***pResult, int *nrow, int *ncolumn, char **errmsg );
	typedef void (*sqlite3_free_table)(char **result);
	
	sqlite3_open psqlite3_open				= (sqlite3_open)GetProcAddress(hd,"sqlite3_open");
	sqlite3_close psqlite3_close			= (sqlite3_close)GetProcAddress(hd,"sqlite3_close");
	sqlite3_errmsg psqlite3_errmsg			= (sqlite3_errmsg)GetProcAddress(hd,"sqlite3_errmsg");
	sqlite3_get_table psqlite3_get_table	= (sqlite3_get_table)GetProcAddress(hd,"sqlite3_get_table");
	sqlite3_free_table psqlite3_free_table	= (sqlite3_free_table)GetProcAddress(hd,"sqlite3_free_table");
	psqlite3_open("test.db", &db);
	
    if(db == NULL)
    { 
        return -1;
    }
	// sqlite3_get_table函数可以返回SQL语句执行之后的记录集，以及行列数
	psqlite3_get_table(db, "SELECT * FROM gg;", &azResult, &nrow, &ncolumn, &zErrMsg);
	
	//nrow表明一共有多少项符合该次查询条件的记录
    for(int k = 1; k <= nrow; k++)
    {
        printf(azResult[k*ncolumn]);   //对应name列的数据
		printf("\t");
		printf(azResult[k*ncolumn+1]);  //对应age列的数据
		printf("\n");
    }
	//释放为记录集分配的内存，如果返回了空记录集并不会发生错误
	psqlite3_free_table(azResult);
	//关闭数据库
    psqlite3_close(db);
	::FreeLibrary(hd);
#endif

	
	return 0;
}
*/

#include <stddef.h>
//#include <StdAfx.h> 
#include <stdlib.h>
#include <stdio.h>

#include "sqlite3.h"
#include <string.h>
#include <afxwin.h>
#include   <sys\stat.h> 

char *GetData(char*psz,int nrow,int ncolumn)
{
	if (NULL == psz)
	{
		return NULL;
	}
	return &psz[nrow*ncolumn];
}
int main (int argc, char **argv)
{
	sqlite3 *db;
	int rc, len, i, cols, type;
	char *next;
	char *zErrMsg = 0;
	sqlite3_stmt *st;
	char buffer2[1024]={'0'};
	FILE*	fp;
	long filesize = 0;
	char* ffile;
	FILE*	fp2;
	size_t sz;

	//  
	//      if (argc != 3) 
	//      {
	//          fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
	//          exit(EXIT_FAILURE);
	//      }
	HINSTANCE hd;
	hd  = ::LoadLibrary("sqlite3.dll");
	if (NULL == hd)
	{
		exit(1);
	}
	
	typedef int (*sqlite3_open)( const char *filename, sqlite3 **ppDb );
	typedef int (*sqlite3_close)(sqlite3 *);
	typedef const char *(*sqlite3_errmsg)(sqlite3*);
	typedef int (*sqlite3_get_table)( sqlite3*, const char *sql, char ***pResult, int *nrow, int *ncolumn, char **errmsg );
	typedef void (*sqlite3_free_table)(char **result);
	typedef int (*sqlite3_prepare)( sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail );
	typedef int (*sqlite3_step)(sqlite3_stmt*);
	
	typedef int (*sqlite3_column_count)(sqlite3_stmt *pStmt);
	typedef int (*sqlite3_column_type)(sqlite3_stmt*, int iCol);
	typedef const char *(*sqlite3_column_name)(sqlite3_stmt*, int N);
	typedef int (*sqlite3_column_int)(sqlite3_stmt*, int iCol);
	typedef double (*sqlite3_column_double)(sqlite3_stmt*, int iCol);
	typedef const unsigned char *(*sqlite3_column_text)(sqlite3_stmt*, int iCol);
	typedef int (*sqlite3_column_bytes)(sqlite3_stmt*, int iCol);
	typedef int (*sqlite3_finalize)(sqlite3_stmt *pStmt);
	typedef int (*sqlite3_exec)( sqlite3*, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg );
	typedef int (*sqlite3_bind_blob)(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
	typedef const void *(*sqlite3_column_blob)(sqlite3_stmt*, int iCol);
	sqlite3_open psqlite3_open				= (sqlite3_open)GetProcAddress(hd,"sqlite3_open");
	sqlite3_close psqlite3_close			= (sqlite3_close)GetProcAddress(hd,"sqlite3_close");
	sqlite3_errmsg psqlite3_errmsg			= (sqlite3_errmsg)GetProcAddress(hd,"sqlite3_errmsg");
	sqlite3_get_table psqlite3_get_table	= (sqlite3_get_table)GetProcAddress(hd,"sqlite3_get_table");
	sqlite3_free_table psqlite3_free_table	= (sqlite3_free_table)GetProcAddress(hd,"sqlite3_free_table");
	sqlite3_prepare psqlite3_prepare		= (sqlite3_prepare)GetProcAddress(hd,"sqlite3_prepare");
	sqlite3_step psqlite3_step			    = (sqlite3_step)GetProcAddress(hd,"sqlite3_step");
	sqlite3_column_count psqlite3_column_count      = (sqlite3_column_count)GetProcAddress(hd,"sqlite3_column_count");
	sqlite3_column_type psqlite3_column_type		= (sqlite3_column_type)GetProcAddress(hd,"sqlite3_column_type");
	sqlite3_column_name psqlite3_column_name		= (sqlite3_column_name)GetProcAddress(hd,"sqlite3_column_name");
	sqlite3_column_int psqlite3_column_int			= (sqlite3_column_int)GetProcAddress(hd,"sqlite3_column_int");
	sqlite3_column_double psqlite3_column_double	= (sqlite3_column_double)GetProcAddress(hd,"sqlite3_column_double");
	sqlite3_column_text psqlite3_column_text		= (sqlite3_column_text)GetProcAddress(hd,"sqlite3_column_text");
	sqlite3_column_bytes psqlite3_column_bytes		= (sqlite3_column_bytes)GetProcAddress(hd,"sqlite3_column_bytes");
	sqlite3_finalize psqlite3_finalize				= (sqlite3_finalize)GetProcAddress(hd,"sqlite3_finalize");
	sqlite3_exec psqlite3_exec = (sqlite3_exec)GetProcAddress(hd,"sqlite3_exec");
	sqlite3_bind_blob psqlite3_bind_blob = (sqlite3_bind_blob)GetProcAddress(hd,"sqlite3_bind_blob");
	sqlite3_column_blob psqlite3_column_blob = (sqlite3_column_blob)GetProcAddress(hd,"sqlite3_column_blob");
	rc = psqlite3_open("test.db", &db);
	if (rc != SQLITE_OK) 
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		psqlite3_close(db);
		exit(EXIT_FAILURE);
	}
	char *pfilename = "APPSMSAPI.rar";
	//insert into 二进制文件
	psqlite3_exec(db,"drop table list",0,0,0);
	psqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip int);", 0, 0, &zErrMsg);
	// 存储二进制文件
	psqlite3_prepare(db, "insert into list values ('APPSMSAPI.rar',?)", strlen("insert into list values ('APPSMSAPI.rar',?)"), &st, 0);
	fp = fopen(pfilename, "rb");
	if(fp != NULL)
	{
		fseek(fp, 0, SEEK_END);
		filesize = ftell(fp);
		fseek(fp, 0, SEEK_SET);
		ffile = new char[filesize+1];
		sz = fread(ffile, sizeof(char), filesize+1, fp);
		fclose(fp);
	}
	
	psqlite3_bind_blob(st, 1, ffile, filesize, NULL);
	psqlite3_step(st);
	
//修改二进制数据
	psqlite3_prepare(db, "update list set fzip = '?' where fliename = 'APPSMSAPI.rar'",\
		strlen("update list set fzip = '?' where fliename = 'APPSMSAPI.rar'"),
		&st, 0);
	fp = fopen("mmm.rar", "rb");
	if(fp != NULL)
	{
		fseek(fp, 0, SEEK_END);
		filesize = ftell(fp);
		fseek(fp, 0, SEEK_SET);
		ffile = new char[filesize+1];
		sz = fread(ffile, sizeof(char), filesize+1, fp);
		fclose(fp);
	}
	
	psqlite3_bind_blob(st, 1, ffile, filesize, NULL);
	psqlite3_step(st);
	//读取二进制文件
	psqlite3_prepare(db, "select * from list", strlen("select * from list"), &st, 0);
	psqlite3_step(st);
	
	const void * test = psqlite3_column_blob(st, 1);
	

	int size = psqlite3_column_bytes(st, 1);
	sprintf(buffer2, "%s", test);
	fp2 = fopen("outfile.rar", "wb");
	
	if(fp2 != NULL)
	{
		size_t ret = fwrite(test, sizeof(char), size, fp2);
		fclose(fp2);
	}	
	delete(ffile);
	psqlite3_finalize(st);

	/*
	len = strlen("select * from gg");
	rc = psqlite3_prepare(db, "select * from gg", len, &st,(const char **) &next);
	if (rc != SQLITE_OK) 
	{
	fprintf(stderr, "Error on sqlite3_prepare: %s\n", psqlite3_errmsg(db));
	psqlite3_close(db);
	exit(EXIT_FAILURE);
	}
	for (;;) 
	{
	rc = psqlite3_step(st);
	if (rc == SQLITE_ROW) 
	{
	cols = psqlite3_column_count(st);
	for (i = 0; i < cols; i++) 
	{
				type = psqlite3_column_type(st, i);
				switch (type) 
				{
				case SQLITE_INTEGER:
				printf("%s[%d] = %d\t", psqlite3_column_name(st, i),
				i,//*(int*)(void*)psqlite3_column_text(st,i) );
				psqlite3_column_int(st, i));
				break;
				case SQLITE_FLOAT:
				printf("%s[%d] = %f\t", psqlite3_column_name(st, i),
				i, psqlite3_column_double(st, i));
				break;
				case SQLITE_TEXT:
				printf("%s[%d] = '%s'\t", psqlite3_column_name(st, i),
				i, psqlite3_column_text(st, i));
				break;
				case SQLITE_BLOB:
				printf("%s[%d] = BLOB[%d bytes]\t",
				psqlite3_column_name(st, i),
				i, psqlite3_column_bytes(st, i));
				break;
				case SQLITE_NULL:
				printf("%s[%d] = NULL\t", psqlite3_column_name(st, i),
				i);
				break;
				default:
				printf("UNDEFINED Column[%d]\t", i);
				break;
				}
				}
				printf("\n");	
				} else if (rc == SQLITE_DONE)
				{
				break;
				} else
				{
				fprintf(stderr, "Error on sqlite3_step: %s\n", psqlite3_errmsg(db));
				psqlite3_close(db);
				exit(EXIT_FAILURE);
				}
				}
				
				  rc = psqlite3_finalize(st);
				  if (rc != SQLITE_OK) 
				  {
				  fprintf(stderr, "Error on sqlite3_finalize: %s\n", psqlite3_errmsg(db));
				  psqlite3_close(db);
				  exit(EXIT_FAILURE);
				  }
	*/	
	psqlite3_close(db);
	::FreeLibrary(hd);
	return 0;
}