SquirrelSQL Client and Informix

I’m doing a database course in my faculty. First of all, I must say that I and some of my friends disagree on the DBMS software our teachers use to introduce SQL and common database features. They use Informix, IMHO, a somewhat deprecated, creepy and privative DBMS. But the purpose of this post is not about complaining, instead, I’ll show the features of an impressive opensource cross-platform interactive SQL client I’ve discovered recently (squirrel-sql client) and a quick “for dummies” guide to connect to a remote Informix database server (in this particular case, my university DBMS):

As the project main page states: SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.

But it’s far better than that, with the included plugins you can have a full-fledged interactive SQL client with syntax highlighting, code completion, batch execution and SQL sentence bookmarks among other features. It has lots of database connectors ready to be used with the most popular database engines without forgetting about rare ones. Unfortunately, our “beloved” informix is a weird case, and it’s not included by default. In any case, don’t worry, the process to add an Informix connector is not complicated at all, and I’ll describe it step by step:

  1. Dowload the .jar from the official sourceforge squirrel-sql project page.
  2. I assume that you have Java Runtime Environment installed correctly on your machine, if it’s not the case, install it first (hint: java.sun.com).
  3. Download the latest Informix JDBC driver from IBM website. And uncompress it wherever you want. You can use bugmenot if you don’t want to register. If you’re using Gentoo GNU/Linux, just do an “emerge dev-java/jdbc-informix” and you’re done.
  4. Execute the .jar file “java -jar squirrel-sql-2.0final-install.jar “.
  5. The installation is straightforward (next, next, yes…), as opposed to Informix DBMS installation procedure (a real PITA).
  6. Configure your VPN connection to FIB using their howto.
  7. Now, just fill the missing fields according to the following screenshots:

squirrel-sql splash screen
jdbc driver blueprint
Beautiful splash screen, isn't it ? Remember to click on "list drivers" after loading the jars.
driver alias blueprint connection settings
Username is your "raco" id. Driver properties-> sub_bbdd
is your database.
catalog view sql interactive editor view
View of the databases and catalog. SQL editor view.

I’ve been trying this program for a while and I should say that it works nicely with standard sql select queries, but there are bugs with table and procedure creation (JDBC reports syntax errors (SQLState -201) where none exist (i’ll try if the same code works with informix sql editor). I’m almost sure that those problems come from the buggy IBM’s JDBC drivers, so it’s not a squirrel-sql editor fault.

I wrote a quick tutorial a few months ago (in catalan) to connect to Informix via socks5 faculty’s gateway using unixODBC and tsocks, it’s now deprecated (there was no VPN then).