Remote Database Access

There are 2 programmatic interfaces. One approach is to use Dynamic SQL, of which the SQL text is passed from client to server. The other approach is to disguise the remote database access underneath the normal database interface. The database schema indicates that certain tables reside on a remote machine. The database is used by the programmer in a normal way, just as if they are local (except for performance and additional error messages to handle).

Remote database access generated large network overhead because of the sheer amount of data that passed back from the server to the client. This is not good for transaction processing. Most database supports stored procedure. This turns remote database access into a form of RPC with 2 differences:

(1) it is run-time and not a compile-time interface with no IDL equivalent
(2) the procedure is typically written in proprietary language or commonly in Java.

Stored procedure approach is faster than remote database access in general. On the other hand, for ad hoc queries, remote database access is more suitable. The reason was the returned data is too dynamic (number, type etc) for RPC-like call to handle efficiently.

There are many remote database access technology. Microsoft has ODBC (Open Database Connectivity), OLD DB (Object Linking and Embedding Database), ADO (Active Data Objects) amd ADO.NET. In Java environment, there is JDBC and JDO (Java Data Objects). Oracle has Oracle Generic Connectivity and Oracle Transparent Gateway. IBM has DRDA. Each vendor wanted the customer to use their technology and integration engine. Most database however supports ODBC and JDBC.