db2 数据转json v0.02

#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 1024typedef 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;  }