Re: Table substitution with PRO*C Dynamic SQL

From: Brent Lambert <brent_at_spss.com>
Date: Tue, 8 Jun 1993 15:13:12 GMT
Message-ID: <C8B6AB.5Mu_at_spss.com>

In article <1v069u$jv1_at_skates.gsfc.nasa.gov>, joefw_at_eosdev1.gsfc.nasa.gov (Joe Fulson-Woytek) writes:
> I am trying my hand at dynamic sql using PRO*C. I was able to basically
> duplicate the example in the manual where I substitute a value into a
> where statement and do a select. But what I really want to do is
> substitute the table name in the select statement. The manual implies this
> is doable, but I was unable to find an example, so I struck out on my own, and
> struck out. I wrote the following code:
>
> /**********************************************************************/
> EXEC SQL INCLUDE sqlca.h;
> EXEC SQL BEGIN DECLARE SECTION;
> VARCHAR sqlstmt[80];
> VARCHAR username[30];
> VARCHAR table[30];
> EXEC SQL END DECLARE SECTION;
> main()
> {
> table.len = sprintf ( table.arr, "PERSON" ) ;
> EXEC SQL WHENEVER SQLERROR GOTO errexit;
> /* do the SQL CONNECT */
> :
> sqlstmt.len = sprintf
> ( sqlstmt.arr, "SELECT USERNAME FROM :v1 WHERE PERSON_ID = 430");
> EXEC SQL PREPARE S FROM :sqlstmt;
> EXEC SQL DECLARE C CURSOR FOR S;
> EXEC SQL OPEN C USING :table;
> EXEC SQL FETCH C INTO :username;
> /* rest of code */
> :
> /**********************************************************************/
>
> I get the error: ORA-00903: invalid table name
> which seems to indicate the substitution is not working.
> The straight sql statement: SELECT USERNAME FROM PERSON WHERE PERSON_ID = 430
> works fine.
>
> Any ideas/suggestions would be appreciated.
>
> (Oracle V6 on SGI )
>
>
> Joe Fulson-Woytek
> Code 902/Goddard DAAC
> NASA/Goddard Space Flight Center
> joefw_at_eosdev1.gsfc.nasa.gov

You can't use a variable for a table name. See the section "Referencing Host Variables" (page 1-10 in my book) of the first chapter in "ORACLE Precompilers, Pro*C Supplement".

One workaround which is fine for your test program but is cetrtainly less useful for real applications is:

    sqlstmt.len = sprintf

        ( sqlstmt.arr, "SELECT USERNAME FROM %s WHERE PERSON_ID = 430",
          table.arr);
    .

    .
    .
    EXEC SQL OPEN C; The reason it's less useful for real applications is that you still need to know the table name (or the universe of possible table names) at compile time. With multiple possible table names, you also have to worry about possibly different column names and columns of interest in each table. Dynamic Method 4 is better (IMO) if you need that kind of flexibility.

-- 
The above statements are not the opinions or policies of SPSS Inc.
The above statements may not be the opinions of Brent Lambert.
The first disclaimer is a policy of SPSS Inc.
Subsequent disclaimers are probably the opinion of Brent Lambert.

Received on Tue Jun 08 1993 - 17:13:12 CEST