Re: Pro*C Prepared statements using CURRVAL/NEXTVAL

"Cary Gerber" <carygerber_at_yahoo.com> wrote in message news:5dac3ab3.0109170753.6fe6b8f3_at_posting.google.com...
> 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.

I believe Pro*C still supports the AT :db_name syntax. Shouldn't that allow you to use static sql instead of dynamic sql?

Using dynamic sql all the time will also severely limit scalability.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Sep 17 2001 - 12:07:06 CDT