关于宏的制表功能

最新推荐文章于 2021-07-04 22:30:34 发布

FlyAnyWhere

最新推荐文章于 2021-07-04 22:30:34 发布

阅读量808

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库互联网

本文链接：https://blog.youkuaiyun.com/FlyAnyWhere/article/details/32710737

互联网同时被 2 个专栏收录

16 篇文章

订阅专栏

数据库

6 篇文章

订阅专栏

宏的制表功能上次用已经是七年前了，最近有段db2的静态SQL操作，刚好能用上，就又写了下，可是总感觉有点突兀，不知道是哪里出了问题了，难道注定苦丁一辈子？

1.数据表映射

这个宏必须使用紧凑格式，原因是在后面的拼装时，竟然cpp 4.4.7竟然会把空格也莫名奇妙的拼装进去，没有仔细检查原因。

#ifdef FIRSET_ORDER_INFO_ITEMS
FIRSET_ORDER_INFO_ITEMS(order_id,___char,64,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_version,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(encoding,___char,8,"GBK",cb_macro)
ORDER_INFO_ITEMS(mer_cert_id,integer,0,0,cb_macro)
ORDER_INFO_ITEMS(signature,___char,20,"",cb_macro)
ORDER_INFO_ITEMS(acc_type,___char,20,"",cb_macro)
ORDER_INFO_ITEMS(acc_no,___char,20,"",cb_macro)
ORDER_INFO_ITEMS(sign_method,___char,4,"",cb_macro)
ORDER_INFO_ITEMS(trans_tp,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(sub_trans_tp,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(biz_type,___char,20,"",cb_macro)
ORDER_INFO_ITEMS(trans_chnl,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_front_url,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_back_url,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(access_tp,___char,1,"",cb_macro)
ORDER_INFO_ITEMS(acq_ins_id_cd,___char,11,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_class,___char,4,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_cd,___char,24,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_name,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_nm,varchar,4,"",cb_macro)
ORDER_INFO_ITEMS(sub_mchnt_cd,___char,32,"",cb_macro)
ORDER_INFO_ITEMS(sub_mchnt_name,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(sub_mchnt_nm,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(mchnt_order_id,varchar,6,"",cb_macro)
ORDER_INFO_ITEMS(trans_tm,___char,17,"",cb_macro)
ORDER_INFO_ITEMS(trans_at,decimal,12,0,cb_macro)
ORDER_INFO_ITEMS(trans_curr_cd,___char,3,"",cb_macro)
ORDER_INFO_ITEMS(customer_info,___char,5,"",cb_macro)
ORDER_INFO_ITEMS(pay_timeout,___char,5,"",cb_macro)
ORDER_INFO_ITEMS(term_id,___char,8,"",cb_macro)
ORDER_INFO_ITEMS(req_resv,varchar,256,"",cb_macro)
ORDER_INFO_ITEMS(reserved,varchar,512,"",cb_macro)
ORDER_INFO_ITEMS(failed_front_url,varchar,256,"",cb_macro)
ORDER_INFO_ITEMS(number_of_installments,___char,5,"",cb_macro)
ORDER_INFO_ITEMS(pay_method,integer,0,0,cb_macro)
ORDER_INFO_ITEMS(iss_ins_id_cd,___char,11,"",cb_macro)
ORDER_INFO_ITEMS(mac_addr,varchar,32,"",cb_macro)
ORDER_INFO_ITEMS(customer_ip,___char,39,"",cb_macro)
ORDER_INFO_ITEMS(trans_no,___char,27,"",cb_macro)
ORDER_INFO_ITEMS(area_cd,___char,4,"",cb_macro)
ORDER_INFO_ITEMS(phone_no,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(trans_ip,___char,39,"",cb_macro)
ORDER_INFO_ITEMS(product_info,varchar,128,"",cb_macro)
ORDER_INFO_ITEMS(inner_reserved1,varchar,128,"",cb_macro)
ORDER_INFO_ITEMS(inner_reserved2,varchar,128,"",cb_macro)
ORDER_INFO_ITEMS(preferential,___char,3,"",cb_macro)
ORDER_INFO_ITEMS(discount,___char,3,"",cb_macro)
ORDER_INFO_ITEMS(resp_cd,___char,7,"",cb_macro)
ORDER_INFO_ITEMS(exchange_date,___char,9,"",cb_macro)
ORDER_INFO_ITEMS(consume_at,decimal,12,0,cb_macro)
ORDER_INFO_ITEMS(preferential_at,decimal,12,0,cb_macro)
ORDER_INFO_ITEMS(reback_sum,decimal,12,0,cb_macro)
ORDER_INFO_ITEMS(proc_st,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(sys_no,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(bind_id,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(pay_card_type,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(security_type,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(query_id,___char,32,"",cb_macro)
ORDER_INFO_ITEMS(bill_no,varchar,64,"",cb_macro)
ORDER_INFO_ITEMS(additional_district_code,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(bill_month,___char,2,"",cb_macro)
ORDER_INFO_ITEMS(bill_query_info,___char,64,"",cb_macro)
ORDER_INFO_ITEMS(usr_id,varchar,128,"",cb_macro)
ORDER_INFO_ITEMS(card_type,varchar,12,"",cb_macro)
ORDER_INFO_ITEMS(other_payer,varchar,128,"",cb_macro)
ORDER_INFO_ITEMS(pri_acct_no,___char,19,"",cb_macro)
ORDER_INFO_ITEMS(trans_dt,___char,8,"",cb_macro)
ORDER_INFO_ITEMS(refund_at,decimal,12,0,cb_macro)
LASTER_ORDER_INFO_ITEMS(trans_idx,___char,27,"",cb_macro)
#undef FIRSET_ORDER_INFO_ITEMS
#undef ORDER_INFO_ITEMS
#undef LASTER_ORDER_INFO_ITEMS
#undef ___char
#undef varchar
#undef integer
#undef decimal
#undef timestamp
#endif

2.分表操作映射

本例中分表一共12张

#ifdef TBL_MAPPING_BEGIN
TBL_MAPPING_BEGIN
TBL_MAPPING_ITEM(01)
#define TBL_NAME MK_TBL_NAME(01)
#include "order_op.h"
TBL_MAPPING_ITEM(02)
#define TBL_NAME MK_TBL_NAME(02)
#include "order_op.h"
TBL_MAPPING_ITEM(03)
#define TBL_NAME MK_TBL_NAME(03)
#include "order_op.h"
TBL_MAPPING_ITEM(04)
#define TBL_NAME MK_TBL_NAME(04)
#include "order_op.h"
TBL_MAPPING_ITEM(05)
#define TBL_NAME MK_TBL_NAME(05)
#include "order_op.h"
TBL_MAPPING_ITEM(06)
#define TBL_NAME MK_TBL_NAME(06)
#include "order_op.h"
TBL_MAPPING_ITEM(07)
#define TBL_NAME MK_TBL_NAME(07)
#include "order_op.h"
TBL_MAPPING_ITEM(08)
#define TBL_NAME MK_TBL_NAME(08)
#include "order_op.h"
TBL_MAPPING_ITEM(09)
#define TBL_NAME MK_TBL_NAME(09)
#include "order_op.h"
TBL_MAPPING_ITEM(10)
#define TBL_NAME MK_TBL_NAME(10)
#include "order_op.h"
TBL_MAPPING_ITEM(11)
#define TBL_NAME MK_TBL_NAME(11)
#include "order_op.h"
TBL_MAPPING_ITEM(12)
#define TBL_NAME MK_TBL_NAME(12)
#include "order_op.h"
TBL_MAPPING_END
#undef TBL_MAPPING_BEGIN
#undef TBL_MAPPING_ITEM
#undef TBL_MAPPING_END
#endif

3.数据表更新操作

#ifdef MK_TBL_NAME

	SET_STRING_DATA(values,order_id,%64s);

	EXEC SQL SELECT
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) name,
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) name
#include "order_items.h"
	INTO
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) :var_##name indicator :indic,
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) :var_##name indicator :indic
#include "order_items.h"
	FROM TBL_NAME
	WHERE order_id = :var_order_id;

	if(sqlca.sqlcode != 0 && sqlca.sqlcode != 100){
		SQL_ERROR;
		return sqlca.sqlcode;
	}
	if(-1 == indic || 100 == sqlca.sqlcode){
		set_default_value();
		set_target_value(values);
		EXEC SQL INSERT INTO TBL_NAME(
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) name,
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) name
#include "order_items.h"
		)VALUES(
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) :var_##name,
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) :var_##name
#include "order_items.h",
		);
		if( 0 != sqlca.sqlcode){
			int __ret__ = sqlca.sqlcode;
			SQL_ERROR;
			EXEC SQL ROLLBACK;
			return __ret__;
		}
		EXEC SQL COMMIT;
		return 0;
	}
	set_target_value(values);

	EXEC SQL UPDATE TBL_NAME SET
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) name = :var_##name,
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h",
	rec_upd_ts = CURRENT TIMESTAMP
		WHERE order_id = :var_order_id;

	if(sqlca.sqlcode != 0) {
		int __ret__ = sqlca.sqlcode;
		SQL_ERROR;
		EXEC SQL ROLLBACK;
		return __ret__;
	}
	EXEC SQL COMMIT;
	return 0;
}
#endif
#undef TBL_NAME

4.最终操作函数映射

#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <sqlutil.h>
#include <sqlenv.h>
#include <sqlcli1.h>
#include <sqlcodes.h>
#include <dk_log.h>
#include "static_modify_order.h"

#define ___char(name,size,default_value,suffix) static const char sc_##name[size + 1] = default_value
#define varchar(name,size,default_value,suffix) static const char sc_##name[size + 1] = default_value
#define integer(name,size,default_value,suffix) static const int  sc_##name = default_value
#define decimal(name,size,default_value,suffix) static const double sc_##name = default_value
#define timestamp(name,size,default_value,suffix) static const char sc_##name[26 + 1]
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) type_name(name,size,default_value,comments);
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h"

EXEC SQL include sqlca;

EXEC SQL BEGIN DECLARE SECTION;
#define ___char(name,size,default_value,suffix) char var_##name[size + 1] = default_value
#define varchar(name,size,default_value,suffix) char var_##name[size + 1] = default_value
#define integer(name,size,default_value,suffix) sqlint32  var_##name = default_value
#define decimal(name,size,default_value,suffix) double var_##name = default_value
#define timestamp(name,size,default_value,suffix) char var_##name[26 + 1]
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) type_name(name,size,default_value,comments);
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h"
short indic = -1;
EXEC SQL END DECLARE SECTION;


#define SET_STRING_DATA(values,name,fmt) do{\
	dk_data_unset_t * __data__ = dk_array_find(values,#name);\
	if(NULL == __data__){\
		break;\
	}\
	sscanf(__data__->str_value_->ptr_,#fmt,var_##name);\
}while(0)

#define SET_VALUE_DATA(values,name,fmt) do{\
	dk_data_unset_t * __data__ = dk_array_find(values,#name);\
	if(NULL == __data__){\
		break;\
	}\
	sscanf(__data__->str_value_->ptr_,#fmt,&var_##name);\
}while(0)


static void set_default_value(void){
#define ___char(name)   memcpy(var_##name,sc_##name,sizeof(var_##name))
#define integer(name)   var_##name = sc_##name
#define varchar   ___char
#define timestamp ___char
#define decimal   integer	
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) type_name(name);
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h"	
}

static void set_target_value(dk_array_t* values){
#define ___char(values,name,size)   SET_STRING_DATA(values,name,%##size##s)
#define integer(values,name,size)   SET_VALUE_DATA(values,name,%d)
#define decimal(values,name,size)   SET_VALUE_DATA(values,name,%lf)
#define varchar   ___char
#define timestamp ___char
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) type_name(values,name,size);
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h"
}

static void hint_final_value(){	
#define ___char(values,name,size)   DK_LOG_TRACE("sss",#name, " = ",var_##name)
#define integer(values,name,size)   DK_LOG_TRACE("ssd",#name, " = ",var_##name)
#define decimal integer
#define varchar   ___char
#define timestamp ___char
#define ORDER_INFO_ITEMS(name,type_name,size,default_value,comments) type_name(values,name,size);
#define FIRSET_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#define LASTER_ORDER_INFO_ITEMS ORDER_INFO_ITEMS
#include "order_items.h"	
}



#define SQL_ERROR DK_LOG_ERROR("ssdsss", "\n-------------------------------------\n",\
		"SQLCODE          = ",sqlca.sqlcode,\
		"SQLSTATE         = ",sqlca.sqlstate,\
		"\n-------------------------------------\n") 


