-
help in dynamic query in Pro*C
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
-
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
Code:
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
Last edited by hrishy; 10-09-2005 at 11:29 AM.
-
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....
-
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
-
Originally Posted by nitin garg
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|