Problem:
Is it possible to access multiple databases within a single OpenESQL application?
Resolution:
Yes.
This is easily accomplished using named connections and the SET CONNECTION statement. At the beginning of your program you must connect to each database you wish to access.
Each CONNECT statement specifies the AS clause which associates a user-defined name to the database connection. When access to a specific database is required the SET CONNECTION statement is issued specifying the name associated with the desired database connection.
All subsequent SQL will access this database connection until another SET CONNECTION statement is issued.
Below is a simple program that demonstrates accessing an SQL Server database and a DB2 database. The program opens a cursor on the DB2 database, fetches a row and, based on the value for discount-stor-id, retrieves a row from the DB2 database.
Data Division.
01 dsqlcode pic s9(4) comp-5.
EXEC SQL INCLUDE DB2DISC END-EXEC
EXEC SQL INCLUDE discounts END-EXEC
EXEC SQL INCLUDE SQLCA END-EXEC
Procedure Division.
******************************************************************
* USE Named Connections to Connect to the Databases *
* Connection Name - DB1 For DB2 Connection *
* Connection Name - MS1 For SQL Server Connection *
******************************************************************
EXEC SQL
CONNECT TO 'UDBSAMPLE' AS DB1 USER 'dbuser.dbpass'
END-EXEC
EXEC SQL
CONNECT TO 'INTMSSQL' AS MS1 USER 'msuser.mspass'
END-EXEC
******************************************************************
* SET Named Connection To SQL Server *
******************************************************************
EXEC SQL SET CONNECTION MS1 END-EXEC
******************************************************************
* Subsequent SQL now access SQL Server *
******************************************************************
EXEC SQL
DECLARE CSR1 CURSOR FOR SELECT
A.stor_id
,A.discount
FROM dbo.discounts A
END-EXEC
EXEC SQL OPEN CSR1 END-EXEC
move sqlcode to dsqlcode
PERFORM UNTIL DSQLCODE < 0 OR DSQLCODE = 100
EXEC SQL
FETCH CSR1 INTO
:discounts-stor-id:discounts-stor-id-NULL
,:discounts-discount
END-EXEC
move sqlcode to dsqlcode
if dsqlcode =0
Perform getdata2
end-if
END-PERFORM
EXEC SQL CLOSE CSR1 END-EXEC
EXEC SQL
DISCONNECT ALL
END-EXEC
STOP RUN.
getdata2.
******************************************************************
* SET Named Connection To DB2 *
******************************************************************
EXEC SQL SET CONNECTION DB1 END-EXEC
******************************************************************
* Subsequent SQL now access DB2 *
******************************************************************
EXEC SQL
SELECT
A.ID
,A.NAME
INTO
:DB2DISC-ID:DB2DISC-ID-NULL
,:DB2DISC-NAME:DB2DISC-NAME-NULL
FROM BAS.DB2DISC A
WHERE ID=:discounts-stor-id
END-EXEC
if sqlcode = 0
display db2disc-name
end-if
******************************************************************
* SET Named Connection Back To SQL Server for Next Fetch *
******************************************************************
EXEC SQL SET CONNECTION MS1 END-EXEC.
Summary
N/A
Notes
N/A
Keywords/Phrases
ODBC Interface, SET CONNECTION, SQL
