今天在学习一个项目的时候遇到了SQLite的使用。我就潜心研究了下。先在c平台下进行了S实验。成果如下
1.创建一个table,操作很简单,open,exec,close即可囊括全部。
代码如下:
#include <stdio.h>
#include <stdlib.h>
#include "util.h"
#include <sqlite3.h>
//#pragma commect(lib,"sqlite3.lib")
int main(int argc, const char * argv[])
{
sqlite3 *db;
char *zErr;
int rc;
char *sql;
rc = sqlite3_open("/Users/lichan/Desktop/test1.db", &db);
if (rc) {
fprintf(stderr, "cant open db %s \n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sql = "create table episodes(name text,id int)";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if (rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error:%s\n",zErr);
sqlite3_free(zErr);
}
}
sql = "insert into episodes(name,id) values('lichan',1)";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL error:%s\n",zErr);
sqlite3_free(zErr);
};
sqlite3_close(db);
//创建完成!
// insert code here...
printf("Hello, World!\n");
return 0;
}
但是出现的一个问题是,如果我不指定路径,只是给了db数据库的名称,我将找不到我的db文件在那里。于是我干脆就直接给了路径,便于我们以后的学习。
我只是通过代码写了第一行,其余的两行是我通过客户端写进去的。方便下面的使用
2.下面看看关于table内容的打印输出。
关于查询所有行的方法也比较简单,open,prepared,step(loop) close
//
// main.c
// sqlite3查询
//
// Created by lichan on 13-12-14.
// Copyright (c) 2013年 lichan. All rights reserved.
//
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
int main(int argc, const char * argv[])
{
char *path = "/Users/lichan/Desktop/test1.db";
int rc,i,ncols;
sqlite3 *db;
sqlite3_stmt *stmt;
char *sql;
const char *tail;
rc = sqlite3_open(path, &db);
if (rc) {
fprintf(stderr, "cant open db %s \n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sql = "select *from episodes;";
rc = sqlite3_prepare(db, sql, (int)strlen(sql), &stmt, &tail);
if (rc != SQLITE_OK) {
fprintf(stderr, "sql err %s \n",sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
ncols = sqlite3_column_count(stmt);//计算所有的行数。
while (rc == SQLITE_ROW) {
for (i = 0; i < ncols; i++) {
fprintf(stderr, " %s \n",sqlite3_column_text(stmt, i));
}
fprintf(stderr, "\n");
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
/*
** The application must finalize every [prepared statement] in order to avoid
** resource leaks. It is a grievous error for the application to try to use
** a prepared statement after it has been finalized. Any use of a prepared
** statement after it has been finalized can result in undefined and
** undesirable behavior such as segfaults and heap corruption.
*/
sqlite3_close(db);
// insert code here...
printf("Hello, World!\n");
return 0;
}
运行结果如下:
lichan
1
xuna
2
zahomingwei
3
Hello, World!
Program ended with exit code: 0