Re: Pro*C -- How to pass a value into a Dynamic SQL (Method 3).
Date: Sat, 25 Mar 1995 19:53:47 GMT
Message-ID: <tssmithD60Hxn.3oG_at_netcom.com>
"Yih-feng Hwang (CS 688)" <yhwang> writes:
>I got a problem about how to pass a value (NAME of table or column) into
>a dynamic SQL (method 3).
>The following problem is a sample.
>============================================
> ....
>strcpy((char *) sqlstmt.arr,
> "SELECT distinct :col FROM :tab");
>sqlstmt.len = strlen((char *) sqlstmt.arr);
> ....
>EXEC SQL PREPARE S FROM :sqlstmt;
>EXEC SQL DECLARE C CURSOR FOR S;
>EXEC SQL OPEN C USING :col_namei, :tab_name;
This is one of the most frequently-asked questions about SQL. Basically, what you are trying to do is use "host" variables for column and table names, and you can't do that. This is a SQL restriction.
One way to do what you want is to just get the database object names at runtime, and build up a SQL statement using the str...() functions. Then do the dynamic SQL PREPARES, EXECUTES, etc. Here's a sketchy C example:
char temp[64];
char stmt[128];
...
strcpy(stmt, "SELECT DISTINCT ");
printf("Enter the column name: ");
gets(temp);
strcat(stmt, temp);
strcat(stmt, "FROM ");
printf("Enter table name: ");
gets(temp);
strcat(stmt, temp);
/* etc, etc. */
This is not elegant code, but it should convey the idea.
--Tim(tssmith_at_oracle.com) Received on Sat Mar 25 1995 - 20:53:47 CET