Linux C Sqllite DataBase Options_csqllitebaseopt-CSDN博客

#include <stdio.h>

#include <string.h>
#include <sqlite3.h>

/* callback函数中：
* arg: 是从主函数传进来的参数
* values: 是查询结果的值
* names: 查询结果列的名字
*/
int wf_callback(void *arg, int nr, char **values, char **names)
{
    int i;
    FILE *fd;
    char str[1024];
    fd = (FILE *)arg; //将void *的参数强制转换为FILE *
    for (i=0; i<nr; i++) { //将查询结果写入到文件中
        memset(str, '\0', 1024);
        sprintf(str, "\t%s\t", values[i]);
        fwrite(str, sizeof(char), sizeof(str), fd);
    }
    memset(str, '\0', 1024);
    fwrite("\n", sizeof(char), 2, fd); //写完一条记录换行
    return 0; //callback函数正常返回0
}

int SQLOption( char * sqlstr,char * database)
{
    sqlite3 *db;

    sqlite3_open(database, &db) ; //打开（或新建）一个数据库

    /* 新建一张表 */
    if(!sqlite3_exec(db, sqlstr, NULL, NULL, NULL))
       return -1;
    sqlite3_close(db);
    return 0;
}

int SQLQuery( char * sqlstr, char * database , FILE * fd)
{
    sqlite3 *db;

    sqlite3_open(database, &db) ; //打开（或新建）一个数据库
    /* 新建一张表 */
    if(!sqlite3_exec(db, sqlstr, wf_callback, fd, NULL))
       return -1;
    sqlite3_close(db);

    return 0;
}

const char * SQLSingle( char * sqlstr, char * database )
{
    sqlite3 *db;
    static char result[1024]="ok";

    sqlite3_open(database, &db) ; //打开（或新建）一个数据库
    /* 新建一张表 */

    char **dbresult;
    int i,j,nrow,ncolumn,index;
    if(0 == sqlite3_get_table(db,sqlstr,&dbresult,&nrow,&ncolumn,NULL))
    {
   memset(result,0,sizeof(result));
        printf("query %i records.\n",nrow);
        index=ncolumn;
        memset(result,0,sizeof(result));
        for(i=0;i<nrow;i++){
                strcat(result,"#");
            for(j=0;j<ncolumn;j++){
                strcat(result,dbresult[index]);
                strcat(result,"|");
                index++;
            }
        }
       sqlite3_free_table(dbresult);
       sqlite3_close(db);
       return result;
    }
    else
    {
       sqlite3_free_table(dbresult);
       sqlite3_close(db);
        memset(result,0,sizeof(result));
        strcpy(result,"error");
       return result;
    }
}

/*
int main()
{
    char sql[128];
    FILE *fd;
    char resu[1024];

    memset(sql, 0, 128);
    strcpy(sql, "create table tbtest(id INTEGER PRIMARY KEY, data TEXT)");
    SQLOption(sql,"test.db");
    strcpy(sql, "insert into tbtest(data) values(\"hello\")");
    SQLOption(sql,"test.db");

    fd = fopen("test", "w");
    fwrite("Result: \n", sizeof(char), 10, fd);
    memset(sql, '\0', 128);
    strcpy(sql, "select * from tbtest");
    SQLQuery(sql, "test.db" , fd);

    memset(resu, '\0', 128);
    strcpy(resu, SQLSingle(sql, "test.db"));
    printf("%s",resu);
    fclose(fd);

    return 0;
}
*/

###############################################################

gcc SqlliteOpt.c -I../include -fPIC -shared -o libSqlliteOpt.so -lsqlite3

###############################################################

>ls

libSqlliteOpt.so main main.c SqlliteOpt SqlliteOpt.c test test.db

###############################################################

root@pfkj-desktop:/wls/jianggq# cat main.c
#include <stdio.h>
#include <string.h>
#include <dlfcn.h>
#include <sqlite3.h>

int main()
{
    char sql[128];
    FILE *fd;
    char resu[1024];
    int (*sqlopt)(char *sqlstr,char *database);
    int (*sqlquery)(char *sqlstr,char *database,FILE *outfile);
    const char * (*sqlsingle)(char *sqlstr,char*database);
    void *handle;

   handle=dlopen("./libSqlliteOpt.so",RTLD_LAZY);//open lib file
   sqlopt=dlsym(handle,"SQLOption");//call dlsym function
   sqlquery=dlsym(handle,"SQLQuery");//call dlsym function
   sqlsingle=dlsym(handle,"SQLSingle");//call dlsym function

    memset(sql, 0, 128);
    strcpy(sql, "create table tbtest2(id INTEGER PRIMARY KEY, data TEXT)");
    sqlopt(sql,"test.db");
    strcpy(sql, "insert into tbtest2(data) values(\"hello\")");
    sqlopt(sql,"test.db");

    fd = fopen("test", "w");
    fwrite("Result: \n", sizeof(char), 10, fd);
    memset(sql, '\0', 128);
    strcpy(sql, "select * from tbtest");
    sqlquery(sql, "test.db" , fd);

    memset(resu, '\0', 128);
    strcpy(resu, sqlsingle(sql, "test.db"));
    printf("%s",resu);

    strcpy(sql, "update tbtest2 set data='updaterow' where rowid=1");
    sqlopt(sql,"test.db");

    fclose(fd);

    dlclose(handle);
    return 0;
}

###############################################################
gcc -o main main.c -ldl

###############################################################

root@pfkj-desktop:/wls/jianggq# ./main
query 11 records.
#1|hello|#2|hello|#3|hello|#4|hello|#5|hello|#6|hello|#7|hello|#8|hello|#9|hello|#10|hello|#11|hello|root@pfkj-desktop:/wls/jianggq#