Local storage query

  

I would like to create a query on local storage "Select sum(length(mediabinary)) from OSUSR_NC5_OrderItems".  The purpose is to measure the total size of images saved as binary data in a local storage table.  The query runs fine in the Chrome debugging tab Application->Web SQL, but I can't see any way to run this query from the mobile app.  I saw some other forum posts talking about no ability to run custom queries in SqlLite, so this may not be possible.  Thanks in advance for your help.

I'm not completely sure, but I don't think the images are saved as binary data... rather base64 strings.  However, my Local Storage entity attirbute is binary... so I'm not sure.

Here's why this might be.  I created a mobile app which includes a Local Storage Entity.  I created the Action to load the Local Entity with data from a remote table.  The images in the remote sql table are longblobs.

Then I managed (wasn't easy) to get the internal name of the local table and query that table directly with SQLite.

Stepping through my code I see that the Local Storage Images are base64 strings... so... either OutSystems is converting the images during the sync to load Local Storage... or SQLite is converting blobs during the select... not sure which.

That's all ok... but the image size will not be exact... does it need to be?  The image size can be adjusted down by approx. 37% to get a closer true blob size if needed.

If you're loading Local Storage directly from the client, I'm not sure what's in Local Storage.  Could you post a sample oml which will let me load Local Storage client-side... and then I can test what's going on?

Davidk wrote:

I'm not completely sure, but I don't think the images are saved as binary data... rather base64 strings.  However, my Local Storage entity attirbute is binary... so I'm not sure.

Here's why this might be.  I created a mobile app which includes a Local Storage Entity.  I created the Action to load the Local Entity with data from a remote table.  The images in the remote sql table are longblobs.

Then I managed (wasn't easy) to get the internal name of the local table and query that table directly with SQLite.

Stepping through my code I see that the Local Storage Images are base64 strings... so... either OutSystems is converting the images during the sync to load Local Storage... or SQLite is converting blobs during the select... not sure which.

That's all ok... but the image size will not be exact... does it need to be?  The image size can be adjusted down by approx. 37% to get a closer true blob size if needed.

If you're loading Local Storage directly from the client, I'm not sure what's in Local Storage.  Could you post a sample oml which will let me load Local Storage client-side... and then I can test what's going on?

David, thank you for the input.  Did you manage to do this with OutSystems code or directly from the browser such as the Chrome debugging tab Application->Web SQL?   "Then I managed (wasn't easy) to get the internal name of the local table and query that table directly with SQLite."

It seems there should be a JavaScript way to directly run this query using a client action or a way to run Length(binarydata).


Directly with OutSystems code.  But it's a process... because I don't know (unfamiliar) any other way to get it done.

When working with OutSystems Local Storage... you're working with SQLite.  In Chrome debugging you can see that OutSystems is opening a database.  I don't know if you can depend on the Database Name because it's a UUID.  After a number of builds, the Database Name has remained the same.

Now that I have the SQLite Database Name, I can query it to get it's Tables.  Once I have my Table name - the OutSystems version of the name - I can query the Table and get data.  Here's the code I used.  In my case I created a Local Storage Entity and filled it with data from a SQL table.

This code is running from a Javascript Node called from a Client Action off a Button.  It's all just basic SQLite stuff... but you have to figure out the Database Name and Table Name.  If there's an easier way to expose that information in OutSystems, I'd love to know it.  I'm going to go try the OutSystems Local Storage Explorer Extension that I found.  I'll report back on that.


debugger;

var dbName = "65acd789-3a52-40e0-bd2d-c3d364a10eec_f0cc0762-fbe9-4647-80d5-fcece511529a";

//var openDB = window.sqlitePlugin.openDatabase({name: dbName, location: 'Documents', createFromLocation: 1}, function(db) {

var openDB = window.sqlitePlugin.openDatabase({name: dbName, location: 'default'}, function(db) {

debugger;
//alert('all good');
    console.log('sqlite database opened');

//get all tables in the database
//db.executeSql("select * from sqlite_master WHERE type = 'table'", [], function (resultSet) {
//db.executeSql("select length(hex(MediaBinary))/2 as medialengh from OSUSR_XYH_OSUSR_NC5_ORDERITEMS", [], function (resultSet) {
db.executeSql("select * from OSUSR_XYH_OSUSR_NC5_ORDERITEMS", [], function (resultSet) {
debugger;
var imageSize = 0;
var myData = resultSet.rows.item(0);
for(i=0;i<resultSet.rows.length;i++){
        imageSize = imageSize + resultSet.rows.item(i).MediaBinary.length;
}

//this was for walking through the resultset of tables... to get the table name I wanted to work with.
//var myResult = resultSet;
//var myData = resultSet.rows.item(0);
//var myData = resultSet.rows.item(1);
//var myData = resultSet.rows.item(2);
//var myData = resultSet.rows.item(3);
//var myData = resultSet.rows.item(4);
//var myData = resultSet.rows.item(5);
//var sheetMusic = [];


}, function(error) {
  debugger;
  var myError = JSON.stringify(err);
console.log("sqlite error : " + myError);
});


}, function(err) {
debugger;
var myError = JSON.stringify(err);
console.log("sqlite error : " + myError);
});