Reading and Writing LOBs En Bloc (SAP Library

Reading and Writing LOBs En Bloc

Use

Reading and writing LOBs en bloc implies that either you retrieve the value of the BLOB and CLOB columns into a byte array or a string, or you insert data from a byte array or a string.

You can use this function with both JDBC and SQLJ.

Activities

Reading LOBs en Bloc

The JDBC API provides the methods getBytes() and getString() methods of java.sql.ResultSet. They enable you to read BLOB or CLOB columns into a byte array or a string respectively.

Example

Reading LOBs en Bloc using JDBC

String query = "select REFMANUAL, PICTURE from BC_CR_VEHICLE " +

"where NAME = ?";

PreparedStatement ps = conn.prepareStatement(query);

try {

ps.setString(1, "Golf");

ResultSet rs = ps.executeQuery();

try {

while (rs.next()) {

String refManual = rs.getString("REFMANUAL");

byte[] image = rs.getBytes("PICTURE");

...

}

finally {

rs.close();

}

finally {

ps.close();

}

When using SQLJ, you can read such columns into host variables in an SQLJ single row query, a FETCH statement, or an iterator, which is illustrated in the following example.

Example

Reading LOBs en Bloc using SQLJ

int employeeId = ...;

String cv;

byte[] image;

#sql [ctx] {

SELECT cv, image

INTO :cv, :image

FROM employee

WHERE employee_id = :employeeId };

Writing LOBs en Bloc

To insert data from a byte array or a string with JDBC, you should use setBytes() or setString() methods of java.sql.PreparedStatement.

Example

Writing LOBs en Bloc using JDBC

String manual = "...";

byte[] picture = ...;

String insertStmt = "insert into VEHICLE " +

"(NAME, REFMANUAL, PICTURE) VALUES (?, ?, ?)";

PreparedStatement ps = conn.prepareStatement(insertStmt);

try {

ps.setString(1, "Golf");

ps.setString(2, manual);

ps.setBytes(3, picture);

ps.executeUpdate()

}finally{

ps.close();

}

With SQLJ you can use a host variable of the relevant type in an UPDATE or INSERT statement. The following example illustrates how you can update an employee record by inserting a new picture (newImage).

Example

Writing LOBs en Bloc using SQLJ

int employeeId = ...;

byte[] newImage = ...;

#sql [ctx] {

UPDATE employee

SET image = :newImage

WHERE employee_id = :employeeId };