help in dynamic query in Pro*C

[nitin garg]

Hello

Can i run the following query in Pro*C:-

EXEC SQL SELECT * FROM :tablename WHERE :attribute_name=:value;

i.e. this statement is embedded in C program and i want to supply table name (from which to select) and also the attribute name at run time only...is there any way to do this and is it first of all possible????????because till now what i have learned is that i can pass run-time values only into select list items and placeholders for input host variables... i have tried to implement it through cursors and descriptors but i have not been successful........please suggest some idea!!!

thanx in advance
nitin

[hrishy]

Hi

EXEC SQL SELECT * FROM :tablename WHERE :attribute_name=:value;

You cannot do that in any language be it pl/sql ,pro *C VB etc.

The table name and the atribute name has to be known before hand

what you need to do is first prepare the sql statment in your case
SELECT * FROM :tablename WHERE :attribute_name=:value;
as a string

something like

char *sql = "select * from "    ;
char *tabname=" tablename ";  /* can be a param that is passed */
char *predicate=" where  "     ;
char *column_name ="attribute_name";

/*now you need to concat the strings */
strcat(sql,tabname);
strcat(sql,predicate);
strcat(sql,column_name);

now having constructed the string you need to prepare it and execute it
EXEC SQL EXECUTE IMMEDIATE :sql using :value

regards
Hrishy

[nitin garg]

Hello Hrishy

thanks for replying...so does it mean that i cannot make a small sort of search engine in Pro*C ......basically what i wanted was that the user will enter just the keyword to search for in the database and i will display him the appropriate tables in which that keyword is present....so for this i have to generate query myself in the code ......

and one more thing...can i store the name of a table in a variable and make
a query like this in pro*c:-
char * variable=tablename;
exec sql select * from variable where name='xyz';
i.e. 'variable' contains the name of the table to select from....

[davey23uk]

there is a procedure on askTom that will do that already. Don't need to write your own code to do it

And you cant bind column names, or table names it wont let you

[hrishy]

Hello Hrishy

and one more thing...can i store the name of a table in a variable and make
a query like this in pro*c:-
char * variable=tablename;
exec sql select * from variable where name='xyz';
i.e. 'variable' contains the name of the table to select from....

Yes you should store it in a variable name and then use strcat as i have shown above to construct the query and execute dynamically.Look for my code above

regards
Hrishy