Clojure, JDBC and using Dates.

I did not find many examples of using dates with Clojure/JDBC, after some initial struggle I got an example working. So here it is, hopefully it helps someone. By the way this page is a good source if you are interested in Clojure/JDBC.

Source File

(ns clojure-bi.simple
  (use clojure.contrib.sql))

(defn connection-props [connect-string user password]
  {:classname "oracle.jdbc.driver.OracleDriver"
	 :subprotocol "oracle:thin"
	 :subname (str "@" connect-string)
	 :user user
	 :password password })

(defn dev-db-props [user password]
  (connection-props "devdb.db.cj.com:1521:devdb" user password))

(defn make-sql-date [year month day]
  (java.sql.Date. 
   (.getTimeInMillis 
    (java.util.GregorianCalendar. year month day))))

(defn get-company-name [db id]
  (with-connection db
    (with-query-results rs ["select organization from company where id = ? " id] 
                  (first rs))))

(defn find-by-date [db date]
  (with-connection db
    (with-query-results rs ["select organization from company where trunc(Date_) = ? " date]
      (first rs))))

Test file

(ns clojure-bi.simple-test
  (:use [clojure-bi.simple] :reload-all)
  (:use [clojure.test])
  (import (java.util Date)))

(def dev-db (dev-db-props "devender" "test"))

(def expected-company {:organization "Client Services"})

(deftest get-company-name-test
  (is (= (get-company-name dev-db 2) expected-company)))

(def date-to-use (make-sql-date 1999 6 15))

(deftest find-by-date-test
  (is (= (find-by-date dev-db date-to-use) expected-company)))

The interesting parts are the find-by-date function in the source and the test function find-by-date-test, you have to pass in a java.util.sql Date.

Some helpful Sqls for Oracle

1. For finding out the current running sql for a particular user

SELECT a.sql_text
 FROM v$session s,
 v$sqlarea a
 WHERE s.user    = 'USER'
AND s.status        ='ACTIVE'
AND s.sql_hash_value=a.hash_value
AND s.sql_address   =a.address;

2. To find locked objects in Oracle

3. To see all the seesion for a user

   select * from v$session s where s.username = 'PROQ';

4. To see locked objects and the session

select     oracle_username || ' (' || s.osuser || ')' username
   ,  s.sid || ',' || s.serial# sess_id
   ,  owner || '.' || object_name object
   ,  object_type
   ,  decode( l.block
      ,       0, 'Not Blocking'
      ,       1, 'Blocking'
      ,       2, 'Global') status
   ,  decode(v.locked_mode
     ,       0, 'None'
     ,       1, 'Null'
     ,       2, 'Row-S (SS)'
     ,       3, 'Row-X (SX)'
     ,       4, 'Share'
     ,       5, 'S/Row-X (SSX)'
     ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
  from       v$locked_object v
  ,  dba_objects d
  ,  v$lock l
  ,  v$session s
  where      v.object_id = d.object_id
  and        v.object_id = l.id1
  and        v.session_id = s.sid
  order by oracle_username
  ,  session_id

And then kill the session with this

alter system kill session '94,2168';

5. Tables by user

select owner,count(*) from all_all_tables group by owner;