From oleg@pobox.com Sun Nov 15 14:03:49 1998 To: oleg@pobox.com Subject: Platform-independent higher-order distributed SQL database interface Date: Sun, 15 Nov 1998 22:05:14 GMT Reply-To: oleg@pobox.com Keywords: SQL, database interface, iterator, CGI, CLI, pipe, Informix, Scheme Newsgroups: comp.lang.scheme,comp.lang.lisp,comp.databases Organization: Deja News - The Leader in Internet Discussion Summary: Introducing a higher-level platform-independent Scheme-SQL interface X-Article-Creation-Date: Sun Nov 15 22:05:14 1998 GMT X-Http-User-Agent: Mozilla/4.08 (Macintosh; I; PPC, Nav) Content-Length: 6399 Status: RO This article describes a higher-order Scheme interface to SQL databases. The interface is similar in spirit to ChezSybase or WB-tree. The major distinction is that the interface and its implementation are database- and platform-independent. It can be used with any database that provides a command-line SQL access to a database, on any platform that supports POSIX pipes, on any Scheme system that has some POSIX interface. Furthermore, the database access tool -- let alone the database server -- don't have to be available on the same computer that runs a client Scheme application, as last paragraphs of this article show. The interface is thus truly distributed. A familiar X/Open SQL Call Level Interface (CLI), which is a part of a ODBC, is rather low level. You submit a query, get a 'result-set', and keep calling its 'next-row' method until the result-set is exhausted. Scheme as a higher-order language can do iterations better, without handing over the internal iteration context -- the result set-- to an application. If a user never gets hold of the result-set, he doesn't have a chance to screw it, pass to wrong methods or at wrong times. Therefore, a database access driver never has to bother checking the validity of the result set the driver obtained from the user. Examples: ; find the tables whose names match a 'table-pat-name' (let ((table-names-rows ; assoc list of table names and # rows (DB:for-each (lambda (tab-name nrows) (cout "Table " tab-name " has " nrows " row(s)\n") (assert (string->number nrows)) (cons tab-name (string->number nrows))) "select trim(N.dbsname) || ':' || N.owner || '.' || N.tabname, " " I.ti_nrows from sysmaster:informix.systabnames N," " sysmaster:informix.systabinfo I " "where N.partnum = I.ti_partnum AND tabname LIKE '" table-pat-name "' ORDER BY 2;"))) ...) ; Execute a query as specified by the query-strings, ; and format the reply in a set of HTML OPTIONS strings. ; The query is expected to return one or several rows ; with two columns: an 'id' and a 'descr' ; where descr is some string associated with an id. ; For each returned row, we write out an HTML OPTION tag ;