asked on

Insert XML data into BLOB field and fetch to get XML string back

I need to insert XML data into Blob field for faster performance(in comparsion to clob).
for this I need to convert XML to binary and they insert into Oracle 10g database.
Then I need to fetch the XML back.
I tried:-
1) StringBuilder but while fetching it it returns non printable character. (file 1)
2) Base64 Encoding but that fails to insert stating:- ORA-01465: invalid hex number(file 2)

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import java.sql.DriverManager;
import org.apache.commons.codec.binary.Base64;
public class testBinary {
	public static String FEDBClassName = "oracle.jdbc.driver.OracleDriver";
	public static String FEDBUsername = "ds_dev1";
	public static String FEDBPassword = "apple123";
	public static String FEDBUrl = "jdbc:oracle:thin:@dsx05:1521:dsdev5";
	private static Connection fromConn = null;

	/**
	 * @param args
	 *       encodedText = new String(Base64.encodeBase64(clearText.getBytes()));
      System.out.println("Encoded: " + encodedText);
      System.out.println("Decoded:" 
          + new String(Base64.decodeBase64(encodedText.getBytes())));
	 */
	public static void main(String[] args) throws Exception{

		String s = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
		byte[] bytes = s.getBytes();
		 //all chars in encoded are guaranteed to be 7-bit ASCII
	// base 64 way	 byte[] binary = Base64.encodeBase64(bytes);
		StringBuilder binary = new StringBuilder();
		for (byte b : bytes) {
			int val = b;
			for (int i = 0; i < 8; i++) {
				binary.append((val & 128) == 0 ? 0 : 1);
				val <<= 1;
			}
		}
		System.out.println("Input Text =" + s );
		System.out.println("Input binary =" + binary );
		
		String binaryStr = binary.toString();
//checking what is passed		
		//new testBinary().decode(binary);
		
		
		Class.forName(FEDBClassName);
		fromConn = DriverManager.getConnection(FEDBUrl, FEDBUsername,
				FEDBPassword);		
		new testBinary().insertXMLData( fromConn, binaryStr);
		new testBinary().retrieveXMLData( fromConn, binaryStr);
	}

	private void decode(StringBuilder value) {
		for (int i = 0; i < value.length(); i += 8) {
			String binary = value.substring(i, i + 8);
			int decimal = Integer.valueOf(binary);
			char[] ch = Character.toChars(decimal);
			System.out.print(ch);	
		}
		System.out.println();
		
		System.out.println("above is output");
	}
	
	
	
	
	public static String insertXMLData(Connection frmconn, String XMLString){
		PreparedStatement pstmt = null;
		String sqlQuery = null;
			
			sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
		String xmlString =null;
		try {
			pstmt = frmconn.prepareStatement(sqlQuery);
			pstmt.setLong(1, new Long(9));
			pstmt.setString(2, XMLString);

			
			ResultSet refRs = pstmt.executeQuery();
			/*while(refRs.next()){
				xmlString=refRs.getString(1);
				if(xmlString==null || xmlString.trim().length()<=0){
					BLOB clob= (BLOB)refRs.getObject(2);
					xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
				}*/
//				Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
//				System.out.println("Binary Stream ="+blob.getBinaryStream());
//				int length1 = (int)blob.length();
//				//long l = 0l;
//				byte[] bytes =blob.getBytes(1, length1);
//				
//				
//				byte[] decoded = Base64.decodeBase64(bytes);
//				/*StringBuilder binary = new StringBuilder();
//				for (byte b : bytes) {
//					int val = b;
//					for (int i = 0; i < 8; i++) {
//						binary.append((val & 128) == 0 ? 0 : 1);
//						val <<= 1;
//					}
//				}*/
//				System.out.println("'" + blob + "' to binary: " + XMLString);
//				
				///new testBinary().decode(XMLString);
			}
		 catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
		return xmlString;
	}
	
	
	
	
	
	
	public static String retrieveXMLData(Connection frmconn, String XMLString){
		PreparedStatement pstmt = null;
		String sqlQuery = null;
			sqlQuery = "select XMLData from TestBlob where id = 9";
			//sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
		String xmlString =null;
		try {
			pstmt = frmconn.prepareStatement(sqlQuery);
			//pstmt.setLong(1, new Long(1));
			//pstmt.setString(2, XMLString);
			
			//retreiveXMLDataFromFE(Connection frmconn, String entType, String entId)
			ResultSet refRs = pstmt.executeQuery();
			/*while(refRs.next()){
				xmlString=refRs.getString(1);
				if(xmlString==null || xmlString.trim().length()<=0){
					BLOB clob= (BLOB)refRs.getObject(2);
					xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
				}*/
			    refRs.next();
				Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
				//System.out.println("This is Binary Stream ="+blob.getBinaryStream());
				int length1 = (int)blob.length();
				//long l = 0l;
				byte[] bytes =blob.getBytes(1, length1);
				
				//System.out.println("This is Byte Array ="+bytes);				
				//byte[] decoded = Base64.decodeBase64(bytes);
				StringBuilder binary = new StringBuilder();
				for (byte b : bytes) {
					int val = b;
					for (int i = 0; i < 8; i++) {
						binary.append((val & 128) == 0 ? 0 : 1);
						val <<= 1;
					}
				}
				System.out.println("Output binary: " + XMLString);
				StringBuilder sb = new StringBuilder(XMLString);
				System.out.println("converting to StringBuilder" + sb);
				
				new testBinary().decode(sb);
			}
		 catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
		return xmlString;
	}
	
}

