OpenDB overview

What is OpenDB?

OpenDB is a middleware that allows D3 to access remote database management system (DBMS) tables through ODBC. From D3, these tables are viewed as if they were D3 files, records as items, and fields as attributes.

Unlike the D3 ODBC Driver, which allows third party software to view D3 as a remote DBMS, OpenDB is a D3 Client that allows D3 to read, write, and select data as if they were D3 data.

How does it work?

OpenDB consists of two parts:

• OpenDB Server–a Microsoft Windows application that enables communication between the Microsoft ODBC Manager and the D3 Open File System Interface (OSFI) network layer through TCP/IP. The Microsoft ODBC Manager is responsible for communicating with the remote DBMS.

• OpenDB Super Q-pointer(s)–allows D3 to communicate with the OpenDB Server through the D3 OSFI network layer.

  

To access the remote DBMS, these items are required:

• An ODBC driver for the targeted remote DBMS, which is provided by the DBMS vendor

• A Data Source Name (DSN) for the target database

• A dm,hosts, entry on the D3 Server to identify the remote DBMS–defines the OSFI host driver used to establish communication with the ODBC Manager. The dm,hosts, entry contains information about the location of the ODBC Manager For example, the server name (the name of the OpenDB Server it is running on and the TCP/IP port it is listening to); the Data Source Name (DSN)–used to link to the remote DBMS; and information on logging on to the DSN (that is, the DSN name, user-ID, user password, and so on).

• A D3 Super Q-pointer–defines the column information to be retrieved (for example, table names, column names, primary keys, data types, filters (SQL WHERE clause), SQL joins, and so on).

• Once these are defined, the D3 Super Q-pointer can be used with any TCL verb and from BASIC programs. As far as the users and developers are concerned, access to the remote DBMS is totally transparent.

  For example:

  To get data from a Customer table from a SQL Server database, these need to be defined:

  • A dm,hosts, item that identifies the ODBC Manager where the DSN of the SQL Server database is configured.

  • For our example, we will call this RemoteSQLServer.

  • A D3 Super Q-pointer that uses the RemoteSQLServer from the dm,hosts, entry and indicates which table and which columns to retrieve data from.

  • For our example, we will call this Super Q-pointer SQLCustomer and define column ID, Name, and Phone.

From TCL

The command below selects all the customers with phone numbers starting with (949), and then orders them by name.

:sselect sqlcustomer with phone = "949]" by name

The command below lists customers from the previous selection and displays only the phone numbers.

:list sqlcustomer phone

The command below edits customer 123 from the SQLCustomer file. If customer 123 exists, the customer is updated. If the customer does not exist, the customer 123 is created.

:ed sqlcustomer 123

From BASIC

001 open "sqlcustomer" to fsqlcust else
002 print "error opening remote sql table sqlcustomer"
003 exit
004 end
005 execute "select sqlcustomer"
006 loop
007 readnext id else exit
008 read cust from fsqlcust,id then
009 print id, cust<1>, cust<2>
010 write cust on fsqlcust, id
011 end
012 repeat

Accessing a remote SQL table, as displayed in the example above, is identical to accessing any D3 file.