SQLNet / ProC Problem

I'm having a problem connecting to multiple databases using ProC.

First, some background. I have six Oracle instances running in six locations throughout the country. Each database layout within each instance is identical. The hardware/OS/RDBMS versions are identical, and are: Pyramid / DC/OSx / Oracle 7.0.16.

The network is TCP/IP - based. I am running SQLNet v2 in all sites, and connectivity has been verified between my local machine and the five remote instances, by way of SQLPlus and SQLForms3.0 and 4.0.

I would like to write a C program which will extract data from each site, return it to my local machine, and format it in a report. I have no problem writing the C, but I would prefer to write the "select" code once, and loop through the code for each instance from which I want to select. So far, I've had no luck. I follow the syntax

     EXEC SQL CONNECT :username IDENTIFIED BY :password
        AT db_name USING :db_string

but the db_name apparently cannot be a variable. And it certainly seems like the cursors I open/fetch/close with each loop must be uniquely identified.

When I attempt to execute the program, I successfully connect to and retrieve data from the first site, but as I change the value for :db_string, the data in the cursors does not change (which I would expect). However, even explicit select statements within the body of the code continue to return data from the first database. I verified through SQLNet logs that no site other than the first was being hit.

Does anyone know of a way to perform this task, without sequentially opening/fetching/closing four different cursors for each of the six sites? Any help/info would be greatly appreciated!

Patrick DiMuzio
dimuzio_at_iglou.com Received on Mon Feb 03 1997 - 00:00:00 CET