Open in new window

import java.sql.Blob;
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import oracle.sql.BLOB;
	import java.sql.DriverManager;
	import org.apache.commons.codec.binary.Base64;

	public class testBinaryBase64Way {
		public static String FEDBClassName = "oracle.jdbc.driver.OracleDriver";
		public static String FEDBUsername = "ds_dev1";
		public static String FEDBPassword = "apple123";
		public static String FEDBUrl = "jdbc:oracle:thin:@dsx05:1521:dsdev5";
		private static Connection fromConn = null;

		/**
		 * @param args
		 *       encodedText = new String(Base64.encodeBase64(clearText.getBytes()));
	      System.out.println("Encoded: " + encodedText);
	      System.out.println("Decoded:" 
	          + new String(Base64.decodeBase64(encodedText.getBytes())));
		 */
		public static void main(String[] args) throws Exception{

			System.out.println("testBinaryBase64Way");
			String s = "Helloworld";//"<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
			byte[] bytes = s.getBytes();
			 //all chars in encoded are guaranteed to be 7-bit ASCII
		// base 64 way
			byte[] binary = Base64.encodeBase64(bytes);
			/*StringBuilder binary = new StringBuilder();
			for (byte b : bytes) {
				int val = b;
				for (int i = 0; i < 8; i++) {
					binary.append((val & 128) == 0 ? 0 : 1);
					val <<= 1;
				}
			}
			System.out.println("'" + s + "' to binary: " + binary);*/
			String binaryStr = binary.toString();
	//checking what is passed		
			//new testBinary().decode(binary);
			System.out.println("testBinaryBase64Way" + binaryStr);

			
			Class.forName(FEDBClassName);
			fromConn = DriverManager.getConnection(FEDBUrl, FEDBUsername,
					FEDBPassword);		
			new testBinary().insertXMLData( fromConn, binaryStr);
			//new testBinary().retrieveXMLData( fromConn, binaryStr);
		}

		private void decode(StringBuilder value) {
			for (int i = 0; i < value.length(); i += 8) {
				String binary = value.substring(i, i + 8);
				int decimal = Integer.valueOf(binary);
				char[] ch = Character.toChars(decimal);
				System.out.print(ch);
			}
			System.out.println("above is output");
		}
		
		public static String insertXMLData(Connection frmconn, String XMLString){
			PreparedStatement pstmt = null;
			String sqlQuery = null;
				//sqlQuery = "select XMLData from TestBlob";
				sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
			String xmlString =null;
			try {
				pstmt = frmconn.prepareStatement(sqlQuery);
				pstmt.setLong(1, new Long(1));
				pstmt.setString(2, XMLString);
				
				//retreiveXMLDataFromFE(Connection frmconn, String entType, String entId)
				ResultSet refRs = pstmt.executeQuery();
				/*while(refRs.next()){
					xmlString=refRs.getString(1);
					if(xmlString==null || xmlString.trim().length()<=0){
						BLOB clob= (BLOB)refRs.getObject(2);
						xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
					}*/
//					Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
//					System.out.println("Binary Stream ="+blob.getBinaryStream());
//					int length1 = (int)blob.length();
//					//long l = 0l;
//					byte[] bytes =blob.getBytes(1, length1);
//										
//					byte[] decoded = Base64.decodeBase64(bytes);
//					/*StringBuilder binary = new StringBuilder();
//					for (byte b : bytes) {
//						int val = b;
//						for (int i = 0; i < 8; i++) {
//							binary.append((val & 128) == 0 ? 0 : 1);
//							val <<= 1;
//						}
//					}*/
//					System.out.println("'" + blob + "' to binary: " + XMLString);
//					
					///new testBinary().decode(XMLString);
				}
			 catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					pstmt.close();
				} catch (SQLException sqlex) {
					sqlex.printStackTrace();
				}
			}
			return xmlString;
		}
		
		
		
		
		
		
		public static String retrieveXMLData(Connection frmconn, String XMLString){
			PreparedStatement pstmt = null;
			String sqlQuery = null;
				sqlQuery = "select XMLData from TestBlob";
				//sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
			String xmlString =null;
			try {
				pstmt = frmconn.prepareStatement(sqlQuery);
				//pstmt.setLong(1, new Long(1));
				//pstmt.setString(2, XMLString);
				
				//retreiveXMLDataFromFE(Connection frmconn, String entType, String entId)
				ResultSet refRs = pstmt.executeQuery();
				/*while(refRs.next()){
					xmlString=refRs.getString(1);
					if(xmlString==null || xmlString.trim().length()<=0){
						BLOB clob= (BLOB)refRs.getObject(2);
						xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
					}*/
				refRs.next();
					Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
					System.out.println("Binary Stream ="+blob.getBinaryStream());
					int length1 = (int)blob.length();
					//long l = 0l;
					byte[] bytes =blob.getBytes(1, length1);
					
					
					//byte[] decoded = Base64.decodeBase64(bytes);
					StringBuilder binary = new StringBuilder();
					for (byte b : bytes) {
						int val = b;
						for (int i = 0; i < 8; i++) {
							binary.append((val & 128) == 0 ? 0 : 1);
							val <<= 1;
						}
					}
					System.out.println("'" + blob + "' to binary: " + XMLString);
					StringBuilder sb = new StringBuilder(XMLString);
	//				new testBinary().decode(sb);
				}
			 catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					pstmt.close();
				} catch (SQLException sqlex) {
					sqlex.printStackTrace();
				}
			}
			return xmlString;
		}
		
	}

