SQAPL and DriverOptions

Dyalog's 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 hand)

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 database table?

Start SQAPL (assume it's in namespace #.SQA as recommended)...

      ⎕io ⎕ml←0 3 
      #.SQA.Init ''
0  SQAPL loaded from: .\cndya61x64Uni Using default translation no aplunicd.ini present
      #.SQA.BrowseConnect 'ABC'
0




then (reeling from the timewarp we appear to enter)...



and we can pluck out the string we need...

      1 0 6⊃0 #.SQA.Describe 'ABC'
DSN=DBF;DBQ=D:\DICK\MYCODE\DYALOG132\APLETS\DOGMAP\MAPS\TL\AIRPORT;DefaultDir=D:\DICK\MYCODE\DYALOG132\APLETS\DOGMAP\MAPS\TL\AIRPORT;DriverId=533;FIL=dBASE 5.0;MaxBufferSize=2048;PageTimeout=600;
      #.SQA.Close 'ABC'
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).

Code Example

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)

    ∇ z←GetDBF(folder square);⎕IO;⎕ML;rc;res
    ⍝ Get entries from .dbf file
      ⎕IO ⎕ML←0 3
      rc←#.SQA.Init''
      rc←#.SQA.Connect'C0' 'DBF'('DriverOptions'('DSN=DBF;DBQ=',file,';DriverId=533;FIL=dBASE 5.0'))
      rc res←(⊂0 2)⌷#.SQA.Do'C0'('Select * from ',square)
      z←⊂[0]⊃res
      rc←#.SQA.Close'C0'
    ∇

In use...

     ⍴GetDBF 'd:\dick\mycode\dyalog132\aplets\dogmap\maps\TQ\RailwayTunnel\' 'TQ '
244 1

Not - you might conclude - the most ambitious database table ever seen (but other gridsquare/feature combinations are larger and more complex).

One 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 experimental phase.

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