#define TBL_MAPPING_BEGIN
#define TBL_MAPPING_ITEM(num) static int modify_order_tbl##num(dk_array_t* values) {
#define TBL_MAPPING_END
#define MK_TBL_NAME(num) tbl_order_inf##num
#include "order_list.h"
#undef MK_TBL_NAME

typedef int (*tbl_operator)(dk_array_t*);
typedef struct table_mapping_tag{
	const char* suffix;
	tbl_operator op;
}table_mapping_t;

#define TBL_MAPPING_BEGIN static table_mapping_t tbl_mapping[] = {
#define TBL_MAPPING_ITEM(num) {#num,modify_order_tbl##num},
#define TBL_MAPPING_END   {NULL,NULL}};
#include "order_list.h"
int modify_order_info_tbl(const char* suffix,dk_array_t* vals){
	const table_mapping_t* tmp = tbl_mapping;
	for(; NULL != tmp->suffix; ++tmp){
		if(0 == strcmp(tmp->suffix, suffix)){
			return tmp->op(vals);
		}
	}
	DK_LOG_ERROR("sss","not found the process function at " , suffix);
	return -1;
}


static int sqlcodefilter(int sqlcode){
	DK_LOG_TRACE("sd","sqlcode =  " , sqlcode);
	switch(sqlcode)	{
		case -1024:
		case -900:
		case -30081:
			return -1;
		default :
			return 0;
	}
}

