[Jscheme-user] Re: Another JScheme question for you

[Timothy H. <tim...@ma...>]

On Sunday, April 14, 2002, at 03:41 PM, Steve Adams wrote:

> Hello again,
> =A0
> Are there any examples available that show database access via JDBC?=A0=20=

> Thanks.


I usually use the "runquery" procedure defined below
it has a simple syntax
(runquery  host/db user pw query)
It creates a connection using the host/db user pw info and
then sends the query and returns a list of the headers and all result=20
rows as a list of lists.

The code I use is shown at the end of this message.
Here's an example of its use, where age,party, votedfor
are servlet parameter and we are using the quasi-quote
notation of jscheme   {...[var]...[var]...} to construct strings:

     (runquery "jdbc:hsqldb:hsql://localhost:9009" "sa" "mynewpassword"
       {INSERT INTO survey VALUES([age],'[party]','[votedfor]')})

You could do this without quasi-quotes using string-append

    (runquery  "jdbc:hsqldb:hsql://localhost:9009" "sa" "mynewpassword"
     (string-append
        "INSERT INTO survey VALUES("
            age
          ",'"
         party
        "','"
        votedfor
         "' "))


Here is the code I use to define runquery. It uses the hsqldb
database/driver. For other databases you need to change
the "connect" procedure slightly.


;;;;;;;;;;;;;;;;;;;;;;;;;;; db.scm ;;;;;;;;;;;;;;;;;;;;;;;;;;
(define (connect host/db user pw)
   (let* ((d (java.lang.Class.forName "org.hsqldb.jdbcDriver")))
      (java.sql.DriverManager.getConnection host/db user pw)))

(define (handlequery con query)
   (let* ((stmt (.createStatement con))
          (rs (.executeQuery stmt query))
          (rsmd (.getMetaData rs))
          (cols (.getColumnCount rsmd))
          (result
            (cons
               (let loop1 ((i 1))
                   (if (> i cols) ()
                       (cons (.getColumnLabel rsmd i)
                             (loop1 (+ i 1)))))
               (let loop ()
                    (if (not (.next rs)) ()
                        (cons
                           (let innerloop ((i 1))
                               (if (> i cols) ()
                                   (cons (.getString rs i)
                                         (innerloop (+ i 1)))))
                           (loop)))))))
       (map .close (list rs stmt))
      result))

(define (runquery host/db user pw query)
   (let* ((con (connect host/db user pw))
          (results (handlequery con query)))
     (.close con)
     results))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

I have some examples in a book I'm writing on Web Programming
using Jscheme, you can get an early rough draft at
http://www.cs.brandeis.edu/~tim/Download/iiwd.pdf
and the jakarta-tomcat webapp with all code from the book
is in
http://www.cs.brandeis.edu/~tim/Download/schemewebapp.zip




> =A0
> Steve Adams
> =A0