[ColdBox 4.1] Loading uploaded files to database

Communities ColdBox HMVC
1

I am writing up some functions in a ColdBox module which I created to put files into blob fields in a database table. Currently I upload the files using a jQuery plugin (http://plugins.krajee.com/file-input) which runs an event. In my model I have cffile uploadAll, then it read the files from a temp area w cffile readBinary, write that binary output to the database then it deletes the file. Is there anything simpler or faster than this method?? The upload to the server is fast but I think reading the file to binary is what is taking all of the time up. Thanks!

2

Depends on your DBMS, but most of them can pipe a file in directly. That would cut out the app server entirely in the file read. MySQL example:

INSERT INTO my_table (created, file_doc) VALUES (now(), LOAD_FILE('/tmp/my_file.pdf'));

For a remote database server, you’ll need to make sure the appropriate permissions are in place to allow the operation (e.g. on MySQL the server daemon will have to be started with --local-infile=1 )

[Note: Typo assistance courtesy of iPhone]

3

It is with Oracle 12c

4

Oracle can read a file directly in to a table column, though the query’s less straightforward and a lot more verbose than MySQL or MSSQL. Here are a couple of different examples: https://community.oracle.com/thread/2400953

Unless you have a shared directory or network location that the DB has permission to access, though, I’m not sure that’s the best approach. You might be better off using the Java FileInputStream directly through the native driver - see the writeBLOBPut method in the following link for the general procdure: http://www.idevelopment.info/data/Programming/java/jdbc/LOBS/BLOBFileExample.java

An approximate (and untested) CFML translation of that method would be:


public function insertFile(required string file){
    var serviceFactory = createObject('java','coldfusion.server.ServiceFactory');
    conn = serviceFactory.getDataSourceService().getDataSource('DataSource Name').getConnection();
    var stmt = conn.createStatement();
     
    var inputBinaryFile = createObject("java","java.io.File").init(arguments.file);
    var jStream = createObject("java","java.io.FileInputStream").init(inputBinaryStream);
     
    var sqlText = "INSERT INTO test_blob (id, image_name, image, timestamp)  VALUES(1, '" & inputBinaryFile.getName() & "', EMPTY_BLOB(), SYSDATE)";
     
    stmt.executeUpdate(sqlText);
     
    sqlText = "SELECT image FROM   test_blob WHERE  id = 1 FOR UPDATE";
     
    rset = stmt.executeQuery(sqlText);

    rset = rset.next();
     
    var image = rset.getBLOB("image");
     
    var chunkSize = image.getChunkSize();
     
    var binaryBuffer = createObject("java","java.lang.Byte").init(chunkSize);
     
    var position = 1;
     
    var bytesWritten = 0;
    var position = 0;
    var totbytesRead = 0;
    var totbytesWritten = 0;
     
    while ((bytesRead = inputFileInputStream.read(binaryBuffer)) != -1) {
        bytesWritten = image.putBytes(position, binaryBuffer, bytesRead);
        position        += bytesRead;
        totbytesRead    += bytesRead;
        totbytesWritten += bytesWritten;
    }    
     
    inputFileInputStream.close();
     
    conn.commit();
    rset.close();
    stmt.close();
     
    return {"bytesRead":totbytesRead,"bytesWritten":totbytesWritten}             
}