[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1984
  • Last Modified:

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

0
chrismjohn
Asked:
chrismjohn
  • 2
1 Solution
 
momi_sabagCommented:
0
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now