75 ways to demystify DB2: # 41 : ExpertAdvice: Sample Java program to retrieve resultset using getString API

Body

A quick demo showing Driver Manager Connection to DB2 on windows, retrieving resultset using getString JDBC API from a table that has empty, valid data and NULL values.

Environment:

DB2 10.5 FP3 on Windows

JCC driver: 3.67.27

db2 "create table t100 (col1 char(10))"
db2 "insert into t100 values('','mary','NULL')"

C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2 "select * from t100"

COL1
----------

mary
NULL

  3 record(s) selected.
 
 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.io.PrintWriter;
import java.sql.Types;
import java.util.*;
public class test
{
    public static void main(String[] args) {
        try {
        
                   String hostname=args[0];

                   // Database name
                   String dbName=args[1];
                   String userID=args[2];
                   String passWord=args[3];
                   String traceOut=args[4];

                   Class.forName("com.ibm.db2.jcc.DB2Driver");
                                    String URL="jdbc:db2://" + hostname + "/" + dbName + ":traceFile=" + traceOut + ";";

                   System.out.println("Trying to connect using JCC T4 driver");
                   Connection con = DriverManager.getConnection(URL, userID, passWord);
                   System.out.println("Connected to database\n");

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "select * from t100";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
             System.out.println("\n" + query + " = " + rs.getString(1));
             
            }

            rs.close();
            stmt.close();
            con.close();
        }
        
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

=======

C:\Program Files\IBM\SQLLIB\java\jdk\bin>java test xxx:50000 test26 xxxx xxxx c:\\temp\\jcctrace.txt"
Trying to connect using JCC T4 driver
Connected to database

select * from t100 =

select * from t100 = mary

select * from t100 = NULL

===

Here is what the  JCC trace snippet looks like:

Data received from server:
[jcc][t4]        RECEIVE BUFFER: QRYDTA              (ASCII)           (EBCDIC)
[jcc][t4] 0000   0021D0530003001B  241BFF00000000FF  .!.S....$.......  ..}.............
[jcc][t4] 0010   000000046D617279  FF000000044E554C  ....mary.....NUL  ...._/.`.....+.<
[jcc][t4] 0020   4C                                  L                 <               
[jc

============
Data returned to the application:

[jcc][Time:2015-06-03-20:35:21.707][Thread:main][ResultSet@6688467b]next () called
[jcc][Time:2015-06-03-20:35:21.707][Thread:main][ResultSet@6688467b]next () returned true
[jcc][Thread:main][SystemMonitor:stop] core: 0.294855ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183]
[jcc][SystemMonitor:start]
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]getString (1) called
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]getString () returned
[jcc][Thread:main][SystemMonitor:stop] core: 0.171308ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183] ================> getString API does not return anything, since the data in that column is empty/blank
[jcc][SystemMonitor:start]
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]next () called
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]next () returned true
[jcc][Thread:main][SystemMonitor:stop] core: 0.106574ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183]
[jcc][SystemMonitor:start]
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]getString (1) called
[jcc][Time:2015-06-03-20:35:21.708][Thread:main][ResultSet@6688467b]getString () returned mary
[jcc][Thread:main][SystemMonitor:stop] core: 0.10854799999999999ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183]================> returned mary
[jcc][SystemMonitor:start]
[jcc][Time:2015-06-03-20:35:21.709][Thread:main][ResultSet@6688467b]next () called
[jcc][Time:2015-06-03-20:35:21.709][Thread:main][ResultSet@6688467b]next () returned true
[jcc][Thread:main][SystemMonitor:stop] core: 0.12039ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183]
[jcc][SystemMonitor:start]
[jcc][Time:2015-06-03-20:35:21.709][Thread:main][ResultSet@6688467b]getString (1) called
[jcc][Time:2015-06-03-20:35:21.709][Thread:main][ResultSet@6688467b]getString () returned NULL
[jcc][Thread:main][SystemMonitor:stop] core: 0.10578499999999999ms | network: 0.0ms | server: 0.0ms [STMT@-1267191183]=================> returned NULL as NULL is in that column
[jcc][SystemMonitor:start]

Thanks for reading!

Please leave a comment if there are any questions or feedback.