This topic provides a brief overview of 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.
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.
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.
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
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.