Accessing ODBC data sources with Core and View

Accessing ODBC data sources

with Core and View

Except for MySQL which is handled by a free protocol written by Dockimbel, the Core and View interpreters provide no other access to different DBMS's. If you want to develop applications using other databases, Rebol/Command and Rebol/IOS seem therefore to be the only solution but they don't know how to communicate with MySQL, Oracle and ODBC (for windows versions only). Therefore it appears difficult to use an Informix, Sybase or even a PostgresSQL database with Rebol. A free solution exists, however: the ODBC Socket Server.

Presenting the ODBC Socket Server

This product, licenced under the GPL, (http://odbcsock.sourceforge.net/) is a service for Windows NT4/2000. Its function consists of receiving SQL requests and supplying the results to the client. The exchanged data is entirely encoded in XML.

Data access with the help of ODBC Socket Server

If you make a Microsoft Windows machine available on your network, all you need to do is declare a collection of data sources on that. Your clients or your servers, (whichever system will be used) will then be able to send XML requests to the ODBC server. This type of architecture is particularly well adapted for use by a network language such as Rebol. Since communication is based on an XML protocol using TCP/IP, it is not very difficult to implement a dedicated protocol.

Some words on the installation of the server

The installation of ODBC Socket Server takes only a few moments and is perfectly explained in the manual (http://odbcsock.sourceforge.net/release/odbcmanual.pdf). The configuration of the product is done with the help of some registry keys (HKEY_Local_Machine\Software\ODBCSocketServer) and I recommend using the script Registry.reg present in the archive ODBCSocketServer.zip. The default values permit you to start quickly and to test the product but if you want to secure the installation, don't forget to modify the keys IP.Allow and IP.Deny afterwards to authorise or limit access to the data sources (accordingly for the IP addresses of the machines). In the domain of optimisation, it is good to put the value "1" in the key UseMSDTD and to set the number of threads in the key MaxThreadCount (depending on the number of clients likely to simultaneously access the data sources).

The communication protocol

The client's request and the server's response are encoded in XML. A request is defined by the <request> tag. This contains the connection information in <connectionstring> and the SQL request in <sql>. The following example defines a request to the data source "mabase" for the user "olivier" who uses the password "homer" :

<?xml version="1.0"?>

<connectionstring>DSN=mabase;UID=olivier;PWD=homer;</connectionstring>

</request>

When the server receives this request on port 9628/tcp, this opens a connection with the appropriate database and executes the request. The result of the processing is formatted in XML and returned to the client.

Equally, in case of failure, a response is generated in XML :

<?xml version="1.0"?>

<error>ODBC reports data source name not found</error>

</result>

Exchanges between the client and the server are encoded in XML.

Development of the OdbcSocketServer

We are left only with the task of defining a new client protocol for Rebol derived from the object root-protocol. The scheme is named OdbcSocketServer and works by default with port 9628/tcp. The property Open-Check is set to none since no negotiation is necessary with the server. The request template is indicated with the property xml-request-template in which the strings "#CONNEXION" and "#REQUETESQL" will be later replaced by the correct values.

make root-protocol [

scheme: 'OdbcSocketServer

port-id: 9628

port-flags: system/standard/port-flags/pass-thru

open-check: none

xml-request-template: system/words/copy {

<?xml version="1.0"?>

<connectionstring>#CONNEXION</connectionstring>

</request>

}

net-utils/net-install OdbcSocketServer self 9628

]

In order to "breathe life" into this protocol, we simply define two methods which are insert sending the request to the server) and copy (reading the response).

The insert method contructs a string containing the connection parameters collected from the information supplied in the URL. It's necessary for that to use the data present in the properties port/target, port/user and port/pass.

Extraction of the information contained in a URL with the help of the port object.

The SQL request is obtained with the help of the data parameter of the method. Once that the copy of the template has been modified, the request is transmitted to the server :

insert: function [ port data ] [ connection-string xml-request ] [

connection-string: system/words/copy ""

if not none? port/target [ append connection-string join "DSN=" [ port/target ";" ] ]

if not none? port/user [ append connection-string join "UID=" [ port/user ";" ] ]

if not none? port/pass [ append connection-string join "PWD=" [ port/pass ";" ] ]

xml-request: system/words/copy xml-request-template

replace xml-request "#CONNEXION" connection-string

replace xml-request "#REQUETESQL" data

system/words/insert port/sub-port xml-request

]

Receiving the data is entrusted to the copy method of which the only and unique function consists of resending a string of characters derived from the information received on the TCP port dedicated to the communication with the server :

copy: func [ port ] [

make string! system/words/copy port/sub-port

]

Once complete, the file OdbcSocketServer-scheme.r looks like the following :

REBOL [

subject: "protocole client vers Odbc Socket Server"

version: "1.0"

author: "Olivier Auverlot"

]

make root-protocol [

scheme: 'OdbcSocketServer

port-id: 9628

port-flags: system/standard/port-flags/pass-thru

open-check: none

xml-request-template: system/words/copy {

<?xml version="1.0"?>

<connectionstring>#CONNEXION</connectionstring>

</request>

}

insert: function [ port data ] [ connection-string xml-request ] [

connection-string: system/words/copy ""

if not none? port/target [ append connection-string join "DSN=" [ port/target ";" ] ]

if not none? port/user [ append connection-string join "UID=" [ port/user ";" ] ]

if not none? port/pass [ append connection-string join "PWD=" [ port/pass ";" ] ]

xml-request: system/words/copy xml-request-template

replace xml-request "#CONNEXION" connection-string

replace xml-request "#REQUETESQL" data

system/words/insert port/sub-port xml-request

]

copy: func [ port ] [

make string! system/words/copy port/sub-port

]

net-utils/net-install OdbcSocketServer self 9628

]

Using the new protocol

To install this protocol, you simply write do %OdbcSocketServer-scheme.r in your Rebol script. The protocol has then been added to the system object and is ready to use. Your Linux server (for example) is able then to connect to the data source mabase using an NT4/2000 server named serveur2000 :

db: open OdbcSocketServer://olivier:homer@serveur2000/mabase

insert db "select * from matable"

print mold copy db

close db

Since the result of the request is in XML, you are able to use the integrated XML parser of Rebol or better yet, the excellent parser of Gavin F.McKenzie (http://www3.sympatico.ca/gavin) in order to extract the sought-after data. I refer you equally to issue 106 (May 2003) of the Login magazine which contains an article on the manipulation of XML documents with Rebol.