sqlite3编译与查询

1、sqlite3

http://www.sqlite.org/

下载

wget http://www.sqlite.org/2014/sqlite-amalgamation-3080403.zip

tar xvf  http://www.sqlite.org/2014/sqlite-amalgamation-3080403.zip

cd sqlite-amalgamation-3080403

2、写调用main函数和makefile

3、编译运行 

打开数据库->查询-> 关闭数据库；

 

获取表信息2种方式：

a) sqlite3_get_table

SQLITE_API int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
SQLITE_API void sqlite3_free_table(char **result);

  b)sqlite3_exec

　　回调函数，读取一行返回给回调函数处理

sqlite3_exec( db,sql, get_sqlite_table_record, NULL, &err);

SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

main函数

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

int get_sqlite_table_record( void * para, int n_column, char ** column_value, char ** column_name )
{
    int i;
    for( i = 0 ; i < n_column; i ++ )
    {
        printf("%s   ,%s\n",  column_name[i], column_value[i] );
    }
    printf("\n-----------------\n");        
    return 0;
}


int get_table(const char *databasename,char* sql)
{
    sqlite3 *db=NULL;
    char *err = 0;

    char **dbResult;
    int nRow,nCol;
    int rc;
    rc = sqlite3_open(databasename, &db);
    if(rc)
    {
        fprintf(stderr, "Can't open database: %s", sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
    else
        printf("You have opened a sqlite3 database  %s",databasename);
        ;

    rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nCol,&err);

    if(rc==SQLITE_OK)
    {
        printf("\nnRow=%d nCol=%d\n",nRow,nCol);
        int index = 0;
        int i = 0;
        int j = 0; 
        for(i=0;i<=nRow;i++)
        {    
            printf("\n");
            for(j=1;j<=nCol;j++)
            {
                printf("%s || ",dbResult[index++]);
            }
            printf("\n");    
        }
    }
    sqlite3_free_table( dbResult ); //free table

    sqlite3_close(db); //close databse
    dbResult= NULL;
    db=NULL;

    return 0;
}

int sqlite_record_call_back(const char *databasename,char *sql)
{
    sqlite3 *db=NULL;
    char *err = 0;
    int rc;
    //open database if not exist create;
    rc = sqlite3_open(databasename, &db);
    if(rc)
    {
        fprintf(stderr, "Can't open database: %s", sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
    else
        printf("opened a sqlite3 database %s",databasename);
        ;

    rc = sqlite3_exec( db,sql, get_sqlite_table_record, NULL, &err);
    if(rc==SQLITE_OK){
        printf("\nok\n");
    }
    else{
        printf("\nfailed\n");
        return -1;
    }    

    return 0;    
}

int main( void )
{
    int ret = -1;
    const char sqlitename[]="sqlite_test.db";
    
    //query all table in sqlite_test.db
    char sql_table[] = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";

    char sql1[] = "select id,url,title from dialinfo";
    char sql2[] = "select * from logos";

    ret = get_table(sqlitename,sql_table);

    ret = get_table(sqlitename,sql1);
    if(ret==0)
        printf("\nsusccess get_table\n");
    else
        printf("\nfailed get_table:%s\n",sql1);    

    ret = -1;
    ret = sqlite_record_call_back(sqlitename,sql2);
    if(ret==0)
        printf("\nsusccess\n");
    else{
        printf("\nfailed call_back:%s\n",sql2);
        return -1;
    }
        
    
    return 0;
}

 

Makefile

CC=gcc -g -Wall

all: a.out
clean:
    rm -rf *.o a.out
a.out: sql.o sqlite3.o
    $(CC)  -ldl -lpthread  -o a.out sqlite_main.o sqlite3.o

sql.o: sqlite_main.c 
    $(CC) -c sqlite_main.c
sqlite3.o: sqlite3.c sqlite3.h
    $(CC) -c sqlite3.c 

pack:
    tar jcvf sqlite_demo-`date +%Y%m%d%H%M%s`.tar.gz  *.c *.h Makefile *.db 

 如果已经安装sqlite3的动态链接库

可通过 gcc -g -Wall -ldl -lsqlite3 -lpthread -o a.out sqlite_main.o

 参考网址：

http://www.cnblogs.com/stephen-liu74/category/348367.html

http://blog.csdn.net/yoli_c_world/article/details/7939442

http://blog.csdn.net/daiyelang/article/details/8641778

  

file:https://files.cnblogs.com/frankwz/demo_sqlite-amalgamation-3080403.zip