| Previous | Table of Contents | Next |
Database connectivity is much more than establishing communications with another database—it is the glue that holds the entire data federation together. Connectivity is achieved with many mechanisms, including application programming interfaces (APIs), remote procedure calls (RPCs), and a variety of vendor solutions. Each mechanism imposes strict rules for establishing database connections, and this chapter will review the most popular ways of making connectivity a reality. We’ll be covering the following areas:
A great deal of confusion exists about the functions of APIs and how they communicate with connectivity tools and databases. In fact, some client/server architectures impose so many layers of interfaces that it is often very difficult to track the flow of information as it passes though all of the layers.
Ignoring the physical details, let’s look at the logical methods for establishing connectivity. The most common type of logical connectivity is between remote databases of the same type. Oracle database software provides this type of mechanism with its SQL*Net software, allowing Oracle databases to connect with each other in a seamless fashion. Connectivity is established in Oracle by creating database links to the remote databases. Once defined by the DBA, these remote databases can participate in queries and updates from within any Oracle application. For example, a database in London and Paris can be defined to the Denver system with the following SQL extension:
CREATE PUBLIC DATABASE LINK london
CONNECT TO london_unix;
CREATE PUBLIC DATABASE LINK paris
CONNECT TO paris_vms;
We can now include any tables from these remote sites by qualifying their remote site name in the SQL query. This example joins three tables: a local order table in Denver, a customer table in Paris, and an orderline table in London.
SELECT customer.customer_name, order.order_date, orderline.quantity_ordered
FROM customer@london, order, orderline@paris
WHERE
customer.cust_number = order.customer_number
AND
order.order_number = orderline.order_number;
But what about remote databases that reside in other relational systems such as Sybase or FoxPro? And what about legacy data from a hierarchical database like IMS or a network database such as CA-IDMS? Here we enter a more complicated scenario—fortunately, we can choose from a variety of tools to accomplish this type of cross-architecture connectivity. The most popular connectivity gateway is Microsoft’s Open Database Connectivity (ODBC) product. However, many users have successfully implemented cross-architecture systems using custom-written RPCs and APIs.
An API is an interface that is generally embedded into an application program to interface with an external database. Database APIs come in two flavors: embedded and call-level. Embedded APIs are placed within an application program to interface with the database management system. Listing 8.1 shows a sample COBOL program that has embedded SQL commands.
Listing 8.1 A COBOL program.
WORKING-STORAGE SECTION.
01 CUST-RECORD.
05 CUSTOMER_NAME PIC X(80).
05 CUSTOMER_ADDRESS PIC X(100).
05 CUSTOMER_PHONE PIC 9(7).
EXEC-SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
OPEN INPUT INPUT-FILE.
READ INPUT-FILE AT END MOVE 'Y' TO EOF-SWITCH.
EXEC-SQL
CONNECT TO :REMOTE_SITE;
END-EXEC.
EXEC-SQL
SELECT * FROM CUSTOMER
WHERE
DB_CUST_NAME = INPUT_CUSTOMER_NAME
END-EXEC.
IF SQLCODE <> 0 THEN PERFORM NOT-FOUND-ROUTINE.
NOT-FOUND-ROUTINE.
DISPLAY "ERROR IN READING DATABASE"
CLOSE INPUT-FILE.
END RUN.
Here we see SQL that has been embedded in a COBOL program for access to a relational database. Unlike a regular COBOL program, special sections are embedded into the code that are foreign to the COBOL compiler. In this sample, the SQL commands are started with EXEC-SQL and ended with END-EXEC. An SQL precompiler is invoked to preprocess these statements, commenting them out and replacing them with native calls statements that the COBOL compiler will recognize. In Listing 8.2 we see that the “native” SQL has been replaced with calls to a routine called RDBINTC. The RDBINTC routine will place the SQL calls on behalf of the program.
Listing 8.2 The RDBINTC routine.
WORKING-STORAGE SECTION.
01 CUST-RECORD.
05 CUSTOMER_NAME PIC X(80).
05 CUSTOMER_ADDRESS PIC X(100).
05 CUSTOMER_PHONE PIC 9(7).
* EXEC-SQL INCLUDE SQLCA END-EXEC.
01 SQLCA.
05 SQL-FIELD1 PIC 99.
05 SQL-FIELD2 PIC X(20).
PROCEDURE DIVISION.
OPEN INPUT INPUT-FILE.
READ INPUT-FILE AT END MOVE 'Y' TO EOF-SWITCH.
* EXEC-SQL
* CONNECT TO :REMOTE_SITE;
* END-EXEC
CALL RDBINTC USING (SQLCA,45,:REMOTE_SITE);
* EXEC-SQL
* SELECT * FROM CUSTOMER
* WHERE
* DB_CUST_NAME = INPUT_CUSTOMER_NAME
* END-EXEC
CALL RDBINTC USING (SQLCA,23,"CUSTOMER", DB-CUST-NAME,
INPUT-CUSTOMER-NAME);
IF SQLCODE <> 0 THEN PERFORM NOT-FOUND-ROUTINE.
NOT-FOUND-ROUTINE.
DISPLAY "ERROR IN READING DATABASE"
CLOSE INPUT-FILE.
END RUN.
At execution time, the COBOL program will interface with the database by making native calls to the database interface, called RDBINTC in this example. The interface will manage all of the I/O against the database on behalf of the COBOL program, and will pass the result set (or a cursor) back to the application program using the SQL Communications Area (SQLCA).
Notice how the COBOL program checks the value of the SQLCODE field. When the database is accessed from a remote program, the calling program must explicitly check the value of the SQLCODE to ensure successful execution of the intended statement.
| Previous | Table of Contents | Next |