db2 数据转json v0.01

直接上代码

#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,NULL);
	destroy_sql_handle(ret);
	return NULL;	
}

void destroy_sql_handle(sql_client_handle h){
	do 
	{
		if(NULL != 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 (NULL != 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,NULL);
	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);
}

static int fetch_data(SQLHSTMT stmt,int col_index,int type,const char*const col_name,char*const buffer,int len){
	SQLCHAR char_buffer[SQL_MAX_MESSAGE_LENGTH] = {0};
	SQLINTEGER buffer_len = 0;
	SQLRETURN rc = SQLGetData(stmt,col_index,SQL_CHAR,char_buffer,sizeof(char_buffer),&buffer_len);
	if(SQL_SUCCESS != rc){
		errors("%s : SQLGetData = %d\n",__FUNCTION__,rc);
		return rc;
	}
	switch(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:
		return snprintf(buffer,len,"%s:%s,",col_name,char_buffer);
	default:
		return snprintf(buffer,len,"%s:\"%s\",",col_name,char_buffer);
	}
}

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;

static int fetch_sql_row(SQLHSTMT stmt,int col_counts,record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE],char* buffer,int len){
	int sum_len = snprintf(buffer,len,"{");
	buffer += sum_len;len -= sum_len;
	for (int i = 0; i < col_counts; ++i){
		int ret = fetch_data(stmt,i + 1,headers[i].Type,headers[i].Name,buffer,len);
		if(ret < 0){
			errors("%s : ret = %d",__FUNCTION__,ret);
			return ret;
		}
		buffer += ret;
		len -= ret;
		sum_len += ret;
	}
	len = snprintf(buffer - 1,len + 1,"},\n");
	return sum_len + len - 1;
}

static int fetch_record_set_header(SQLHSTMT stmt,record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE]){
	SQLSMALLINT SQLColCount = 0,i = 0,NameLen = 0;
	SQLRETURN rc = SQLNumResultCols(stmt,&SQLColCount);
	memset(headers , 0 , sizeof(record_set_header)*SQL_MAX_COLUMNS_IN_TABLE);
	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)
	{
		rc = SQLDescribeCol(stmt,i + 1,headers[i].Name,sizeof(headers[i].Name),&NameLen,
			&headers[i].Type,&headers[i].Define,&headers[i].Scale,&headers[i].IsNUll);
		if(SQL_SUCCESS != rc){
			errors("%s : SQLDescribeCol = %d\n",__FUNCTION__,rc);
			return rc;
		}
	}
	return i;
}

static int fetch_sql(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len){
	record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE] = {0};
	int sum_counts = len;
	int row_counts = fetch_record_set_header(stmt,headers);
	if (row_counts < 0)
	{
		return row_counts;
	}
	len -= snprintf(buffer,len,"[\n");
	buffer += (sum_counts - len);
	for (;SQL_NO_DATA != SQLFetch(stmt);){
		int ret = fetch_sql_row(stmt,row_counts,headers,buffer,len);
		if(ret < 0){
			errors("%s : ret = %d",__FUNCTION__,ret);
			return ret;
		}
		len -= ret;
		buffer += ret;
	}
	len -= snprintf(buffer - 2, len + 2, "\n]\n");
	return sum_counts - len + 3;
}

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