[plt-scheme] help with schemeql

From: Daniel Lyle (daniel.lyle at waterford.org)
Date: Tue Aug 5 16:05:20 EDT 2003

I've been experimenting with schemeql in order to process some tables in
a SQL Server 2000 database.
I set up an ODBC connection via Microsoft Window's 
>>Control Panel 
     ->Administrative Tools  
       ->Data Sources (ODBC)
then I selected MQIS SQL Server and configured it with the default
"MQIS" name. 
With such setup done, I successfully connected with the database via
schemeql and excitingly
started writing some schemeql code to experiment.
I noticed that the macro expansions didn't all work with SQL Server 2000
- probably because
SQL Server 2000 might not be completely SQL 92 compliant. For instance I
had to modify the ALTER TABLE
macro expansion so that instead of expanding to "ADD COLUMN <colName>"
it expanded to "ADD <colName>.
However I noticed some unexpectedness in schemeql despite using SQL
Server.  For instance, using the
order-by! macro it expanded it into "GROUP BY" instead of "ORDER BY" in
the actual SQL printout.  Even this was easy to fix, but perhaps the
sourceforge maintainers should know that order-by! macro is expanding
I am rather perplexed about how to get the "insert" macro to work.  It
seems to not correctly parse the column def list and/or the
corresponding value list.  The macro expansion in this macro seems quite
hairy so I haven't been able to figure out how it works myself.  If
anyone has some concrete examples I could go off of that would be great.
The documentation is kind of confusing and experimenting is hard since
it's very hard to predict how a macro might expand and frustrating
trying to second guess how you need to quote certain inputs to the macro
-- i.e. whether the macro takes arguments that are real lists or a lists
of quoted values or lists of unquoted values or a quasiquoted list of
unquoted values etc.
Here is some of my experiment code: everything works fine except when I
try to insert into my database table near the bottom where I've added
Any help or direction would be great.  Schemeql seems to have good
potential, but it seems like there aren't many resources available to
keep it well maintained or better documented.
;;;>>My example schemeql code:
(require (lib "schemeql.ss" "schemeql"))
;(require (lib "trace.ss"))
(define dbms "MQIS")
(define name "my-login-name")
(define password "my-secret-password")
(connect-to-database dbms name password)
;the results table name as a string:
;(to use this string variable's value simply unquote it 
(define resultsTable "\"ResultsTable\"")
;the input table
(define inputTable "\"K Science A\"")
(schemeql-execute (drop-table ,resultsTable))
(schemeql-execute (create-table ,resultsTable (studentID (varchar 50))
(rowID INT)))
(define my-query (distinct! (order-by! (query ("QUESTION")
(,inputTable)) ("QUESTION")) ))
(define studids-query (distinct! (order-by! (query ("STUDID")
(,inputTable)) "STUDID"))) 
(define questions (result-cursor (schemeql-execute my-query)))
(re-connect-to-database "MQIS")
(define studids (result-cursor (schemeql-execute studids-query)))
(define (traverse-cursor cursor)
  (cond ((cursor-null? (cursor-cdr cursor)) '())
          (cons (car (cursor-car cursor)) (traverse-cursor (cursor-cdr
(display "got here!")
;(traverse-cursor questions)
(define (cursor-for-each proc a-cursor)
  (if (cursor-null? a-cursor)
      (begin (proc (cursor-car a-cursor))
             (cursor-for-each proc (cursor-cdr a-cursor)))))
(define append-next-question-col 
  (lambda (question)
                 (re-connect-to-database "MQIS")
               (let ((col-name (car question)))
                   (set! col-name (string-append "\"" col-name))
                   (set! col-name (string-append col-name "\""))
                (display col-name)
                (schemeql-execute (alter-table ,resultsTable ADD
,col-name ((varchar 50) NULL)))))))
(define insert-next-studid
  (lambda (studid)
      (re-connect-to-database "MQIS")
      (let ((next-id (car studid)))
        ;(set! next-id (string-append "\"" next-id))
        ;(set! next-id (string-append next-id "\""))
        (display "next-id ")
        (display next-id)
        (schemeql-execute (insert ,resultsTable ("studentid")
      (display "inside insert-next-studid"))))
(cursor-for-each append-next-question-col questions)
(display "about to call insert-next-studid")
;(car (cursor-car studids))
;(cursor-cdr studids)
;(cursor-for-each insert-next-studid studids)
(define cols '("rowid" "studentid"))
(define vals '('s111' 0))
;;The following insert macro doesn't seem to like my arguments --
;; gives errors like "car: expects argument of type <pair>; given
(schemeql-execute (insert ,resultsTable ,cols ,vals)) 
(disconnect-from-database "MQIS") 
;;;<<end of My example schemeql code>>
My tables basically look like this:
K Science A:
Result Table:
I'm trying to group and transpose my data so that each row of the result
table has a collection of test
responses for a given STUDID.  In SQL the closest thing to processing
row by row for such transposing
and dynamic creation of a new table is using cursors.  So I'm trying to
use cursors in schemeql since
the cursor-map function seems easy to use.  However the macro
'quasi-functions' that I want to use in
cursor-map is where the headache is coming from.
Again, concrete, actual working code examples, even toy examples are
often worth a thousand --abstracted syntax-- manual pages.
Daniel Lyle
Software Engineer
Waterford Institute


This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20030805/08966576/attachment.html>

Posted on the users mailing list.