Consultor Eletrônico

Status: Verified

GOAL:

SQL-92: How to create and query an SQL-92 view against Multiple databases using the OpenEdge Architect?

FACT(s) (Environment):

Windows
OpenEdge 10.1B
OpenEdge 10.1C
OpenEdge 10.2x

FIX:

In OpenEdge Release 10.1B, ODBC and JDBC clients can connect to multiple databases that reside on the same host. Although the following steps used the OpenEdge Architect and JDBC connections, the same SQL-92 script may be used with other ODBC and JDBC connections and clients:
1. Start a proenv session:
Start > Programs > OpenEdge > Proenv
2. Create three copies of the sports2000 database:
prodb firstdb sports2000
prodb seconddb sports2000
prodb thirddb sports2000
3. Serve all three databases:
proserve firstdb -H localhost -N tcp -S 12345
proserve seconddb -H localhost -N tcp -S 23456
proserve thirddb -H localhost -N tcp -S 34567
4. Start an OpenEdge Architect session:
Start > Programs > OpenEdge > OpenEdge Architect Clean
5. In The DB Navigator Perspective Connections View, Create and test the three Connection Profiles.
6. In The DB Navigator Perspective Connections View, open connection for the firstdb.
7. File > New > Other > DB Navigator > SQL Editor > MultiDBViewExample.sql
8. Paste and run the following SQL-92 script the SQL Editor:
CONNECT 'C:\OpenEdge\WRK101B\seconddb' AS CATALOG second;
CONNECT 'C:\OpenEdge\WRK101B\thirddb' AS CATALOG third;
CREATE VIEW ThreeDBView (FieldFromFirstDB, FieldFromSecondDB, FieldFromThirdDB) AS
(
SELECT
firstdb.Pub.Customer.CustNum,
second.Pub.Order.OrderNum,
third.Pub.Orderline.itemnum
FROM
firstdb.Pub.Customer,
second.pub.order,
third.pub.orderline
WHERE
firstdb.Pub.Customer.CustNum = second.Pub.Order.CustNum AND
second.Pub.Order.OrderNum = third.Pub.Orderline.OrderNum
);
9. File > New > Other > DB Navigator > SQL Editor > ExecuteQueryAgainstView.sql
10. Paste and run the following SQL-92 script the SQL Editor:
SELECT * from ThreeDBView;