The SQL standard is very popular with a large and increasing number of conforming implementations. It is, or soon will be, the basis of definition for a majority of Federal databases and database applications involving structured data.
The basic structure of the relational model is a table, consisting of rows and columns. Data definition includes declaring the name of each table to be included in a database, the names and datatypes of all columns of each table, constraints on the values in and among columns, and the granting of table manipulation privileges to prospective users. Tables can be accessed by inserting new rows, deleting or updating existing rows, or selecting rows that satisfy a given search condition for output. Tables can be manipulated to produce new tables by Cartesian products, unions, intersections, joins on matching columns, or projections on given columns.
The purpose of the SQL language standard is to provide portability of database definitions and database application programs among conforming implementations. Use of the SQL language standard is appropriate in all cases where there is to be some interchange of database information between systems. The SQL definition language may be used to interchange database definitions and application specific views. The SQL data manipulation language provides the data operations that make it possible to interchange complete application programs.
RDA (Remote Database Access) is a communications protocol for remote database access that has been adopted as an ISO/IEC. This standard is in two parts:
Part 1 specifies the generic model, service, and protocol for arbitrary database connection and Part 2 specifies additional protocols for connecting databases conforming to the Database Language SQL.
RDA provides standard protocols for establishing a remote connection between a database client and a database server. The client is acting on behalf of an application program while the server is interfacing to a process that controls data transfers to and from a database. The goal is to promote the interconnection of database applications in a multivendor environment.
RDA is appropriate for remote access to a database in any context where lower layer transport protocols have already been established. RDA protocols have been shown to work properly in both OSI and Internet communications environments. The Internet RFC1006 is the guide used for executing RDA over a TCP/IP connection.
The RDA Service Interface consists of service elements for association control, for transfer of database operations and parameters from client to server, for transfer of resulting data from server to client, and for transaction management. Association control includes establishing an association between the client and server remote sites and managing connections to specific databases at the server site. Database operations are sent as character strings conforming to the SQL language. Resulting data and/or errors and exceptions are described and represented using the ISO ASN.1 standard. Transaction management includes capabilities for both one-phase and two-phase commit protocols.
RDA is appropriate in situations where it is not desirable, or possible, to run the same vendor's software at both ends of a communication line. Interconnection among database products from the same vendor will likely continue to use vendor specific communication and interchange forms.
Security work for RDA has been on-going. The RDA protocol maintains the security already inherent in a relational database with regard to access control. And the standard itself has left 'placeholders' for added security needs in the area of authentication. As with any standard for interoperability, the algorithms and methods must be agreed upon by all potential users.
Figure 3 depicts how SQL/RDA can be used in a multivendor network environment. The RDA protocol is used to communicate between three different SQL databases, manufactured by three different vendors, on three different hardware platforms. This configuration demonstrates the viability of the RDA standard with SQL databases in a heterogeneous environment.
The application program accesses each database server by means of a standard application program interface (API). The attached sample program in figure 4 provides an example of how this API may be used. The client application makes a connection to the server by specifying the server name (machine name in this case) and the data resource to be opened (the username on the database). The client can then initiate transactions on the database by sending an SQL string, and get back a table of results.
Work to standardize this interface will soon be completed, and is currently being prototyped at NIST. The Call Level Interface(CLI) (ISO/IEC 9075-3:1995) will provide a standard API to the RDA protocol. The CLI is a super-set of the familiar and very popular ODBC (Open Database Connectivity) defacto standard. By adding RDA to an ODBC/CLI API, the need for numerous drivers on both the client and server will disappear.
// assume the following are already defined
// hostname - char*, name of host where RDA server is running
// server - char*, name of the RDA server
// rname - char*, name of the resource
// password - char*, password to server
// status - int, return value
// dia_id - int, dialogue id
// rsc_id - int, resource id
// colc - int, column count
// colv - char**, pointer to array of column names
// rowc - int, row count
// rowv - char**, pointer to array of row values
// sqlcmd - char*, ASCII string of the SQL command to execute
// errorcode - int, error code
// errortext - char*, error message string
/* init a dialogue */
rda_setup();
status = rda_init(&dia_id, hostname, server, password, 0);
if(rda_error(status)) rda_signal( status, 0);
/* Open the data resource */
status = rda_open( did, &rsc_id, rname, password, RDA_C_UPDATE);
if(rda_error(status)) rda_signal( status, 0);
/* start the transaction */
status = rda_begin( dia_id);
if(rda_error( status)) rda_signal( status, 0);
/* perform the transaction */
status = rda_execSQL( dia_id, rsc_id, sqlcmd, &colc, &colv,
&rowc, &rowv, &errorcode, &errortext);
/* commit the transaction */
status = rda_commit(dia_id);
rda_signal(status, 0);