Technical Blog Post
Abstract
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.
UID
ibm11141048