Phil codes in Tcl and asked me to figure out how to use ODBC to talk to PostgreSQL so that he'd have a cross platform system for development that would also let him talk to numerous other databases.
Here's what's required to talk to PostgreSQL running on a Linux box from a Tcl client on a Windows ME (and I assume most other Windows variants) or a Linux box.
Get the PostgreSQL source. I'm running the 7.1 beta from the CVS tree:
export CVSROOT=:pserver:anoncvs@postgresql.org:/home/projects/pgsql/cvsroot
cvs login
(password is "anoncvs").
When building it, add --enable-odbc to your configure, ie:
cvs checkout pgsql
cd pgsql
./configure --enable-odbc
make
su
make install
As root, add /usr/local/pgsql/lib to /etc/ld.so.conf and run ldconfig.
If you don't already have a postgres user, create one, make a data directory, and link /usr/local/pgsql/data to ~postgres/data.
su - postgresql
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Edit /usr/local/pgsql/data/pg_hba.conf to allow connections from the real world. Search for "Examples:". Below the line that read:
# TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP
I added a line that read:
host all 192.168.1.0 255.255.255.0 trust
Start postmaster with the "-i" option, ie:
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -o "-i" start
(This should be integrated into your init system so it runs on bootup.)
Set up the base template database (from which new ones will be derived) to be ODBC accessible.
/usr/local/pgsql/bin/psql -d template1 -f /usr/local/pgsql/share/odbc.sql
Create a user that mirrors your usual Un*x user (and what the heck, we're running on firewalled networks, give it full privileges):
/usr/local/pgsql/bin/createuser danlyke
Then su to that user and run:
/usr/local/pgsql/bin/createdb mytestdb
You can use:
/usr/local/pgsql/bin/psql mytestdb
to type SQL at the thing and get stuff back, like:
CREATE TABLE test (id SERIAL, stuff TEXT);
INSERT INTO test (stuff) VALUES ('abc');
INSERT INTO test (stuff) VALUES ('123');
SELECT * FROM test;
Go to http://www.solagem.fi/~rnurmi/tclodbc.html and download the tclodbc22.zip driver. Install it (unzip and double-click setup.tcl).
Get ftp://ftp.postgresql.org/pub/odbc/latest/postdrv.exe
Run it and let it install.
Go to "Start->Settings->Control Panel". Run "ODBC Data Sourc.." Click on the "Drivers" tab and confirm that you've got a PostgreSQL data source.
Click back to "User DSN" (or "System DSN", whatever scope you want this at) and click "Add...". Double click on "PostgreSQL". Give the data source a name (I called mine "whateverdrivername"), and enter the "Database" name you set up earlier ("mytestdb", the server IP (in my case it was "192.168.1.2"), leave the port at the default (5432), and fill in the "User Name" and "Password".
Pop up "tclsh" and:
%package require tclodbc
2.2
%database db whateverdrivername
db
%db "select * from test"
...
Get the tclodbc and oratcl distributions from:
http://sourceforge.net/projects/tclodbc
http://sourceforge.net/projects/oratcl
Untar both, and copy the "conf" subdirectory from oratcl over to tclodbc. cd into tclodbc and patch tclodbc.hxx.in as:
wynand:/home/danlyke/tcl/tclodbc# diff tclodbc.hxx.in.original tclodbc.hxx.in
124d123
< /* I am not sure what version this is...
127,129d125
< #include <isqlext.h>*/
< /* iODBC 2.12 */
< #include <isql.h>
131,132d126
< #include <odbc_funcs.h>
< #include <odbc_types.h>
wynand:/home/danlyke/tcl/tclodbc#
In other words, open tclodbc.hxx.in, search for "I am not sure what version this is" and change that block to read:
extern "C" {
#ifdef HAVE_IODBC // Using the free IODBC driver
#include <iodbc.h>
#include <isql.h>
#include <isqlext.h>
#else
#include <sql.h>
#include <odbcinst.h>
#include <sqlext.h>
#endif
}
Now we can build. Still in the tclodbc subdirectory:
autoconf ./configure --verbose --prefix=/usr/ --exec-prefix=/usr/bin/ \
--with-odbcinclude=/usr/local/pgsql/include/ \
--with-odbclibrary=/usr/local/pgsql/lib/libpsqlodbc.so
make su
make install
Create a ~/.odbc.ini file that looks something like:
[ODBC Data Sources]
whateverdrivername = PostgreSQL ODBC Driver
[whateverdrivername]
Driver=/usr/local/pgsql/lib/libpsqlodbc.so
Description=PostgreSQL ODBC Driver
Database=mytestdb
Username=danlyke
Password=danlyke
Servername=localhost
Port=5432
Then run:
tclsh
And tell tclsh:
% package require tclodbc
2.2
% load "/usr/lib/libtclodbc2.2.so"
% database db "whateverdrivername"
db
% db "select * from test"
The weirdness I can't account for right now is what's wrong with Tcl's package management that I have to do the explicit "load" of the .so on Linux.
Monday, February 19th, 2001 danlyke@flutterby.com