Pro*C Dynamic SQL calling functions/procedures
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