1.创建数据库,及建立表格
#define CAN_SQLDB_FILE "/home/root/can.db3"
/************************************************************
*函数说明
* 初始化数据sqlite3
*参数
*
*
*返回值
* ret: 0成功
*备注
*
**************************************************************/
static int init_sqlite(void)
{
int ret = 0;
char *sql_cmd = NULL;
char *zErrMsg = NULL;
char exit_symbol = 0;
/*判断文件是否存在*/
exit_symbol = access(CAN_SQLDB_FILE, F_OK);
if (0 == exit_symbol) {
printf("######there have sqlite3 ! \n");
}
/*打开或创建数据库*/
ret = sqlite3_open(CAN_SQLDB_FILE, &(g_caninfo.pCanDb));
if (0 != ret) {
printf("######sqlite3 open or creat error! \n");
return ret;
}
/*第一次创建sqlite,需要创建表格, 否则,不创建表格*/
if (0 != exit_symbol) {
/*CREATE TABLE 语句: 表格项定义*/
sql_cmd = "CREATE TABLE CANDATA (" \
"ID integer," \
"TIME integer," \
"DATA blob);";
/*创建表格*/
ret = sqlite3_exec(g_caninfo.pCanDb, sql_cmd, NULL, 0, &zErrMsg);
if ( ret != SQLITE_OK ) {
printf("SQL error: %s \n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
printf("Table created successfully \n");
}
}
return ret;
}
2.反初始化操作
/************************************************************
*函数说明
* 反初始化数据sqlite3
*参数
*
*
*返回值
* ret: 0成功
*备注
*
**************************************************************/
static int uninit_sqlite()
{
return sqlite3_close(g_caninfo.pCanDb);
}
3.插入数据操作
/************************************************************
*函数说明
* 插入数据
*参数
*
*
*返回值
*
*备注
*
**************************************************************/
static int insert_sqlite_data(int id, struct timeval cur_time, char* buf, int len)
{
sqlite3_stmt *stat;
int rc = 0, time, retry = 5;
/*预编译流程*/
/*创建一个sqlite3_stmt对象*/
rc = sqlite3_prepare( g_caninfo.pCanDb, "insert into CANDATA( ID, TIME,
DATA) values( ?, ?, ? )", -1, &stat, 0);
if (rc != SQLITE_OK) {
printf("ERROR: insert prepare failed!\n");
return rc;
}
/*获取保存时的系统时间 自定义函数*/
get_date_time(cur_time, &time);
/*绑定预编译字段的值*/
rc = sqlite3_bind_int( stat, 1, id);
if (SQLITE_OK != rc)
return rc;
rc = sqlite3_bind_int( stat, 2, time);
if (SQLITE_OK != rc)
return rc;
rc = sqlite3_bind_blob( stat, 3, buf, len, NULL );
if (SQLITE_OK != rc)
return rc;
/*执行SQL语句*/
rc = sqlite3_step(stat);
while ((rc == SQLITE_BUSY) && retry--) {
usleep(200);
rc = sqlite3_step(stat);
}
if ((rc != SQLITE_ROW) && (rc != SQLITE_OK) && (rc != SQLITE_DONE))
return rc;
/*销毁资源*/
rc = sqlite3_finalize(stat);
return rc;
}
4 . 删除操作
/************************************************************
*函数说明
* 删除数据
*参数
*
*
*返回值
*
*备注
*
**************************************************************/
static int delete_sqlite_data(int id)
{
char seq_cmd[128];
int rc=0;
char *zErrMsg;
/*删除指定id行*/
sprintf(seq_cmd, "DELETE from CANDATA where ID=%d; ", id);
rc = sqlite3_exec(g_caninfo.pCanDb, seq_cmd, NULL, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
diagnosis_err(rc);
}
return rc;
}
5.显示数据库数据
void show_sqlite_data()
{
static int index = 0;
static char col = 0;
sqlite3_stmt * stat;
int rc, id, time, len, i;
char buf[128];
char *pdata = buf;
/*插入10条才显示一次*/
if (index++ < 10)
return;
index = 0;
rc = sqlite3_prepare(g_caninfo.pCanDb, "select * from CANDATA", -1, &stat, 0);
if (rc != SQLITE_OK)
printf("ERROR: show sqlite data prepare !\n");
rc = sqlite3_step(stat);
while (rc == SQLITE_ROW) {
id = sqlite3_column_int(stat, 0);
time = sqlite3_column_int(stat, 1);
pdata = sqlite3_column_blob(stat, 2);
/*得到所在列的字节数*/
len = sqlite3_column_bytes(stat, 2);
memcpy(buf, pdata, len);
my_debug("\n show sqlite begin, len = %d, \n", len);
my_debug("id = %d, time=0x%x \n", id, time);
/*打印blob值*/
for (i = 0; i < len; i++) {
if (col == 10) {
col = 0;
printf("\n");
}
col++;
my_debug("index[%d] = 0x%x. ", i, buf[i]);
}
col = 0;
my_debug("\n");
memset(buf, 128, 0);
rc = sqlite3_step(stat);
}
sqlite3_finalize(stat);
}