static int getsqlcode( void ){
	EXEC SQL BEGIN DECLARE SECTION;
	char db_timestamp[27];
	EXEC SQL END DECLARE SECTION;
	EXEC SQL SELECT CURRENT TIMESTAMP INTO :db_timestamp FROM SYSIBM.SYSDUMMY1;
	return(sqlca.sqlcode);
}
int connect_db(const char* db,const char* user,const char* pass,const char* schema){
	EXEC SQL BEGIN DECLARE SECTION;
	struct {
		/*
		 数据库名
		 */
		char db[20];
		/*
		 用户名
		 */
		char user[20];
		/*
		 用户密码
		 */
		char pass[20];
		/*
		 SCHEMA
		 */
		char schema[20];
	} cfg;
	EXEC SQL END DECLARE SECTION;
	/*
	 *判断数据库是否已经连接上，
	 *如果没有连接上，
	 *就获取配置文件信息，
	 *重新连接数据库
	 */
	if (0 == sqlcodefilter(getsqlcode())) {
		DK_LOG_TRACE("s","DB2 is connected");
		return 0;
	}
	EXEC SQL CONNECT RESET;
#define SET_CFG_VALUE(name) snprintf(cfg.##name,sizeof(cfg.##name),"%s",name)
	SET_CFG_VALUE(db);
	SET_CFG_VALUE(pass);
	SET_CFG_VALUE(user);

	/*
	 连接数据库
	 */
	EXEC SQL CONNECT TO :cfg.db	USER :cfg.user USING :cfg.pass;
	if (sqlca.sqlcode != 0) {
		SQL_ERROR;
		return -1;
	}
	/*
	 设置SCHEMA
	 */
	if(NULL == schema){
		return 0;
	}
	SET_CFG_VALUE(schema);
#undef SET_CFG_VALUE		
	EXEC SQL SET CURRENT SCHEMA: cfg.schema;
	if (sqlca.sqlcode != 0) {
		SQL_ERROR;
		return -1;
	}
	return 0;
}

int disconnect_db(void){
	/*
	 *判断数据库的连接状态是否是断开状态
	 *如果不是断开状态需要重新断开
	 *
	 */
	EXEC SQL CONNECT RESET;
	SQL_ERROR;
	return (sqlca.sqlcode != 0) ? -1 : 0;
}