2

I have a google sheet connected to my MySQL database with a Google script. I'm able to retrieve all the text data I want instead of the blob stored in the database. Here is the php code:

$insert = $conn->query("INSERT into images (image) VALUES ('$base64Img')");

I only left one field to make it clear

The blob column in the table is set to "LONGBLOB". How can I view my BLOB as an image in my google sheet?

Here is what I tried :

function readFromTable() {
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('SELECT image FROM images');
  var numCols = results.getMetaData().getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var arr=[];
  while (results.next()) {
    var arr = [];
    for (var col = 0; col < numCols; col++) {
      arr.push(results.getBlob(col + 1));
    }
    sheet.appendRow(arr);
  }

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

I don't get any error message but on my sheet it prints : JdbcBlob in the cell instead of the actual image. I would like to put the image into a cell of my google sheet

Arnaud NaNo
  • 55
  • 8
  • We may need to see how you stored the file into the database to help here – RiggsFolly Aug 05 '20 at 11:48
  • Have you tried anything? – TheMaster Aug 05 '20 at 11:57
  • I added my php code. Yes i tried multiple things with getBlob method but i didn't manage to print the blob on my sheet. But every other fields work. I'm fairly new with google script. – Arnaud NaNo Aug 05 '20 at 12:05
  • 1
    [`insertImage(blobSource, column, row, offsetX, offsetY)`](https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertimageblobsource,-column,-row,-offsetx,-offsety) – Rafa Guillermo Aug 05 '20 at 12:53
  • You're fetching each column as a string. Binary data is not a string. – Mark Rotteveel Aug 05 '20 at 13:38
  • Thank you @RafaGuillermo for your insight. I tried to adapt it as an example on a separate spreadsheet with `var blob = Utilities.newBlob(results, 'image/png', 'MyImageName'); sheet.insertImage(blob, 2, 1, 10, 10);` But i have an "error coming from a service : stylesheet" – Arnaud NaNo Aug 05 '20 at 13:49
  • @MarkRotteveel I didn't include any code for the blob integration on my original post. I'm looking for hints on how to add it. – Arnaud NaNo Aug 05 '20 at 13:56
  • @RiggsFolly i added the php code but i don't think the problem come from here since the blob is stored in the database with a name like so : [BLOB - 199.7 KiB] – Arnaud NaNo Aug 05 '20 at 13:58
  • `addslashes(file_get_contents($image));` Instant file corrution! Instead convert the file to BASE64 Encoded – RiggsFolly Aug 05 '20 at 14:00
  • 1
    Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values – RiggsFolly Aug 05 '20 at 14:02
  • 1
    You are executing `SELECT * FROM mytable` and then using `getString` on all retrieved columns, which include your blob column. Given a blob is binary data, you would need to use `getBytes` or maybe `getBlob` or `getInputStream`. – Mark Rotteveel Aug 05 '20 at 14:07
  • 2
    If you tried, Show the tried code(getBlob) and quote the error or the problem. [Edit] to show. About `var blob = Utilities.newBlob(results, 'image/png', 'MyImageName'); sheet.insertImage(blob, 2, 1, 10, 10);`, `results` is not a apps script `blob` see https://developers.google.com/apps-script/reference/jdbc/jdbc-result-set – TheMaster Aug 05 '20 at 14:46
  • @TheMaster I tried several things with `getAppsScriptBlob()` since the description in the doc says = Gets the content of this JdbcBlob as an Apps Script blob. Which seems to be my issue. Tell me if i'm wrong – Arnaud NaNo Aug 05 '20 at 16:57
  • You shouldn't getBlob on all columns. Only the last one. You can insert the image into sheet, but not inside the cell. – TheMaster Aug 05 '20 at 18:05

1 Answers1

2
const jdbcBlob = results.getBlob(col + 1);
const appsScriptBlob = jdbcBlob.getAppsScriptBlob();
sheet.insertImage(appsScriptBlob,1,1);

//if  image data is base64 encoded:
sheet.insertImage(Utilities.newBlob(Utilities.base64Decode(appsScriptBlob.getDataAsString())),1,1)

Another way would be to get byte array using getBytes():

const byteArr = results.getBytes(col + 1);
//if  image data is base64 encoded:
sheet.insertImage(Utilities.newBlob(Utilities.base64Decode(Utilities.newBlob(byteArr).getDataAsString())),1,1)
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • i get : Exception: Error from a service : JdbcBlob on the insertimage code line. Tryied base64 and normal encoding code. – Arnaud NaNo Aug 06 '20 at 12:36
  • 1
    @ArnaudNaNo Added another method. If that doesn't work, You're going to do some digging and investigate – TheMaster Aug 06 '20 at 13:31