Re: swi-prologe for SQL

Hi William,

On Jan 19, 2014, at 3:53 PM, William Sweet <[email protected]> wrote:

> I am a swi-prolog newbie. I am evaluating swi-prolog for passing
> prolog-like commands to a sql database. I see that this is possible through
> ODBC. Performance is not a concern, more mportant is stability,
> reliability. Can anyone provide any feedback on how well this feature
> works? .What open source database does the swi-prolog ODBC driver work well
> with?


I have used it, albeit not extensively, with PostgreSQL to great effect. By coincidence, I had to set this up today on my Mac. I'm using OS X Mavericks with MacPorts. In the off chance this is your configuration, this is what you need to do to get started.

1. Install PostgreSQL:

  $ sudo port install postgresql93 postgresql93-server

  When the process is complete, it will give you some additional instructions involving running initdb which you must follow to get a working database. You may also want to run 'sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql93-server.plist' to get it to start up automatically. 

  Test that this is working by running 'psql', which should report that your user doesn't yet exist. You may want to create a user for yourself and a scratch database for testing like so:

  $ createuser `whoami`
  $ createdb `whoami`

2. Install UnixODBC and the Postgres driver:

  $ sudo port install unixODBC psqlODBC

  This won't take all that long.

3. Rebuild/install SWI.

  This step is necessary to detect and install the ODBC drivers.

  $ sudo port install swi-prolog-devel

4. Set up ODBC.

  Make a file /opt/local/etc/odbcinst.ini with the following content:

  [PostgreSQL]
  Description     = PostgreSQL driver for Linux & Win32
  Driver          = /opt/local/lib/psqlodbcw.so
  FileUsage       = 1

5. Make a template file for your database connections.

  As far as I can tell, the way ODBC works, you'll need to run a command for each database you want to connect to. First make a template file that looks like this:

  [dbname]
  Description         = Test to Postgres
  Driver              = PostgreSQL
  Trace               = No
  Database            = dbname
  Servername          = localhost
  UserName            = fusion
  Password            =
  Port                = 5432
  #Protocol            = 6.4
  ReadOnly            = No
  RowVersioning       = No
  ShowSystemTables    = No
  ShowOidColumn       = No
  FakeOidIndex        = No
  ConnSettings        =

  PostgreSQL's default security settings are quite lax. Assuming you're playing with your local test database, a file like this should be just fine. Replace fusion with whatever your username is and dbname with the database name you want to connect to. Be sure to catch both occurrences of dbname! Name this file something like 'odbc-template.txt'.

6. Add the configuration to ODBC:

  $ odbcinst -i -s -f odbc-template.txt

7. Connect to it from Prolog:

  $ swipl
  Welcome to SWI-Prolog (Multi-threaded, 64 bits, Version 7.1.5)
  Copyright (c) 1990-2013 University of Amsterdam, VU Amsterdam
  SWI-Prolog comes with ABSOLUTELY NO WARRANTY. This is free software,
  and you are welcome to redistribute it under certain conditions.
  Please visit http://www.swi-prolog.org for details.

  For help, use ?- help(Topic). or ?- apropos(Word).

  ?- [library(odbc)].
  %    library(pairs) compiled into pairs 0.00 sec, 22 clauses
  %   library(lists) compiled into lists 0.01 sec, 122 clauses
  %  library(shlib) compiled into shlib 0.01 sec, 163 clauses
  % library(odbc) compiled into odbc 0.02 sec, 264 clauses
  true.

  ?- odbc_connect(dbname, _, [alias(dbname)]).
  true.

  ?- odbc_query(dbname, 'SELECT COUNT(*) FROM foo', X).
  X = row(29514).

I hope you find this useful,

 — 
Daniel Lyons