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