sqlite3存取数据
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 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, 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;
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· 手把手教你更优雅的享受 DeepSeek
· AI工具推荐:领先的开源 AI 代码助手——Continue
· 探秘Transformer系列之(2)---总体架构
· V-Control:一个基于 .NET MAUI 的开箱即用的UI组件库
· 乌龟冬眠箱湿度监控系统和AI辅助建议功能的实现