<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Verdana size=2><SPAN class=703003119-05082003>I've been
experimenting with schemeql in order to process some tables in a SQL
Server 2000 database.</SPAN></FONT></DIV>
<DIV><FONT face=Verdana size=2><SPAN class=703003119-05082003>I set up an ODBC
connection via Microsoft Window's </SPAN></FONT></DIV>
<DIV><FONT face=Verdana size=2><SPAN class=703003119-05082003>>>Control
Panel </SPAN></FONT></DIV>
<DIV><FONT face=Verdana size=2><SPAN
class=703003119-05082003> ->Administrative
Tools </SPAN></FONT></DIV>
<DIV><FONT face=Verdana size=2><SPAN
class=703003119-05082003> ->Data Sources
(ODBC)</SPAN></FONT></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>then I selected
MQIS SQL Server and configured it with the default "MQIS" name.
</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>With such setup
done, I successfully connected with the database via schemeql and
excitingly</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>started writing
some schemeql code to experiment.</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>I noticed that the
macro expansions didn't all work with SQL Server 2000 - probably
because</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>SQL Server
2000 might not be completely SQL 92 compliant.</FONT> <FONT face=Verdana
size=2>For instance I had to modify the ALTER TABLE</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>macro expansion so
that instead of expanding to "ADD COLUMN <colName>" it expanded to "ADD
<colName>.</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>However I noticed
some unexpectedness in schemeql despite using SQL Server. For
instance, using the</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>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
wrong.</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>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 </FONT></SPAN><SPAN
class=703003119-05082003><FONT face=Verdana size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>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 comments:</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003> </SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>;;;>>My example schemeql
code:</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(require
(lib "schemeql.ss" "schemeql"))<BR>;(require (lib "trace.ss"))<BR>(define dbms
"MQIS")<BR>(define name "my-login-name")<BR>(define password
"my-secret-password")<BR>(connect-to-database dbms name
password)</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>;the results
table name as a string:<BR>;(to use this string variable's value simply unquote
it <BR>(define resultsTable "\"ResultsTable\"")</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>;the input
table<BR>(define inputTable "\"K Science A\"")</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>(schemeql-execute (drop-table
,resultsTable))<BR>(schemeql-execute (create-table ,resultsTable (studentID
(varchar 50)) (rowID INT)))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
my-query (distinct! (order-by! (query ("QUESTION") (,inputTable)) ("QUESTION"))
))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
studids-query (distinct! (order-by! (query ("STUDID") (,inputTable)) "STUDID")))
<BR>(define questions (result-cursor (schemeql-execute
my-query)))<BR>(re-connect-to-database "MQIS")<BR>(define studids (result-cursor
(schemeql-execute studids-query)))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
(traverse-cursor cursor)<BR> (cond ((cursor-null? (cursor-cdr cursor))
'())<BR>
(else<BR> (cons (car
(cursor-car cursor)) (traverse-cursor (cursor-cdr
cursor))))))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(display
"got here!")<BR>;(traverse-cursor questions)</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
(cursor-for-each proc a-cursor)<BR> (if (cursor-null?
a-cursor)<BR>
'done<BR> (begin (proc (cursor-car
a-cursor))<BR>
(cursor-for-each proc (cursor-cdr a-cursor)))))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
append-next-question-col <BR> (lambda
(question)<BR>
(begin<BR>
(re-connect-to-database
"MQIS")<BR>
(let ((col-name (car
question)))<BR>
(set! col-name (string-append "\""
col-name))<BR>
(set! col-name (string-append col-name
"\""))<BR>
(display
col-name)<BR>
(schemeql-execute (alter-table ,resultsTable ADD ,col-name ((varchar 50)
NULL)))))))</SPAN></FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>(define
insert-next-studid<BR> (lambda (studid)<BR>
(begin<BR> (re-connect-to-database
"MQIS")<BR> (let ((next-id (car
studid)))<BR> ;(set! next-id
(string-append "\"" next-id))<BR>
;(set! next-id (string-append next-id
"\""))<BR> (display "next-id
")<BR> (display
next-id)<BR>
(newline)<BR> (schemeql-execute
(insert ,resultsTable ("studentid")
(stud-id)))<BR>
)<BR> (display "inside
insert-next-studid"))))</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>(cursor-for-each append-next-question-col
questions)<BR>(display "about to call insert-next-studid")<BR>;(car (cursor-car
studids))<BR>;(cursor-cdr studids)<BR>;(cursor-for-each insert-next-studid
studids)<BR>(define cols '("rowid" "studentid"))<BR>(define vals '('s111'
0))</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>;;The following
insert macro doesn't seem to like my arguments --</FONT></SPAN></DIV>
<DIV><SPAN class=703003119-05082003><FONT face=Verdana size=2>;; gives errors
like "car: expects argument of type <pair>; given
#<procedure:rest></FONT></SPAN></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>(schemeql-execute (insert ,resultsTable ,cols ,vals))
</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>(disconnect-from-database "MQIS")
<BR>(close-all-connections!)</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003></SPAN><SPAN
class=703003119-05082003>;;;<<end of My example schemeql
code>></SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>My tables
basically look like this:</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>K Science
A:</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>cols =
(</SPAN></FONT></FONT><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>STUDID, QUESTION ,RESPONSE)</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>Result
Table:</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>cols =
(STUDID) (QUESTION0) (QUESTION1) ...</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>I'm trying
to group and transpose my data so that each row of the result table has a
collection of test</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>responses
for a given STUDID. In SQL the closest thing to processing row by row for
such transposing</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>and dynamic
creation of a new table is using cursors. So I'm trying to use cursors in
schemeql since</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>the
cursor-map function seems easy to use. However the macro 'quasi-functions'
that I want to use in</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>cursor-map
is where the headache is coming from.</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN class=703003119-05082003>Again,
concrete, actual working code examples, even toy examples are often
worth a thousand --abstracted syntax-- manual pages.</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><SPAN
class=703003119-05082003>Sincerely,</SPAN></FONT></FONT></DIV>
<DIV align=left><FONT face=Verdana size=2>
<DIV align=left>
<DIV align=left><STRONG><FONT color=#182688>Daniel Lyle</FONT></STRONG></DIV>
<DIV align=left><FONT face=Arial color=#182688 size=2>Software
Engineer</FONT></DIV>
<DIV align=left><FONT face=Arial color=#182688 size=2>Waterford
Institute</FONT></DIV>
<DIV align=left><FONT face=Arial color=#182688
size=2></FONT> </DIV></DIV></FONT></DIV>
<DIV> </DIV>
<p></p>
*************************************<br>
<p></p>
This e-mail may contain privileged or confidential material intended for the named recipient only.<br>
If you are not the named recipient, delete this message and all attachments.<br>
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.<br>
We reserve the right to monitor e-mail sent through our network. <br>
<p></p>
*************************************<br>
<p></p>
</body>
</html>