simple java program to test JDBC

ODBC is not an enigma to me, I can write C, VBScript or vba / vb code to use ODBC and interrogate databases.  It’s simple and easy.  what is a bit more of an enigma is java.  I’ve had to write java code (but only simple) when I’m doing load testing and editing scripts in openScript.

But, I quite often want to know what is the exact version of a java driver, or whether it’s actually working or is it right, or does it work, so now that is simple: 

The main purpose of this script was used to ensure that the firewalls between servers are open enough for JDBC – another super frustrating area.

This example is used to test AS/400 ODBC – but you get the picture.

You’ll need jt400.jar too

You’ll need a jdk, as this has the compiler, also required for what we are doing.

"C:\Program Files\Java\jdk1.8.0\bin\javac" dbconnect.java

Note that the above will create u a class file, then use it to run.

H:\scripts\JDBC>"C:\Program Files\Java\jdk1.8.0\bin\java" -cp .;.\jt400.jar dbconnect

Program worked if there are no errors above!

 

dbconnect.java contains:

import java.io.*;
import java.util.*;
import java.sql.*;
   
public class dbconnect
{
    public static void main(String[] args) {
    try {
    Properties props = new Properties();
    props.load(new FileInputStream("connect.properties"));
    String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
    String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
    //Connect to iSeries
    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection(URL, props.getProperty("userId").trim(), props.getProperty("password").trim());
    System.out.println("Creating statement...");
        Statement stmt = conn.createStatement();
    String sql = "SELECT COUNT(1) as RecCount FROM " + props.getProperty("owner").trim() + ".F0101";
        ResultSet rs = stmt.executeQuery(sql);
        //STEP 5: Extract data from result set
        int RecordCount=0;
    while(rs.next())
        {
          //Retrieve by column name
          RecordCount  = rs.getInt("RecCount");
    }
         System.out.print("\nF0101 count: " + RecordCount + "\n");

       rs.close();
       conn.close();
}
catch (Exception e) {
System.out.println(e);
}    
System.out.println("Program worked if there are no errors above!"); // Display the string.
}
}

You also need the following in your working dir, called connect.properties

#I-series ip or host name
local_system=10.148.86.181
#I-series UserId, used for login and library list
userId=JDE
#I-series Password
password=goAwayTurk3y
#Schema
owner=PRODDTA