howto: JDBC + interpreter vs compilation bug (was: java.sql.ResultSet.getObject() Kawa(?) bug, (as <int> 1))

["Hoehle, Joerg-Cyril" <Joerg-Cyril dot Hoehle at t-systems dot com>]

Per Bothner wrote:
>Otherwise, I need a real testcase with simple instructions.   Note
>that I don't have any JDBC experience, and I'd need to know how to
>set up a simple database (under Linux).  (It would be great if someone
>wrote up a little how-to document on using Kawa with JDBC.)

Appended is a file which shows how to query a database using Kawa's primitives. I believe it shows enough to get anyone started on JDBC database queries. It also serves as a testcase.

When compiled using kawa -C, it passes the test -- (invoke rs 'getObject 1) works.
When submitted line by line to an interactive kawa REPL, it generates the error I mentioned previously.
I cannot currently test whether the errors arises when loaded from a BRL servlet.

I conclude:
 o there is compilation and interpretation in Kawa (whatever the details)
 o both exhibit different behaviour here (for getObject/getInt/getString/ etc.)
 o (require <>) behaviour is minor difference

>java -classpath kawa-1.6.98.jar kawa.repl -C jdbcprimer.scm
(compiling jdbcprimer.scm)
jdbcprimer.scm:26:2: warning - no method `next' in java.lang.Object
jdbcprimer.scm:28:2: warning - no method `getObject' in java.lang.Object
jdbcprimer.scm:32:2: warning - no method `getObject' in java.lang.Object
jdbcprimer.scm:33:2: warning - no method `findColumn' in java.lang.Object
jdbcprimer.scm:34:2: warning - no method `getString' in java.lang.Object
jdbcprimer.scm:38:2: warning - no method `getMetaData' in java.lang.Object
jdbcprimer.scm:39:2: warning - no method `getColumnCount' in java.lang.Object
jdbcprimer.scm:40:2: warning - no method `getColumnName' in java.lang.Object
jdbcprimer.scm:41:2: warning - no method `getColumnType' in java.lang.Object
jdbcprimer.scm:42:2: warning - no method `getColumnType' in java.lang.Object

>java -classpath kawa-1.6.98.jar;. kawa.repl
#|kawa:1|# (load "jdbcprimer")
1Redundant Array3#!null4AbkId
^getObject 1            ^getColumnName 1
 ^getObject "Bedeutung"
                ^findColumn "Bedeutung"
                 ^getString 4 (SQL NULL)
                       ^getColumnType 1
#|kawa:2|#

------------------jdbcprimer.scm
; loading automatically installs the named driver
(require <sun.jdbc.odbc.JdbcOdbcDriver>)
;(sql-driver "oracle.jdbc.driver.OracleDriver") ; BRL function

; It seems require is not considered at load-time of .class file,
; so must do an explicit call
(invoke-static <java.lang.Class> 'forName "sun.jdbc.odbc.JdbcOdbcDriver")

; use Sun's ODBC driver to connect to datasource "Abkuerzungen"
;(define conn (invoke-static <java.sql.DriverManager> 'getConnection "jdbc:odbc:Abkuerzungen" "thisuser" "mypassword"))
(define conn  :: <java.sql.Connection>
  (invoke-static <java.sql.DriverManager> 'getConnection "jdbc:odbc:Abkuerzungen"))

; MS SQL ("jdbc:microsoft:sqlserver://<hostname>:1433;DatabaseName=Abkuerzungen-Test1" "sa" "")
; Oracle ("jdbc:oracle:thin:@192.68.1.9:1521:mydb" "user" "passwd")

; Adding explicit types prevents compiler warnings when using kawa -C foo.scm
(define st  :: <java.sql.Statement>
  (invoke conn 'createStatement))

; Turn debugging on:
;(invoke-static <java.sql.DriverManager> 'setLogStream (static-field <java.lang.System> 'out))

(define rs (invoke st 'executeQuery "SELECT * FROM Abkuerzungen"))
(invoke rs 'next) ; prepares first row

(display (invoke rs 'getObject 1)) ; yields first column's content
;(display (invoke rs 'getInt 1))
;(display (invoke rs 'getLong 1))
;(display (invoke rs 'getBigDecimal 1))
(display (invoke rs 'getObject "Bedeutung"))
(display (invoke rs 'findColumn "Bedeutung"))
(display (invoke rs 'getString 4))

; Result Set Meta Data
;(define rsmd (sql-rsmd rs)) ; BRL function
(define rsmd (invoke rs 'getMetaData))
(display (invoke rsmd 'getColumnCount))
(display (invoke rsmd 'getColumnName 1))
(invoke rsmd 'getColumnType 1); -> 4
(invoke rsmd 'getColumnType 2); -> 12

; All jtds URLs describe JTDS implementation, not JDBC standard
; still useful
;http://jtds.sourceforge.net/
;http://jtds.sourceforge.net/com/internetcds/jdbc/tds/TdsResultSetMetaData.html
;http://jtds.sourceforge.net/com/internetcds/jdbc/tds/AbstractResultSet.html

;(invoke conn 'setAutoCommit true/false)
;(invoke conn 'commit)
;(invoke rs 'close)
(invoke conn 'close)

How to setup a DB under Linux is beyond my current knowledge. MySQL and Postgres and all come with sample databases that could be used for the test, but one still has to locate a JDBC driver. I don't know which UNIX database provides an ODBC interface so that Sun's JDBC-ODBC bridge could be used (it's included in JDK).

Regards,
	Jorg Hohle.