Connecting to two diffrent data bases using Pro* C code

Question

how can we connect to two different databases using Pro* C code. I have entries in tnsnames.ora file. By default it is connection to one of the Databases using EXEC SQL CONNECT statement which I am not sure how it is connecting. I want to connect to another DB which is having an entry in tnsnames.ora file. I am using OpenVMS OS.

Answer 1

Vijay Kumar

Below outlines what I have done in the pass to accomplish this.

Default connection

If you do not have a CONNECT statement in your code, Oracle will perform an auto connect when it encounters the first SQL statement. It uses credential information configure by the person who setup the database.

Multiple connections

To connect to multiple databases in Pro C, you must make an explicit connection to all databases reference by the code.

Outline below are the 3 steps I done in the pass to accomplish this:

• Declare a database alias name. This will be used to reference the connection.
• Explicitly connect to the database and assigned the alias to the connection. This is done by including an AT clause in the CONNECT statement.
• In following SQL statements, reference the connection by including an AT clause.

Example code:

/*
 * Declare the database aliases
 */ 
EXEC SQL DECLARE DB1 DATABASE;
EXEC SQL DECLARE DB2 DATABASE;


/*
 * Explicitly connect to the database.  Include an AT clause to name the connection
 */ 
EXEC SQL CONNECT :db1_uid IDENTIFIED BY :db1_pwd
         AT DB1;
EXEC SQL CONNECT :db2_uid IDENTIFIED BY :db2_pwd
         AT DB2;


/*
 * In the SQL statements, reference the connection by including the AT clause.
 */ 
EXEC SQL AT DB1 SELECT COL1 INTO :var1
         FROM TABLE_1
         WHERE COL2 = 'some_value';

EXEC SQL AT DB2 SELECT COLUMN1 INTO :var2
         FROM TABLE_2
         WHERE COLUMN2 = 'some_value';

EXEC SQL AT DB1 COMMIT WORK RELEASE;
EXEC SQL AT DB2 COMMIT WORK RELEASE;

Things to note:

• DB1 and DB2 are programmer define alias names.

• Passwords, represented by the variables db1_pwd and db2_pwd, must be variables and not hard coded values.

• The username value, stored in variables db1_uid and db1_uid2, was in the following format: username@DatabaseName

Note I believe there are other methods for accomplish this. This is the method I was successful in getting to work.

You can find more information on connecting to databases in the Oracle Pro *C/C++ Programmer’s Guide.