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来处理。