Re: Dynamic Table Names in Dynamic SQL
Date: 1995/09/25
Message-ID: <dlm3-2509951817520001_at_wyndmoor1-57.slip.netaxs.com>#1/1
robert.goodwin_at_msfc.nasa.gov (Robert Goodwin) wrote:
>I wonder if anyone else has had this problem...
>We're developing Pro*C code in which the table names in our SQL
statements are
>dynamic...that is, the table names are different in different situations. We
>can accomplish this by simply building the entire SQL statement using the
>capabilities of C, and then executing the SQL statement. But, we'd like
to be
>able to have a variable (a bind variable?) for the table name, and simply use
>that variable in the SQL statement. I would assume that this would be a
>little more efficient and straightforward than building the entire SQL
>statement each time. However, the Pro*C precompiler doesn't seem to like
>this. We can use bind variables for values of fields, but not for the table
>names. Does anyone know if there is a way to do what we're wanting to do?
>Thanks for any help. E-mail response if possible.
One solution to this problem is not particularly straightforward, but it works well, once you've set it up. (Please note that I have not myself coded this so copying the code fragment below directly might be frustrating... I did at least suggest this method to a programmer working for me on a similar problem, and it worked great.)
Do you have a finite number of tables you wish to select from ? If you do, create an array containing the list of them, table_list. Then set up a routine which performs the following:
void setup_table(tablename *char)
{
exec sql DROP SYNONYM SELECTED_TABLE
switch lookup(tablename,table_list)
{
tablename1:
exec sql CREATE SYNONYM SELECTED TABLE FOR <tablename1>;
tablename2:
exec sql CREATE SYNONYM SELECTED TABLE FOR <tablename2>;
etc...
}
}
The 'lookup' function compares tablename to an array of table names and returns the index of the matching name. The tablename1, tablename2 etc. entries would have to be defined as constants, but are used only for clarity.
It's not pretty, but it cleans up the rest of your code. You can drop the synonym to clean up afterward.
Hope this helps.
don mccorvey
aka mccorveyd_at_pgate.he.boeing.com
dba at large for the helicopters division vehicle management systems group...
don mccorvey | dlm3_at_netaxs.com | Philadelphia, PA
"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects.
- Robert A. Heinlein