Programming with SQL Relay using ODBC

The SQL Relay distribution provides an ODBC driver, enabling applications which use ODBC to access SQL Relay.

Unix/Linux

On Unix or Linux platforms, using unixODBC or iODBC, you can create an instance for the SQL Relay driver by adding an entry like the following to your ODBC instances file (usually /etc/odbcinst.ini)

[SQLRelay]
Description=ODBC for SQL Relay
Driver=/usr/local/firstworks/lib/libsqlrodbc.so
FileUsage=0

Note that the Driver attribute must bet set to the full pathname of the libsqlrodbc.so driver. Note also that on Mac OS X you'll need to replace the .so suffix with .dylib

Once the instance is defined you can add a DSN for SQL Relay by adding an entry like the following to your ODBC INI file (usually /etc/odbc.ini)

[sqlrexample]
Description=Connection to SQL Relay
Driver=SQLRelay
Server=sqlrserver
Port=9000
Socket=/tmp/example.socket
User=exampleuser
Password=examplepass

Here the Driver attribute refers to the instance in odbcinst.ini.

Alternatively, you could create a combined instance/DSN by adding an entry like the following to your ODBC INI file (usually /etc/odbc.ini)

[sqlrexample]
Description=Connection to SQL Relay
Driver=/usr/local/firstworks/lib/libsqlrodbc.so
FileUsage=0
Server=sqlrserver
Port=9000
Socket=/tmp/example.socket
User=exampleuser
Password=examplepass

Windows

On Windows, just use the ODBC control panel to create a DSN.

Attributes

The following attributes can be configured:

The following attributes can be used to establish Kerberos or Active Directory encryption and authentication with the server:

See the SQL Relay Configuration Guide for more information about Kerberos and Active Directory configurations. In particular, User and Password are not typically used when using Kerberos/AD.

The following attributes can be used to establish TLS/SSL encryption and authentication with the server:

See the SQL Relay Configuration Guide for more information about TLS/SSL configurations.

Note that the supported Tlscert and Tlsca file formats may vary between platforms. A variety of file formats are generally supported on Linux/Unix platfoms (.pem, .pfx, etc.) but only the .pfx format is currently supported on Windows.

Sample Session

Now you can use the isql command line utility that comes with iODBC or unixODBC to access the database through SQL Relay as follows:

isql sqlrexample

Here is a sample session:

[dmuse@fedora ~]$ isql sqlrodbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> create table exampletable (col1 int, col2 varchar2(20))
SQLRowCount returns 0
SQL> insert into exampletable values (1,'hello')
SQLRowCount returns 1
SQL> insert into exampletable values (2,'goodbye')
SQLRowCount returns 1
SQL> select * from exampletable
+-----+--------+
| COL1| COL2   |
+-----+--------+
| 1   | hello  |
| 2   | goodbye|
+-----+--------+
SQLRowCount returns 0
2 rows fetched
SQL> update exampletable set col2='bye' where col1=2
SQLRowCount returns 1
SQL> select * from exampletable
+-----+------+
| COL1| COL2 |
+-----+------+
| 1   | hello|
| 2   | bye  |
+-----+------+
SQLRowCount returns 0
2 rows fetched
SQL> delete from exampletable
SQLRowCount returns 2
SQL> select * from exampletable
+-----+-----+
| COL1| COL2|
+-----+-----+
+-----+-----+
SQLRowCount returns 0
SQL> drop table exampletable
SQLRowCount returns 0
SQL> quit
[dmuse@fedora ~]$

If you get an error like ISQL ERROR: Could not SQLConnect when you run isql then it's possible that it can't find your ODBC INI file. Some versions of unixODBC, when compiled and installed with non-standard prefixes, still look for odbc.ini in /etc rather than under their prefix. For example, I discovered that on Mac OS X, unixODBC 2.2.12 installed under /sw should look for /sw/etc/odbc.ini but instead it looks for /etc/odbc.ini, even though odbcinst -j looks under /sw.

You can work around this by setting environment variables to override whatever default search path unixODBC uses. For example, to force it to look under /sw/etc, use:

export ODBCINST=/sw/etc/odbcinst.ini
export ODBCINI=/sw/etc/odbc.ini

Any program that uses ODBC can be configured to use SQL Relay via ODBC by setting its DSN to the SQL Relay DSN that you defined.

You can also use SQL Relay with any language that supports ODBC directly or has a database abstraction layer that supports ODBC. For example, lets say you have an existing PHP app that uses MDB2 to connect to Oracle and you want to use SQL Relay with it. There is no PHP MDB2 driver for SQL Relay, but since MDB2 suppots ODBC, you could use:

PHP -> MDB2 -> ODBC -> SQL Relay -> Oracle

Similarly, you could use SQL Relay via ODBC with PHP/PDO, PHP/ADODB, Java/JDBC (using the JDBC-ODBC bridge), with the ODBC Adapter for Ruby on Rails, and so on.

Developing applications which use ODBC is beyond the scope if this document but many examples and tutorials can be found on the web.