Sqlite3 从创建数据库、表，到读写内容（源码实现）

最新推荐文章于 2022-09-24 23:40:03 发布

天道TD

最新推荐文章于 2022-09-24 23:40:03 发布

阅读量387

点赞数

分类专栏： SQLite 文章标签： sqlite c++

本文链接：https://blog.youkuaiyun.com/chuxiaobobo/article/details/118176120

版权

SQLite 专栏收录该内容

1 篇文章

订阅专栏

#include "sqlite3.h"

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

void sql_exec(char* command)
{
	char* zErrMsg = 0;
	int rc;

	/* Execute SQL statement */
	rc = sqlite3_exec(db, command, callback, 0, &zErrMsg);
	if (rc != SQLITE_OK) {
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	else 
	{
	}
}

void ConnectPictureDB(void)
{
	char* zErrMsg = 0;
	int rc;
	rc = sqlite3_open("Picture.db", &db);

	if (rc)
	{
	    printf("Can't open database: %s\n", sqlite3_errmsg(db));
	    return;
	}
	else
	{
	    printf("Opened database successfully\n");
	}


	/* Create SQL statement */
	char sql[512] = "CREATE TABLE IF NOT EXISTS AlarmInfo("  \
		"TIME           TEXT    NOT NULL," \
		"UUID           CHAR(32)    NOT NULL," \
		"NAME           CHAR(52)    NOT NULL," \
		"TYPE           INT     NOT NULL," \
		"LON			INT," \
		"LAT			INT," \
		"Speed			INT," \
		"AlarmNum		INT," \
		"rcvNum			INT," \
		"UploadFlag		INT," \
		"ErrorCode      INT );";

	/* Execute SQL statement */
	rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
	if (rc != SQLITE_OK) {
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	else {
		printf("AlarmInfo Table created successfully\n");
	}


	// 删除15天之前的记录
	char str[256] = "DELETE FROM AlarmInfo WHERE date('now', '-15 day') >= date(TIME);";
	sql_exec((char*)str);

	//sqlite3_close(db);
}

void InsertAlarmInfo(sDBAlarmInfo* p)
{
	char* zErrMsg = 0;
	int rc;

	char sql[256];
	sprintf(sql, "INSERT INTO AlarmInfo VALUES('%s', '%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d); ", \
		p->time, p->UUID, p->name, p->type, p->lon, p->lat, p->speed, p->AlarmNum, p->RcvNum, p->UploadFlag, p->ErrorCode);

	/* Execute SQL statement */
	if (db == NULL)
	{
		return;
	}
	rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	else
	{
		printf("Records created successfully\n");
	}
}

void UpdateAlarmDB(char* name, char* Field, int value)
{
	char* zErrMsg = 0;
	int rc;

	char sql[256];
	memset(sql, 0, 256);
	sprintf(sql, "UPDATE AlarmInfo set %s  = '%d'   where NAME = '%s';", Field, value, name);

	/* Execute SQL statement */
	if (db == NULL)
	{
		return;
	}
	rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	else
	{
		printf("Records created successfully\n");
	}
}
// 读取表全部内容
void SelectDB(void)
{
	char sql[50] = "SELECT * from AlarmInfo";
	sql_exec((char*)sql);
}

详见：https://www.runoob.com/sqlite/sqlite-tutorial.html

按时间删除记录：https://blog.youkuaiyun.com/weixin_30732825/article/details/98013733