Re: Q:Pro*C host vars

Jeffrey Pickett (jdp_at_pittslug.sug.org) wrote:

: I have a question. I would like to concatenate an owner to the front of
: a table name, but I can't seem to find the answer. All I am trying to
: do is a simple embedded sql query like so:
 

: SELECT ITEM_ID into :item_id FROM OWNER.SOMETABLE
: WHERE ITEM_NBR = :number;
 

: My problem is my 'OWNER' is variable. I tried turning the table name
: into a host variable :table and concatenating the owner to the front,
: but to no avail. Is what I want to do possible? Any help would be
: greatly appreciated.

As I recall, Oracle does not permit you to bind to database objects (such as tables); you therefore couldn't use a variable in the place of a table name (editorial comment: real programmers despise petty restrictions :) ).

There is an alternative, and that is to have your C program assemble your SELECT statement at runtime (i.e. build a char array containing your statement, replacing OWNER.SOMETABLE with the appropriate value previously determined).

You should modify the SELECT statement to something like:

   SELECT ITEM_ID FROM OWNER.TABLE WHERE ITEM_NBR = :v1 (For what follows, ":v1" is simply a placeholder, it is not a variable that has to be declared anywhere. OWNER.TABLE would be substituted with the acual owner name and table name. This whole statement would be residing peacefully in a VARCHAR declared in your DECLARE Section, with the size portion of that structure properly initialized - for this example we'll call it "sqlstmnt"). Note - do not include a semicolon as part of your statement.

Once "sqlstmnt" has been properly set up, it must be prepared, as in:

   EXEC SQL PREPARE s1 FROM :sqlstmnt;

Declare a cursor for this statement:

   EXEC SQL DECLARE c1 CURSOR FOR s1;

Open the cursor:

   EXEC SQL OPEN c1 USING :number;
(this will substitute :number in the statement where you had the placeholder)

And finally, do the fetch:

   EXEC SQL FETCH c1 INTO :item_id;

Remember to close any cursors you open when you are through processing.

It is a lot more hassle, but I have used this procedure successfully to deal with the sort of problems you describe.

---Kevin Lukes

Kevin J. Lukes  Programmer/Analyst   |  klukes_at_novatel.cuc.ab.ca
NovAtel Communications Ltd.          |  (403)295-4573
Calgary, Alberta CANADA              |  "Opinions expressed are my own"