Binary Files and BLOBs

You may need to read in binary files (such as images from a card system) and do something with them. Here's an example of loading in JPGs and inserting them into a database.

The Input Connector

Use a file connector with a connection string similar to this to get a list of all the files to process. Parse the result into a filename, and add any other attributes you will need later into the input map with blank values.

find /home/gattis/TDI/input/card_images/ -mtime -1 -type f -name "*.jpg" | sort

Data Flow

Component 1 - A script object
Use a script object to load the binary file into an attribute. For java 1.6 versions of TDI (TDI 7.1.1 at least) you don't have access to the java.nio, and so must use slightly older input stream based methods.  As of 7.2 IBM uses a 1.7.0 JVM. See the updated example at the bottom.


// Load the file contents into the work object

var fileName = work.FILENAME.getValue()

IMAGE_NUM++;
task.logmsg("INFO","Getting Photo " + IMAGE_NUM + " and PID from " + fileName);

try {
// Get a file object using the full path and name originally from FILENAME
var imageFile = new java.io.File( fileName );
// Verify the size of the file is less than our Max Array Size
var length = imageFile.length();
if (length > java.lang.Integer.MAX_VALUE){
throw("File length is greater than we can handle: " + length);
}

// Create a File Input Stream from the file
var imageFIS = java.io.FileInputStream(imageFile);

// Create a byte array to hold the contents and load it
var imageContents = new byte [length];
imageFIS.read(imageContents);
}
catch(err){
task.logmsg("ERROR","Unable to process picture: " + fileName);
task.logmsg(err);
if (imageFIS) imageFIS.close();
system.skipEntry();
}

// Load the PHOTO attribute and close the stream
work.PHOTO.setValue(imageContents);
imageFIS.close();




Component 2 - A JDBC Connector
Use a JDBC connector to load the BLOB normally at this point in your flow.


Component 3 - A Script Connector or Script
You can use a Script Connector, but a Script Component works just as well if you're just exporting files

// Let's set a file name
var someFile = "output/" + work.MD_OUID.getValue() + ".jpg";

// get the attribute blob read into an input stream and open an output stream
try {
// get the raw object, as opposed to a string, fed into an input stream
var is = new java.io.ByteArrayInputStream(work.getObject("PHOTO"));
var os = new java.io.FileOutputStream(someFile);
// Transfer bytes from in to out
var buf = new byte[1024];
var len;
while ((len = is.read(buf)) > 0) {
    os.write(buf, 0, len);
}

is.close();
os.close();
}
catch(err){
task.logmsg("ERROR","Unable to write picture");
task.dump(err);
system.skipEntry();
}


Update

Since ISDI now uses java 1.7 we can use the newer nio library as overviewed here:

https://docs.oracle.com/javase/tutorial/essential/io/file.html

Assuming you have small files;

    var fileArray = java.nio.file.Files.readAllBytes("input/someFile");

I'll post working code once I've actually used it with a project.


Troubleshooting


ORA-01704: string literal too long

If you see this error from your destination database, you're not using a prepared statement. Go the the advanced settings of the connector and make sure you've selected "Used Prepared Statements"


Comments