www.sixfingeredman.net
..................................................
::. .  .   .     .       .          .

HOWTO--schemeql+postgresql on debian

I'll describe briefly the headaches and undocmented twists involved in getting PostgreSQL to work with both UnixODBC and iODBC on Debian together with SchemeQL.

I installed unixodbc, unixodbc-bin, libiodbc2, iodbc, odbc-postgresql, and libmyodbc. Following instructions from odbc-postgresql and libmyodbc I set up /etc/odbcinst.ini like so:

 [ODBC Drivers]
 PostgreSQL = Installed
 MySQL = Installed

 [PostgreSQL]
 Description     = PostgreSQL ODBC driver for Linux and Windows
 Driver          = /usr/lib/postgresql/lib/psqlodbc.so
 Setup           = /usr/lib/odbc/libodbcpsqlS.so
 Debug           = 0
 CommLog         = 1
 FileUsage       = 1

 [MySQL]
 Description     = MySQL driver
 Driver          = /usr/lib/odbc/libmyodbc.so
 Setup           = /usr/lib/odbc/libodbcmyS.so
 CPTimeout       = 
 CPReuse         = 
 FileUsage       = 1

The "ODBC Drivers" section is used by the iodbcadm-gtk interface to list drivers; it only lists drivers installed here. The first iodbc weirdness.

Now I set up a DSN (basically a unique name for a data source) in /etc/odbc.ini:

 [ODBC Data Sources]
 csr_office = PostgreSQL

 [csr_office]
 Description         = CSR Office Database
 Driver              = /usr/lib/postgresql/lib/psqlodbc.so
 Database            = csr_office
 Servername          = localhost
 Port                = 5432
 Protocol            = 6.4

"ODBC Data Sources" is more iodbc GUI pickiness. You can specify a UserName and Password or not. If you do, then they will automatically be used to connect. If you don't, they must be supplied when connecting (I haven't tested this thoroughly).

Now, UnixODBC will set up the Driver to point to the original Driver definition in odbcinst.ini by name (i.e. "PostgreSQL"). iodbc can't handle this, you must specify the library path. How ugly.

A final detail about schemeql -- where it uses the term "DBMS" it means "DSN". "name" = username. So we connect to the above with:

 (connect-to-database "csr_office" "csr" "******")