help in dynamic query in Pro*C
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: help in dynamic query in Pro*C

Hybrid View

  1. #1
    Join Date
    Sep 2005
    Posts
    12

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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 12:29 PM.

  3. #3
    Join Date
    Sep 2005
    Posts
    12
    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....

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Quote 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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width