How to Store Blob in Google Cloud SQL Using JDBC Library

Question

I'm working with a Google Apps Script that presents an HTML page to the user on launch. I've included a File Input field on the webpage so that one can upload both PDFs and images of various formats. Next, I want to take the uploaded file, return it to the Google script through running google.script.run., and then inside the function store the file as a Blob in my Google Cloud SQL database.

Here's a bit of my code thus far:

HTML:

<input type="file" id="cert">
<input type="button" id="btnCheck" onclick="readFiles()" value="Read Files">

JavaScript:

function readFiles() {
  var x = document.getElementById("cert");
  if ('files' in x) {
    if (x.files.length != 0) {
      for (var i = 0, f; f = x.files[i]; i++) {
        var reader = new FileReader();
        // Closure to capture the file information.
        reader.onload = (function(theFile) {
          return function(e) {
            google.script.run.storeFile(e.target.result);
          };
        })(f);
        reader.readAsBinaryString(f);
      }
    }
  }
}

Google Script:

function storeFile(f) {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var stmt = conn.createStatement(Jdbc.ResultSet.TYPE_SCROLL_INSENSITIVE, Jdbc.ResultSet.CONCUR_UPDATABLE);

  var results = stmt.executeQuery('select * from my_table');

  results.moveToInsertRow();

  var newBlob = conn.createBlob();
  var uBlob = Utilities.newBlob(f, 'application/pdf');
  newBlob.setBytes(1, uBlob.getBytes());

  results.updateBlob('file', newBlob);

  results.insertRow();
  results.moveToCurrentRow();

  results.close();
  stmt.close();
}

So now for the issue at hand. At this point data is being stored in the database, but a lot of extra information seems to have been appended and when I use MySQL Workbench to export the data to a file (in this case I'm testing with PDFs) the file cannot be opened.

If I manually insert a file into the database using MySQL Workbench the data has the correct size, can be exported, and will open properly. I've noticed that if I print the size of uBlob.getBytes() the value is 754244 and if I print the size of uBlob.getDataAsString() the value is 528004. The original file size is 516kB (~528384). Looking at the raw data there are definitely similarities, observe below.

When Stored Using MySQL Workbench (First 20 Lines):

    %PDF-1.3
    %âãÏÓ
    1 0 obj
    > 
    endobj
    2 0 obj
    > 
    stream
    ÿØÿà JFIF ,,  ÿÛ  

    
    
    



When Stored Using Google Apps Script (First 20 Lines):

    %PDF-1.3
    %âãÃÃ
    1 0 obj
    > 
    endobj
    2 0 obj
    > 
    stream
    ÿÃÿà JFIF ,,  ÿà  

    
    
    
    

I'm limited in what I can send to my Google Script function, but the Binary String received seems legal. newBlob is a JdbcBlob and I'm setting it using the setBytes function with its second parameter having type BlobSource, so I'm creating a Blob to use as the BlobSource using Utilities.newBlob with the file Binary String from the webpage as its input. results.updateBlob has its second parameter with type JdbcBlob, so here I'm providing newBlob as input.

Help would be much appreciated as I'm stumped at this point. Thanks!

Answer 1

Alright, it looks like I've found a solution!

Encode the Binary String Result from the FileReader to Base64 using the function btoa(...). Then in the Google Script use Utilities.base64Decode(...) to get the data as a Blob.

JavaScript:

function readFiles() {
  var x = document.getElementById("cert");
  if ('files' in x) {
    if (x.files.length != 0) {
      for (var i = 0, f; f = x.files[i]; i++) {
        var reader = new FileReader();
        // Closure to capture the file information.
        reader.onload = (function(theFile) {
          return function(e) {
            google.script.run.storeFile(btoa(e.target.result));
          };
        })(f);
        reader.readAsBinaryString(f);
      }
    }
  }
}

Google Script:

function storeFile(f) {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var stmt = conn.createStatement(Jdbc.ResultSet.TYPE_SCROLL_INSENSITIVE, Jdbc.ResultSet.CONCUR_UPDATABLE);

  var results = stmt.executeQuery('select * from my_table');

  results.moveToInsertRow();

  var newBlob = conn.createBlob();
  newBlob.setBytes(1, Utilities.base64Decode(f));

  results.updateBlob('file', newBlob);

  results.insertRow();
  results.moveToCurrentRow();

  results.close();
  stmt.close();
}