Clojure Program Doesn't Work After Compiling

0
votes

I have a simple Clojure program that queries an Oracle database and prints out results in a comma delimited format. This program works just fine running it normally through Clojure.main. I want to compile this program and followed the instructions from here:

http://java.ociweb.com/mark/clojure/article.html#Compiling

I compiled and ran another simple program using this method described just fine. So I don't think this has anything to do with this method of compiling. This program compiles just fine but when I attempt to run the compiled class it returns this error:

Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Exhausted Resultset at clojure.lang.LazySeq.sval(LazySeq.java:47) at clojure.lang.LazySeq.seq(LazySeq.java:56) at clojure.lang.RT.seq(RT.java:440) at clojure.core$seq__4245.invoke(core.clj:105) at bill.myquery$dump_db_csv__24.invoke(myquery.clj:27) at bill.myquery$main_49.invoke(myquery.clj:41) at clojure.lang.AFn.applyToHelper(AFn.java:171) at clojure.lang.AFn.applyTo(AFn.java:164) at bill.myquery.main(Unknown Source) Caused by: java.lang.RuntimeException: java.sql.SQLException: Exhausted Resultset at clojure.lang.LazySeq.sval(LazySeq.java:47) at clojure.lang.LazySeq.seq(LazySeq.java:56) at clojure.lang.Cons.next(Cons.java:37) at clojure.lang.RT.boundedLength(RT.java:1128) at clojure.lang.RestFn.applyTo(RestFn.java:135) at clojure.core$apply__4370.invoke(core.clj:438) at clojure.core$resultset_seq_6276$thisfn_6290$fn__6291.invoke(core.clj:3842) at clojure.lang.LazySeq.sval(LazySeq.java:42) ... 8 more Caused by: java.sql.SQLException: Exhausted Resultset at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:900) at clojure.core$resultset_seq__6276$row_values_6284$fn_6286.invoke(core.clj:3839) at clojure.core$map_5053$fn_5055.invoke(core.clj:1760) at clojure.lang.LazySeq.sval(LazySeq.java:42)

Here it is:

(ns bill.myquery (:gen-class))

;; Import java.sql classes
(import '(java.sql DriverManager Connection PreparedStatement ResultSet))

;; load jdbc/odbc driver
(. Class forName "oracle.jdbc.driver.OracleDriver")

(def dbname "myOracleServer:1522:myOracleDatabase")
(def service_account "account")
(def service_password "password")

(def conn (. DriverManager (getConnection (str "jdbc:oracle:thin:@" dbname) service_account service_password)))

(def sql "
        SELECT name, address, phone
        FROM addresss_book
        ORDER BY name")

;; Function to dump data with comma delimited fields
(defn dump-db-csv [db]
  (doseq [rec db] ;; for all rows
    (doseq [[key value] rec] ;; for all fields
        (if (= key :phone) ;; if last field
              (print (.trim (str value))) ;; don't print comma
              (print (format "%s%s" (.trim (str value)) ","))))
    (println)))

;; Execute query and get recordset
(def rs (.. conn (prepareStatement sql) (executeQuery)))

;; convert recordset to sequence
(def rset (resultset-seq rs))

;; Main call the function to print rows
(defn -main (dump-db-csv rset))

;; close the recordset
(. rs (close))
2
oracleclojure

2 Answers

3
votes

Don't use def - everything from the top level is executed at class load time (or even at compile time), so you close result set (rs) before running -main. Always open and close connection in a single function - it will prevent a lot of errors.

Also consider using clojure.contrib.sql. Nice examples can be found at wikibooks.

For both methods don't forget to use doall for any possible lazy sequences.

0
votes

I took Andrei's advice and used clojure.contrib.sql. It does make this even more simple and less error prone. I still didn't want to give up def for the db and sql global variables.

Thanks Andrei

(ns my.query (:use [clojure.contrib.sql]))

(def *db* {:classname "oracle.jdbc.driver.OracleDriver" ; must be in classpath
           :subprotocol "oracle:thin"
           :subname "myserver:1521:mydatabase"
           ; Any additional keys are passed to the driver
           ; as driver-specific properties.
           :user "account"
           :password "password" })

(def *sql* "select * from phonebook")

(defn dump-db-csv []
  (with-connection *db*
    (transaction
      (with-query-results rs [*sql*]
          (doseq [row rs] ;; for all rows
            (doseq [[key value] row] ;; for all fields
                (if (= key :phone) ;; if last field
                  (print (.trim (str value))) ;; do not print comma
                  (print (format "%s%s" (.trim (str value)) ","))))
            (println))))))

;; Main - call the function to print rows
(dump-db-csv)