SQLCL - Blob loading from a file

There is a ton of ways having new scripting in sqlcl can help. One example is a simpler way to load a local file into a BLOB in a column.

Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this.

Enter SQLCL and javascript method #1.

Here's a simple script which loads a local file into a temp BLOB and uses that as a bind variable in the insert statement

/*
/*
*  Function to take in a filename and add or create it to a map
*  with bind variables
*/
function addBindToMap(map,bindName,fileName){
     /*  conn is the actual JDBC connection */
     var b = conn.createBlob();

     var out = b.setBinaryStream(1);

     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);

     /* slurp the file over to the blob */
     java.nio.file.Files.copy(path, out);
     out.flush();

     if ( map == null ) {
         /* java objects as binds needs a hashmap */
         var HashMap = Java.type("java.util.HashMap");
         map = new HashMap();
     }
     /* put the bind into the map */
     map.put("b",b);
 return map;
}


/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = addBindToMap(null,"b",file);

/* add more binds */
binds.put("path",file);

/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

/* print the results */
sqlcl.setStmt("select path,dbms_lob.getlength(blob_content) from k order by when desc;");

sqlcl.run();

Running it is as expected. One new row and showing the blob size. The only difference here is that you can avoid typing s-c-r-i-p-t and save 2 letters with just scri.

Method #2 shared library

I'll be making a library intended to make some things easier such as file loading and creating the hashmap. So taking out this blob loading as an example there first 2 things to put into such a library in helpers.js

/*
    This is a javascript library to make some of the common things
    in SQLCL simplier.
*/

var helpers = {} ;

/* for complex type a java hashmap is needed for binds */
helpers.getBindMap = function(){
   var HashMap = Java.type("java.util.HashMap");
   map = new HashMap();
   return map;
};

/* create a temp blob and load it from a local to sqlcl file */
helpers.getBlobFromFile=function (fileName){
     var b = conn.createBlob();
     var out = b.setBinaryStream(1);
     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);
     java.nio.file.Files.copy(path, out);
     out.flush();
 return b;
};

Now the blob loading script can be reduced to something like this

load('helpers.js');

/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = helpers.getBindMap();

/* add more binds */
binds.put("path",file);

blob = helpers.getBlobFromFile(file);

binds.put("b",blob);


/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

I'll be cleaning up this helpers.js and hosting it soon with frequent additions to new functions.