Open in new window

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Sperber

ASKER

i used but it didnot work.
currently the bytes get inserted correctly into DB but while reading out from the DB I see only half the binary data that get displayed.

That is:-
Input Text =A
Input binary =01000001
Output binary=1001

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import java.sql.DriverManager;
import org.apache.commons.codec.binary.Base64;
public class testBinary {
	public static String FEDBClassName = "oracle.jdbc.driver.OracleDriver";
	public static String FEDBUsername = "ds_dev1";
	public static String FEDBPassword = "pwd";
	public static String FEDBUrl = "jdbc:oracle:thin:@dsx05:1521:dsdev5";
	private static Connection fromConn = null;

	
	public static void main(String[] args) throws Exception{
		String s = "A";//<?xml version=\"1.0\" encoding=\"UTF-8\"?>
		byte[] bytes = s.getBytes("UTF-8");//
		 //all chars in encoded are guaranteed to be 8-bit ASCII
		StringBuilder binary = new StringBuilder();
		for (byte b : bytes) {
			int val = b;
			for (int i = 0; i < 8; i++) {
				binary.append((val & 128) == 0 ? 0 : 1);
				val <<= 1;
			}
		}
		System.out.println("Input Text =" + s );
		System.out.println("Input binary =" + binary);
		
		String binaryStr = binary.toString();		
		
		Class.forName(FEDBClassName);
		fromConn = DriverManager.getConnection(FEDBUrl, FEDBUsername,
				FEDBPassword);		
		new testBinary().insertXMLData( fromConn, binaryStr);
		new testBinary().retrieveXMLData( fromConn);
	}

