db2 数据转json v0.02

原创 于 2011-11-11 16:54:57 发布 · 1.3k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#json #db2 #sql #buffer #null #header

本文介绍了一种利用SQL执行数据查询并将结果转换为JSON格式的方法，通过创建SQL客户端连接并执行预定义的SQL语句，获取数据集，然后使用自定义的函数将查询结果转换为JSON字符串。该过程包括数据集头信息的获取、数据的逐行读取与解析，并最终生成JSON格式的数据输出。此外，还讨论了如何优化内存管理和错误处理，确保高效且可靠的转换过程。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <memory.h>
#include <sqlcli1.h>
#ifndef _countof
#define _countof(x) (sizeof(x)/sizeof(x[0]))
#endif
#define hints printf
#define errors printf
#define SQL_STATE_LEN 256
#define SQL_ERROR_MSG 256
#define SQL_DBNAME_LEN 256 
#define SQL_USER_NAME_LEN 64
#define SQL_USER_PASS_LEN 64
#define SQL_STATEMENT_LEN 1024


typedef struct  sql_client_information_tag
{
	SQLHENV env_handle;
	SQLHDBC dbc_handle;
}sql_client_information,*sql_client_handle;

typedef int (*fetch_row)(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len);
typedef struct sql_fetch_row_info_tag
{
	char* buffer;
	int   length;
	fetch_row fetch;
}sql_fetch_row_info,*sql_fetch_row_handle;

static int fetch_sql(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len);
static void sql_error(sql_client_handle h,SQLHSTMT stm_handle);
static int clean_statement(sql_client_handle h,SQLHSTMT stmt,SQLRETURN frc);
static int sql_excute(sql_client_handle h,const char*const sql,sql_fetch_row_handle fh);
void destroy_sql_handle(sql_client_handle h);



sql_client_handle create_sql_handle(const char*const db_name,const char*const u_name,const char*const u_pass){
	SQLCHAR DBName[SQL_MAX_DSN_LENGTH + 1] = {0};
	SQLCHAR USRName[SQL_USER_NAME_LEN] = {0};
	SQLCHAR USRPass[SQL_USER_PASS_LEN] = {0};
	sql_client_handle ret = malloc(sizeof(sql_client_information));
	if(NULL == ret){
		return NULL;
	}
	memset(ret , 0 , sizeof(sql_client_information));
	do {
		SQLRETURN  rc = SQLAllocEnv(&ret->env_handle);
		if(rc != SQL_SUCCESS) break;
		rc = SQLAllocConnect(ret->env_handle,&ret->dbc_handle);
		if(rc != SQL_SUCCESS) break;
		strncpy((char*)DBName,db_name,sizeof(DBName) - 1);
		strncpy((char*)USRName,u_name,sizeof(USRName) - 1);
		strncpy((char*)USRPass,u_pass,sizeof(USRPass) - 1);
		rc = SQLConnect(ret->dbc_handle,DBName,SQL_NTS,USRName,SQL_NTS,USRPass,SQL_NTS);
		if(rc != SQL_SUCCESS) break;
		return ret;
	} while (0);
	sql_error(ret,SQL_NULL_HSTMT);
	destroy_sql_handle(ret);
	return NULL;	
}

void destroy_sql_handle(sql_client_handle h){
	do 
	{
		if(SQL_NULL_HDBC != h->dbc_handle){
			if(SQL_SUCCESS != SQLDisconnect(h->dbc_handle)) break;
			if(SQL_SUCCESS != SQLFreeConnect(h->dbc_handle)) break;
			h->dbc_handle = SQL_NULL_HDBC;
		}
		if (SQL_NULL_HENV != h->env_handle)
		{
			if(SQL_SUCCESS != SQLFreeEnv(h->env_handle)) break;
			h->env_handle = SQL_NULL_HENV;
		}
		free(h);
		return;
	} while (0);
	sql_error(h,SQL_NULL_HSTMT);
	free(h);
}

int sql_excute_no_result(sql_client_handle h, const char*const sql_statement){
	return sql_excute(h,sql_statement,NULL);
}


int sql_excute_with_result(sql_client_handle h ,char* sql_statment,char*const result,int len){
	sql_fetch_row_info fetch_row = {result,len,fetch_sql};
	return sql_excute(h,sql_statment,&fetch_row);
}

static void sql_error(sql_client_handle h,SQLHSTMT stm_handle){
	SQLCHAR  sql_state[SQL_SQLSTATE_SIZE + 1] = {0};
	SQLCHAR  sql_error[SQL_MAX_MESSAGE_LENGTH + 1] = {0};
	SQLINTEGER native_error = 0;
	SQLSMALLINT pcb_error = 0;
	SQLRETURN  rc = SQLError(h->env_handle,h->dbc_handle,stm_handle,
		sql_state,&native_error,sql_error,sizeof(sql_error),&pcb_error);
	if(rc == SQL_NO_DATA_FOUND){
		return;
	}
	errors("sql state : %s\n",sql_state);
	errors("sql error : %s\n",sql_error);
}

static int clean_statement(sql_client_handle h,SQLHSTMT stmt,SQLRETURN frc){
	sql_error(h,stmt);
	switch(frc){
	case SQL_SUCCESS: 
	case SQL_SUCCESS_WITH_INFO:break;
	case SQL_ERROR:
	case SQL_INVALID_HANDLE:
	default:
		SQLTransact(h->env_handle,h->dbc_handle,SQL_ROLLBACK);
		return frc;
	}
	return 0; 
}


static int sql_excute(sql_client_handle h,const char*const sql,sql_fetch_row_handle fh){
	SQLCHAR SQLState[SQL_STATEMENT_LEN] = {0};
	SQLHSTMT stmt = SQL_NULL_HSTMT;
	SQLRETURN rc = SQLAllocStmt(h->dbc_handle,&stmt);
	do 
	{
		if(SQL_SUCCESS != rc) break;
		strncpy((char*)SQLState,sql,sizeof(SQLState) - 1);
		rc = SQLExecDirect(stmt,SQLState,SQL_NTS);
		if(SQL_SUCCESS != rc) break;
		if(NULL != fh){
			if(SQL_SUCCESS != fh->fetch(h,stmt,fh->buffer,fh->length)) break;
		}
		rc = SQLTransact(h->env_handle,h->dbc_handle,SQL_COMMIT);
		if(SQL_SUCCESS != rc) break;
		SQLFreeStmt(stmt,SQL_DROP);
		return 0;
	} while (0);
	if(SQL_NULL_HSTMT != stmt){
		SQLFreeStmt(stmt,SQL_DROP);
		stmt = SQL_NULL_HSTMT;
	}
	return clean_statement(h,stmt,rc);
}


typedef struct record_set_header_tag{
	SQLCHAR Name[SQL_MAX_COLUMN_NAME_LEN];
	SQLUINTEGER Define;
	SQLSMALLINT Type;
	SQLSMALLINT Scale;
	SQLSMALLINT IsNUll;
}record_set_header,*record_set_header_handle;


typedef struct record_set_tag{
	record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE];
	int  col_counts;
	int  row_counts;
	int  buffer_length;
	int  data_size;
	char data_buffer[];
}record_set,*record_set_handle;

#define record_data_buffer(h) (&h->data_buffer[h->data_size])
#define record_data_space(h) (h->buffer_length - h->data_size)


static int fetch_record_set_header(record_set_handle h,SQLHSTMT stmt){
	record_set_header* rs_header = h->headers;
	SQLSMALLINT SQLColCount = 0,NameLen = 0, i = 0;
	SQLRETURN rc = SQLNumResultCols(stmt,&SQLColCount);
	if (SQL_SUCCESS != rc){
		errors("%s: SQLNumResultCols = %d\n",__FUNCTION__,rc);
		return rc;
	}
	for (i = 0 ; i < SQLColCount && i < SQL_MAX_COLUMNS_IN_TABLE; ++i,++rs_header)
	{
		rc = SQLDescribeCol(stmt,i + 1,rs_header->Name,sizeof(rs_header->Name),&NameLen,
			&rs_header->Type,&rs_header->Define,&rs_header->Scale,&rs_header->IsNUll);
		if(SQL_SUCCESS != rc){
			errors("%s : SQLDescribeCol = %d\n",__FUNCTION__,rc);
			return rc;
		}
	}
	return (h->col_counts = i);
}


static int fetch_row_data(record_set_handle h, SQLHSTMT stmt){
	SQLCHAR char_buffer[SQL_MAX_MESSAGE_LENGTH] = {0};
	SQLINTEGER buffer_len = 0;
	int i = 0;
	for(i = 0; i < h->col_counts; ++i){
		SQLRETURN rc = SQLGetData(stmt,i + 1,SQL_CHAR,char_buffer,sizeof(char_buffer),&buffer_len);
		if(SQL_SUCCESS != rc){
			errors("%s : SQLGetData = %d\n",__FUNCTION__,rc);
			return rc;
		}
		if((buffer_len + 4) > record_data_space(h)){
			errors("%s : buffer_len = %d, free_len = %d\n",buffer_len,h->buffer_length);
			break;
		}
		h->data_size += snprintf(record_data_buffer(h),record_data_space(h),"%4d",buffer_len);
		memcpy(record_data_buffer(h),char_buffer,buffer_len);
		h->data_size += buffer_len;
	}
	return h->data_size;
}

static int fetch_sql_data(record_set_handle h, SQLHSTMT stmt){
	int ret = 0;
	for (h->row_counts = 0;h->data_size < h->buffer_length;++h->row_counts){
		if(SQL_NO_DATA == SQLFetch(stmt)){
			break;
		}
		ret = fetch_row_data(h,stmt);
		if(ret < 0){
			errors("%s : ret = %d",__FUNCTION__,ret);
			return ret;
		}
	}
	return h->data_size;
}

void destroy_record_set(record_set_handle h){
	free(h);
}

record_set_handle create_record_set(SQLHSTMT stmt,int buffer_size){
	record_set_handle ret = malloc(sizeof(record_set) + buffer_size);
	if (NULL == ret || SQL_NULL_HSTMT == stmt){
		return NULL;
	}
	memset(ret , 0 , sizeof(record_set) + buffer_size);
	ret->buffer_length = buffer_size;
	do 
	{
		if (fetch_record_set_header(ret,stmt) < 0) break;
		if (fetch_sql_data(ret,stmt) < 0) break;
		return ret;
	}while(0);
	destroy_record_set(ret);
	return NULL;
}

typedef struct json_convertor_tag{
	const char* data_buffer;
	char* json_buffer;
	int buffer_length;
	int json_size;
	int data_offset;
}json_convertor,*json_convertor_handle;

#define item_len_lable 4
#define header_size (SQL_MAX_COLUMN_NAME_LEN + 4)
#define data_sapce(h) (h->data_buffer + h->data_offset)
#define json_space(h) (h->json_buffer + h->json_size)
#define space_leng(h) ((h->buffer_length  > h->json_size) ? (h->buffer_length - h->json_size) : 0)

static int get_item_length(json_convertor_handle h){
	char str_len[item_len_lable + 1] = {0};
	int offset = 0;
	strncpy(str_len,data_sapce(h),item_len_lable);
	offset = atoi(str_len);
	if ((offset + h->json_size + header_size) > h->buffer_length){
		errors("json buffer not space : offset = %d,buffer_length = %d,json_size = %d\n",
			offset,h->buffer_length,h->json_size);
		return -1;
	}
	h->data_offset += item_len_lable;
	return offset;
}

