SQLITE c API

昨天看了下sqlite的使用，照着官方的文档，写了个c程序，记在这里。

test_sqlite3.c

#include <stdio.h>
#include <unistd.h>
#include <sqlite3.h>
#include <strings.h>
#include <string.h>

int main()
{
        struct sqlite3 *db;
        struct sqlite3_stmt *db_stmt;


        if (SQLITE_OK != sqlite3_open("ouye.db", &db))
                goto ERR;

        char *sql;
        char *pzTail;

        /* 插入数据 */
        sql = "create table if not exists ouye (id integer primary key autoincrement, use bobol not null, mark text not null);insert into ouye values(NULL, 0, 'ouye');insert into ouye values(NULL, 1,'hoho');";
        pzTail = sql;

        while (strcmp(pzTail, "") != 0)
        {
                if (SQLITE_OK != sqlite3_prepare(db, sql, strlen(sql), &db_stmt, (const char **)&pzTail) ||
                        SQLITE_DONE != sqlite3_step(db_stmt))
                        goto ERR;

                sql = pzTail;
        }

        /* 释放db_stmt 必须要释放否者内存泄漏 */
        if (SQLITE_OK != sqlite3_finalize(db_stmt))
                        goto ERR;

        /* 把插入的数据读出来 */
        sql="select * from ouye;";
        pzTail = sql;

        if (SQLITE_OK != sqlite3_prepare(db, sql, strlen(sql), &db_stmt, (const char **)&pzTail))
                goto ERR;

        int i, row=0, count;
        const unsigned char *result;

        while (1)
        {

                /* 执行编译好的语句 */
                switch (sqlite3_step(db_stmt))
                {
                        case SQLITE_ROW:
                                printf("SQLITE_ROW \n");
                                break;
                        case SQLITE_DONE:
                                printf("SQLITE_DONE \n");
                                goto END;
                        case SQLITE_BUSY:
                                printf("SQLITE_BUSY \n");
                                goto ERR;
                        case SQLITE_MISUSE:
                                printf("SQLITE_MISUSE \n");
                                goto ERR;
                        case SQLITE_ERROR:
                                printf("SQLITE_ERROR \n");
                                goto ERR;
                        default:
                                goto ERR;
                }


                /* 打印总共有多少列返回 */
                if (row == 0)
                {
                        count = sqlite3_column_count(db_stmt);
                        printf("column = %d\n", count);
                }


                /* 打印数据 */
                for (i=0; i<count; i++)
                {
                        result = sqlite3_column_text(db_stmt, i);
                        printf("row=%d column=%d result=%s\n", row, i, result);
                }

                row++;
        }

END:
        /* 释放db_stmt 必须要释放否者内存泄漏 */
        if (SQLITE_OK != sqlite3_finalize(db_stmt))
                        goto ERR;

        sqlite3_close(db);

        return 0;

ERR:
        printf("%s\n", sqlite3_errmsg(db));

        return -1;

}

makefile

CC=gcc
CFLAGS=-Wall -g
LIBS=-l sqlite3

test_sqlit3: test_sqlit3.o
        $(CC) -o $@ $< $(LIBS)

clean:
        rm test_sqlit3 *.o


欢迎大家指正