Pro*C Dynamic SQL calling functions/procedures

From: <loyeah_at_gmail.com>
Date: Wed, 13 Feb 2008 17:58:08 -0800 (PST)
Message-ID: <51457a63-75a7-4d45-89b0-4e5a4eaba774@s37g2000prg.googlegroups.com>

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;

Received on Wed Feb 13 2008 - 19:58:08 CST