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))))))

Why Moving Special Casing To The Data Can Be A Good Thing

From June 2013 through mid-February 2014, I have worked almost exclusively on rewriting our town’s water billing system. It works in conjunctions with our AMR system. That system  serves as a store and forward mechanism, which sends meter configuration changes to our hosted application, and also collects daily reads for every water meter in our “district”.

We chose Perl for the implementation language, but even more important than the implementation language were the choices we made to avoid special casing and to make things flexible. Here is one example, a meter that measures the amount of water your home or business uses, is completely different from a fire service line. A fire service line is required to provide a separate source of water to fire suppression sprinklers.

Despite these differences, our software treats these two entities similarly. Every meter has a row in the meter table, and every fire service also has a row in the meter table. With this, software can go look up the charge for a fire service line, just as if that line were a meter. This avoided special casing in the main software, and, what I’ve found over the years is when data can be treated similarly, there are fewer checks and things that can go wrong in software. One way of looking at this is the special casing moves from conditional testing in software to the data itself.

In the case of a meter or a fire service, the data contains the decision-making. A column in the meter table contains a value, that if not present means the data represents a meter. If a known value is present, the data represents a fire service line. There are fewer checks in the software.

This isn’t a unique discovery. I certainly didn’t invent it. It just seems hard to practice when you are under a tight deadline for a project, but it seems to pay off handsomely at the end.


The Most Important Aspect Of A Programming Language*

*when used for a municipal programming project

Since June of 2013, I have been working on re-writing my town’s water billing system. This project has included rewriting part of the meter configuration and water billing systems, originally written using Informix 4GL. So this project has included writing new software in Perl and re-writing existing software in Informix 4GL.

The greatest aspect of using Perl — and I can see parallels in Clojure lists, vectors, and maps — is Perl hashes are very flexible. Flexibility is very important in a municipal project. Requirements tend to be come in on the fly, and water consumption and billing rules are certainly not, as folks like to say rocket science, but there are a lot of rules, like what happens when a water meter’s digits rollover to 0000, or you replace a meter and need to calculate consumption based on the old and new meters’ reads. And, most of these rules are documented in one place, the software. And you can only read those rules, if you own the software.

Your programming language needs flexibility, like the ability to cache away a meter reading value that will be displayed on a bill, as opposed to a meter reading value that can be subtracted from another value in which a meter rollover may or may not have occurred. As we now get ready to approve and mail out the first water bills with 3-tier rates, billing for fire service lines (if you have one), as well as the usual administration charge, I credit the [almost] success of the project to the ability to be flexible and modular in our programming language.


Clojure and Perl: Noticing First Differences

This post is not intended to be a contest of one language versus another. We are using Perl for a water project for a number of pragmatic reasons, like folks working on the water project who do not know Clojure and Perl’s DBI support to name two.

Instead, this post is about something I’ve noticed, having used Clojure on a few small projects, and now returning, like Kellog’s Corn Flakes, to Perl again for the first time (since 2000, and then again in 2003).

I am re-writing three major Informix 4GL programs in Perl. They rely heavily on Perl’s DBI, and hence need to store a lot of intermediate data. So, there are quite a few module and subroutine scope variables. For me, the striking difference between Perl (and perhaps languages like it, including 4GL, VB, and so on) and Clojure is my Clojure programs don’t seem to need variables, other than global vars and data bound in let statements.

I believe my appreciation for Clojure’s immutable data is as full as it can be for someone who has worked with the language for a couple of years, so I appreciate that you cannot initialize a variable and modify it. The design of my Clojure programs always was different, despite the fact I could have bound a lot of let variables wherever I needed them, but I just never needed to do that.

The data seemed to come and go. My Clojure programs read in data; manipulate that data, make network I/O calls using that data; and then write some of that input data and new data out to disk.

When I first started learning Clojure, the luminaries said my views of designing would change, and I believe they have.


Cleaning up Perl Programs

I don’t know why, but my Perl programs have always seemed to lend themselves to lengthiness over brevity. That is not the fault of Perl itself. Part of my problem is [re-]learning a new language again for the first time, and keeping all the code in one place for easier debugging, and part of it is I am only just seeing what could be possible in the ability to use both array and hash references.

For example, like variables could be created in a subroutine and returned as part of a  hash ref or array ref. Variables can be declared as needed, something I do not remember as part of Perl, when I used it thirteen and ten years ago, respectively.

As I write several new programs for new rate structure and quarterly water billing, the code will get cleaner.


Problem with vim, netrw (ftp), and DOS File Format

Programmer’s care about their editors. I used Lugaru’s Epsilon for a while, and think both it and Emacs are wonderful editors.  A few years ago, I just started using vim and gVim. I could blame getting Linux certification, but I just started using vim, and the rest is history.

Most recently, I ran into an annoying problem. gVim and vim both started writing Unix file formatted files out as dos format using ftp to a Linux system. I had just recently rebuilt my 32-bit Linux workstation on CentOS 6.4 to be a more compatible environnent with our production and test environments. (It’s 32-bit, because our Informix SE tools and DB are 32-bit.) That is when the trouble started. vim started writing out unix-formatted files as dos-formatted files. I put a post into stackoverflow, and it was suggested that the .vimrc was wrong — specifically that

 set fileformats=unix,dos 

be added to .vimrc — but, after building the 7.4a Beta, the problem corrected itself.

This turned out not to be the case, so after putting in a bug report, it appeared the problem might be with the version of vim, so I retrieved the beta.

After unpacking the beta, I configured using these parameters:

 ./configure --enable-gui=auto --disable-gtktest 

and then ran make followed by make install (as root).

Everything works.


Bye Bye Ubuntu

For the past six years, I have used Ubuntu to do development and at home. Most of the Informix tools installed on it easily, and although I had to make a few adjustments from our production RPM based environment, things ran well. Well, now I have to get all my Informix tool that are installed on our production system working, in order to run the Perl Informix DBI, Bundle::DBD::Informix.  It isn’t going to happen on Ubuntu.

To be fair, this isn’t Ubuntu’s fault, but Ubuntu’s massive UI change — massive at least to me — along with a kind of removal of the user from deeper configuration access — makes the transition back to RPM-based development systems easier to take. The IBM/Informix tools, especially the Perl Informix DBI won’t install on Ubuntu, or, at least neither a consultant nor I can get those tools installed. And, with an emphasis on Perl development now, an RPM system where Bundle::DBD::Informix will install is paramount.

I started using Ubuntu in 2006. I liked the way it installed, as well as the user interface. You could easily install server components, like LAMP. You can still install server components on the latest Ubuntu, but that nice simplistic server and crisp user interface dividing line seems to have blurred and gone way. The unity interface, at least to me, is just plain strange.

While the world is moving towards tablets and smart phones, are these going to be tomorrow’s preferred development environments? Is having a good monitor, keyboard, mouse, and traditional pedestal desktop a thing of the past? While tablets and smartphones might make good Tricorders of today, they don’t seem like good development tools, at least to me. I am referring of course to Ubuntu/Cannonical’s Mark Shuttleworth’s allegedly wanting Ubuntu to run on smart phones. I wish he would still focus on the conventional Linux system, and not encourage the UI design to go screaming off into the weeds.

Just my thoughts. I’ll miss you, Ubuntu. The African musical startup combined with the energy behind this distro was really something I will not soon forget.


Follow

Get every new post delivered to your Inbox.