AQTableWriter currently can not modify [MYSQL].[longblob] data types.

by Nov 3, 2018

I’m receiving this message when trying to use AQTableWriter to write a row to a MySQL table that contains a longblob column.  It’s obvious from the error msg, this Class (by design) cannot accommodate writing a MySql longblob datatype. Wondering if there’s an alternative to using this class? Perhaps there’s a solution coming soon?

// specify connections for SQL Server instance
var connIN = aqua.project.getServerConnection('cga-app-sql');
connIN.connect();
connIN.changeDatabase('bugs');

var sql = 'select attachments.* from attachments join load_status on load_status.tablename = \'attachments\' and load_status.endtime < attachments.creation_ts';
var resultSet = connIN.executeQuery(sql);
var rowCount = 0;

// specify connections for mySQL Server instance
var connOUT = aqua.project.getServerConnection('csc-t-casezilla');
connOUT.connect();
connOUT.changeDatabase('bugs');

aqua.console.println('Import started...');
var table = connOUT.metadata.extractTable('bugs', '<DEFAULT>', 'attachments');
var wr = table.getWriter();
wr.setDateFormat('MM/dd/yyyy');
wr.setDateTimeFormat('yyyy-MM-dd hh:mm:ss');
wr.setTimeFormat('hh:mm:ss');
wr.setNumberFormat('#,##0.###');

while((resultSet.next()) != null)
{
var outputRow = wr.newDataRow();

// attach_id --> attach_id
outputRow.set(0, resultSet.getString(0));
// bug_id --> bug_id
outputRow.set(1, resultSet.getString(1));
// creation_ts --> creation_ts
outputRow.set(2, resultSet.getString(2));
// description --> description
outputRow.set(3, resultSet.getString(3));
// mimetype --> mimetype
outputRow.set(4, resultSet.getString(4));
// ispatch --> ispatch
outputRow.set(5, resultSet.getString(5));
// filename --> filename
outputRow.set(6, resultSet.getString(6));

// thedata --> thedata (longblob) -- all of the following attempts fail
// outputRow.set(7, resultSet.getObject(7));
// outputRow.set(7, resultSet.getBlob(7));
 // outputRow.set(7, resultSet.getString(7));
 outputRow.set(7,'test'); 

// submitter_id --> submitter_id
outputRow.set(8, resultSet.getString(8));
// isobsolete --> isobsolete
outputRow.set(9, resultSet.getString(9));
// groupset --> groupset
outputRow.set(10, resultSet.getString(10));
// last_modified --> last_modified
outputRow.set(11, '');
if (resultSet.isNull(11) != true) {
outputRow.set(11, resultSet.getTimestamp(11));
}
wr.write(outputRow);
}
wr.close;
connIN.disconnect();
connOUT.disconnect();
aqua.console.println('Import complete...');

Response

nhilam over 4 years ago

You’re correct. The OpenAPI currently does not support longblob. I’ve opened a new issue to add this in a future release:

Issue #15671