SQAPL and DriverOptions
SQAPL is a very convenient tool for dealing with data in relational
databases, simple enough to set up the ODBC data source(s) you need to
handle and then use code from the SQAPL workspace to do what's what
(just read the documentation).
Somewhere that this simple approach is in danger of collapse is when
there are a very large (and varying) number of possible data sources -
it's just impracticable to create an ODBC data source for each.
An example is UK Ordnance Survey OpenDat, where a .dbf file
contains mapping data (one file per OS gridsquare and feature type -
over 1000 files). Fortunately ODBC/SQAPL gives us an
alternative to formal creation of data sources - it's called
DriverOptions. Not so fortunately the documentation is a bit
sketchy, which is where this page might come in useful.
Create a Generic ODBC Source
In this example we'll use the 64-bit Microsoft Access DBASE Driver (it
may not be a good choice, but it does seem to work for the files at
There's an entry in the main ODBC administration window,...
and in the configuration window for that entry...
Retrieve a DriverOptions String
So, what do we tell SQAPL when we want to use this with a specific
Start SQAPL (assume it's in namespace #.SQA as recommended)...
0 SQAPL loaded from: .\cndya61x64Uni Using default
translation no aplunicd.ini present
then (reeling from the timewarp we appear to enter)...
and we can pluck out the string we need...
1 0 6⊃0
which has a little more content than we absolutely need (notice that
it's specifying a folder not an individual file - .dbf seems to operate
with a simple file/table correspondence).
The folder/filename structure is gridsquare (two letters - for example
TL), feature type (for example Airport) taking you to a folder which
contains four files, one of which is the .dbf file (a single table) -
the file and table names are the same tow letters (this is all abit
opaque - the data is within a context known as an ESRI Shapefile and
somewhat antique, it also isn't helped by "something" breaking for
filenames which don't conform to the old 8.3 convention.
Here it is encapsulated into a simple function (touchingly optimistic
about the reliability of ODBC and SQAPL)
⍝ Get entries from .dbf file
⎕IO ⎕ML←0 3
2)⌷#.SQA.Do'C0'('Select * from ',square)
'd:\dick\mycode\dyalog132\aplets\dogmap\maps\TQ\RailwayTunnel\' 'TQ '
Not - you might conclude - the most ambitious database table ever seen
(but other gridsquare/feature combinations are larger and more complex).
word of warning - when experimenting with this it seems some leakage is
possible, in particular the APL working folder is prone to change.
Don't know why, and it seems more stable now that I'm out of the
Hope this helps - but the author bears no
responsibility for problems encountered. No copyright or other
rights deliberately infringed.
Page created 17 January
2013; Copyright © Dick Bowman 2013