[ColdBox 4.1] Loading uploaded files to database

[Chad Baloga]

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!

[Jon Clausen]

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]

On Nov 22, 2015, at 4:56 PM, Chad Baloga <cba...@gmail.com> wrote:

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!

--
--
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
For News, visit http://blog.coldbox.org
For Documentation, visit http://wiki.coldbox.org
For Bug Reports, visit https://ortussolutions.atlassian.net/browse/COLDBOX
---
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to coldbox+u...@googlegroups.com.
To post to this group, send email to col...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/b7e3ae04-0d25-4806-a9d3-28e98b3fb4bb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chad Baloga]

It is with Oracle 12c

You received this message because you are subscribed to a topic in the Google Groups "ColdBox Platform" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/coldbox/QBDHMzwpnuc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to coldbox+u...@googlegroups.com.

To post to this group, send email to col...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/5C20E870-911B-4774-8087-C66CD985A07D%40silowebworks.com.

[Jon Clausen]

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}             
}

To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/CAN86zfRhKu33Waq-EoQzE9YRuUCmR_LP20Eq3U-yLLAvvuoFog%40mail.gmail.com.