	private void decode(StringBuilder value) {
		for (int i = 0; i < value.length(); i += 8) {
			String binary = value.substring(i, i + 8);
			int decimal = Integer.valueOf(binary);
			char[] ch = Character.toChars(decimal);
			System.out.print(ch);	
		}
		System.out.println();
		
		System.out.println("above is output");
	}
	
	
	
	
	public static String insertXMLData(Connection frmconn, String XMLString){
		PreparedStatement pstmt = null;
		String sqlQuery = null;
			
			sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
		String xmlString =null;
		try {
			pstmt = frmconn.prepareStatement(sqlQuery);
			pstmt.setLong(1, new Long(9));
			pstmt.setString(2, XMLString);

			
			ResultSet refRs = pstmt.executeQuery();
			/*while(refRs.next()){
				xmlString=refRs.getString(1);
				if(xmlString==null || xmlString.trim().length()<=0){
					BLOB clob= (BLOB)refRs.getObject(2);
					xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
				}*/
//				Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
//				System.out.println("Binary Stream ="+blob.getBinaryStream());
//				int length1 = (int)blob.length();
//				//long l = 0l;
//				byte[] bytes =blob.getBytes(1, length1);
//				
//				
//				byte[] decoded = Base64.decodeBase64(bytes);
//				/*StringBuilder binary = new StringBuilder();
//				for (byte b : bytes) {
//					int val = b;
//					for (int i = 0; i < 8; i++) {
//						binary.append((val & 128) == 0 ? 0 : 1);
//						val <<= 1;
//					}
//				}*/
//				System.out.println("'" + blob + "' to binary: " + XMLString);
//				
				///new testBinary().decode(XMLString);
			}
		 catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
		return xmlString;
	}
	
	
	
	
	
	
	public static String retrieveXMLData(Connection frmconn){
		PreparedStatement pstmt = null;
		String sqlQuery = null;
			sqlQuery = "select XMLData from TestBlob where id = 9";
		String xmlString =null;
		try {
			pstmt = frmconn.prepareStatement(sqlQuery);
			ResultSet refRs = pstmt.executeQuery();
			/*while(refRs.next()){
				xmlString=refRs.getString(1);
				if(xmlString==null || xmlString.trim().length()<=0){
					BLOB clob= (BLOB)refRs.getObject(2);
					xmlString=clob.getSubString(1, new Long(clob.length()).intValue());
				}*/
			    refRs.next();
				Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
				//System.out.println("This is Binary Stream ="+blob.getBinaryStream());
				int length1 = (int)blob.length();
				//long l = 0l;
				InputStream is = blob.getBinaryStream();
				//is.read();
				
				for(int ch = is.read(); ch != -1; ch = is.read()){
					if (ch == '<')
				System.out.print("&lt;");
				System.out.print(ch);
				}		
				System.out.println();
				System.out.println("Above is output");
				/*
				byte[] bytes =blob.getBytes(1, length1);
				StringBuilder strbuilder = new StringBuilder();
				for (byte b : bytes) {
					int val = b;
					for (int i = 0; i < 8; i++) {
						binary.append((val & 128) == 0 ? 0 : 1);
						val <<= 1;
					}
				}
				System.out.println("Output binary: " + XMLString);
				StringBuilder sb = new StringBuilder(XMLString);
				System.out.println("converting to StringBuilder" + sb);
				*/
				//new testBinary().decode(sb);
			}
		 catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
		return xmlString;
	}
	
}

Open in new window

Steve Sperber

ASKER

this is because 2 binary characters are converted into 1.

CEHJ

>>pstmt.setString(2, XMLString);

should be

pstmt.setString(2, byteArray);

Open in new window

CEHJ

Oops! Sorry

pstmt.setBytes(2, byteArray);

Open in new window

Steve Sperber

ASKER

I am getting all the binary characters.
to display the input character I am using attached function to display characters.

this gives me ? as output.
when I debug this it is showing me 2 funny characters in the character array.

