SQLITE3 - 3.6.23.1
记录select 数据库代码
DBeaver结果
C代码结果
int sqlite_select_demo()
{
int db_ret = 0;
char sql[256]={0};
char buf[256]={0};
int line=0;
int row=0;
int data_len=0;
const void *blob=0;
const unsigned char *text=0;
const char *ff="MeterAddr";
int break_line=0;
sqlite3 *db;
int ret = sqlite3_open("/data0/record.db3",&db);
if(ret)
return -1;
sprintf(sql,"select * FROM FORMAT WHERE MITEM == :tableId AND FIELD=:ff ");//AND FIELD ==\"MeterAddr\"
const char *errMsg;
sqlite3_stmt * stmt;
ret = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if( ret != SQLITE_OK )
{
errMsg = sqlite3_errmsg(db);
LOG(DEBUG_K,"SQL error: %s\n", errMsg);
sqlite3_finalize(stmt);
}
else
{
for(int id=42000;id<42002;id++)
{
line = 0;
break_line = 0;
sqlite3_bind_int(stmt,1,id);
sqlite3_bind_text(stmt,2,ff,strlen(ff),SQLITE_STATIC);
while(1)
{
memset(buf,0,256);
db_ret = sqlite3_step(stmt);
if(db_ret == SQLITE_ROW)
{
printf("line=%d,sqlite3_step SQLITE_ROW\n",++line);
row = sqlite3_column_count(stmt);
for(int i=0;i<row;i++)
{
int type = sqlite3_column_type(stmt, i);
data_len = sqlite3_column_bytes(stmt,i);
if(data_len>0)
{
switch(type)
{
case SQLITE_INTEGER:
printf("SQLITE_INTEGER:[%d]\n",sqlite3_column_int(stmt, i));
break;
case SQLITE_FLOAT:
printf("SQLITE_INTEGER:[%d]\n",sqlite3_column_int(stmt, i));
break;
case SQLITE_TEXT://文本数据
text = sqlite3_column_text(stmt,i);
printf("SQLITE_TEXT:[%s]\n",text);
break;
case SQLITE_BLOB://二进制数据,图片 音频等
printf("SQLITE_BLOB\n");
blob = sqlite3_column_blob(stmt, i);
if(blob!=NULL)
{
//data_len
}
break;
case SQLITE_NULL:
printf("SQLITE_NULL\n");
break;
default:
errMsg = sqlite3_errmsg(db);
printf("SQL error: %s\n", errMsg);
break_line = 1;
break;
}
}
else
{
printf("data_len=0\n");
}
}
if(break_line)
{
sqlite3_reset(stmt);
break;
}
}
else if(db_ret == SQLITE_DONE)//处理完所有行
{
printf("sqlite3_step SQLITE_DONE\n");
sqlite3_reset(stmt);
break;
}
else
{
errMsg = sqlite3_errmsg(db);
printf("SQL error: %s\n", errMsg);
sqlite3_reset(stmt);
break;
}
}
}
sqlite3_finalize(stmt);
}
sqlite3_close(db);
return 0;
}
创建表例子
create_table_sql table_list[]={
{0,"EVENT_LOG1","CREATE TABLE [EVENT_LOG1] ([EventId] int NOT NULL,[ActTime] int NOT NULL,[REG] int NOT NULL,PRIMARY KEY([EventId],[ActTime]))",90},
{0,"EVENT_LOG2","CREATE TABLE [EVENT_LOG2] ([EventId] int NOT NULL,[ActTime] int NOT NULL,[REG] int NOT NULL,[MeterId] bigint NOT NULL,PRIMARY KEY([EventId],[ActTime]))",90},
{0,"EVENT_LOG3","CREATE TABLE [EVENT_LOG3] ([EventId] int NOT NULL,[ActTime] int NOT NULL,[MeterIDs] blob,[temperature] int,[writing_cycle] int,[flash_space] int,[cpu] int,[ram] int,PRIMARY KEY([ActTime]))",90},
{0,"EVENT_LOG4","CREATE TABLE [EVENT_LOG4] ([EventId] int NOT NULL,[ActTime] int NOT NULL,[REG] int NOT NULL,[MeterId] bigint NOT NULL,[phase_old] int,[phase_new] int,PRIMARY KEY([MeterId],[ActTime]))",90},
};
int ZD2_checkAndCreatTable(sqlite3 *ppDb)
{
int ret = 0;
unsigned int i = 0;
sqlite3_stmt *stmt=NULL;
char buf[256];
string sql="select name from sqlite_master where type == 'table'";
ret = sqlite3_prepare_v2(ppDb,sql.c_str(),sql.size(),&stmt,NULL);
if(ret != SQLITE_OK)
{
SVC_LOG(ERROR_L,"line=%d,%s\n",__LINE__,sqlite3_errmsg(ppDb));
sqlite3_finalize(stmt);
return ret;
}
while(1)
{
ret = sqlite3_step(stmt);
if(ret == SQLITE_ROW)
{
MY_LOG("SQLITE_ROW\n");
//type name tbl_name rootpage sql
//
const unsigned char *name = sqlite3_column_text(stmt,0);
if(name != NULL)
{
SVC_LOG(DEBUG_K,"find table [%s]\n",name);
for(i=0;i<sizeof(table_list)/sizeof(create_table_sql);i++)
{
if(0==strcmp(table_list[i].table_name,(char*)name))
{
table_list[i].ok = 1;
}
}
}
}
else if(ret == SQLITE_DONE)//处理完所有行
{
MY_LOG("sqlite3_step SQLITE_DONE\n");
sqlite3_reset(stmt);
break;
}
else
{
SVC_LOG(ERROR_L,"line=%d,%s\n",__LINE__,sqlite3_errmsg(ppDb));
sqlite3_reset(stmt);
break;
}
}
sqlite3_finalize(stmt);
//创建表,控制表大小
for(i=0;i<sizeof(table_list)/sizeof(create_table_sql);i++)
{
if(0==table_list[i].ok)
{
ret = sqlite3_exec(ppDb,table_list[i].create_sql,NULL,NULL,NULL);
if(ret != SQLITE_OK)
{
SVC_LOG(ERROR_L,"line=%d,create %s,%s\n",__LINE__,table_list[i].table_name,sqlite3_errmsg(ppDb));
return ret;
}
else
{
SVC_LOG(DEBUG_K,"create %s ok\n",table_list[i].table_name);
}
}
else
{
sprintf(buf,"DELETE from %s where ActTime < %ld",table_list[i].table_name,time(0)-table_list[i].save_days*3600*24);
ret = sqlite3_exec(ppDb,buf,NULL,NULL,NULL);
if(ret != SQLITE_OK)
{
SVC_LOG(ERROR_L,"line=%d,%s,%s\n",__LINE__,buf,sqlite3_errmsg(ppDb));
return ret;
}
else
{
SVC_LOG(DEBUG_K,"%s,ok\n",buf);
}
}
}
return ret;
}
replace例子
int ZD2_WriteEventLog3(time_t t,vector<uint64_t> meterList,int tp,
int wr,int rsc,int cpu,int ram
)
{
int ret = 0;
uint64_t buf[256]={0};//最多250只表
sqlite3_stmt *stmt=NULL;
char sql[512];
ZD2_DCU_ALARM_BIT eventId = DCU_EVENT_DCU_DATA_SAVE;
for(uint32_t i=0;i< meterList.size() && i<300;i++)
{
buf[i] = meterList[i];
}
sqlite3 *ppDb=NULL;
ret = sqlite3_open(DB_NAME,&ppDb);
if(ret)
{
SVC_LOG(ERROR_L,"line=%d,%s\n",__LINE__,sqlite3_errmsg(ppDb));
ret = -2;
goto err_out;
}
sprintf(sql,"replace INTO EVENT_LOG3 (EVENTID,ActTime,MeterIDs,temperature,writing_cycle,flash_space,cpu,ram) VALUES (%d,%lu,:meterids,%d,%d,%d,%d,%d)",eventId,t,tp,wr,rsc,cpu,ram);
SVC_LOG(DEBUG_K,"%s\n",sql);
ret = sqlite3_prepare_v2(ppDb,sql,strlen(sql),&stmt,NULL);
if(ret != SQLITE_OK)
{
SVC_LOG(ERROR_L,"line=%d,%s\n",__LINE__,sqlite3_errmsg(ppDb));
sqlite3_finalize(stmt);
ret = -3;
goto err_out;
}
sqlite3_bind_blob(stmt,1,buf,sizeof(uint64_t)*meterList.size(),SQLITE_STATIC);
ret = sqlite3_step(stmt);
if(ret == SQLITE_ROW)
{
MY_LOG("SQLITE_ROW\n");//replace 不会到这里
}
else if(ret == SQLITE_DONE)//处理完所有行
{
MY_LOG("sqlite3_step SQLITE_DONE\n");
sqlite3_reset(stmt);
}
else
{
SVC_LOG(ERROR_L,"line=%d,%s\n",__LINE__,sqlite3_errmsg(ppDb));
sqlite3_reset(stmt);
ret = -4;
}
sqlite3_finalize(stmt);
err_out:
sqlite3_close(ppDb);
return 0;
}
通用select例子
void log_buf_data(uint8_t *data,int len)
{
int i=0;
printf("len=%d\n",len);
while(i<len)
{
printf("%02X %02X %02X %02X %02X %02X %02X %02X\n",
data[i],data[i+1],data[i+2],data[i+3],
data[i+4],data[i+5],data[i+6],data[i+7]);
i+=8;
}
}
int sqlite_select_demo()
{
int i=0;
int db_ret = 0;
char sql[256]={0};
char buf[256]={0};
int line=0;
int row=0;
int data_len=0;
const void *blob=0;
const unsigned char *text=0;
int break_line=0;
sqlite3 *db;
int ret = sqlite3_open(DB_NAME,&db);
if(ret)
return -1;
sprintf(sql,"SELECT * FROM EVENT_LOG3");
const char *errMsg;
sqlite3_stmt * stmt;
ret = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if( ret != SQLITE_OK )
{
errMsg = sqlite3_errmsg(db);
MY_LOG("SQL error: %s\n", errMsg);
sqlite3_finalize(stmt);
}
else
{
{
line = 0;
break_line = 0;
//sqlite3_bind_int(stmt,1,id);
//sqlite3_bind_text(stmt,2,ff,strlen(ff),SQLITE_STATIC);
while(1)
{
memset(buf,0,256);
db_ret = sqlite3_step(stmt);
if(db_ret == SQLITE_ROW)
{
printf("line=%d,sqlite3_step SQLITE_ROW\n",++line);
row = sqlite3_column_count(stmt);
for(i=0;i<row;i++)
{
int type = sqlite3_column_type(stmt, i);
data_len = sqlite3_column_bytes(stmt,i);
if(data_len>0)
{
switch(type)
{
case SQLITE_INTEGER:
printf("SQLITE_INTEGER:[%d]\n",sqlite3_column_int(stmt, i));
break;
case SQLITE_FLOAT:
printf("SQLITE_INTEGER:[%d]\n",sqlite3_column_int(stmt, i));
break;
case SQLITE_TEXT://文本数据
text = sqlite3_column_text(stmt,i);
printf("SQLITE_TEXT:[%s]\n",text);
break;
case SQLITE_BLOB://二进制数据,图片 音频等
printf("SQLITE_BLOB\n");
blob = sqlite3_column_blob(stmt, i);
if(blob!=NULL)
{
uint8_t *data = (uint8_t *)blob;
log_buf_data(data,data_len);
}
break;
case SQLITE_NULL:
printf("SQLITE_NULL\n");
break;
default:
errMsg = sqlite3_errmsg(db);
printf("SQL error: %s\n", errMsg);
break_line = 1;
break;
}
}
else
{
printf("data_len=0\n");
}
}
if(break_line)
{
sqlite3_reset(stmt);
break;
}
}
else if(db_ret == SQLITE_DONE)//处理完所有行
{
printf("sqlite3_step SQLITE_DONE\n");
sqlite3_reset(stmt);
break;
}
else
{
errMsg = sqlite3_errmsg(db);
printf("SQL error: %s\n", errMsg);
sqlite3_reset(stmt);
break;
}
}
}
sqlite3_finalize(stmt);
}
sqlite3_close(db);
return 0;
}