Re: embedded SQL question?
Date: 1996/12/10
Message-ID: <32ad7d33.2243896_at_dcsun4>#1/1
You can and the code would look like this (can't use bind variables on sql identifiers, must physically build the query):
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR stmt[500];
int n;
VARCHAR x[10];
EXEC SQL END DECLARE SECTION;
char * tname;
strcpy( x.arr, "A%" );
x.len = strlen( x.arr );
tname = "ALL_CATALOG";
sprintf( stmt.arr,
"select count(*) from %s where table_name like :x", tname ); stmt.len = strlen( stmt.arr );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :stmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C USING :x;
EXEC SQL FETCH C INTO :n;
printf( "Table %s has %d matching rows\n", tname, n );
tname = "ALL_CONSTRAINTS";
sprintf( stmt.arr,
"select count(*) from %s where table_name like :x", tname ); stmt.len = strlen( stmt.arr );
EXEC SQL PREPARE S FROM :stmt;
EXEC SQL OPEN C USING :x;
EXEC SQL FETCH C INTO :n;
printf( "Table %s has %d matching rows\n", tname, n );
EXEC SQL CLOSE C;
}
On 10 Dec 1996 07:48:50 GMT, ruihua_at_cat.cs.mu.OZ.AU (Rui Hua Sarah YANG) wrote:
>Hello,
>
> Can anyone tell me whether I can use a variable to replace a table name in a
> embedded SQL program(The purpose is to make the program generic)? If you
> know the answer, would you please tell me how to do it? Thanks!
> my email address is : ruihua_at_cs.mu.oz.au
>
> We assume there is a table student in a database myDatabase.
>
> The code is:
>
>#include <stdio.h>
>
>EXEC SQL BEGIN DECLARE SECTION;
> char name1[50];
> char address1[80];
> char relName[20]; /*Is it correct to declare table name here?*/
>EXEC SQL END DECLARE SECTION;
>
>EXEC SQL INCLUDE SQLCA;
>
>main()
>{
> strcpy(relName, "student"); /* copy the table student to relName*/
> strcpy(name1, "duncan");
> strcpy(address1, "Melbourne");
>
> EXEC SQL CONNECT myDatabase;
>
> EXEC SQL INSERT INTO
> relName(name, address) /* can I use relName to replace table student?*/
> ^^^^^^^
> VALUES(:name1, :address1);
>
> EXEC SQL DECLARE pt CURSOR FOR
> SELECT name, address
> FROM relName /* can I use relName to replace table student?*/
> ^^^^^^^
> WHERE name='duncan';
>
> ...................
>
> EXEC SQL DISCONNECT;
> exit(0);
>}
>
>
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Dec 10 1996 - 00:00:00 CET