private void decode(StringBuilder value) {
		for (int i = 0; i < value.length(); i += 8) {
			String binary = value.substring(i, i + 8);
			int decimal = Integer.valueOf(binary);
//Below shows 2 funny characters in the debug mode.
			char[] ch = Character.toChars(decimal);
 //Below Resulting character is ? Character.		
			System.out.print(ch);	
		}
	}

Open in new window

Steve Sperber

ASKER

Thanks.
pstmt.setBytes(2, byteArray);

worked to get all the binary characters.

CEHJ

So you're ok now?

Steve Sperber

ASKER

I had to make some tweaks. finally this is the final code. Anyway byte array idea worked as you pointed out

import java.io.InputStream;
	import java.sql.Blob;
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import oracle.sql.BLOB;
	import java.sql.DriverManager;
	import org.apache.commons.codec.binary.Base64;
	public class testBlobXMLField {
		public static String FEDBClassName = "oracle.jdbc.driver.OracleDriver";
		public static String FEDBUsername = "ds_dev1";
		public static String FEDBPassword = "passwd";
		public static String FEDBUrl = "jdbc:oracle:thin:@dsx05:1521:dsdev5";
		private static Connection fromConn = null;
		
		public static void main(String[] args) throws Exception{
			String s = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";//"A"; //"<?xml version=\"1.0\" encoding=\"UTF-8\"?>";//
			byte[] bytes = s.getBytes("UTF-8");//
		
			Class.forName(FEDBClassName);
			fromConn = DriverManager.getConnection(FEDBUrl, FEDBUsername,
					FEDBPassword);		
			insertXMLData( fromConn, bytes);
			retrieveXMLData( fromConn);
		}

		private static void decode(StringBuilder value) {
			String outstring = "";
			for (int i = 0; i<value.length(); i+=8){
			String bytes = value.substring(i,i+8);
			int charVal = Integer.valueOf(bytes,2); // base 2 string
			char c = (char)charVal;
			outstring += String.valueOf(c);
			}
			System.out.println(outstring);
		}
		
		public static String insertXMLData(Connection frmconn, byte[] byteArray){
			PreparedStatement pstmt = null;
			String sqlQuery = null;
				
				sqlQuery = "INSERT INTO TestBlob (id, XMLData)VALUES (?,?)";
			String xmlString =null;
			try {
				pstmt = frmconn.prepareStatement(sqlQuery);
				pstmt.setLong(1, new Long(19));
				pstmt.setBytes(2, byteArray);
				
				
				ResultSet refRs = pstmt.executeQuery();
				
				}
			 catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					pstmt.close();
				} catch (SQLException sqlex) {
					sqlex.printStackTrace();
				}
			}
			return xmlString;
		}
		
		
		
		
		
		
		public static String retrieveXMLData(Connection frmconn){
			PreparedStatement pstmt = null;
			String sqlQuery = null;
				sqlQuery = "select XMLData from TestBlob where id = 19";
			String xmlString =null;
			try {
				pstmt = frmconn.prepareStatement(sqlQuery);
				ResultSet refRs = pstmt.executeQuery();
				
				    refRs.next();
					Blob blob= refRs.getBlob("XMLData"); //getBinaryStream(1);
						
					byte[] bytes =blob.getBytes(1, (int)blob.length());
					StringBuilder strbuilder = new StringBuilder();
					for (byte b : bytes) {
						int val = b;
						for (int i = 0; i < 8; i++) {
							strbuilder.append((val & 128) == 0 ? 0 : 1);
							val <<= 1;
						}
					}
					System.out.println("Output binary: " + strbuilder);
					StringBuilder sb = new StringBuilder(strbuilder);
					System.out.println("converting to StringBuilder" + sb);
					
					decode(strbuilder);
				}
			 catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					pstmt.close();
				} catch (SQLException sqlex) {
					sqlex.printStackTrace();
				}
			}
			return xmlString;
		}
		
	}

Open in new window

CEHJ

You need to accept my comment http:#34118812

insertXMLData should actually be using executeUpdate, not executeQuery. You don't seem to be using the return value...

Steve Sperber

ASKER

Good suggestions all the way

CEHJ