ProC动态步骤四
ProC动态方法四
动态方法四（第一种）:
/*
* function name ：login2ASF
* Author ：XGT
* Version ：1.0.0
* 1.0.0 2011/12/20 XGT Create
* Description
* 登录到正式的ASF表，包括基础信息表和展开表
* Parameters
* void
* return values
* void
*/
void login2ASF(){
//SQL内部使用变量声明
EXEC SQL BEGIN DECLARE SECTION;
char columnName[64];
char selectStr[5120];
char insertStr[3072];
char date_s[8 + 1];
char date_e[8 + 1];
char resultArray[128];
int dataLegth, ANSI_varchar_type, occurs;
char *strSql = "SELECT ut.column_name FROM user_tab_columns ut WHERE ut.table_name = 'TEMP_PL_M_ASF' AND ut.column_name in \
(SELECT uta.column_name FROM user_tab_columns uta WHERE uta.table_name = 'PL_M_ASF') ORDER BY ut.column_name ";
char *strSql_len = "SELECT ut.data_length FROM user_tab_columns ut WHERE ut.table_name = 'TEMP_PL_M_ASF' AND ut.column_name in \
(SELECT uta.column_name FROM user_tab_columns uta WHERE uta.table_name = 'PL_M_ASF') ORDER BY ut.column_name ";
int arrayCount;
short indi;
char *cpySelectStr;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE DESCRIPTOR 'in' ;
EXEC SQL ALLOCATE DESCRIPTOR 'out' ;
char *comma = ",";
ANSI_varchar_type = 97;
dataLegth = 128;
/* 初始化字符数组 */
memset(columnName, '\0', sizeof(columnName));
memset(selectStr, '0', sizeof(selectStr));
strcpy_s(selectStr, "SELECT ");
//strcpy_s(date_s, gch_data_s);
//strcpy_s(date_e, gch_data_e);
strcpy_s(date_s, "20111204");
strcpy_s(date_e, "20111206");
/* 获取应该插入的列 */
EXEC SQL PREPARE stat_login FROM :strSql;
EXEC SQL DECLARE cur_login2ASF CURSOR FOR stat_login;
EXEC SQL OPEN cur_login2ASF;
EXEC SQL WHENEVER NOT FOUND DO BREAK;//当循环时，没有数据则break.此语句要写在循环语句上面
for(;;){
EXEC SQL FETCH cur_login2ASF INTO :columnName;
TrimBlank(columnName);
strcat(selectStr, columnName);
strcat(selectStr, comma);
}
EXEC SQL CLOSE cur_seta;
selectStr[strlen(selectStr) - 1] = '\0';
strcat(selectStr, " FROM temp_pl_m_asf WHERE asfinsertflag = '0' AND carinfoflag = '1' AND (TestCarImpFlag = 1 OR TestCarFlag = 0) AND \
bodyonplandate BETWEEN '");
strcat(selectStr, date_s);
strcat(selectStr, "' AND '");
strcat(selectStr, date_e);
strcat(selectStr, "'");
printf("selectStr len:%d", strlen(selectStr));
EXEC SQL PREPARE stat_insert_asf FROM :selectStr;
EXEC SQL DESCRIBE INPUT stat_insert_asf USING DESCRIPTOR 'in';
EXEC SQL DECLARE cur_insert_asf CURSOR FOR stat_insert_asf;
printf("sqlcode:%d\n",sqlca.sqlcode);
printf("sqlerrmc:%s\n",sqlca.sqlerrm.sqlerrmc);
printf("sqlerrd:%d\n",sqlca.sqlerrd[4]);
EXEC SQL OPEN cur_insert_asf USING DESCRIPTOR 'in';
printf("sqlcode:%d\n",sqlca.sqlcode);
printf("sqlerrmc:%s\n",sqlca.sqlerrm.sqlerrmc);
printf("sqlerrd:%d\n",sqlca.sqlerrd[4]);
EXEC SQL DESCRIBE OUTPUT stat_insert_asf USING DESCRIPTOR 'out';
EXEC SQL GET DESCRIPTOR 'out' :arrayCount = COUNT;
printf("arrayCount:%d\n",arrayCount);
for (int i=1; i <= arrayCount; i++){
occurs = i;
EXEC SQL SET DESCRIPTOR 'out' VALUE :occurs TYPE = :ANSI_varchar_type, LENGTH = :dataLegth;
EXEC SQL GET DESCRIPTOR 'out' VALUE :occurs :columnName = NAME;
printf("columnName:%s\n",columnName);
}
EXEC SQL WHENEVER NOT FOUND DO BREAK ;
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH cur_insert_asf INTO DESCRIPTOR 'out' ;
for(int i=0;i<arrayCount;i++){
occurs = i + 1;
EXEC SQL GET DESCRIPTOR 'out' VALUE :occurs :resultArray = DATA ;
//printf("resultArray = %s\n", resultArray) ;
}
}
system("pause");
EXEC SQL CLOSE cur_insert_asf;
EXEC SQL DEALLOCATE DESCRIPTOR 'in' ;
EXEC SQL DEALLOCATE DESCRIPTOR 'out' ;
EXEC SQL WHENEVER NOT FOUND CONTINUE;//需变更回<NOT FOUND>的处理方式
}
动态方法处理四之二
void dynamicMethod_SQLDA ()
{
EXEC SQL BEGIN DECLARE SECTION;
char* dyn_statement = "SELECT t.typecode, t.mccode FROM pl_m_alc t WHERE t.msglen ='10'";
char dyn_sql[2048];
int deptno_type = 3, deptno_len = 2, deptno_data = 10 ;
int ename_type = 97, ename_len = 16 ;
char ename_data[256] ;
int empno_type = 97, empno_len = 2 ;
char empno_data[2 + 1];
long SQLCODE = 0 ;
int columnIndex;
int arrayCount;
EXEC SQL END DECLARE SECTION;
SQLDA *select_dp;
SQLDA *bind_dp;
int null_ok, index;
char title[40];
bind_dp = SQLSQLDAAlloc(SQL_SINGLE_RCTX_0, MAX_ITEMS, (size_t)MAX_VNAME_LEN, (size_t)MAX_INAME_LEN);
select_dp = SQLSQLDAAlloc(SQL_SINGLE_RCTX_0, MAX_ITEMS, (size_t)MAX_VNAME_LEN, (size_t)MAX_INAME_LEN);
for(int j = 0; j<= 122; j++){
select_dp->V[j] = (char *)malloc(256 * sizeof(char));
select_dp->I[j] = (short *)malloc(256 * sizeof(short));
bind_dp->V[j] = (char *)malloc(256 * sizeof(char));
bind_dp->I[j] = (short *)malloc(256 * sizeof(short));
}
strcpy(dyn_sql,"SELECT AREACODE,ASFNO,ASSEMBLYCODE,ASSEMBLYCOMMENT,ASSEMBLYOFFPLANDATE,ASSEMBLYOFFPLANSEQNO,ASSEMBLYOFFPLANSHIFT,ASSEMBLYONPLANDATE,ASSEMBLYONPLANSEQNO,ASSEMBLYONPLANSHIFT,\
BACKLEFTAIRBAG,BACKRIGHTAIRBAG,BASICENGINETYPE,BODYCHECKFLAG,BODYCOMMENT,BODYIMPORTANTFLAG,BODYNO,BODYOFFPLANDATE,BODYOFFPLANSEQNO,BODYOFFPLANSHIFT,BODYONPLANDATE,BODYONPLANSEQNO,BODYONPLANSHIFT,\
CHECKLISTCODE,CUPDUSER,DEALERCODE,DEALERSIGNFLAG,DES1REACHMONTHDAY,DESIGNCHANGECODE,DESTINATION1,DESTINATION2,DESTINATIONCODE,DESTINATIONMARK,DRIVERAIRBAG,DUMMYA,DUMMYR,DUMMYV,\
ENGINEKEYSNO,ENGINESNO,ENGINETYPE,EXPORTCODE,FINALLINE,FINALTP,FINISHCOMMENT,FINISHSTOPCANCELDATE,FINISHSTOPCANCELUSER,FINISHSTOPCOMMENT,FINISHSTOPFLAG,FINISHSTOPSETDATE,FINISHSTOPSETUSER,FRONTLEFTAIRBAG,\
FRONTRIGHTAIRBAG,GASCHECKFLAG,HARBORCODE,HQASSEMBLYLINE,HQBODYLINE,HQPAINTLINE,HQTESTLINE,INSIDEBASICCOLOR,INSIDEDESIGNCODE,KNEEAIRBAG,LANEMARK,LANENO,LASTLINE,LASTPLANTDATE,LASTTP,LASTTPSEQNO,LEFTCURTAINAIRBAG,\
LOCATIONNO,LOTNO,LOTSEQNO,MCCODE,MRN,OUTSIDECOLORCODE,OUTSIDECOLORMARK,OUTSIDEDESIGNCODE,PAINTCOMMENT,PAINTOFFPLANDATE,PAINTOFFPLANSEQNO,PAINTOFFPLANSHIFT,PAINTONPLANDATE,PAINTONPLANSEQNO,PAINTONPLANSHIFT,PASSEDQTY,PBSGROUPNO,\
PBSSEQNO,PBSSPECIALDESIGN,PBSSTAYCANCELDATE,PBSSTAYCANCELUSER,PBSSTAYCODE,PBSSTAYCOMMENT,PBSSTAYSETDATE,PBSSTAYSETUSER,PRESENTLINE,PRESENTTP,\
TRANSMISSIONSNO, TRANSMISSIONTYPE, TRANSPORTCODE, TYPECODE,\
VEHICLETYPEPROCESSCODE,VICEDRIVERAIRBAG,VINNO FROM temp_pl_m_asf WHERE asfinsertflag = '0' AND carinfoflag = '1' AND \
(TestCarImpFlag = 1 OR TestCarFlag = 0) AND bodyonplandate = '20120116'");
//printf("dyn_sql:%s", dyn_sql) ;
EXEC SQL PREPARE s_sqlda FROM :dyn_sql ;
EXEC SQL DECLARE c_sqlda CURSOR FOR s_sqlda ;
bind_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE BIND VARIABLES FOR s_sqlda INTO bind_dp;
bind_dp->N = bind_dp->F;
printf("bind_dp->F = %d", bind_dp->F) ;
select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR s_sqlda INTO select_dp;
select_dp->N = select_dp->F;
printf("bind_dp->F = %d", select_dp->F) ;
EXEC SQL OPEN c_sqlda USING DESCRIPTOR bind_dp;
for(int i=0; i< select_dp->F; i++){
SQLColumnNullCheck(0, (unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok);
switch(select_dp->T[i]){
case 12 : select_dp->L[i] = 12; break;
}
select_dp->T[i] = 5;//数据类型
select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i] + 1);
memset(title, 0, sizeof(title));
strncpy_s(title, select_dp->S[i], select_dp->C[i]);
//printf("\t%-.*s ", select_dp->L[i], title);
printf("title = %s;", title);
printf("len =%d\n", select_dp->L[i]);
}
index = 0;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while(SQLCODE == 0)
{
index = index + 1;
printf("第%d条数据：\n", index);
EXEC SQL FETCH c_sqlda USING DESCRIPTOR select_dp;
for(int i=0; i< select_dp->F; i++){
if(*select_dp->I[i] >= 0)
printf("\t%.*s", select_dp->L[i], select_dp->V[i]) ;
}
}
EXEC SQL CLOSE c_sqlda ;
}
使用SQLDA来处理。