SQL IRIS Editor and IRIS JAVA CONNECTION

WIN SQL is the normal editor used by most of the users .But we can't download large amount of data using winsql . So I have written a tutorial how to connect with a new Java based editor called Squirrel SQL which can easily download or export data in excel or any other formats. Also I included a Java JCBC connection program to connect with the IRIS database especially a mirroring/failover server.

SQL Java based Editor to Export large amount of data and Java JDBC program for IRIS connection

SQL Java based Editor to Export large amount of data

WinSql is the editor used normally to extract data from Iris database but unfortunately there is no option to export large amount of data without the licensed winsql.

The solution is to use java-based editor which is called Squirrel SQL. This is a java-based editor and you can export large amount of data from the editor after you do the sql fetch from the IRIS database. This is open source SQL client built in Java, which uses JDBC to connect to IRIS database.

Features of Squirrel SQL

• Java 19 Compatibility
• Multiple caret/cursor editing
• Global Preferences and New Session Properties search
• Several improvements of Saved Sessions (feature to save and restore all SQL editors of a Session)
• Configurable right mouse menus
• Redesigned add/edit JDBC-Driver dialog

Steps to Install Squirrel SQL

Squirrel SQL can download from Squirrel Website https://squirrel-sql.sourceforge.io/

Steps for Connecting to IRIS database

1. Adding a driver to Squirrel Sql

• Click on “+” icon to create a new driver as shown below

• In the “Add Driver Dialog Box”, select the “Extra Class Path” and click “Add” to add a new entry for the “Intersystems-jdbc-3.2.0.jar” (jdbc driver jar file) as shown below. If you have installed IRIS on C drive on your local machine, this will be the normal path based on the IRIS version

 C:\InterSystems\IRISHealth2\dev\java\lib\JDK18\intersystems-jdbc-3.2.0.jar.

• As shown in the screenshot below,

• Enter a name for the driver as “Intersystems IRIS” (Choose any meaningful name)
• Enter the Example URL as jdbc:IRIS://<host>:<port>/<database>
• Website URL is optional.
• Click the “List Drivers” button on the right and select “com.intersystems.jdbc.IRISDriver” as shown below.

• Click OK to save the driver entry. Now you can see the driver on the left menu bar under drivers.

2. Adding an Alias(connection) based on the Driver

• Select the “Aliases” tab on the left side of the squirrel sql and click on “+” to add a new alias as shown below.

• In the “Add Alias” window enter a meaningful name for the Alias.
• Select the IRIS driver which we created newly from the dropdown menu. After selecting the driver, URL format will be populating as in the newly created driver config. Edit the URL by adding the correct hostname or IP address, port number and database namespace.

For instance : jdbc:IRIS://00.00.00.00.00:12345/TEST-TRAK

• Enter the username and password for the IRIS databse that has SQL privileges
• Click on test button and verify the connection is successful.

• Click OK to save the new Alias

3. Connecting to IRIS Database

• Double click on the new created Alias to connect to the databse and the squirrel editor will open, and you can try with the sql queries.

JDBC Connection for IRIS database for writing programs

import java.sql.*;
import com.intersystems.jdbc.*;
import java.util.logging.*;
import java.io.IOException;
import java.util.*;

public class Extract {
    public static Connection TrakCache() throws Exception
    {
             IRISDataSource ds = new IRISDataSource();
             Connection conn = null;

             ds.setURL("jdbc:IRIS://1.12.333.444:12345/NAMESPACE-TRAK");
             ds.setUser("username");
             ds.setPassword("Password");

              try
              {
                        conn = ds.getConnection();
              }
              catch (Exception e) {
                System.out.println("catch" +conn);
                //You can write another connection here if automatically fail over to another server.
              }
              return conn;
    }
}