Re: Pro*C Dynamic SQL calling functions/procedures

On Feb 14, 9:58 am, loy..._at_gmail.com wrote:
> Hi all,
>
> In the Pro*C code at the bottom, I was trying to execute a function
> that returns a cursor. However, as I would only know the exact name of
> the function during runtime, I tried to create a dynamic statement
> calling the function (by inserting the GroupName variable in front of
> the function name).
>
> /*    EXEC SQL EXECUTE BEGIN
>                 :cursor_2 :=
> DEFAULT_GET_SEQUENTIAL_TXNS(:txnSeqNumStart, :txnSeqNumMax);
>     END; END-EXEC; */
> The above works fine and gives me correct output:
> sql status: 0seq start: 10001seq max: 10009
> TXN SEQ NUM 2 [10386]; REC COUNT [10386] ; STATUS
> [Y                                 ]
> TXN SEQ NUM 2 [10436]; REC COUNT [10436] ; STATUS
> [Y                                 ]
> TXN SEQ NUM 2 [14486]; REC COUNT [14486] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [15736]; REC COUNT [15736] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [16036]; REC COUNT [16036] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [16636]; REC COUNT [16636] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [17386]; REC COUNT [17386] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [18236]; REC COUNT [18236] ; STATUS
> [N                                 ]
> TXN SEQ NUM 2 [18586]; REC COUNT [18586] ; STATUS
> [N                                 ]
>
> But the dynamic sql one gives me the followings,
> sql status: 0seq start: 10001seq max: 10009
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
> TXN SEQ NUM 2 [0]; REC COUNT [0] ; STATUS []
>
> Not sure if there is any other workaround.
> Looking forward to getting your help.
> Thanks.
>
> My code
> **********
>         EXEC SQL BEGIN DECLARE SECTION;
>         char sqlCommand[MAX_SQL_COMMAND_SIZE];
>         long txnSeqNumStart;
>         long txnSeqNumMax;
>         long txnSeqNum;
>         char status[35];
>          int recCount;
>         SQL_CURSOR cursor_2;
>         EXEC SQL END DECLARE SECTION;
>
>         txnSeqNumStart = 10001;
>           txnSeqNumMax = 10009;
>
>         EXEC SQL ALLOCATE :cursor_2;
>
> /*    EXEC SQL EXECUTE BEGIN
>                 :cursor_2 :=
> DEFAULT_GET_SEQUENTIAL_TXNS(:txnSeqNumStart, :txnSeqNumMax);
>     END; END-EXEC; */
>
>         sprintf(sqlCommand, "BEGIN :v1 :=
> %s_GET_SEQUENTIAL_TXNS(:v2, :v3) ;END;", GroupName);
>
>         EXEC SQL PREPARE stmt FROM :sqlCommand;
>         EXEC SQL EXECUTE stmt
> USING :cursor_2, :txnSeqNumStart, :txnSeqNumMax;
>
>         for(int i=1 ;i<10;i++ )
>         {
>                 EXEC SQL FETCH :cursor_2
> INTO :txnSeqNum, :recCount, :status;
>                 cout << "TXN SEQ NUM 2 [" << txnSeqNum << "]; REC
> COUNT [" << recCount << "] ; STATUS ["  << status << "] \n";
>         }
>
>         EXEC SQL CLOSE :cursor_2;

Could anyone kindly help?
Thanks. Received on Wed Feb 27 2008 - 21:03:09 CST