db2 数据转json v0.03

关注

原创最新推荐文章于 2026-07-14 10:40:42 发布 1.5k 阅读 AI 写同款· GEO 优化 ›

Python爬虫---中国大学MOOC爬取数据（文中有数据集）本文为大二在校学生所做，内容为爬取中国大学Mooc网站的课程分类数据、课程数据、评论数据。数据集大佬们需要拿走。主要是希望大佬们能指正代码问题。阅读详情

#ifndef DB2_CLI_H_
#define DB2_CLI_H_
#ifdef __cplusplus
extern "C"{
#endif
	struct sql_client_information_tag;
	typedef struct sql_client_information_tag* sql_client_handle;
	/**
	 *  函数:   create_sql_handle
	 *          生成一个sql查询客户端句柄
	 *  参数: 
	 *          const char * const db_name  数据库名称
	 *          const char * const u_name   数据库用户名
	 *          const char * const u_pass   数据库密码	 
	 *  返回值:
	 *          NULL 创建失败
	 *          其他 创建成功
	 *  版本号：1.0
	 */
	sql_client_handle create_sql_handle(const char*const db_name,const char*const u_name,const char*const u_pass);
	/**
	 *  函数:   sql_excute_no_result
	            执行一个没有返回值的sql语句，比如表创建，表插入，表删除等动作
	 *  参数: 
	 *          sql_client_handle h   sql查询客户端句柄
	 *          const char * const sql_statement sql语句 
	 *  返回值:
	 *          0   执行成功
	 *			非0 执行失败
	 *  版本号：1.0
	 */
	int sql_excute_no_result(sql_client_handle h, const char*const sql_statement);
	/**
	 *  函数:   sql_excute_with_result
	 *          执行一个有返回值的sql语句，比如select操作
	 *  参数: 
	 *          sql_client_handle h  sql查询客户端句柄
	 *          const char*const sql_statment  sql查询语句
	 *          char * const result  sql查询结果缓存
	 *          int *len			 [in]sql查询结果缓存大小
	 *								 [out]查询结果的json字符流大小
	 *  返回值:
	 *         0  执行成功
	 *		   非0 执行失败
	 *  版本号：1.0
	 *
	 */
	int sql_excute_with_result(sql_client_handle h ,const char*const sql_statment,char*const result,int *len);
	/**
	 *  函数:   destroy_sql_handle
	 *          关闭sql查询句柄
	 *  参数: 
	 *          sql_client_handle h sql查询句柄 
	 *  返回值:
	 *          无
	 *  版本号：1.0
	 *
	 */
	void destroy_sql_handle(sql_client_handle h);
#ifdef __cplusplus
};
#endif
#endif //DB2_CLI_H_

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <memory.h>
#include <sqlcli1.h>
#include <db2_cli.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;

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 ,const char*const 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 SQL_SUCCESS; 
}


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;
		}
	}
	h->col_counts = i;
	return SQL_SUCCESS;
}


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

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

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 (SQL_SUCCESS != fetch_record_set_header(ret,stmt)) break;
		if (SQL_SUCCESS != fetch_sql_data(ret,stmt)) 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);
	if(0 == offset){
		const char*const null_str = "null";
		offset = strlen(null_str);
		strncpy(json_space(h),null_str,offset);
	}else{
		strncpy(json_space(h),data_sapce(h),offset);
		h->data_offset += offset;
	}
	h->json_size += offset;
	h->json_size += snprintf(json_space(h),space_leng(h),",");
	return offset;
}

static int convert_json_string(const char*const src,char* dst,int len){
	const char*const 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;
	}
	if( 0 == offset){
		h->json_size += snprintf(json_space(h),space_leng(h),"\"%s\":null,",item_name);
		h->data_offset += offset;
		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->row_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){
	json_convertor json = {0};
	record_set_handle rsh = create_record_set(stmt,*len);
	if (NULL == rsh){
		return SQL_ERROR;
	}
	json.data_buffer = rsh->data_buffer;
	json.json_buffer = buffer;
	json.buffer_length = *len;
	*len = format_2_json(&json,rsh);
	destroy_record_set(rsh);
	return (*len >= 0) ? SQL_SUCCESS : SQL_ERROR;
}

#ifdef db2_json_test
static char result[1024 * 1024 * 4];
int main(int argc, char* argv[])
{
	char cmd[1024];
	if(argc < 4){
		hints("usage : %s db_name db_user db_pass\n",argv[0]);
		return -1;
	}
	sql_client_handle h = create_sql_handle(argv[1],argv[2],argv[3]);
	if (NULL == h){
		return -1;
	}
	for (gets(cmd);strcmp(cmd,"q") != 0;gets(cmd)){
		int len = sizeof(result);
		int ret = 0;
		memset(result , 0 , sizeof(result));
		hints("cmd = %s\n",cmd);
		ret = sql_excute_with_result(h,cmd,result,&len);
		if(ret < 0) {
			errors("sql_excute_with_result = %d\n",ret);
			break;
		}
		hints("%s : %d\n",result,len);
		hints("next command\n");
	}
	destroy_sql_handle(h);
	return 0;
}
#endif

收录于