JAVA STORE VARCHAR FOR BIT DATA IN VARIABLE DB2

Hello Experts,

As always your help is sincerely appreciated.

My question is how can I store the value from a column with a datatype of [VARCHAR (16) FOR BIT DATA] (from DB2 database) in a JAVA variable so that I may use it in another query?

I have a table that looks something like this:

CREATE TABLE "TABLEXYZ"
(
  "OBJECT_ID" VARCHAR (16) FOR BIT DATA NOT NULL,
  "OBJECT_CLASS_ID" VARGRAPHIC(255),
  "CREATE_DATE" TIMESTAMP,
)

The OBJECT_ID column contains a PRIMARY KEY that's used in relationships with other tables. I've included my test code which runs but doesn't return any results. (I've changed some things around so the syntax might not be correct but when I run this example I do not receive any errors). Running the following query directly yields results:

SELECT SOMECOL FROM SOMETBL WHERE PARENT_ID = (SELECT OBJECT_ID FROM TABLEXYZ WHERE OBJECT_CLASS_ID = 'Something' ORDER BY CREATE_DATE FETCH FIRST 1 ROWS ONLY)

package com.ibm.swg.sds.data;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
 
public class Test {
 
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		// TODO Auto-generated method stub
 
		String query = "" +
		"SELECT " +
			"OBJECT_ID " +
		"FROM " +
			"TABLEXYZ " + 
		"WHERE " +
			"OBJECT_CLASS_ID = 'Something')" +
		"ORDER BY " +
			"MODIFY_DATE " +
		"FETCH FIRST 1 ROWS ONLY";
		
		Connection conn;
		
		Statement stmt;
		ResultSet rs;
		
		String obid = null;
		
		
		try
		{
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:db2:DB1", "USER", "PASS");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				obid = rs.getString("OBJECT_ID");
			}
			
			rs.close();
			
			rs = stmt.executeQuery("SELECT SOMECOL FROM SOMETBL WHERE PARENT_ID = '" + obid + "'");
			
			while(rs.next())
			{
				System.out.println(rs.getString("SOMECOL"));
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
		
		
	}
 
}

Open in new window

chrismjohnAsked:
Who is Participating?
 
chrismjohnAuthor Commented:
Momi, Your suggest lead me to the correct answer. I read the variable into a byte array and changed my statements to PreparedStatements which includes the setBytes() function. I've attached the modified code.
package com.ibm.swg.sds.data;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
public class Test {
 
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		// TODO Auto-generated method stub
 
		String query = "" +
		"SELECT " +
			"OBJECT_ID " +
		"FROM " +
			"TABLE1 " + 
		"WHERE " +
			"OBJECT_CLASS_ID = 'SOMETHING' ";
		
		Connection conn;
		
		PreparedStatement stmt;
		ResultSet rs;
		
		byte[] bObj = null;
		
		try
		{
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:db2:MYDB", "MYUSER", "MYPASS");
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();
			
			while(rs.next())
			{
				bObj = rs.getBytes("OBJECT_ID"); 
			}
			
			rs.close();
			
			stmt = conn.prepareStatement("SELECT VALUE1 FROM TABLE2 WHERE PARENT_ID = ?");
			stmt.setBytes(1, bObj);			
			rs = stmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getString("VALUE1"));
			}
			
			rs.close();
			stmt.close();
			conn.close();
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
 
}

Open in new window

0
 
chrismjohnAuthor Commented:
Momi, Thank you for the suggestion. I've updated the question with the working modified code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.