JDBC test harness–oracle based

This is another simple program, very similar to my AS/400 based JDBC text harness – this is using oracle drivers and oracle connections.

It’s also handy when OATs will not connect to your RAC based database and you are getting angry because you’ve tried about 100 variations of the stupid RAC based JDBC connect string.  And you know you’ve had it working before.  Conspiracy theories start swimming in your head “database firewall, must be some sort of firewall”…  But before you flush your credibility down the drain (and get your picture on the network guys community dart board), try and test it yourself!

BTW, JDBC RAC oracle connection string looks like this, 2 node RAC.

Jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=vserv-rh-db32.shannon.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=vserv-rh-db31.shannon.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ERP91DEVSRV.shannon.com)))

first, create two files

dbconnect.java

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("dbconnect.properties"));
    String DRIVER = "oracle.jdbc.driver.OracleDriver";
    String URL = "jdbc:oracle:thin:@" + props.getProperty("local_system").trim() + ":1521:" + props.getProperty("serviceName").trim() + "";
    //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.
}
}

second (note that there is a slight name change, previous was connect.properties, I did not like that).

dbconnect.properties

#OracleHostName
local_system=e1ent2.mits.local
#I-series UserId, used for login and library list
userId=JDE
#I-series Password
password=S0mP@s5W
#Schema
owner=TESTDTA
#serviceName
serviceName=orcl

Compile and run

you need a jdk to compile, www.oracle.com find java

then does not matter, 64bit or 32 bit…javac and java

make sure that you have the oracle jdbc drivers, ojdbc6.jar or ojdbc5.jar (\\e1dev2\c$\oracle\product\11.2.0\client_1\jdbc\lib) in a dir that is similar to the above you’ll find them (like yoda I type).

compile like last time

C:\Java\jdk1.7.0_67\bin\javac dbconnect.java

or

C:\Java32\jdk1.7.0_67\bin\javac dbconnect.java

Run:

C:\Java\jre7\bin\java -cp .\ojdbc6.jar;. dbconnect

C:\Users\shannonm>C:\Java\jre7\bin\java -cp .\ojdbc6.jar;. dbconnect
Creating statement...

F0101 count: 1130
Program worked if there are no errors above!