Re: Host Variables in PRO*C

In article ujl_at_msuinfo.cl.msu.edu, herouxch_at_caspian.uucp (Christopher R Heroux) writes:
> Is there a way of using host variables for table names and for variables in
> select statements? Something like:
>
> EXEC SQL SELECT :column
> INTO :value
> FROM :table
> WHERE YEAR = :year;
>
> I'm working with a large number of tables and variable columns and
> my code is getting mighty long.
>
> Thanks,

If you know in advance what column(s) you will be fetching, you can do something like this:

    strcpy(buffer,"SELECT MIN(STATION), MAX(STATION) FROM FLD_SAMP WHERE . . .");

    EXEC SQL PREPARE S0 FROM :buffer;
    EXEC SQL DECLARE C0 CURSOR FOR S0;
    EXEC SQL OPEN C0;     EXEC SQL FETCH C0 INTO :minstation, :maxstation;

If you don't know in advance the name of the column(s), you will have to do dynamic SQL. There are examples of this type of program with each Pro*C distribution, and I would imagine they come with the other pre-compilers as well. While this kind of code is not as simple to write, you will probably find that you use it over and over again for all types of applications.

Beverly Zygmunt
Oak Ridge National Lab

>
> Chris Heroux
> --
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> _/ Entomology Spatial Analysis Laboratory _/
> _/ E-mail: herouxch_at_cps.msu.edu _/
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Received on Wed Jan 05 1994 - 14:24:13 CET