Unable to convert BLOB to String with Cordova sqlite plugin for offline maps

Question

I'm following this tutorial : https://kuamoto.wordpress.com/2016/02/26/myth-1-cant-make-offline-apps/ to get offline maps in an ionic 2 app based on a sqlite database (.mbtiles). I use Cordova Sqlite plugin to query the database as shown in this repo : https://github.com/wilblack/offline-map-example

The database contains Blob corresponding to a combinaison of x, y and z which come for the map location I want to display.

I've succeeded to open the database and to query it but I got stuck with the following error :

unknown error (Sqlite code 0): Unable to convert BLOB to string, (OS error - 2:No such file or directory)"

It seems to be a common issue, but I only found solutions for Android directly. Here is my Typescript code:

getTileUrl: function(tilePoint, zoom, tile) {
                var z = this._getZoomForUrl();
                z = Math.round(z);
                var x = tilePoint.x;
                var y = tilePoint.y;
                y = Math.pow(2, z) - y - 1;
                var base64Prefix = 'data:image/gif;base64,';
                this.mbTilesDB.transaction((tx) => {
                    tx.executeSql("SELECT tile_data FROM tiles WHERE zoom_level = ? AND tile_column = ? AND tile_row = ?;", [z, x, y], (tx, res) => {
                        //Never get here
                        tile.src = base64Prefix + res.rows.item(0).tile_data;
                    }, (err, msg) => {
                        console.log('[MapPage.getTileUrl] error with executeSql', err);
                    });
                }, (err, msg) => {
                    console.log("[MapPage.getTileUrl] Transaction err:", err);    
                });
            },
            _loadTile: function(tile, tilePoint, zoom) {
                tile._layer = this;
                tile.onload = this._tileOnLoad;
                tile.onerror = this._tileOnError;
                this.getTileUrl(tilePoint, zoom, tile);
            }

The code breaks just after launching the query. If I launch the query inside a Database browser, I obtain a result as a Blob.

The issue is closed to this one: How to set tile_data from .mbtiles file as tiles for leaflet? except that I don't even get a result from the query

Thank you for your help

score 0 · Accepted Answer · answered Feb 13 '17 at 08:31

As far as I know, most JavaScript implementations do not support blobs.

That code assumes that the data in the database is stored as Base64-encoded text (or that the database driver does this conversion automatically).

You have to tell the database to convert the blob into some text format (SELECT hex(tile_data) ...), and then convert that hex string into some useful format in your code.

score 0 · Answer 2 · answered Jan 09 '18 at 14:24

Cordova-sqlite-ext supports reading BLOBs from pre-populated sqlite databases like .mbtiles-files.

In order to build data-URLs containing tile data you can adapt the following code taken from the README:

```

db.readTransaction(function(tx) {
  tx.executeSql("SELECT BASE64(data) AS base64_data FROM MyTable", [], function(tx, resultSet) {
    console.log('BLOB data (base64): ' + resultSet.rows.item(0).base64_data);
  });
});

```

Unable to convert BLOB to String with Cordova sqlite plugin for offline maps

2 Answers2