I have been messing around with using Paul Meurer's SQL/ODBC module
with PostgreSQL and unixODBC. I have it all working on FreeBSD.
In the hopes that someone will find this helpful, I have thrown
together the following notes.
Get the SQL/ODBC software from the cl-http distribution, or via ftp
from ftp://amirani.hit.uib.no/.
PostgreSQL has a web site at http://www.postgresql.org/. I used
the FreeBSD ports collection to install it. The following commands
do the complete job of fetching, compiling, and installing it:
cd /usr/ports/databases/postgresql; make install
unixODBC has a web site at http://www.unixodbc.org/. I used
the FreeBSD ports collection to install it. The following commands
do the job:
cd /usr/ports/databases/unixODBC; make install
After starting up postgres, as the postgres user (pgsql in my case),
create a user to use from lisp:
$ createuser lispuser
Enter user's postgres ID -> 3000
Is user "lispuser" allowed to create databases (y/n) n
Is user "lispuser" a superuser? (y/n) n
createuser: lispuser was successfully added
Shall I create a database for "lispuser" (y/n) y
Still as user pgsql, set a password for lispuser:
$ psql lispuser
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i386-unknown-freebsd3.3, compiled by cc ]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: lispuser
lispuser=> alter user lispuser with password "xyzpdq";
ALTER USER
lispuser=> ^D
Edit ~pgsql/data/pg_hba.conf, and add a line like the following.
host all 192.168.0.3 255.255.255.255 password
Replace 192.168.0.3 with your machine's IP address. This will permit
your machine to connect to postgres via tcp/ip. (Note: if you install
postgresql by hand, be sure that you start the postmaster with the -i
switch.)
Check the permissions on the files in ~pgsql/data, and make sure
that they're appropriate. In particular, see that pg_pwd is mode 600.
Now, as yourself (not the postgres user), verify that you can connect
to the database via tcp/ip:
% psql -h 192.168.0.3 -u
Username: lispuser
Password: <type in the password>
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i386-unknown-freebsd3.3, compiled by cc ]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: lispuser
lispuser=>
Okay, that works.
Now, set up unixODBC. There are some graphical tools included with
the package, but I've never used them. The files you need to worry
about are odbc.ini and odbcinst.ini. On my system, they're installed
in /usr/local/etc. They should look like the following examples.
This is the odbc.ini file:
Description = Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = /tmp/sql.log
Database = lispuser
Servername = 192.168.0.3
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
This is the odbcinst.ini file:
[PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/local/lib/libodbcpsql.so
FileUsage = 1
You'll need to make the obvious changes.
Finally, cd to where you unpacked SQL/ODBC. Edit the file
odbc/odbc-ff-interface.lisp and where it says "adapt the library
location to your needs", add the line:
#+(and :allegro :unix) (setf *foreign-module* "/usr/local/lib/libodbc.so")
Change /usr/local/lib/libodbc.so to the correct pathname for where
you installed the unixODBC stuff, if needed.
At last, start up lisp, and follow the transcript below:
USER(1): (setf (logical-pathname-translations "sql")
'(("**;*.*" "/var/users/rme/lisp-db/sql/*")))
(("**;*.*" "/var/users/rme/lisp-db/sql/*"))
USER(2): (require :sql "sql:sql;sql-system")
; many messages elided
T
USER(3): (use-package :sql)
T
USER(4): (initialize-database-type :odbc)
; many messages elided. foreign code is loaded here.
:ODBC
USER(5): (sql:connect "PostgreSQL" :user-id "lispuser" :password "xyzpdq")
; Autoloading for FOREIGN-FUNCTIONS:STRING-TO-CHAR*:
; Fast loading from bundle code/ffcompat.fasl.
#<ODBC-DATABASE "PostgreSQL" @ #x103ba832>
USER(6):
You can now follow the examples in the file sample-session.lisp that
is included with SQL/ODBC, except table names with spaces in them
don't work (i.e., the [foo bar] examples don't work---use [foobar]
instead).
Just as a quick example:
USER(6): (enable-sql-reader-syntax)
#<readtable @ #x103bb3fa>
USER(7): (create-table [junk]
'((id integer :primary-key)
(text (varchar 32))))
0
USER(8): (with-transaction
(insert-records :into [junk] :values '(42 "is the answer")))
0
USER(9): (select [*] :from [junk])
((42 "is the answer"))
#<ODBC::ODBC-QUERY @ #x1042db32>
USER(10):
That's about it.
-matt
"R. Matthew Emerson" <···@nightfly.apk.net> wrote in message
···················@nightfly.apk.net...
> I have been messing around with using Paul Meurer's SQL/ODBC module
> with PostgreSQL and unixODBC. I have it all working on FreeBSD.
>
> In the hopes that someone will find this helpful, I have thrown
> together the following notes.
>...
Very useful!
I plan to switch from NT to FreeBSD with a similar configuration, so your
post can save me some hours.
Marc Battyani