Re: Dynamic Table Names in Dynamic SQL
Date: 1995/09/20
Message-ID: <43nokl$k4e_at_serv.hinet.net>#1/1
>
>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.
>
>
> :
> /^\
> : :
> :___:
>___.^:_._:^._______________________________________________________________
> :-:.^.:-: :
> :_:|=|:_: Robert Goodwin : phone: (205)461-4582
> : :|.|: : Database Development : fax: (205)461-4999
> :-/|.|\-: New Technology, Inc. :
> :/ |.| \: Huntsville, AL :
(__|.|__) :> //\ /\\
> :_:(|):_: NASA/MSFC : e-mail: robert.goodwin_at_msfc.nasa.gov
> --/^\^|^/^\---------------------------------------------------------------
> /\\ //\
> /\\\ //\\\
>
you can try following statement :
EXEC SQL BEGIN DECLARE SECTION;
.
static VARCHAR sqlstmt[512];
static char table_name[30];
.
EXEC SQL BEGIN DECLARE SECTION; sprintf(table_name,"dynamic_table_name_%ld",time(NULL));
sqlstmt.len = sprintf(sqlstmt.arr,
"SELECT FIELD_A,FIELD_B,.... FROM %s",table_name);
EXEC SQL PREPARE S1 FROM :sqlstmt;
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
Thomas Huang Received on Wed Sep 20 1995 - 00:00:00 CEST