sqlite3存取数据 - lightwear

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

苦闷的鞭炮 · Understanding sqlite3 ...· 2 天前 ·

听话的硬盘 · SQLite - 将值绑定到准备好的语句 [zh]· 2 天前 ·

神勇威武的楼梯 · How to store arrays ...· 2 天前 ·

咆哮的冰棍 · sqlite3存取数据 - ...· 2 天前 ·

帅气的创口贴 · tips/sources/SQLite的使用 ...· 2 天前 ·

个性的电池 · CCTV.com-张羽简历· 1 月前 ·

深情的单杠 · 刘雳宇课题组招聘信息 - 科研岗 - ...· 2 月前 ·

犯傻的水龙头 · openwrt 还原设置-掘金· 7 月前 ·

果断的李子 · java new Date() ...· 9 月前 ·

英俊的哑铃 · 拾色器设置 — Krita Manual ...· 9 月前 ·

sqlite3存取数据

本文主要使用sqlite3函数结合sqlite3语句来操作存取数据

sqlite3函数详细说明介绍见 https://www.sqlite.org/c3ref/funclist.html

另，sqlite3源文件可以在 https://www.sqlite.org/index.html 中下载

此次工作涉及到的函数如下：

/* 有则打开，无则创建 */
int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
/* 关闭 */
int sqlite3_close(sqlite3*);
/* 准备语句 */
int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
//
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_step(sqlite3_stmt*);
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 */
sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);
int sqlite3_finalize(sqlite3_stmt *pStmt);

sqlite3的一些语句：

/* 创建表*/
"create table table_name(volum_name type_name，...)"
/* 计算表内数据条数*/
"create table record"
/* 删除表内第一条数据*/
"delete from record where rowid in(select rowid from record limit 1 offset 0)"
/* 获取时间*/
"select datetime()"
/* 在表尾插入数据*/
"insert into record values(value1, value2, ...);"
/* 查找表内倒数第n条数据*/
"select * from record order by rowid desc limit 1 offset n"
/* 更新表内倒数第n条数据*/
"update record set volum_name1=(?), ... where rowid=(select rowid from record where rowid order by rowid desc limit 1 offset n)"

实际代码如下：

static int sql3_count_cb(void *arg,int count,char **val,char **name)
{
char arr[3] = {0};
uint16_t num = 0;
int ret = -1;

if(arg == NULL)
{
DLOGD("arg is null!");
return 0;
}

strcpy(arr,*val);
if(arr[1] == 0x00)
{
num = arr[0] - 0x30;
}
else if(arr[2] == 0x00)
{
num = (arr[0] - 0x30) * 10 + (arr[1] - 0x30);
}
else
{
num = (arr[0] - 0x30) * 100 + (arr[1] - 0x30)*10 + (arr[2] - 0x30);
}

*(uint16_t *)arg = num;

return 0;
}

static int sql3_datetime_cb(void *arg,int count,char **val,char **name)
{
if(arg != NULL)
{
strcpy((char *)arg, *val);
}
return 0;
}

static void sql_record_Db_Init(void)
{
int ret = -1;
char *sqlErrMsg = NULL;
char *sql = NULL;
sqlite3 *recordDb = NULL;

ret = sqlite3_open("./record.db", &recordDb);
if(ret != SQLITE_OK)
{
DLOGE("Error! open record.db unsuccessfully.");
return ;
}

/* table tag: upload flag | fresh date | record data */
sql = "create table record(uploadflag int, recorddate uint8_t[20], updatetime uint8_t[20], msgid uint8_t[64], data uint8_t[1000])";
ret = sqlite3_exec(recordDb, sql, NULL, NULL, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql delete unsuccessfully.");
sqlite3_close(recordDb);
return ;
}

sqlite3_close(recordDb);
}

int sql_storage_record(uint8_t *recordData, uint32_t dataLen, int upload, uint8 *msgId)
{
int ret = -1;
uint16_t count = 0;
uint32_t maxRowid = 0;
char *sqlErrMsg = NULL;
char *sql = NULL;
char datetime[20] = {0};
sqlite3 *recordDb = NULL;
sqlite3_stmt *stat = NULL;

ret = sqlite3_open("./record.db", &recordDb);
if(ret != SQLITE_OK)
{
DLOGE("Error! open record.db unsuccessfully.");
return -1;
}

/* delete the first data if record count to 100 */
sql = "select count(*) from record";
ret = sqlite3_exec(recordDb, sql, sql3_count_cb, &count, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql select count unsuccessfully.");
sqlite3_close(recordDb);
return -1;
}

/* only storage 100 record, so delete the earlist record */
if(count >= 100)
{
sql = "delete from record where rowid in(select rowid from record limit 1 offset 0)";
ret = sqlite3_exec(recordDb, sql, NULL, NULL, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("fail to sqlite3_exec:%s",sqlErrMsg);
sqlite3_free(sqlErrMsg);
return -1;
}
}

/* acquire datetime */
sql = "select datetime()";
ret = sqlite3_exec(recordDb, sql, sql3_datetime_cb, datetime, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql select datetime unsuccessfully.");
sqlite3_close(recordDb);
return -1;
}

/* prepare sql content */
ret = sqlite3_prepare_v2(recordDb,"insert into record values(?, ?, ?, ?, ?);", -1, &stat, NULL);
if(ret != SQLITE_OK)
{
DLOGE("[%s]:Error! execute sql prepare unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 1 */
ret = sqlite3_bind_int(stat, 1, upload);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 2 */
ret = sqlite3_bind_blob(stat, 2, datetime, 20, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 3 */
ret = sqlite3_bind_blob(stat, 3, datetime, 20, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 4 */
ret = sqlite3_bind_blob(stat, 4, recordData, dataLen, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 5 */
ret = sqlite3_bind_blob(stat, 5, msgId, 36, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* execute sql content */
ret = sqlite3_step(stat);
if(ret != SQLITE_DONE)
{
DLOGE("Error! execute sql content unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

sqlite3_finalize(stat);

/* prevent maxRowid overflow */
maxRowid = sqlite3_last_insert_rowid(recordDb);
if(maxRowid >= 0x7fffffff)
{
sql = "drop table record";
ret = sqlite3_exec(recordDb, sql, NULL, NULL, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql select datetime unsuccessfully.");
sqlite3_close(recordDb);
return -1;
}
sql_record_Db_Init();
}

sqlite3_close(recordDb);
return 0;
}

int sql_load_unupload_record(uint8_t *recordData, int *upload, int num, uint8_t *msgId)
{
int ret = -1;
char datetime[20] = {0};
uint16_t *pDataCount = NULL;
uint8_t *ptem = NULL;
char *sql = NULL;
char *sqlErrMsg = NULL;
sqlite3 *recordDb = NULL;
sqlite3_stmt *stat = NULL;

ret = sqlite3_open("./record.db", &recordDb);
if(ret != SQLITE_OK)
{
DLOGE("Error! open record.db unsuccessfully.");
return -1;
}

sql = "select * from record where rowid=(select max(rowid) from record)";

ret = sqlite3_prepare_v2(recordDb, "select * from record order by rowid desc limit 1 offset ?", -1, &stat, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql prepare unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 1 */
ret = sqlite3_bind_int(stat, 1, num);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

ret = sqlite3_step(stat);
if(ret != SQLITE_ROW)
{
DLOGE("Error! execute sql content unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

*upload = (uint16_t)sqlite3_column_int(stat, 0);
uint16_t dataLen = sqlite3_column_bytes(stat,0);

dataLen = sqlite3_column_bytes(stat,3);
const void *data = sqlite3_column_blob(stat, 3);
memcpy(msgId, data, 36);

data = sqlite3_column_blob(stat, 4);
dataLen = sqlite3_column_bytes(stat, 4);
memcpy(recordData, data, dataLen);

ret = sqlite3_step(stat);
if(ret != SQLITE_DONE)
{

const char * errs = sqlite3_errmsg(recordDb);
DLOGE("%s", errs);
DLOGE("Error! execute sql content unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

sqlite3_finalize(stat);
sqlite3_close(recordDb);
return 0;
}

int sql_update_unupload_record(uint8_t *recordData, uint32_t dataLen, int upload, int num)
{
int ret = -1;
char *sqlErrMsg = NULL;
char *sql = NULL;
char datetime[20] = {0};
sqlite3 *recordDb = NULL;
sqlite3_stmt *stat = NULL;

ret = sqlite3_open("./record.db", &recordDb);
if(ret != SQLITE_OK)
{
DLOGE("Error! open record.db unsuccessfully.");
return -1;
}

/* acquire datetime */
sql = "select datetime() from record";
ret = sqlite3_exec(recordDb, sql, sql3_datetime_cb, datetime, &sqlErrMsg);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql select datetime unsuccessfully.");
sqlite3_close(recordDb);
return -1;
}

/* prepate sql content */
ret = sqlite3_prepare_v2(recordDb, "update record set uploadflag=(?), updatetime=(?), data=(?) where rowid=(select rowid from record where rowid order by rowid desc limit 1 offset ?)", -1, &stat, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql prepare unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 3 */
ret = sqlite3_bind_blob(stat, 3, recordData, dataLen, NULL);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* bind sql content with arg 4 */
ret = sqlite3_bind_int(stat, 4, num);
if(ret != SQLITE_OK)
{
DLOGE("Error! execute sql bind unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

/* execute sql content */
ret = sqlite3_step(stat);
if(ret != SQLITE_DONE)
{
DLOGE("Error! execute sql content unsuccessfully.");
sqlite3_finalize(stat);
sqlite3_close(recordDb);
return -1;
}

sqlite3_finalize(stat);
sqlite3_close(recordDb);
return 0;
}