Inserting BLOB Values with setBytes() Method

JDBC for SQL Server - Herong's Tutorial Examples

∟SQL Server BLOB (Binary Large Object) - BLOB

∟Inserting BLOB Values with setBytes() Method

This section describes how to insert BLOB values with the PreparedStatement.setBytes() method.

Another way to insert a binary string into a BLOB column is to create a PreparedStatement object and use the setBytes() method. See the sample program below:

/* SqlServerBlobSetBytes.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class SqlServerBlobSetBytes {
  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();

// Deleting the record for re-testing
      String subject = "Test of the setBytes() method";
      Statement sta = con.createStatement();
      sta.executeUpdate("DELETE FROM Image WHERE Subject = '"
        +subject+"'");

// Inserting CLOB value with PreparedStatement.setBytes()
      PreparedStatement ps = con.prepareStatement(
        "INSERT INTO Image (Subject, Body) VALUES (?,?)");
      ps.setString(1, subject);
      byte[] bodyIn = {(byte)0xC9, (byte)0xCB, (byte)0xBB,
        (byte)0xCC, (byte)0xCE, (byte)0xB9,
        (byte)0xC8, (byte)0xCA, (byte)0xBC,
        (byte)0xCC, (byte)0xCE, (byte)0xB9,
        (byte)0xC9, (byte)0xCB, (byte)0xBB};
      ps.setBytes(2, bodyIn);
      int count = ps.executeUpdate();
      ps.close();

// Retrieving BLOB value with getBytes()
      ResultSet res = sta.executeQuery(
        "SELECT * FROM Image WHERE Subject = '"+subject+"'");
      res.next();
      System.out.println("The inserted record: ");
      System.out.println("   Subject = "+res.getString("Subject"));
      System.out.println("   Body = "
        +new String(res.getBytes("Body")));
      res.close();
      sta.close();

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

Compilation and execution of this program is below. The output confirms that the binary string value, stored in a byte array, was correctly inserted into the CLOB column:

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

The inserted record:
   Subject = Test on INSERT statement
   Body = ╔╦╗╠╬╣╚╩╝╠╬╣╔╦╗