【Sqlite3-学习笔记一】C-API select查询结果操作

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;
}

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
c语⾔数据库查询系统,C语⾔实现sqlite3数据库查询的基本⽅ 法 sqlite回传函数相关 说了3种基本⽅法:callback,gettable和预处理stmt⽅法 下⾯给出测试代码和测试⽤数据库,代码如下 #include #include #include #include "sqlite3.h" // //typedef int (*sqlite3_callback)( // void* data, /* Data provided in the 4th argument of sqlite3_exec() */ // int ncols, /* The number of columns in row */ // char** values, /* An array of strings representing fields in the row */ // char** headers /* An array of strings representing column names */ //); int callback(void* data, int ncols, char** values, char** headers) { int i; int len =0; int ll=0; for(i=0; i < ncols; i++) { if(strlen(headers[i])>len) len = strlen(headers[i]); } for(i=0; i < ncols; i++) { ll = len-strlen(headers[i]); while(ll) { fprintf(stdout," "); --ll; } fprintf(stdout, "%s: %sn", headers[i], values[i]); } fprintf(stdout, "n"); return 0; } int search_by_callback(const char* db_name, const char* sql_cmd) { int i = 0 ; int j = 0 ; int nrow = 0, ncolumn = 0; char **azResult; //⼆维数组存放结果 sqlite3 *db=NULL; char *zErrMsg = 0; int rc; int len=0; if(access(db_name, 0) == -1) { fprintf(stderr, "%s not foundn", db_name); return -1; } rc = sqlite3_open(db_name, &db); if( rc != SQLITE_OK) { fprintf(stderr, "%s open failed: %sn", db_name,sqlite3_errmsg(db)); sqlite3_close(db); return -1; } //查询数据 rc = sqlite3_exec( db,sql_cmd, callback, NULL, &zErrMsg ); if( rc != SQLITE_OK) { fprintf(stderr, "%s %s: %sn", db_name,sql_cmd, sqlite3_errmsg(db)); if(zErrMsg) { fprintf(stderr,"ErrMsg = %s n", zErrMsg); sqlite3_free(zErrMsg); } sqlite3_close(db); return -1; } if(zErrMsg) { sqlite3_free(zErrMsg); } //关闭数据库 sqlite3_close(db); return 0; } int search_by_table(const char* db_name, const char* sql_cmd) { int i = 0 ; int j = 0 ; int nrow = 0, ncolumn = 0; char **azResult; //⼆维数组存放结果 sqlite3 *db=NULL; char *zErrMsg = 0; int rc; int len=0; if(access(db_name, 0) == -1) { fprintf(stderr, "%s not foundn", db_name); return -1; } rc = sqlite3_open(db_name, &db); if( rc != SQLITE_OK) { fprintf(stderr, "%s open failed: %sn"

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值