Tag: blob

How to read and write CLOB fields

The Character Large Object (or CLOB) is a commonly found in databases and used to store high quantity of characters.

At MySQL, for example, this field is called MEMO.

Writing CLOB field

The method setAsciiStream of PreparedStatement allow to pass data to a CLOB.

ps.setAsciiStream(bindPosition, inputStream, textLength);

bindPosition – Position in PreparedStatment’s CLOB field.
inputStream – Used to pass data.
textLength – Data (text) length.

Full code:

String sql = "INSERT INTO TABLE (text) VALUES(?)";
		try{
			String txt = readTxtFile();
			ByteArrayInputStream bais = new ByteArrayInputStream(txt.getBytes());

			PreparedStatement ps = conexao.prepareStatement(sql);
			//CLOB is '?' at first position
			ps.setAsciiStream(1, bais, txt.length());

			ps.execute();

			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}

Retrieving CLOB data

We’re using SELECT clause, this clause returns a ResultSet to fetch data and, calling getClob method, passing column name or your position in the query, it give to you a Clob object.

rs.getClob("xml");

Full code:

String sql = "SELECT xml FROM TEST";
		try{
			PreparedStatement ps = conexao.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				Clob clob = rs.getClob("xml");
				BufferedReader reader = new BufferedReader(clob.getCharacterStream());
				StringBuffer strBuf = new StringBuffer();

				String linha = null;
				while((linha = reader.readLine()) != null){
					strBuf.append(linha);
//Character.LINE_SEPARATOR insert break line
					strBuf.append((char)Character.LINE_SEPARATOR);
				}

				System.out.println("=========== CLOB ===========");
				System.out.println(strBuf.toString());
			}

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

That’s it! Easy and painless 😉

Download full code here

To write a BLOB see this tutorial.


Saving files in BLOB table column of a data base

To insert a file, is it in any format, you need call the method setBinaryStream, implemented by PreparedStatement.

PreparedStatemente.setBinaryStream(int index, Inputstream is, int length);

In sample, we set a table called FILEthat contains BLOB column called BIN.

//Normal connection, as any JDBC connection
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<IP>:<PORT>:<SID>","<USER>","<PASSWORD>");

//Reading the file and retrieving an InputStream
File file= new File("<COMPLETE_FILE_PATH>");
FileInputStream fis = new FileInputStream(file);

//Preparing statement
PreparedStatement ps = conn.prepareStatement("INSERT INTO FILE(bin) VALUES(?)");

//Passing InputStream and file length
ps.setBinaryStream(1, fis, (int)file.length());

ps.execute();

ps.close();
conn.close();

I used Oracle 8i to execute this sample. I haven’t a MySQL/PostgreSQL/MS SQL Server in my dispose, then you’ll responsible for testing in this data bases and send me the results, OK 😉

Thanks! Until next time!


  • Advertisement

  • Advertisement

  • Copyright © 1996-2010 André L. S.. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress