One or both of unixODBC and iODBC are available for most Unix-like platforms and SQL Relay supports either.
On modern Linux platforms, they can be installed via yum, aptitude, apt-get or similar. On BSD systems, pkg_add or pkgin may be used to install one or the other. Modern versions of Solaris and Mac OS come with unixODBC pre-installed. I have also been able to build and install many versions of each from source on many different platforms.
ODBC provides a common API for accessing different databases, but to access a database, you also need an ODBC driver for that database. ODBC drivers are generally available as shared object libraries from the database manufacturer. Most databases have ODBC drivers available for them, but not all drivers are available for Unix or Linux.
ODBC drivers are generally installed in the libdir of the ODBC package- generally /usr/lib or /usr/local/lib, but they can really be installed anywhere.Accessing a Database
To access a database via ODBC, you have to add entries to two separate files.odbcinst.ini
The file odbcinst.ini contains entries describing the database drivers. Specifically, they map a driver name (such as Oracle) to a user-friendly description of the driver and the driver files themselves.
For example, lets say you're using a database called MythDB and received an ODBC driver for Linux from the manufacturer. The following lines would need to be appended to odbcinst.ini to make the system aware of the driver.
[MythDB] Description = ODBC for MythDB Driver = /usr/lib/libmythdbodbc.so Setup = /usr/lib/libodbcmythdbS.so Driver64 = /usr/lib64/libmythdbodbc.so Setup64 = /usr/lib64/libodbcmythdbS.so FileUsage = 1
The [MythDB] line defines the name that will be used in other files when referring to this driver.
The Driver and Driver64 arguments are set to the full pathname of the 32-bit driver and 64-bit drivers respectively. These arguments are optional and each line may be excluded if you were not provided the corresponding driver. For example, if you were only provided a 32-bit driver, you should leave out the entire Driver64 line.
The Setup and Setup64 arguments are set to the full pathname of the 32-bit and 64-bit "setup" libraries. These libraries are used by the Driver Manager UI to describe parameters and are not essential. They may or may not have been provided by the manufacturer. The arguments are optional and each line may be excluded if you were not provided the corresponding file.
The FileUsage parameter is ambiguous. I've never been able to find a good description of it. It appears to always exist and always be set to 1.odbc.ini
The file odbc.ini defines specific database connections, often called Data Source Names or DSN's for short.
For example, lets say that you wanted to connect to a database on the machine dbhost on port 6600 using username testuser and password testpassword and then use database testdb once logged in. Your odbc.ini entry might look like:
[TestDB] Description = Connection to TestDB Driver = MythDB Host = dbhost Port = 6600 UserName = testuser Password = testpassword Database = testdb
The [TestDB] line defines the name of this DSN to be TestDB. Programs which use ODBC may use the name TestDB to refer to this set of connection parameters.
The Description parameter defines a user-friendly name for this DSN.
The Driver parameter refers back to a driver definied the odbcinst.ini file. Applications which use this DSN will load that driver.
The remaining parameters are specific to the driver, are given only as examples and might be different for other drivers. For example, another driver may not have the Database parameter at all, may refer to the UserName as User instead or may have additional parameters.
That said, the UserName and Password parameters are fairly well standardized and many tools allow them to be overridden at connect-time. As such, they are often left blank in the odbc.ini file. The line is included, but no value is placed after the equals sign.isql
The command line program isql can be used to test a DSN.
Note that Firebird also comes with a command line program, also called isql and it is important to make sure you are using the ODBC program rather than the Firebird program. Running the isql command with no arguments should give you some indication of which one it is. The isql program that comes with unixODBC has output like the following:
********************************************** * unixODBC - isql * ********************************************** * Syntax * * * * isql DSN [UID [PWD]] [options] * * * * Options * * * * -b batch.(no prompting etc) * * -dx delimit columns with x * * -x0xXX delimit columns with XX, where * * x is in hex, ie 0x09 is tab * * -w wrap results in an HTML table * * -c column names on first row. * * (only used when -d) * * -mn limit column display width to n * * -v verbose. * * -lx set locale to x * * -q wrap char fields in dquotes * * -3 Use ODBC 3 calls * * -n Use new line processing * * --version version * * * * Commands * * * * help - list tables * * help table - list columns in table * * help help - list all help options * * * * Examples * * * * isql WebDB MyID MyPWD -w < My.sql * * * * Each line in My.sql must contain * * exactly 1 SQL command except for the * * last line which must be blank (unless * * -n option specified). * * * * Please visit; * * * * http://www.unixodbc.org * * email@example.com * * firstname.lastname@example.org * **********************************************
To connect to the database defined by the TestDB DSN, run the following command:
isql TestDB testuser testpassword
to override the user and password.
You should now be presented with a session resembling the command line interfaces to other databases. You can run queries, view result sets, etc.Accessing a Database With SQL Relay
Accessing an ODBC database from SQL Relay requires an instance entry in your sqlrelay.conf file for the instance that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called odbctest. This instance connects to the TestDB DSN.
<?xml version="1.0"?> <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> <instances> <instance id="odbctest" port="9000" socket="/tmp/odbctest.socket" dbase="odbc" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5"> <users> <user user="odbctest" password="odbctest"/> </users> <connections> <connection connectionid="odbctest" string="dsn=TestDB" metric="1"/> </connections> </instance> </instances>
Now you can start up this instance with the following command.
sqlr-start -id odbctest
To connect to the instance and run queries, use the following command.
sqlrsh -id odbctest
The following command shuts down the SQL Relay instance.
sqlr-stop odbctestODBC Quirks
Many databases support named bind variables. For example, in Oracle, you can have a query like:
select * from mytable where col1=:value1 and col2=:value2
In this query, :value1 and :value2 are bind variables. Since Oracle supports binding by both name and position, SQL Relay also allows you to bind values to them by name or by position. For example, either of the following bits of code should work.
cur->prepareQuery("select * from mytable where col1=:value1 and col2=:value2"); cur->inputBind("value1","hello"); cur->inputBind("value2","goodbye"); cur->executeQuery();
cur->prepareQuery("select * from mytable where col1=:value1 and col2=:value2"); cur->inputBind("1","hello"); cur->inputBind("2","goodbye"); cur->executeQuery();
When using SQL Relay directly against Oracle, both bits of code work as expected. However, ODBC does not appear to support binding by name, even if the underlying database does, or if ODBC does support it, I can't figure out how. As such, if you use ODBC to connect SQL Relay to a database, then your client programs must bind by position, not by name, even if the underlying database supports binding by name.