static int set_json_number(json_convertor_handle h, const char* item_name){
	int  offset = get_item_length(h);
	if(offset < 0){
		return offset;
	}
	h->json_size += snprintf(json_space(h),space_leng(h),"\"%s\":",item_name);
	strncpy(json_space(h),data_sapce(h),offset);
	h->json_size += offset;
	h->json_size += snprintf(json_space(h),space_leng(h),",");
	h->data_offset += offset;
	return offset;
}

static int convert_json_string(const char*const src,char* dst,int len){
	char* head = dst;
	int i = 0;
	for (i = 0; i < len ; ++i)
	{
		switch(src[i]){
		case '\\':{
			*dst++ = '\\';
			*dst++ = '\\';
			break;
				  }
		case '"':{
			*dst++ = '\\';
			*dst++ = '"';
			break;
				 }
		default:{
			*dst++ = src[i];
			break;
				}
		}
	}
	return (int)(dst - head);
}

static int set_json_string(json_convertor_handle h,const char* item_name){
	int offset = get_item_length(h);
	if (offset < 0){
		return offset;
	}
	h->json_size += snprintf(json_space(h),space_leng(h),"\"%s\":\"",item_name);
	h->json_size += convert_json_string(data_sapce(h),json_space(h),offset);
	h->json_size += snprintf(json_space(h),space_leng(h),"\",");
	h->data_offset += offset;
	return offset;

}

static int format_row_2_json(json_convertor_handle h,record_set_handle rs){
	int i = 0;
	for(i = 0; i < rs->col_counts; ++i){
		switch(rs->headers[i].Type){
		case SQL_NUMERIC:
		case SQL_DECIMAL:
		case SQL_INTEGER:
		case SQL_SMALLINT:
		case SQL_FLOAT:
		case SQL_REAL:
		case SQL_DOUBLE:
		case SQL_DATETIME:
			{
				set_json_number(h,(const char*)rs->headers[i].Name);
				break;
			}
		default:
			{
				 set_json_string(h,(const char*)rs->headers[i].Name);
				 break;
			}
		}
	}
	return i;
}

static int format_2_json(json_convertor_handle h,const record_set_handle rs){
	int i = 0;
	if( 0 == rs->col_counts){
		h->json_size += snprintf(json_space(h),space_leng(h),"[]");
		return h->json_size;
	}
	h->json_size += snprintf(json_space(h),space_leng(h),"[\n");
	for (i = 0; i < rs->row_counts; ++i){
		h->json_size += snprintf(json_space(h),space_leng(h),"{");
		format_row_2_json(h,rs);
		h->json_size += snprintf(json_space(h) - 1,space_leng(h) + 1,"},\n") - 1;
	}
	h->json_size += snprintf(json_space(h) - 2,space_leng(h) + 2,"\n]") - 2;
	return h->json_size;
}

static int fetch_sql(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len){
	int ret = 0;
	json_convertor json = {0};
	record_set_handle rsh = create_record_set(stmt,len);
	if (NULL == rsh){
		return -1;
	}
	json.data_buffer = rsh->data_buffer;
	json.json_buffer = buffer;
	json.buffer_length = len;
	ret = format_2_json(&json,rsh);
	destroy_record_set(rsh);
	return ret;
}
static char result[1024 * 1024 * 4];  
int main(int argc, char* argv[])  
{  
    char cmd[1024];  
    sql_client_handle h = NULL;  
    if(argc < 4){  
        errors("usage : \n db2jsion db_name u_name u_pass \n");  
        return -1;  
    }  
    h = create_sql_handle(argv[1],argv[2],argv[3]);  
    if (NULL == h){  
        return -1;  
    }  
    for (gets(cmd);strcmp(cmd,"q") != 0;gets(cmd)){  
        memset(result , 0 , sizeof(result));  
        hints("cmd = %s\n",cmd);  
        sql_excute_with_result(h,cmd,result,sizeof(result));  
        hints("%s\n",result);  
        memset(cmd, 0 , sizeof(cmd));  
        hints("next command\n");  
    }  
    destroy_sql_handle(h);  
    return 0;  
}



评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值