Storing byte array in HTML5 sqlite database

Question

I am working on an offline capable mobile web app and need to store large amounts of raw byte data in a HTML5 database. I want to store them as compact as possible, so a joined string is not an option. Here is a sample code that creates a db, table, inserts data and retrieves it again.

Example:

var bytes=[97, 0, 6, 244, 98, 66, 76, 65, 131, 5, 7, 142, 81, 184, 112, 33];

openDatabase('_test_', 1.0, '_test-', 5000).transaction(function(tx) {
tx.executeSql("DROP TABLE IF EXISTS MYTABLE", [], function(){
tx.executeSql("CREATE TABLE IF NOT EXISTS MYTABLE(content BLOB);",[],function(){
tx.executeSql("INSERT INTO MYTABLE values(?)", [bytes], 
    function()
    {
        tx.executeSql("SELECT * FROM MYTABLE ", [], function(transaction, results)
        {
            console.log(results.rows.item(0))
        });
},function(transaction, error){console.log(error)})
},function(transaction, error){console.log(error)})
})
})

I am trying to store the array as is, which actually saves as a joined string :"97, 0, 6, 244, 98, 66, 76, 65, 131, 5, 7, 142, 81, 184, 112, 33". Not what I need since it will be way too large.

I am converting the array into a string now:

openDatabase('_test_', 1.0, '_test-', 5000).transaction(function(tx) {
tx.executeSql("DROP TABLE IF EXISTS MYTABLE", [], function(){
tx.executeSql("CREATE TABLE IF NOT EXISTS MYTABLE(content BLOB); ", [], function(){
tx.executeSql("INSERT INTO MYTABLE values(?)", [s], 
    function()
    {
        tx.executeSql("SELECT * FROM MYTABLE ", [], function(transaction, results)
        {
            console.log(results.rows.item(0))
        });
},function(transaction, error){console.log(error)})
},function(transaction, error){console.log(error)})
})
})

What the DB now returns is simply "a".

So my question is how do I serialize a javascript byte array in HTML5 database without resorting to a joined string?

Why do you have `'0' in your array, which is a null character? — Benoit
– Benoit, Commented Apr 10, 2011 at 15:11
the data simply contains a byte with the value 0. Makes sense? — Stan Wiechers
– Stan Wiechers, Commented Apr 10, 2011 at 15:14

Pol · Accepted Answer · 2011-04-10 15:46:57Z

1

Convert data to hexadecimal values and insert like this:

INSERT INTO t2 VALUES(X'53514C697465');

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character

Literal Values

edited Apr 10, 2011 at 15:46

answered Apr 10, 2011 at 15:30

Pol

5,1445 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stan Wiechers Over a year ago

How can I do that for a DB column that is supposed to hold more then one byte? I need to encode a sequence of bytes.

Stan Wiechers Over a year ago

So for [97, 256, 6, 244, 98, 66, 76, 65, 131, 5, 7, 142, 81, 184, 112, 33] I am inserting X'611006F462424C4183578E51B87021' as its hex representation. I am not clear on how to cast the value from a select to the original javascript array again. Could you help here as well?

Peter Rust Over a year ago

Assuming SQLite returns a hexadecimal string that looks like "611006F462424C4183578E51B87021", you can iterate every two characters, slice, parseInt and push onto an array. Since every two characters represents a byte, just write a for loop that increase the index by 2 every time instead of by 1. You can grab the two characters via String.slice(), like so: hex.slice(ix, ix+2). You can convert these to an integer via parseInt(str, 16). So it would look like: arr = []; for(var ix = 0; ix < hex.length; ix = ix+2) { arr.push(parseInt(hex.slice(ix, ix+2), 16)); }

thalisk Over a year ago

This answer has not been upvoted once the past 8 years?

Collectives™ on Stack Overflow

Storing byte array in HTML5 sqlite database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related