| SQLCURSOR variable and dynamic SQL [message #93872] |
Fri, 28 February 2003 08:45  |
Peter Anson
Messages: 1
Registered: February 2003
|
Junior Member |
|
|
I know you can use a cursor variable in the following way :
EXEC SQL ALLOCATE :ecursor;
EXEC SQL EXECUTE
BEGIN
OPEN :ecursor FOR SELECT empno, ename FROM emp;
END;
END-EXEC;
EXEC SQL FETCH :ecursor INTO :numbers, :names;
I also know that you can have a dynamic SQL statement in the following way :
sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL PREPARE STMT FROM :sqlstmt;
EXEC SQL DECLARE B_CURS CURSOR FOR STMT;
EXEC SQL OPEN B_CURS;
EXEC SQL FETCH B_CURS INTO :numbers, :names;
What I need to do is combined those two - have a SQLCURSOR cursor variable that uses a
dynamic sql statement. In my case building the "sqlstmt" isn't as simple as the above
example- it's a complex procedure (otherwise I would do it the same way as the first example). And I need to be able to do the fetch using the SQLCURSOR variable (so the second method isn't an option) (I thought I'd mention that before anyone suggested I just use the second method. I can give the details of exactly "why" that's the case, but I'm trying to keep my question as simple as possible)
Does anyone know how I can have a SQLCURSOR variable using a dynamically built SQL statement ?
Here is a portions of the pro*fortran code I tried the following but got an error in the precompiler :
sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL ALLOCATE :ecursor;
EXEC SQL EXECUTE
BEGIN
OPEN :ecursor FOR sqlstmt;
END;
END-EXEC;
- the OPEN statement generated the following error:
PCC-S-0026: Invalid host variable "ecursor"
(ecursor was declared as a SQLCURSOR in the "EXEC SQL BEGIN DECLARE SECTION")
I then tried the following:
sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL ALLOCATE :ecursor;
SQL_STMT = 'OPEN :ecursor FOR ' // SQL_STMT
EXEC SQL EXECUTE IMMEDIATE :SQL_STMT
EXEC SQL FETCH :ecursor INTO
1 :numbers,
1 :names;
This time I got a different error:
PCC-S-0003: Invalid SQL Identifier
on the FETCH line (and "numbers" and "names" *were* properly declared in the "EXEC SQL BEGIN DECLARE SECTION" section)
I would greatly appreciate any help !
|
|
|
|
| Re: SQLCURSOR variable and dynamic SQL [message #93878 is a reply to message #93872] |
Sun, 02 March 2003 10:03  |
langda
Messages: 5
Registered: March 2003
|
Junior Member |
|
|
Ideally you should Use Dynamic SQL Method 4 for this.
but give it a shot for the below procedure ...
Write a Stored Procedure which returns REF cursor for sqlstatment passed. And call this SP passing the sql_cursor
CREATE PACKAGE demo_cur_pkg AS
TYPE cur_type IS REF CURSOR ;
PROCEDURE open_emp_cur (
curs IN OUT cur_type,
dept_num IN NUMBER);
END;
CREATE PACKAGE BODY demo_cur_pkg AS
CREATE PROCEDURE open_emp_cur (
curs IN OUT cur_type,
sqlstmt varchar2 ) IS
BEGIN
OPEN curs FOR sqlstmt;
END;
END
sql_cursor emp_cursor;
char sql_stmt[[20]]="select * from emp";
...
EXEC SQL ALLOCATE :emp_cursor; /* allocate the cursor variable */
...
/* Open the cursor on the server side. */
EXEC SQL EXECUTE
begin
demo_cur_pkg.open_emp_cur(:emp_cursor, :sqlstmt);
end;
END-EXEC;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
EXEC SQL FETCH :emp_cursor INTO :emp_name;
printf("%sn", emp_name);
}
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
