Re: Pro*C Prepared statements using CURRVAL/NEXTVAL

In article <5dac3ab3.0109170753.6fe6b8f3_at_posting.google.com>, carygerber_at_yahoo.com says...
>
>Why do I get this behavior? We design all our apps to prepare all
>statements up front, because our database name can be switched at a
>moment's notice.
>
> /* Insert new trip plan */
> sprintf(stmt,
> "select %s.xyz_seq.nextval from dual ",
> database
> );
> EXEC SQL prepare get_serial from :stmt;
> if (!SQLOK()) exit(1);
>
> exec sql execute get_serial into :curr_tp->db->xyz_seq;
> if (!SQLOK()) exit(1);
>
>This always yields 0 every time thru a loop...
>
> exec sql select mydb.xyz_seq.nextval into :curr_tp->db->xyz_seq
>from dual;
> if (!SQLOK()) exit(1);
>
>This yields the right answer (next sequence) each time... but we have
>to hardcode our database/table name.

Well, you might consider using an ORACLE Schema in the same fashion you used an Informix database.

Then, you can simply issue:

   alter session set current_schema = mydb1;

and subsequent:

   select * from t;

will really be

   select * form MYDB1.t;

automatically. No muss, no fuss, no worries. You need not do what you are doing at all.... You can use static sql....

As for the pro*c as it exists.. The execute into is used with procedural code. We can use it with this but it would look like this:

static void process()
{
int x;
char stmt[255];

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     sprintf( stmt, "begin select %s.xyz_seq.nextval into :x from dual; end;",

                   "scott" );

    exec sql prepare get_serial from :stmt;

    exec sql execute get_serial using :x into :x;     printf( "I Got %d\n", x );
}

that works. What is more "proper" I suppose is this:

static void process()
{
int x;
char stmt[255];

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     sprintf( stmt, "select %s.xyz_seq.nextval from dual", "scott" );

    exec sql prepare get_serial from :stmt;     exec sql declare get_serial_c cursor for get_serial;

    exec sql open get_serial_c;
    exec sql fetch get_serial_c into :x;

    printf( "I Got %d\n", x );

(you never need to close the cursor -- just open it again later. you never want to close the cursor again if you are going to use it later)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp