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) 2007 by Dr. Herong Yang. 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(1269);
ds.setDatabaseName("AdventureWorksLT");
ds.setUser("Herong");
ds.setPassword("TopSecret");
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) {
System.err.println("Exception: "+e.getMessage());
}
}
}
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:
C:\>java -cp .;\local\lib\sqljdbc.jar SqlServerBlobSetBytes
The inserted record:
Subject = Test on INSERT statement
Body = ╔╦╗╠╬╣╚╩╝╠╬╣╔╦╗