Unanswered: About retrieve data from a dynamic ResultSet in SQL stored procedure
How can I retrieve a field value in results sets by the name of field or the number of field in SQL stored procedure like ResultSet.getXXX() procedure in JDBC. Because the field list will be changed while the caller call it every time.
create procedure dyna_cursor(in SOURCE varchar(128),
in FIELDLIST varchar(1000))
dynamic result sets 1
declare stmt varchar(1024);
declare at_end int default 0;
declare CONTINUE HANDLER FOR NOT FOUND
set at_end = 1;
set stmt = 'select '||FIELDLIST||' from '||SOURCE;
prepare ps from stmt;
declare c1 cursor with return to caller for ps;
--comment: the FIELDLIST is a list of fields in SOURCE table.
In another SQL stored procedure I will call the DYNA_CURSOR SQL stored procedure. I can't predifine the number and the type of fields included in the FIELDLIST variable, so I can't pre-declare variables corresponding the fields included in the FIELDLIST variable in another SQL procedure. How can I dynamic get the field-value returned by the dyna_cursor.
Thank you for your help! But I can't get the field value as the old. Because I can't dynamic declare those variables corresponding the values returned from DYNA_CURSOR procedure in another SQL procedure.