(= (+ Kawa Derby) Fun)

A Kawa + Derby Tutorial

---

Background

Kawa is an implementation of Scheme (and other languages) that compiles to Java byte code. Derby (also known as Cloudscape) is a SQL database engine written in Java which, as in this tutorial, can be run as an in-process server. Together, they are a complementary combination of elegant, functional programming (Scheme), an extensive library of utility code (Java's runtime libraries) and a powerful SQL database (Derby) that can be deployed with just a few JAR files.

---

1. Download the Kawa .jar file and the Derby .jar files and put them in your CLASSPATH. Then you can play with Kawa's Scheme REPL like this:

   C:\harold>java kawa.repl
   #|kawa:1|# (+ 1 2)
   3
   #|kawa:2|# (exit)
   
   C:\harold>
   

2. As a first program, write a simple "Hello, World" program, compile it, and run it:

   C:\harold>cat > hello.scm
   
   (display "Hello, World!")
   (newline)
   
   ^Z
   C:\harold>java kawa.repl --main -C hello.scm
   (compiling hello.scm)
   
   C:\harold>java hello
   Hello, World!
   

3. Then, try a simple database example to get started:

   dbtest.scm

   ; Load the Derby JDBC driver
   (java.lang.Class:forName "org.apache.derby.jdbc.EmbeddedDriver")
   
   ; Connect, creating DB if needed
   (define uri "jdbc:derby:testdb;create=true")
   (define conn (java.sql.DriverManager:getConnection uri))
   
   ; Create a table
   (define stmt (*:createStatement conn))
   (*:execute stmt "create table t (c1 int)")
   
   ; Disconnect
   (*:close conn)
   

4. Which, when run, works as expected (1):

   C:\harold>java kawa.repl --main -C dbtest.scm
   (compiling dbtest.scm)
   dbtest.scm:10:14: warning - no accessible method 'createStatement' in java.lang.Object
   dbtest.scm:11:1: warning - no accessible method 'execute' in java.lang.Object
   dbtest.scm:14:1: warning - no accessible method 'close' in java.lang.Object
   
   C:\harold>java dbtest
   
   C:\harold>java dbtest
   SQL Exception: Table/View 'T' already exists in Schema 'APP'.
   ... (exception stack) ...
   

5. Here is some helper code, to get things going:

   ;;; ======================================
   ;;;
   ;;; Use Java Classes
   ;;;
   ;;; ======================================
   
   ; Set up some Kawa namespaces for invoking static methods
   
   ; SQL
   (define-namespace Class <java.lang.Class>)
   (define-namespace DrMgr <java.sql.DriverManager>)
   (define-namespace Conn  <java.sql.Connection>)
   (define-namespace Stmt  <java.sql.Statement>)
   
   ; Regular Expressions
   (define-namespace Pattern <java.util.regex.Pattern>)
   
   ;;; ======================================
   ;;;
   ;;; Globals
   ;;;
   ;;; ======================================
   
   ; Current connection
   (define conn :: <java.sql.Connection> #!null)
   
   ;;; ======================================
   ;;;
   ;;; Functions
   ;;;
   ;;; ======================================
   
   ; Connect, creating the DB if needed
   (define (connect)
       (Class:forName "org.apache.derby.jdbc.EmbeddedDriver")
       (set! conn (DrMgr:getConnection "jdbc:derby:testdb;create=true")))
   
   ; Disconnect from DB
   (define (disconnect)
       (*:close conn)
       (set! conn #!null))
   
   ; Execute a list of SQL statements with no results
   (define (runSQL . sqlStrings)
       (let ((stmt (*:createStatement conn)))
         (map (curry *:execute stmt) sqlStrings)))
   
   ; Execute a single SQL statement and return the result
   (define (getSQL sqlString)
       (let ((stmt (*:createStatement conn)))
         (*:executeQuery stmt sqlString)
         (*:getResultSet stmt)))
   
   ; Run a function across the result sets of a query
   (define (map-rs fn rs)
       (letrec ((loop (lambda ()
                        (if (*:next rs) 
                            (begin (fn rs) (loop)) #t))))
               (loop)))
   
   (define (curry fn . args)
       (lambda moreArgs
         (apply fn (append args moreArgs))))
   
   (define (displayn . args)
       (apply display args)
       (newline))
   
   (define (regex pattern)
       (let ((pattern (Pattern:compile pattern)))
         (lambda (string)
           (let ((matcher (*:matcher pattern string)))
             (if (*:find matcher 0) matcher #f)))))
   

6. Now it is even easier to write a nice program to create a table if it doesn't exist, insert a row, and query the table:

   (connect)
   
   ; Create the table if it does not already exist
   (try-catch
    (begin (runSQL "create table t1 (tabname char(20), created date)")
           (displayn "Table T1 Created..."))
    (e <java.sql.SQLException>
       (displayn "Table T1 Exists...")))
   
   ; Insert a row
   (runSQL "insert into t1 values ('Hello', current date)")
   
   ; Print out the contents of the table
   (map-rs (lambda (rs) 
             (display (*:getString rs 1))
             (display " - ")
             (display (*:getString rs 2))
             (newline))
           (getSQL "select * from t1"))
   
   (disconnect)
   

Enjoy!

---

1: The warnings mean that Scheme's dynaming typing cannot guarantee the method exists on the object you reference. You can use the :: <Java Type> syntax to give a variable a type, but it seems to misbehave if you use an interface (like ResultSet) as the type instead of a class.