Utility and Setup for Testing JDBC Connections

This is a Windows based solution.  Sorry Linux fans.  The utility is available in a Linux format, so I would assume this could easily be ported to Linux.

This was used for testing Oracle connections, but the utility should support any standard jdbc connection.

1.  Ensure java is installed and in your PATH.

2.  Download Jisql from http://www.xigole.com/software/jisql/jisql.jsp.  Note the names in the uncompressed LIB directory.

3.  Ensure you have the Oracle JDBC drivers installed and note the path to the jar files.

4.  Now, you should be ready for some simple testing.  A simple batch file like this will do the trick.
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using SID >> logfile.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
         -driveroraclethin -cstring jdbc:oracle:thin:@address-scan:1521:sidname-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using SERVICE_NAME >> z:\mike\runit.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
    -driver oraclethin -cstring jdbc:oracle:thin:@//address-scan:1521/servicname.yourcompany.com -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT

5.  You can alternatively use TNS entries for testing the connections.  This was the best way I found to test a more complicated setup, such as load balanced, fault tolerant connection.  Something like this: <em>(note - I made a copy of the tnsnames.ora file to c:\temp then trimmed it down to just have the two entries I wanted to test)</em>
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using TNSENTRY>> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@TNSENTRY-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using TNSENTRY >> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@WAREHOUSE3 -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT