Re: Connect to Different Databases in Pro*C

nick_at_embassy.org wrote:
> I tried to use the following Pro*C statement to connect to different
> oracle database instance on the same Solaris machine.
>
> EXEC SQL CONNECT :username IDENTIFIED BY :password AT :databasename;
>
> The statement executes and returns succesfully, but all the subsequent
> EXEC SQL EXECUTE calls return with an error of not logged on.
>
> If I remove "AT: databasename" in the above connect statement, i.e. to use
> the default database specified by ORACLE_SID environment variable, then
> all the subsequent EXEC SQL EXECUTE calls get executed successfully. But
> this limits the application's ability to connect to only one single
> database.
>
> So, what's the correct way to connect to different database server in
> Pro*C?

What you want to do is:  

 EXEC SQL CONNECT :username IDENTIFIED BY :password USING :sidname;

The "AT :databasename" syntax is meant to allow several simultaneous connections to different Oracle instances. The Pro*C always has a default (unnamed) connection and all others must be named and declared with:

 EXEC SQL DECLARE dbname DATABASE;

Connection to a Oracle instance is done with:

 EXEC SQL CONNECT :username [IDENTIFIED BY :password] [USING :sidname] AT dbname

And each SQL statement using non-default database connection must tell which connection it wants to use:

 EXEC SQL AT dbname EXECUTE ...;

The "AT dbname" can also use a host variable: "AT :hostdbname".

 Frederic LACHASSE (ECP 86)
 Internet: lachass_at_worldnet.fr
 CompuServe: 100530,2005 Received on Mon Sep 29 1997 - 00:00:00 CDT