Hi,
The above cursor is supposed to fetch the data for a specific row, how can i fetch it into a variable?
here's how its supposed to go:
DECLARE vStmt Varchar (250);
DECLARE wsJobI INTEGER
DECLARE wsOrdI INTEGER
DECLARE dispCSR CURSOR FOR v_SQL_stmt;
SET vStmt = 'SELECT JOB_I FROM "ALI.TDSP_JOB_XRF" WHERE ORD_I = ? AND DSP_NBR = 1 WITH UR;';
PREPARE v_SQL_stmt FROM vStmt;
OPEN dispCSR USING wsOrdI;
FETCH dispCSR
INTO wsJobI;
I get this error--> WSJOBI" is not valid in the context where it is used. SQLSTATE=42703
CLOSE dispCSR;
how do i fetch data and store it in a variable for future use?
also dont i have to declare v_SQL_stmt as well? what would it be declared as?
Thanks for all the help!
That error is usually raised when you dont declare the variable. Just check that.
Edit you following statement as
SET vStmt = 'SELECT JOB_I FROM ALI.TDSP_JOB_XRF WHERE ORD_I = ? AND DSP_NBR = 1 WITH UR';
To fetch the statement you have wriiten is enough.
How ever is more than 1 row is returned , u need to have a loop to read each value.
see the example
CREATE PROCEDURE SAMPLE_PROC"
language sql
begin
declare v_var varchar(100);
declare col1 varchar(100);
declare v_flag integer default 1;
declare v_sql_stmt varchar(500);
declare v_cursor cursor for v_sql;
declare continue handler for not found
begin
set v_flag=-1;
end;
set v_var='name';
set v_sql_stmt='select * from table1 where col1 = ?';
prepare v_sql from v_sql_stmt ;
open v_cursor using v_var;
while(v_flag=1)
do
fetch v_cursor into col1;
if(v_flag!=-1) then
insert into table2(col1) values(col1);
end if;
end while;
close v_cursor;
end
The datatype v_sql variable is taken as STATEMENT.
Hope this was what u needed.
Cheers
21
