Below is an example on how to insert or query using an image with a bianry column (See the comments in the code):
//Create a handle to the ArrayUtils that we use
//later to convert primitive byte[] to java.lang.Byte[]
var ArrayUtils = org.apache.commons.lang.ArrayUtils;
//Drop the temp table if it exists
var dropStatement = "IF EXISTS " +
"(SELECT * FROM " +
"INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_NAME = 'tmp_common_types') " +
"DROP TABLE tmp_common_types;";
var statementCount = qie.doUpdateQuery('works', dropStatement);
qie.debug("drop table count: " + statementCount);
//Create the temp table
var createStatement = "" +
"CREATE TABLE tmp_common_types (" +
"binaryCol BINARY(8000), " +
"varbinaryCol VARBINARY(8000) " +
");" +
"";
statementCount = qie.doUpdateQuery('works', createStatement);
qie.debug("create table count: " + statementCount);
//Insert the first record
var insertStatement = "" +
"INSERT INTO tmp_common_types (" +
" binaryCol, " +
" varbinaryCol " +
") VALUES (" +
" :binaryVal, " +
" :varbinaryVal " +
")";
var pQuery = qie.getParameterizedQuery(insertStatement);
//Read in the file. The qie.readfile returns a byte[]
var firstFileBytes = qie.readFile(new java.io.File("C:\\tmp\\1.png"));
//The setBytes() method take a java.lang.Byte[]
//instead of a primitive byte[] for consistency in null handling
pQuery.setBytes('binaryVal', ArrayUtils.toObject(firstFileBytes));
//For large files an InputStream can be used to
//avoid loading the entire file into memory
pQuery.setBinaryStream('varbinaryVal', new java.io.ByteArrayInputStream(firstFileBytes));
statementCount = pQuery.doUpdateQuery('works');
qie.debug("BINARY - insert count: " + statementCount);
//Insert a second record
pQuery.clearParameters();
//Just create a file handle and let the stream
//do the reading.
var secondFileHandle = new java.io.File("C:\\tmp\\2.png");
pQuery.setObject('binaryVal', new java.io.FileInputStream(secondFileHandle), java.sql.Types.BINARY);
//The setObject() method can also be used.
//Note that a primitive byte[] is used.
pQuery.setObject('varbinaryVal', qie.readFile(secondFileHandle), java.sql.Types.VARBINARY);
statementCount = pQuery.doUpdateQuery('works');
qie.debug("BINARY - insert count: " + statementCount);
//We can also query on binary columns.
var selectStatement = "" +
"SELECT " +
" 'Found a match' " +
" FROM tmp_common_types " +
" WHERE " +
" binaryCol = :binaryVal " +
" AND varbinaryCol = :varbinaryVal " +
"";
pQuery = qie.getParameterizedQuery(selectStatement);
pQuery.setBytes('binaryVal', ArrayUtils.toObject(firstFileBytes));
pQuery.setBinaryStream('varbinaryVal', new java.io.ByteArrayInputStream (firstFileBytes));
var results = pQuery.doQuery('works', true);
qie.debug("BINARY - " + results);
See also:
How to convert a 'Standard Query' to a 'Parameterized Query'?
How to insert nulls using a parameterized query
