当前访客身份:游客 [ 登录 | 加入开源中国 ]

代码分享

当前位置:
代码分享 » C/C++  » 数据库编程
margaritaville

SQLite Samples

margaritaville 发布于 2016年08月19日 10时, 0评/363阅
分享到: 
收藏 +0
0
samples
标签: <无>

代码片段(6) [全屏查看所有代码]

1. [代码] callback function     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

#include <stddef.h> 
#include <stdlib.h> 
#include <stdio.h> 
#include <sqlite3.h> 

static int callback (void *NotUsed, int argc, char **argv, char **azColName) 
{ 
    int i; for (i = 0; i < argc; i++) 
    { 
        if (argv[i] == NULL) 
        { 
            printf("%s = NULL\n", azColName[i]); 
        } 
        else
        { 
            printf("%s = %s\n", azColName[i], argv[i]); 
        }
    } 
    printf("\n"); return (0); 
} 

int main (int argc, char **argv) 
{ 
    sqlite3 *db; char *zErrMsg = 0; 
    int rc; 
    if (argc != 3) 
    { 
        fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]); 
        exit(EXIT_FAILURE); 
    } 
    rc = sqlite3_open(argv[1], &db); 

    if (rc != SQLITE_OK) 
    { 
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); 
        sqlite3_close(db); 
        exit(EXIT_FAILURE); 
    } 
    rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg); 
    if (rc != SQLITE_OK)
    { 
        fprintf(stderr, "SQL error: %s\n", zErrMsg); 
    } 
    sqlite3_close(db);
    exit(EXIT_SUCCESS); 
}

2. [代码] sqlite3_stmt read data     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

#include <stddef.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>

