Retrieving BLOB Values with getBytes() Method

JDBC for SQL Server - Herong's Tutorial Examples

∟SQL Server BLOB (Binary Large Object) - BLOB

∟Retrieving BLOB Values with getBytes() Method

This section describes how to retrieve BLOB values with the ResultSet.getBytes() method.

The simplest way to retrieve the character string value from a BLOB column is to use the getBytes() method on the ResultSet object. Here is short example program:

/* SqlServerBlobGetBytes.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.io.*;
import java.sql.*;
public class SqlServerBlobGetBytes {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.microsoft.sqlserver.jdbc.SQLServerDataSource ds
        = new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
      ds.setServerName("localhost");
//      ds.setPortNumber(60782);
      ds.setInstanceName("SQLEXPRESS");
      ds.setDatabaseName("AdventureWorks2019");
      ds.setUser("Herong");
      ds.setPassword("T0pSecret");
      con = ds.getConnection();

// Retrieving BLOB value with getBytes()
      Statement sta = con.createStatement();
      ResultSet res = sta.executeQuery("SELECT * FROM Image");
      int i = 0;
      while (res.next() && i<3) {
        i++;
        System.out.println("Record ID: "+res.getInt("ID"));
        System.out.println("   Subject = "+res.getString("Subject"));
        byte[] body = res.getBytes("Body");
        String bodyHex = bytesToHex(body, 32);
        System.out.println("   Body in HEX = "+bodyHex+"...");
      }
      res.close();

      sta.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public static String bytesToHex(byte[] bytes, int max) {
    StringBuffer buffer = new StringBuffer();
    for (int i=0; i<bytes.length && i<max; i++) {
      buffer.append(Integer.toHexString(bytes[i] & 0xFF));
    }
    return buffer.toString().toUpperCase();
  }
}

bytesToHex() method is used to convert a byte array to a Hex string. The output of the program confirms that CLOB values can be retrieved with getBytes() method on the ResultSet object:

herong> java -cp .;mssql-jdbc-9.4.1.jre16.jar SqlServerBlobGetBytes

Record ID: 1
   Subject = Test on INSERT statement
   Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 2
   Subject = Test of the setBytes() method
   Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 4
   Subject = Test of setBinaryStream() methods
   Body in HEX = CAFEBABE000320B5A03B0487049A02048804AA0204BA...