slqite3库查询数据处理方式_C语言实现sqlite3数据库查询的基本方法

sqlite回传函数相关说了3种基本方法：callback，gettable和预处理stmt方法

下面给出测试代码和测试用数据库，代码如下

#include

#include

#include

#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;

}

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;

}

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

{

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;

}

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] \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;

}

编译 gcc sqlite3_search.c   sqlite3.c -o sqlite3_search -lpthread -ldl

测试

./sqlite3_search callback  KTV.sqlite  "SELECT id,name FROM OVT_SONG where SONG_STAR =鄭秀文'"

./sqlite3_search table  KTV.sqlite  "SELECT id,name FROM OVT_SONG where SONG_STAR =鄭秀文'"

./sqlite3_search stmt  KTV.sqlite  "SELECT id,name FROM OVT_SONG where SONG_STAR =鄭秀文'"