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