下面的代码要执行一个动态sql,sql语句是通过编辑dyn_sql,然后动态sql执行的结果保存到assign_info中去。提示的错误是:dyn_sql没有申明。要怎么写才正确?谢谢!
int assign_table_deal(int table_no, assign_data_info_t * assign_header)
{
int all_getnum = 0;
int getnum = 0;
int i;
assign_data_info_t * assign_p; EXEC SQL BEGIN DECLARE SECTION;
const int f_getnum = FETCH_MAX;
struct assign_inf{
long pattern_id;
}assign_info[FETCH_MAX]; struct assign_ind_inf{
short s_pattern_id;
}assign_ind_info[FETCH_MAX];
char dyn_sql[MAX_STATEMENT_LEN];
EXEC SQL END DECLARE SECTION; assign_header = NULL;
memset(dyn_sql,'\0',sizeof(dyn_sql));
sprintf(dyn_sql,"SELECT DISTINCT %sNO FROM %s WHERE ",table_list[table_no].table_name,table_list[table_no].assign_table_name);
if(table_list[table_no].column_symbol[0] == "DEF_TYPE")
{
sprintf(dyn_sql,"DEF_TYPE = 1 ");
}
else if(table_list[table_no].column_symbol[0] == "MMSNO")
{
sprintf(dyn_sql,"MMSNO = 9999 ");
}
else
{
sprintf(msg, "%s table define info error!",table_list[table_no].table_name);
com_log(ON, msg, __FILE__, __LINE__);
return(COM_ERR);
}
sprintf(dyn_sql,"ORDER BY %sNO ",table_list[table_no].table_name);
EXEC SQL DECLARE n_cur CURSOR FOR dyn_sql;
EXEC SQL OPEN n_cur;
if (sqlca.sqlcode != 0)
{
*status = sqlca.sqlcode;
com_dblog(table_list[table_no].assign_table_name, "OPEN", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc, __FILE__, __LINE__);
EXEC SQL CLOSE n_cur;
return(COM_DB_ERR);
}
while(1)
{
EXEC SQL FOR :f_getnum
FETCH
n_cur
INTO
:assign_info:assign_ind_info; if (sqlca.sqlcode != 0 && sqlca.sqlcode != NOT_FOUND)
{ *status = sqlca.sqlcode; com_dblog(table_list[table_no].assign_table_name, "FETCH", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc, __FILE__, __LINE__); EXEC SQL CLOSE n_cur;
assign_list_release(assign_header);
return(COM_DB_ERR);
}
getnum = sqlca.sqlerrd[2] - all_getnum; all_getnum = sqlca.sqlerrd[2]; if (getnum == 0)
{
break;
}
else
{
/* the first record,assign_header point to it */
if(assign_header == NULL)
{
assign_header = (assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_header->value = assign_info[0].pattern_id;
assign_p = assign_header;
for(i=1;i<getnum;i++)
{
assign_p->next_record = ( assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_p = assign_p->next_record;
assign_p->value = assign_info[i].pattern_id;
assign_p->next_record = NULL;
}
}
else
{
for(i=0;i<getnum;i++)
{
assign_p->next_record = ( assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_p = assign_p->next_record;
assign_p->value = assign_info[i].pattern_id;
assign_p->next_record = NULL;
}
}
if(getnum < FETCH_MAX)
{
break;
}
}
}
EXEC SQL CLOSE n_cur;
return (COM_NOR);
}
int assign_table_deal(int table_no, assign_data_info_t * assign_header)
{
int all_getnum = 0;
int getnum = 0;
int i;
assign_data_info_t * assign_p; EXEC SQL BEGIN DECLARE SECTION;
const int f_getnum = FETCH_MAX;
struct assign_inf{
long pattern_id;
}assign_info[FETCH_MAX]; struct assign_ind_inf{
short s_pattern_id;
}assign_ind_info[FETCH_MAX];
char dyn_sql[MAX_STATEMENT_LEN];
EXEC SQL END DECLARE SECTION; assign_header = NULL;
memset(dyn_sql,'\0',sizeof(dyn_sql));
sprintf(dyn_sql,"SELECT DISTINCT %sNO FROM %s WHERE ",table_list[table_no].table_name,table_list[table_no].assign_table_name);
if(table_list[table_no].column_symbol[0] == "DEF_TYPE")
{
sprintf(dyn_sql,"DEF_TYPE = 1 ");
}
else if(table_list[table_no].column_symbol[0] == "MMSNO")
{
sprintf(dyn_sql,"MMSNO = 9999 ");
}
else
{
sprintf(msg, "%s table define info error!",table_list[table_no].table_name);
com_log(ON, msg, __FILE__, __LINE__);
return(COM_ERR);
}
sprintf(dyn_sql,"ORDER BY %sNO ",table_list[table_no].table_name);
EXEC SQL DECLARE n_cur CURSOR FOR dyn_sql;
EXEC SQL OPEN n_cur;
if (sqlca.sqlcode != 0)
{
*status = sqlca.sqlcode;
com_dblog(table_list[table_no].assign_table_name, "OPEN", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc, __FILE__, __LINE__);
EXEC SQL CLOSE n_cur;
return(COM_DB_ERR);
}
while(1)
{
EXEC SQL FOR :f_getnum
FETCH
n_cur
INTO
:assign_info:assign_ind_info; if (sqlca.sqlcode != 0 && sqlca.sqlcode != NOT_FOUND)
{ *status = sqlca.sqlcode; com_dblog(table_list[table_no].assign_table_name, "FETCH", sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc, __FILE__, __LINE__); EXEC SQL CLOSE n_cur;
assign_list_release(assign_header);
return(COM_DB_ERR);
}
getnum = sqlca.sqlerrd[2] - all_getnum; all_getnum = sqlca.sqlerrd[2]; if (getnum == 0)
{
break;
}
else
{
/* the first record,assign_header point to it */
if(assign_header == NULL)
{
assign_header = (assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_header->value = assign_info[0].pattern_id;
assign_p = assign_header;
for(i=1;i<getnum;i++)
{
assign_p->next_record = ( assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_p = assign_p->next_record;
assign_p->value = assign_info[i].pattern_id;
assign_p->next_record = NULL;
}
}
else
{
for(i=0;i<getnum;i++)
{
assign_p->next_record = ( assign_data_info_t *)malloc(sizeof(assign_data_info_t));
assign_p = assign_p->next_record;
assign_p->value = assign_info[i].pattern_id;
assign_p->next_record = NULL;
}
}
if(getnum < FETCH_MAX)
{
break;
}
}
}
EXEC SQL CLOSE n_cur;
return (COM_NOR);
}
现在要实现以下功能:
delete_clause_set是用来设置dyn_sql,下面执行动态sql,有问题吗?
EXEC SQL BEGIN DECLARE SECTION;
char dyn_sql[MAX_STATEMENT_LEN];
char assign_sql[MAX_STATEMENT_LEN];
char syspara_sql[MAX_STATEMENT_LEN];
EXEC SQL END DECLARE SECTION; delete_clause_set(dyn_sql,table_no,p1); EXEC SQL EXECUTE IMMEDIATE :dyn_sql; EXEC SQL COMMIT WORK RELEASE;