int
main (int argc, char **argv)
{
    sqlite3 *db;
    int rc, len, i, cols, type;
    char *next;
    sqlite3_stmt *st;

    if (argc != 3) {
        fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_open(argv[1], &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    len = strlen(argv[2]);
    rc = sqlite3_prepare(db, argv[2], len, &st, &next);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_prepare: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    for (;;) {
        rc = sqlite3_step(st);
        if (rc == SQLITE_ROW) {
            cols = sqlite3_column_count(st);
            for (i = 0; i < cols; i++) {
                type = sqlite3_column_type(st, i);
                switch (type) {
case SQLITE_INTEGER:
    printf("%s[%d] = %d\n", sqlite3_column_name(st, i),
        i, sqlite3_column_int(st, i));
    break;
case SQLITE_FLOAT:
    printf("%s[%d] = %f\n", sqlite3_column_name(st, i),
        i, sqlite3_column_double(st, i));
    break;
case SQLITE_TEXT:
    printf("%s[%d] = '%s'\n", sqlite3_column_name(st, i),
        i, sqlite3_column_text(st, i));
    break;
case SQLITE_BLOB:
    printf("%s[%d] = BLOB[%d bytes]\n",
        sqlite3_column_name(st, i),
        i, sqlite3_column_bytes(st, i));
    break;
case SQLITE_NULL:
    printf("%s[%d] = NULL\n", sqlite3_column_name(st, i),
        i);
    break;
default:
    printf("UNDEFINED Column[%d]\n", i);
    break;
                }
            }
            printf("\n");
        } else if (rc == SQLITE_DONE) {
            break;
        } else {
            fprintf(stderr, "Error on sqlite3_step: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
    }
    rc = sqlite3_finalize(st);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_finalize: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    sqlite3_close(db);
    exit(EXIT_SUCCESS);
}

3. [代码]An example using wildcard & bind      跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

#include <stddef.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>

#define BUF_LEN (1000)
#define SQL_ST "SELECT * FROM list WHERE name LIKE ?;"
/* assume the DB includes a table named 'list' consists of 'name' and etc. */

int
main (int argc, char **argv)
{
    sqlite3 *db;
    int rc, len, i, cols, type;
    char *next, buf[BUF_LEN];
    sqlite3_stmt *st;

    if (argc != 2) {
        fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_open(argv[1], &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    len = strlen(SQL_ST);
    rc = sqlite3_prepare(db, SQL_ST, len, &st, &next);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_prepare: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    printf("Please input name pattern.\n");
    if (fgets(buf, BUF_LEN, stdin) == NULL) {
        fprintf(stderr, "Something occurred on stdin\n");
        exit(EXIT_FAILURE);
    }
    len = strlen(buf);
    if (buf[len - 1] == '\n') {
        len--;
        buf[len] = '\0';
    }
    rc = sqlite3_bind_text(st, 1, buf, len, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_bind_text: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    for (;;) {
        rc = sqlite3_step(st);
        if (rc == SQLITE_ROW) {
            cols = sqlite3_column_count(st);
            for (i = 0; i < cols; i++) {
                type = sqlite3_column_type(st, i);
                switch (type) {
case SQLITE_INTEGER:
    printf("%s[%d] = %d\n", sqlite3_column_name(st, i),
        i, sqlite3_column_int(st, i));
    break;
case SQLITE_FLOAT:
    printf("%s[%d] = %f\n", sqlite3_column_name(st, i),
        i, sqlite3_column_double(st, i));
    break;
case SQLITE_TEXT:
    printf("%s[%d] = '%s'\n", sqlite3_column_name(st, i),
        i, sqlite3_column_text(st, i));
    break;
case SQLITE_BLOB:
    printf("%s[%d] = BLOB[%d bytes]\n",
        sqlite3_column_name(st, i),
        i, sqlite3_column_bytes(st, i));
    break;
case SQLITE_NULL:
    printf("%s[%d] = NULL\n", sqlite3_column_name(st, i),
        i);
    break;
default:
    printf("UNDEFINED Column[%d]\n", i);
    break;
                }
            }
            printf("\n");
        } else if (rc == SQLITE_DONE) {
            break;
        } else {
            fprintf(stderr, "Error on sqlite3_step: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
    }
    rc = sqlite3_finalize(st);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_finalize: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    sqlite3_close(db);
    exit(EXIT_SUCCESS);
}

4. [代码]Input data from CSV file      跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

/* An example using wildcard & bind */

/* Input data from CSV file */
/* Schema - CREATE TABLE list (id INTEGER PRIMARY KEY, name TEXT); */
/* CSV - ``id,name'' */

#include <stddef.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>

#define BUF_LEN (1000)
#define SQL_INSERT "INSERT INTO list (id, name) valueS (?, ?);"

int
main (int argc, char **argv)
{
    sqlite3 *db;
    int rc, len, i, cols, type;
    char *next, buf[BUF_LEN];
    sqlite3_stmt *st;
    char *err_msg;
    int id;

    if (argc < 2) {
        fprintf(stderr, "Usage: %s database\n", argv[0]);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_open(argv[1], &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on BEGIN TRANSACTION: %s\n", err_msg);
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    len = strlen(SQL_INSERT);
    rc = sqlite3_prepare(db, SQL_INSERT, len, &st, &next);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_prepare: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    while (fgets(buf, BUF_LEN, stdin) != NULL) {
        len = strlen(buf);
        if (len > 1) {
            if (buf[len - 1] == '\n') {
                buf[len - 1] = '\0';
                len--;
            }
        }
        rc = sqlite3_reset(st);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "Error on sqlite3_reset: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
        id = strtol(buf, NULL, 10);
        rc = sqlite3_bind_int(st, 1, id);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "Error on sqlite3_bind_int: %s\n",
                sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
        next = strchr(buf, ',');
        if (next == NULL) {
            rc = sqlite3_bind_null(st, 2);
            if (rc != SQLITE_OK) {
                fprintf(stderr, "Error on sqlite3_bind_null: %s\n",
                    sqlite3_errmsg(db));
                sqlite3_close(db);
                exit(EXIT_FAILURE);
            }
        } else {
            next++;
            len = strlen(next);
            rc = sqlite3_bind_text(st, 2, next, len, SQLITE_TRANSIENT);
            if (rc != SQLITE_OK) {
                fprintf(stderr, "Error on sqlite3_bind_text: %s\n",
                    sqlite3_errmsg(db));
                sqlite3_close(db);
                exit(EXIT_FAILURE);
            }
        }
        rc = sqlite3_step(st);
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "Error on sqlite3_step: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
    }
    rc = sqlite3_finalize(st);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_finalize: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on COMMIT TRANSACTION: %s\n", err_msg);
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    sqlite3_close(db);
    exit(EXIT_SUCCESS);
}

5. [代码]Input data from CSV file with char-code conversion      跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

/* Input data from CSV file with char-code conversion */
/* Schema - CREATE TABLE list (id INTEGER PRIMARY KEY, name TEXT); */
/* CSV - ``id,name'' */

#include <stddef.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <errno.h>
#include <iconv.h>

#define BUF_LEN (1000)
#define SQL_INSERT "INSERT INTO list (id, name) valueS (?, ?);"
#define DEFAULT_CODE "eucJP" /* EUC-JP, Shift-JIS = "sjis" */
#define SQLITE_CODE "utf8"

int
main (int argc, char **argv)
{
    sqlite3 *db;
    int rc, len, i, cols, type;
    sqlite3_stmt *st;
    char *err_msg;
    int id;
    iconv_t ic;
    char *code = DEFAULT_CODE;
    char in_buf[BUF_LEN], out_buf[BUF_LEN], *in_p, *out_p, *next;
    size_t in_len, out_len;

    if (argc < 2) {
        fprintf(stderr, "Usage: %s database [char-code]\n", argv[0]);
        exit(EXIT_FAILURE);
    }
    if (argc > 2) {
        code = argv[2];
    }
    ic = iconv_open(SQLITE_CODE, code);
    if (ic == (iconv_t)-1) {
        perror("iconv_open");
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_open(argv[1], &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on BEGIN TRANSACTION: %s\n", err_msg);
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    len = strlen(SQL_INSERT);
    rc = sqlite3_prepare(db, SQL_INSERT, len, &st, &next);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_prepare: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    while (fgets(in_buf, BUF_LEN, stdin) != NULL) {
        in_len = strlen(in_buf);
        if (in_len > 1) {
            if (in_buf[in_len - 1] == '\n') {
                in_buf[in_len - 1] = '\0';
                in_len--;
            }
        }
        in_p = in_buf;
        out_p = out_buf;
        out_len = BUF_LEN;
        if ((size_t)-1 == iconv(ic, &in_p, &in_len, &out_p, &out_len)) {
            perror("iconv");
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
        *out_p = '\0';
        rc = sqlite3_reset(st);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "Error on sqlite3_reset: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
        id = strtol(out_buf, NULL, 10);
        rc = sqlite3_bind_int(st, 1, id);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "Error on sqlite3_bind_int: %s\n",
                sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
        next = strchr(out_buf, ',');
        if (next == NULL) {
            rc = sqlite3_bind_null(st, 2);
            if (rc != SQLITE_OK) {
                fprintf(stderr, "Error on sqlite3_bind_null: %s\n",
                    sqlite3_errmsg(db));
                sqlite3_close(db);
                exit(EXIT_FAILURE);
            }
        } else {
            next++;
            len = strlen(next);
            rc = sqlite3_bind_text(st, 2, next, len, SQLITE_TRANSIENT);
            if (rc != SQLITE_OK) {
                fprintf(stderr, "Error on sqlite3_bind_text: %s\n",
                    sqlite3_errmsg(db));
                sqlite3_close(db);
                exit(EXIT_FAILURE);
            }
        }
        rc = sqlite3_step(st);
        if (rc != SQLITE_DONE) {
            fprintf(stderr, "Error on sqlite3_step: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(EXIT_FAILURE);
        }
    }
    rc = sqlite3_finalize(st);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on sqlite3_finalize: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error on COMMIT TRANSACTION: %s\n", err_msg);
        sqlite3_close(db);
        exit(EXIT_FAILURE);
    }
    sqlite3_close(db);
    exit(EXIT_SUCCESS);
}

6. [代码]Access Counter Program for WWW-SSI Pages      跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

/* Access Counter Program for WWW-SSI Pages */
/* Schema: counter(uri, count), primary-key=uri */

#include <stddef.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <unistd.h>

#define BUF_LEN (1000)

#ifndef DB_NAME
#define DB_NAME "counter.db"
#endif /* !DB_NAME */

#define CREATE_TBL "CREATE TABLE counter ( \
uri TEXT PRIMARY KEY, \
count INTEGER NOT NULL DEFAULT 1 \
);"
#define INSERT_URI "INSERT INTO counter (uri) valueS (?);"
#define COUNT_UP "UPDATE counter SET count = count + 1 \
WHERE uri = ?;"
#define GET_COUNT "SELECT count FROM counter WHERE uri = ?;"

void
error_exit (sqlite3 *db, char *message)
{
    fprintf(stderr, "Error on %s: %s\n", message, sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(EXIT_FAILURE);
}

int
main (int argc, char **argv)
{
    sqlite3 *db;
    int rc, len;
    char *next, *err_msg, *uri;
    sqlite3_stmt *st;
    int count;

    if (argc < 2) {
        fprintf(stderr, "usage: %s URI\n", argv[0]);
        exit(EXIT_FAILURE);
    }
    uri = argv[1];
    rc = sqlite3_open(DB_NAME, &db);
    if (rc != SQLITE_OK) error_exit(db, "open database");
    for (;;) {
        /* waiting for exclusive locking */
        rc = sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL,
            &err_msg);
        if (rc == SQLITE_OK) break;
        if (rc != SQLITE_BUSY) error_exit(db, "BEGIN EXCLUSIVE TRANSACTION");
#ifdef DEBUG
        fprintf(stderr, "DB is locked by other processes, waiting...\n");
#endif /* DEBUG */
        sleep(1);
    }
    len = strlen(GET_COUNT);
    rc = sqlite3_prepare(db, GET_COUNT, len, &st, &next);
    if (rc != SQLITE_OK) {
        /* table is not defined? */
        rc = sqlite3_exec(db, CREATE_TBL, NULL, NULL, &err_msg);
        if (rc != SQLITE_OK) error_exit(db, "CREATE_TBL");
        rc = sqlite3_prepare(db, GET_COUNT, len, &st, &next);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_prepare/GET_COUNT");
    }
    len = strlen(uri);
    rc = sqlite3_bind_text(st, 1, uri, len, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) error_exit(db, "sqlite3_bind_text/GET_COUNT");
    rc = sqlite3_step(st);
    if (rc == SQLITE_DONE) {
        /* uri does not exist in DB */
        rc = sqlite3_finalize(st);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_finalize/GET_COUNT");
        len = strlen(INSERT_URI);
        rc = sqlite3_prepare(db, INSERT_URI, len, &st, &next);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_prepare/INSERT_URI");
        len = strlen(uri);
        rc = sqlite3_bind_text(st, 1, uri, len, SQLITE_TRANSIENT);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_bind_text/INSERT_URI");
        rc = sqlite3_step(st);
        if (rc != SQLITE_DONE) error_exit(db, "sqlite3_step/INSERT_URI");
        count = 1;
    } else if (rc == SQLITE_ROW) {
        /* uri already exists in DB */
        count = sqlite3_column_int(st, 0);
        rc = sqlite3_finalize(st);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_finalize/GET_COUNT");
        len = strlen(COUNT_UP);
        rc = sqlite3_prepare(db, COUNT_UP, len, &st, &next);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_prepare/COUNT_UP");
        len = strlen(uri);
        rc = sqlite3_bind_text(st, 1, uri, len, SQLITE_TRANSIENT);
        if (rc != SQLITE_OK) error_exit(db, "sqlite3_bind_text/COUNT_UP");
        rc = sqlite3_step(st);
        if (rc != SQLITE_DONE) error_exit(db, "sqlite3_step/COUNT_UP");
        count++;
    } else {
        /* error? */
        error_exit(db, "sqlite3_step/GET_COUNT");
    }
#ifdef DEBUG
    {
        char buf[BUF_LEN];
        fprintf(stderr, "please type enter-key\n");
        fgets(buf, BUF_LEN, stdin);
    }
#endif /* DEBUG */
    rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) error_exit(db, "COMMIT TRANSACTION");
    sqlite3_close(db);
    printf("%d\n", count);
    exit(EXIT_SUCCESS);
}


开源中国-程序员在线工具:Git代码托管 API文档大全(120+) JS在线编辑演示 二维码 更多»

开源从代码分享开始 分享代码
margaritaville的其它代码 全部(18)...