repos_util/SQLite3/testsqlite3/Example.h

#if 0 // 示例查询方式，本项目中不能运行;

#include <stdio.h> 
#include <stdlib.h> 
#include <string.h>
#include "sqlite3.h" 

//
//typedef int (*sqlite3_callback)( 
//    void* data,       /* Data provided in the 4th argument of sqlite3_exec() */ 
//    int ncols,        /* The number of columns in row */ 
//    char** values,    /* An array of strings representing fields in the row */ 
//    char** headers    /* An array of strings representing column names */ 
//); 

int callback(void* data, int ncols, char** values, char** headers)
{
	int i;
	int len =0;
	int ll=0;
	for(i=0; i < ncols; i++)
	{
		if(strlen(headers[i])>len)
			len = strlen(headers[i]);
	}

	for(i=0; i < ncols; i++) 
	{
		ll = len-strlen(headers[i]);
		while(ll)
		{
			fprintf(stdout," ");
			--ll;
		}
		fprintf(stdout, "%s: %s\n", headers[i], values[i]);
	}

	fprintf(stdout, "\n");
	return 0;
}

/************************************************************************/
/*  函数：search_by_callback[11/13/2016 IT];
/*  描述：按回调函数来查询表数据;
/*  参数：;
/*  	[IN] ：;
/*  	[OUT] ：;
/*  	[IN/OUT] ：;
/*  返回：void;
/*  注意：;
/*  示例：;
/*
/*  修改：;
/*  日期：;
/*  内容：;
/************************************************************************/
int search_by_callback(const char* db_name, const char* sql_cmd) 
{  
	int i = 0 ;  
	int j = 0 ;  
	int nrow = 0, ncolumn = 0;  
	char **azResult; //二维数组存放结果  
	sqlite3 *db=NULL;  
	char *zErrMsg = 0; 
	int rc;
	int len=0;

	if(access(db_name, 0) == -1)
	{
		fprintf(stderr, "%s not found\n", db_name);  
		return -1;  
	}

	rc = sqlite3_open(db_name, &db);

	if( rc != SQLITE_OK)
	{  
		fprintf(stderr, "%s open failed: %s\n", db_name,sqlite3_errmsg(db));  
		sqlite3_close(db);  
		return -1;  
	}  

	//查询数据  
	rc = sqlite3_exec( db,sql_cmd, callback, NULL, &zErrMsg );
	if( rc != SQLITE_OK)
	{  
		fprintf(stderr, "%s %s: %s\n", db_name,sql_cmd, sqlite3_errmsg(db));  
		if(zErrMsg)
		{
			fprintf(stderr,"ErrMsg = %s \n", zErrMsg); 
			sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);  
		return -1;  
	}

	if(zErrMsg)
	{
		sqlite3_free(zErrMsg);
	}

	//关闭数据库  
	sqlite3_close(db); 
	return 0;  

}

/************************************************************************/
/*  函数：search_by_table[11/13/2016 IT];
/*  描述：按sqlite3_get_table函数来查询表数据;
/*  参数：;
/*  	[IN] ：;
/*  	[OUT] ：;
/*  	[IN/OUT] ：;
/*  返回：void;
/*  注意：;
/*  示例：;
/*
/*  修改：;
/*  日期：;
/*  内容：;
/************************************************************************/
int search_by_table(const char* db_name, const char* sql_cmd) 
{  
	int i = 0 ;  
	int j = 0 ;  
	int nrow = 0, ncolumn = 0;  
	char **azResult; //二维数组存放结果  
	sqlite3 *db=NULL;  
	char *zErrMsg = 0; 
	int rc;
	int len=0;

	if(access(db_name, 0) == -1)
	{
		fprintf(stderr, "%s not found\n", db_name);  
		return -1;  
	}

	rc = sqlite3_open(db_name, &db);

	if( rc != SQLITE_OK)
	{  
		fprintf(stderr, "%s open failed: %s\n", db_name,sqlite3_errmsg(db));  
		sqlite3_close(db);  
		return -1;  
	}  

	//查询数据  
	rc = sqlite3_get_table( db , sql_cmd, &azResult , &nrow , &ncolumn , &zErrMsg );  
	if( rc != SQLITE_OK)
	{  
		fprintf(stderr, "%s %s: %s\n", db_name,sql_cmd, sqlite3_errmsg(db));  
		if(zErrMsg)
			fprintf(stderr,"ErrMsg = %s \n", zErrMsg); 
		sqlite3_free_table( azResult ); 
		sqlite3_close(db);  
		return -1;  
	}  

	for(j=0; j < ncolumn; j++)
	{
		if(strlen(azResult[j])>len)
			len = strlen(azResult[j]);
	}

	//从第0索引到第 nColumn - 1索引都是字段名称
	//从第 nColumn 索引开始，后面都是字段值
	for( i = 0 ; i < nrow; i++ )
	{
		for(j=0; j < ncolumn; j++)
		{
			int ll = (len- strlen(azResult[j]));
			while(ll)
			{
				printf(" ");
				--ll;
			}
			printf( "%s: %s\n", azResult[j], azResult[(i+1)*ncolumn+j]);
		}
		printf("\n");
	}

#if 0	
	for( j = 0 ; j < ncolumn; j++ )
	{
		printf( "%s	", azResult[j]);
	}
	printf( "\n");	

	for( i=ncolumn ; i<( nrow + 1 ) * ncolumn ; i++ )
	{
		if(((i-ncolumn+1)%ncolumn) ==0)
			printf( "%s\n",azResult[i]);
		else
			printf( "%s	",azResult[i] ); 
	}
#endif
	//与sqlite3_get_table对应，释放掉  azResult 的内存空间  
	sqlite3_free_table( azResult ); 
	//关闭数据库  
	sqlite3_close(db); 
	return 0;  

} 

