C语言实现sqlite3数据库查询的基本方法
来源:互联网 发布:短域名生成算法 编辑:程序博客网 时间:2024/04/30 05:42
sqlite回传函数相关 说了3种基本方法:callback,gettable和预处理stmt方法
下面给出测试代码和测试用数据库,代码如下
- #include <stdio.h>
- #include <stdlib.h>
- #include <string.h>
- #include "sqlite3.h"
-
-
-
-
-
-
-
-
-
- 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]);
- }
-
-
-
- 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_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:
- 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;
- }
编译 gcc sqlite3_search.c sqlite3.c -o sqlite3_search -lpthread -ldl