The SQL Relay distribution provides an ODBC driver, enabling applications which use ODBC to access SQL Relay.
On Windows, just use the ODBC control panel to create a DSN.
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=1
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)
[sqlrtest] Description=Connection to SQL Relay Driver=SQLRelay Server=sqlrserver Port=9000 Socket=/tmp/test.socket User=testuser Password=testpass
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)
[sqlrtest] Description=Connection to SQL Relay Driver=/usr/local/firstworks/lib/libsqlrodbc.so FileUsage=1 Server=sqlrserver Port=9000 Socket=/tmp/test.socket User=testuser Password=testpass
In addition to the attributes described above, 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.
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 sqlrtest
Here is a sample session:
[dmuse@fedora ~]$ isql sqlrodbc
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> create table test (col1 int, col2 varchar2(20))
SQLRowCount returns 0
SQL> insert into test values (1,'hello')
SQLRowCount returns 1
SQL> insert into test values (2,'goodbye')
SQLRowCount returns 1
SQL> select * from test
+-----+--------+
| COL1| COL2 |
+-----+--------+
| 1 | hello |
| 2 | goodbye|
+-----+--------+
SQLRowCount returns 0
2 rows fetched
SQL> update test set col2='bye' where col1=2
SQLRowCount returns 1
SQL> select * from test
+-----+------+
| COL1| COL2 |
+-----+------+
| 1 | hello|
| 2 | bye |
+-----+------+
SQLRowCount returns 0
2 rows fetched
SQL> delete from test
SQLRowCount returns 2
SQL> select * from test
+-----+-----+
| COL1| COL2|
+-----+-----+
+-----+-----+
SQLRowCount returns 0
SQL> drop table test
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.