/************************************************************************/
/*  函数：search_by_stmt[11/13/2016 IT];
/*  描述：按sqlite3_prepare_v2函数来查询表数据;
/*  参数：;
/*  	[IN] ：;
/*  	[OUT] ：;
/*  	[IN/OUT] ：;
/*  返回：void;
/*  注意：;
/*  示例：;
/*
/*  修改：;
/*  日期：;
/*  内容：;
/************************************************************************/
int search_by_stmt(const char* db_name, const char* sql_cmd) 
{  
	sqlite3 *db=NULL;  
	sqlite3_stmt* stmt = 0;
	int ncolumn = 0;
	const char *column_name;
	int vtype , i;
	int rc;

	if(access(db_name, 0) == -1)
	{
		fprintf(stderr, "%s not found\n", db_name);  
		return -1;  
	}

	rc = sqlite3_open(db_name, &db);

	if( rc != SQLITE_OK)
	{  
		fprintf(stderr, "%s open failed: %s\n", db_name,sqlite3_errmsg(db));  
		sqlite3_close(db);  
		return -1;  
	}  

	//查询数据  
	rc = sqlite3_prepare_v2(db, sql_cmd, -1, &stmt, 0);
	if (rc != SQLITE_OK)
	{
		fprintf(stderr, "%s %s: %s\n", db_name,sql_cmd, sqlite3_errmsg(db));  
		sqlite3_close(db);  
		return -1;  
	}

	ncolumn = sqlite3_column_count(stmt);

	while (sqlite3_step(stmt) == SQLITE_ROW)
	{	
		for(i = 0 ; i < ncolumn ; i++ )
		{
			vtype = sqlite3_column_type(stmt , i);
			column_name = sqlite3_column_name(stmt , i);
			switch( vtype )
			{
			case SQLITE_NULL:
				fprintf(stdout, "%s: null\n", column_name);	
				break;
			case SQLITE_INTEGER:
				fprintf(stdout, "%s: %d\n", column_name,sqlite3_column_int(stmt,i));	
				break;
			case SQLITE_FLOAT:
				fprintf(stdout, "%s: %f\n", column_name,sqlite3_column_double(stmt,i));	
				break;
			case SQLITE_BLOB: /* arguably fall through... */
				fprintf(stdout, "%s: BLOB\n", column_name);	
				break;
			case SQLITE_TEXT: 
				fprintf(stdout, "%s: %s\n", column_name,sqlite3_column_text(stmt,i));	
				break;
			default:
				fprintf(stdout, "%s: ERROR [%s]\n", column_name, sqlite3_errmsg(db));	
				break;
			}
		}
	}

	sqlite3_finalize(stmt);

	//关闭数据库  
	sqlite3_close(db); 
	return 0;  

}

int main(int argc, char* argv[])
{
	int i=0;
	if(argc != 4)
	{
		fprintf(stderr, "usage: %s [table/callback/stmt] <db_name> <sql_cmd>\r\n", argv[0]);
		return -1;
	}

	if((strcmp(argv[1],"table") ==0))
		search_by_table(argv[2],argv[3]);
	else if((strcmp(argv[1],"callback") ==0))
		search_by_callback(argv[2],argv[3]);
	else
		search_by_stmt(argv[2],argv[3]);
	return 0;
}


#endif