Problem:
I can use the following if I know the table name, but need something when tablename is not known at compile time -
exec sql
declare ccurs cursor for
query odbc columns tablename 'table'
end-exec
Resolution:
You need to use dynamic sql:
1. declare the cursor to be used to fetch the column information
EXEC SQL
declare vcurs cursor for dynamic_sql
END-EXEC
2. prepare the string containing the query to be executed, using a parameter marker (?) for the tablename (note declare string large enough to hold the statement)
string
"query odbc columns tablename ? " delimited size
into sql-text
EXEC SQL
prepare dynamic_sql from :sql-text
END-EXEC
3. Open the cursor, passing the tablename thru a declared host variable
EXEC SQL
open vcurs using :tblname
END-EXEC
4. fetch column information
EXEC SQL
fetch vcurs into :column-catalog:col-inds
END-EXEC
where column information is defined as:
01 column-catalog.
03 col-qualifier pic x(32).
03 col-owner pic x(32).
03 col-table-name pic x(32).
03 col-name pic x(32).
03 col-data-type pic s9(4) comp-5.
03 col-type-name pic x(32).
03 col-precision pic s9(9) comp-5.
03 col-length pic s9(9) comp-5.
03 col-scale pic s9(4) comp-5.
03 col-radix pic s9(4) comp-5.
03 col-nullable pic s9(4) comp-5.
03 col-remarks pic x(254).
01 column-indicators.
03 col-inds pic s9(4) comp-5 occurs 12.
