[sprog-users] DBI Gear

[Grant M. <gr...@mc...>]

Hi All (and perhaps Tony in particular :-))

Obviously Sprog needs the ability to source data from a database via
DBI.  The tricky part of writing data source gears is the need for
non-blocking IO.  If someone submits a query that takes several minutes
to complete, then we obviously want the 'Stop' button to be functional
during that time.

Does anyone know if there are any hooks in DBI for using it
asynchronously?

Failing that, my short-term plan is to simply fork a process to handle
the database interaction and communicate with it over a pipe/socket
using the non-blocking framework I already have.

It's probably worth pointing out that one of the reasons I haven't done
the DBI gear yet is that I'm getting by with the following workaround:

I start my machine with a TextInput gear into which I type my SQL query.

I connect that to a CommandFilter gear and configure it to talk to my
Postgres database via psql like this:

  psql -f - -H dbname

The '-f -' tells psql to read the query from STDIN and the '-H' requests
HTML formatted output.

I then use a FindReplace gear to turn   (which psql uses for NULLs)
into a normal space.

A ParseHTMLTable gear turns the HTML into list output and a Strip
Whitespace gear removes the leading/trailing whitespace.

This workaround also has an advantage over a real DBI gear.  I can use
it to talk to database servers that sit behind firewalls.  I can't
connect directly to the Postgres server socket from my workstation, but
I can ssh in, so I use a command filter like this:

  ssh hostname psql -f - -H dbname

Of course if I had a real DBI gear I could use it with SSH's port
forwarding to achieve a similar effect.

Presumably other databases have command-line utilities that could be
exploited similarly.  Either HTML or CSV output should be easy to deal
with in Sprog.

Regards
Grant