Preparing statements in a stored procedure...

Folks, let me start this by saying I know I'm trying to do something that I'm not supposed to be able to do. I'm trying to create a table based on table names and column names stored in a data table within my legacy 4GL application. The table that contains the names is in the form: tab_n char(18) (table name) col_n char(18) (column name) col_id integer (link to the table that contains user data) The table that contains the user data has the following form: join_id integer (hook to other information I need) col_id integer (reference back to the previously defined table) sort smallint (determines whether this is the 1st, 2nd, etc entry for this particular join_id item) valu char(15) (actual user data) The table that I want to create is of the form: join_id integer (same as the hook in the previous table) sort smallint (same as the sort in the previous table) rating char(1) (this is the valu from the previous table where the col_id in the first table matches the col_id in the previous table, and the col_n in the first table is "rating") . . . The idea here, is that the first table is metadata that determines the table and column to insert/update values in the third table with the value in the second table. Whew! In any case, this is relatively easy if I can prepare a statement. The example follows (pardon the pseudo 4GL; I started this as a procedure definition before I realized that I can't do prepares in a stored procedure): ######################################################################### function do_data(l_join_id integer, l_col_id smallint, l_sort smallint, l_valu char(15), l_module char(2)) ######################################################################### # Created 03/18/97 # # Description: _data table to real table conversion procedure # # This procedure updates the tab_n.fld_n value in the tab_n table # when a row is inserted into an _data table, based on the values in # tab_n and fld_n in ci_fld. If the tab_n table does not exist, this # procedure calls the procedure to create it. If the fld_n column does # not exist, this procedure calls the procedure to create it. If the # data row does not exist, this procedure inserts a row into the tab_n # table so that the update can be done. # # Procedure Plan # # 1. Retrieve the tab_n and col_n values from ci_fld for the given # col_id and module. # 2. Check existence of the tab_n table (select from systables). If # it does not exist, call the mk_cfg_table() procedure. # 3. Check the existence of the fld_n column in the tab_n table # (select from syscolumns). If it does not exist, call the # mk_cfg_column() procedure. # 4. Convert the data in the l_valu field based on the datatype in # the ci_fld table, if appropriate. # 5. Update the column in the tab_n table with the converted value # in the l_valu field. # 6. If the update is unsuccessful, insert a row with the appropriate # join_id and sort into the tab_n table. # 7. If the new row was inserted, update the tab_n table again with # the converted value. # # Modification History: # # Date Initials/Description # ------ ------------------------------------------------------------ # ######################################################################### define l_cnv_valu char(50); g record c_str char(1000), answer char(15), rc smallint end record; l_fld record fld_n char(18), tab_n char(18), datatype char(1), lngth smallint end record; l_table record tab_n char(18), tab_id smallint end record; ######################################################################### # Declare Cursors ######################################################################### let g.c_str = 'select unique tab_n, ', ' fld_n, ', ' datatype, ', ' datasize ', 'from ci_fld ', 'where col_id = ? ', ' and module = ? ' prepare s_ci_fld from g.c_str declare c_ci_fld cursor for s_ci_fld let g.c_str = 'select tabname, ', ' tabid ', 'from systables ', 'where tabname = ? ' prepare s_tab_xst from g.c_str declare c_tab_xst cursor for s_tab_xst let g.c_str = 'select count(*) ', 'from syscolumns ', 'where tabid = ? ', ' and colname = ? ' prepare s_col_xst from g.c_str declare c_col_xst cursor for s_col_xst let g.c_str = 'update ', l_fld.tab_n clipped, ' ', 'set ', l_fld.col_n clipped, ' = "', l_cnv_valu clipped, '" ' 'where join_id = ', 'l_join_id clipped, ' ', ' and sort = ', l_sort clipped, ' ' prepare e_upd_row from g.c_str let g.c_str = 'insert into ', l_fld.tab_n clipped, ' ', '(join_id, sort) values (?, ?) ' prepare e_ins_row from g.c_str ######################################################################### # Procedure Body ######################################################################### open c_ci_fld using l_col_id, l_module fetch c_ci_fld into l_fld.* open c_tab_xst using l_fld.tab_n fetch c_tab_xst into l_table.* case when sqlca.sqlcode = 100 call mk_cfg_table(l_fld.tab_n, l_module, "c_local") call do_data(l_join_id, l_col_id, l_sort, l_valu, l_module) exit function when sqlca.sqlcode <> 0 exit function end case open c_col_xst using l_fld.col_n, l_table.tab_id fetch c_col_xst into g.rc case when sqlca.sqlcode = 100 call mk_cfg_column(l_fld.tab_n, l_fld.col_n, l_fld.datatype, l_fld.lngth) call do_data(l_join_id, l_col_id, l_sort, l_valu, l_module) exit function when sqlca.sqlcode <> 0 exit function end case case l_fld.datatype when "D" let l_cnv_valu = date(l_valu + 0) using "MM/DD/YYYY" when "N" let l_cnv_valu = l_valu - 10**(l_fld.lngth - 1) using "##############&" otherwise let l_cnv_valu = l_valu end case execute e_upd_row case when sqlca.sqlcode = 0 if sqlca.sqlerrd[3] = 0 then@@N