A Simple Clojure Program To Read an Informix Database

This post assumes you have Informix properly installed and have access to database tools like dbaccess. My configuration is running on CentOS 6.5 32-bit. It also assumes you can get the Informix JDBC driver installed into maven.

From the day I got my hands on The Joy of Clojure and eventually other excellent Clojure books, I have wanted to use Clojure to access an Informix database. In our case, it is an Informix SE database running on Linux.

This is what I did to get it running, with help from these stackoverflow questions and IBM Support. Although my first stackoverflow question involved korma, it made sense to keep things simple, and just use the Informix JDBC driver directly.

1) For SE, the latest support jdbc driver is 3.50.JC9, so that is what I installed.

2) You need to create a database. I created the stores7 directory and stores7.dbs. The demos I used all require that a database exist or be created.

3) Simple Java file installed at /opt/IBM/Informix_JDBC_Driver/demo/basic/SimpleSelect.java that we modified in a local directory, rebuilt, and ran


/**************************************************************************
*
* Licensed Materials - Property of IBM Corporation
*
* Restricted Materials of IBM Corporation
*
* IBM Informix JDBC Driver
* (c) Copyright IBM Corporation 1998, 2004 All rights reserved.
*
****************************************************************************/
/***************************************************************************
* Title: SimpleSelect.java
*
* Description: Demo a simple select operation
*
* An example of running the program:
*
* java SimpleSelect
* 'jdbc:informix-sqli://myhost:1533:informixserver=myserver;user=;password='
*
* Expected result:
*
* >>>Simple Select Statement test.
* URL = "jdbc:informix-sqli://myhost:1533/testDB:informixserver=myserver;user=;password="
* Select: column a = 11
* >>>End of Simple Select Statement test.
*
***************************************************************************
*/

import java.sql.*;
import java.util.*;

public class SimpleSelect {

public static void main(String[] args)
{
     String newUrl = "";

     newUrl = "jdbc:informix-sqli://steamboy:1498/testdb:informixserver=steamboy;usev5server=1;DBPATH=/home/cnorton/stores7";

     Connection conn = null;
     int rc;
     String cmd=null;
     Statement stmt = null;

     String testName = "Simple Select Statement";

     System.out.println(">>>" + testName + " test.");
     System.out.println("URL = "" + newUrl + """);

     try
     {
          Class.forName("com.informix.jdbc.IfxDriver");
     }
     catch (Exception e)
     {
          System.out.println("FAILED: failed to load Informix JDBC driver.");
     }

     try
     {
          conn = DriverManager.getConnection(newUrl);
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: failed to connect!");
     }

     // Drop table before starting - ignore errors
     try
     {
          Statement dstmt = conn.createStatement();
          dstmt.executeUpdate("drop table x");
     }
     catch (SQLException e)
     { ; }
     try
     {
          stmt = conn.createStatement();
          cmd = "create table x (a smallint);";
          rc = stmt.executeUpdate(cmd);
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: execution failed - statement: " + cmd);
          System.out.println("FAILED: " + e.getMessage());
     }

     try
     {
          cmd = "insert into x values (11);";
          rc = stmt.executeUpdate(cmd);
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: execution failed - statement: " + cmd);
          System.out.println("FAILED: " + e.getMessage());
     }

     try
     {
          cmd = "insert into x values (22);";
          rc = stmt.executeUpdate(cmd);
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: execution failed - statement: " + cmd);
          System.out.println("FAILED: " + e.getMessage());
     }

     // INFORMIX_EXTEXT_BEGIN Simple1.jav
     try
     {
          PreparedStatement pstmt = conn.prepareStatement("Select * from x "+ "where a = ?;");
          pstmt.setInt(1, 11);
          ResultSet r = pstmt.executeQuery();

          while(r.next())
          {
                 short i = r.getShort(1);

                 // verify result
                 if (i != 11)
                        System.out.println("FAILED: Expected = 11 Returned = " + i);
                 else
                        System.out.println("Select: column a = " + i);
          }
          r.close();
          pstmt.close();
     }
     catch (SQLException e)
     {
         System.out.println("FAILED: Fetch statement failed: " + e.getMessage());
     }
     // INFORMIX_EXTEXT_END Simple1.jav

     try
     {
          cmd = "drop table x";
          rc = stmt.executeUpdate(cmd);
          stmt.close();
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: execution failed - statement: " + cmd);
          System.out.println("FAILED: " + e.getMessage());
     }

     try
     {
          conn.close();
     }
     catch (SQLException e)
     {
          System.out.println("FAILED: failed to close the connection!");
     }

     System.out.println(">>>End of " + testName + " test.");
   }
}

Here is the Clojure project.clj


(defproject db-test "0.1.0-SNAPSHOT"
     :description "Clojure database test"
     :url "http://example.com/FIXME"
     :license {:name "Eclipse Public License"
     :url "http://www.eclipse.org/legal/epl-v10.html"}
     :dependencies [[org.clojure/clojure "1.5.1"]
                    [org.clojure/tools.cli "0.1.0"]
                    [com.informix.jdbc/com.springsource.com.informix.jdbc "3.0.0.JC3"]
                    [org.clojure/java.jdbc "0.3.3"]]
     :repositories [["springsource-release" "http://repository.springsource.com/maven/bundles/release"]
                    ["springsource-external" "http://repository.springsource.com/maven/bundles/external"]]
     :main db-test.core
     :aot [db-test.core])

And here is the Clojure core.clj. test file.


(ns db-test.core
    (require [clojure.string :as str])
    (require [clojure.java.jdbc :as j])
    (:use [clojure.tools.cli])
    (:import java.util.Date)
    (:gen-class))

; Parses for options passed in on the command line.

(def if_SE_engine_type "SE")
(def if_SE_absolute_log_path "/home/cnorton/stores7/stores7.log")

(defn parse-opts
     "Using the newer cli library, parses command line args."
     [args]
     (cli args
      (required ["-host" "Informix host"] )
      (required ["-server" "Informix server"])
      (required ["-dbpath" "Full path to database directory"])
      (optional ["-port" "Informix host's service port" :default "1498"] )
      (optional ["-username" "user name"] )
      (optional ["-password" "password"] )
      (optional ["-database" "Informix host's database" :default "stores7/testdb.dbs" ] )))

(defn -main
       [& args]
       (if (= 0 (count args))
          (println "Usage: db-test -host  [-port ] -database  -dbpath  -username  -password  -server ")

       (let [opts (parse-opts args)
                   start-time (str (Date.))]

            (def informix-db {:classname "com.informix.jdbc.IfxDriver"
                              :subprotocol "informix-sqli"
                              :subname (format "//%s:%s/%s:informixserver=%s;usev5server=1;DBPATH=%s"
                                         (:host opts)
                                         (:port opts)
                                         (:database opts)
                                         (:server opts)
                                         (:dbpath opts))})

            (let [customer-list
                  (j/query informix-db
                  ["select * from customer"])]

                  (doseq [customer customer-list]
                      (println customer))))))
Advertisements

Leave a comment

Filed under Clojure, informix

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s