Could I use a host variable for replacing the table name in SQC program?

[young]

Hello all,

I would like to use same SQL statement in my SQC program for several
tables
I tried something like that:

- sql_sts="SELECT col1, col2 FROM ?"
- EXEC SQL PREPARE dsql FROM :sql_sts;
- EXEC SQL DECLARE cTab CURSOR WITH HOLD FOR dsql;
- EXEC SQL OPEN cTab USING :tabName;
EXEC SQL FETCH cTab;
- EXEC SQL CLOSE cTab;

where tabName is a parameter, and it does not work!
Could I use a host variable like this way in my SQC?
Thanks for your help

Y.

[mike]

Which db2 version and fixpak on which operating system?
What is the exact error that you get ?

[young]

Which db2 version and fixpak on which operating system?
We have DB2 v8.1.1.96 FixPak 10 on AIX 5.2

What is the exact error that you get ?

I got SQLCODE=-727 and SQLSTATE=56098

I think the table name can not be used as parameter in a prepare
statement? Is that correct?

Thanks

D.

[Knut Stolze]

young wrote:

Hello all,

I would like to use same SQL statement in my SQC program for several
tables
I tried something like that:

- sql_sts="SELECT col1, col2 FROM ?"
- EXEC SQL PREPARE dsql FROM :sql_sts;
- EXEC SQL DECLARE cTab CURSOR WITH HOLD FOR dsql;
- EXEC SQL OPEN cTab USING :tabName;
EXEC SQL FETCH cTab;
- EXEC SQL CLOSE cTab;

where tabName is a parameter, and it does not work!
Could I use a host variable like this way in my SQC?
Thanks for your help

You can't do that. If you have static embedded SQL, then DB2 would attempt
to compile the statement. And that's impossible if DB2 doesn't know the
table it is working with. If you have dynamic embedded SQL (it looks that
way to me), you don't really need a parameter marker there. Just build the
SQL statement with the actual table name using sprintf() or other means.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany