Skip to content Skip to sidebar Skip to footer

Sqlite Access In Javascript

I want to access Sql Lite Database with JavaScript code. The JavaScript code is used in html5 and has to be deployed on blackberry 10 platform. I use the following code without suc

Solution 1:

You may consider using the WebSQL API, which is supported on BlackBerry 7 and 10 according to http://caniuse.com/#feat=sql-storage.

Note that the API will never become an official standard and development on it has stopped. But if you just want to target BlackBerry, it may be a valid choice.

Solution 2:

There were a few problems with the code you've posted, including a reference to .lenght instead of .length and use of try catch blocks when there are success and error handlers built-in. So I worked up a demo.

First, it does not seem to make a difference, but this is HTML5 right? Instead of an HTML 4.01 Transitional doctype, use the HTML5 doctype:

<!DOCTYPE html><html><head><metahttp-equiv="content-type"content="text/html; charset=UTF-8"><title>demo by userdude</title>
...

Next, I modified the markup for the purposes of the demonstration. In this case, we have:

<body>
<input type="button"id="run" value='Run Query'>
<div id="query"></div>
<table id="table" border="1" cellspacing="1" cellpadding="5"></table>
</body>
</html>

In the head element, I use an event listener to wait for the DOM to load. Keep in mind, I do not have a Blackberry to test this with, and with Blackberry or other devices, you should probably use deviceready instead of load. I think. I also attach the event handler for button that run's the query using .addEventListener, but notice I do that within the load handler. You have to wait before trying to access the DOM.

Also, IE supports attachEvent instead of addEventListener. I would imagine Blackberry supports the latter, but I'm not sure.

window.addEventListener('load', functionload(){
    var run = document.getElementById('run'),
        data = document.getElementById('table'),
        qtext = document.getElementById('query'),
        dropped = false,
        created = false,
        cities = ['Houston', 'Dallas', 'Paris', 'New York', 'Buenos Aires', 'London'],
        shortName = 'Cities',
        version = '1.0',
        displayName = 'Cities Demo',
        maxSize = 5 * 1024 * 1024,
        db = false,
        queries = [];

    run.addEventListener('click', query);

This establishes my database, including running the initial call to populate() so we have some data use.

open();

This is the function I added to the run button.

    function query() {
        transact('SELECT * FROM Cities', view);
    }

This is just meant to add data to the database. See the cities variable above.

functionpopulate(tx) {
        var city,
            i = 0;

I block this from running once I've emptied the cities array of entries to INSERT. dropped and created do the same thing for the DROP and CREATE transactions.

Take special note how I'm doing this; see the transact('...', populate)? I use populate in this situation to loop back until I've finished adding all of the cities entries. This is asynchronous, so you have to setup the callbacks to wait if necessary for the previous queries to run. In this case, I could end up dropping the table after adding my rows. So I have to wait, then loop through the cities list.

if (cities) {
            if (!dropped) {
                dropped = true;
                transact('DROP TABLE IF EXISTS Cities', populate);

                return;
            }

            if (!created) {
                created = true;
                transact('CREATE TABLE IF NOT EXISTS Cities (id unique, City)', populate);

                return;

            }

I don't need to iterate back to populate here, since I just need to INSERT and move on.

while (city = cities.pop()) {
                transact('INSERT INTO Cities (id, City) VALUES (' + i++ + ', "' + city + '")');
            }

            cities = false;
        }
    }

All this function does is give either an opened or new reference to the database, or return false. This short-circuits the execution of transact().

functionopen() {
        if (!db && window.openDatabase) {
            db = window.openDatabase(shortName, version, displayName, maxSize);
        }

        if (cities) {
            db.transaction(populate);
        }

        return db;
    }

This is the meat of the script. I call it from query(), and the callback in this case is view, which points to the function which runs through the result set and creates a table from the set.

functiontransact(query, callback) {
        var cb = callback,
            qel = document.createElement('p'),
            qid = queries.length;

        if (!open()) {
            console.log('HTML5 Database not supported.');

            returnfalse;
        }

        db.transaction(transact_cb);

        qel.innerHTML = query + ' Query Result: <span id="q' + qid + '">Pending...</span>';

        qtext.appendChild(qel);

        queries[qid] = query;

Note the last two arguments, transact_success, transact_error. This is how you handle these asynchronous calls.

        function transact_cb(tx) {
            tx.executeSql(query, [], transact_success, transact_error);
        }

Not quite sure why there's an eval in there...?

functiontransact_success(tx, result) {
            var rtext = document.getElementById('q' + qid);

            rtext.className = 'success';
            rtext.innerHTML = 'Success.';

            if (typeof cb == "function") {
                cb(result);
            } elseif (cb != undefined) {
                eval(cb + "(result)");
            }
        }

Note the console.log(error);.

functiontransact_error(tx, error) {
            var rtext = document.getElementById('q' + qid);

            rtext.className = 'error';
            rtext.innerHTML = 'Error logged to console.';

            console.log(error);
        }
    }

And this function creates the table result set view. You'll probably notice I loop through each row, and each row's columns.

functionview(result) {
        var thead = '<thead><tr>',
            tbody = '<tbody>',
            row,
            col;

        for (var i = 0, rows = result.rows.length; i < rows; ++i) {
            row = result.rows.item(i);

            tbody += '<tr>';

            for (col in row) {
                if (i === 0) {
                    thead += "<th>" + col + "</th>";
                }

                tbody += '<td>' + row[col] + '</td>';
            }

            tbody += '</tr>';
        }

        thead += '</tr></thead>';
        tbody += '</tbody>';

        data.innerHTML = thead + tbody;
    }

});

You can download the file and run it locally (due to a security error, it won't run on jsFiddle) by downloading the HTML file here:

http://pastebin.com/FcSiu6ZZ

So there you go. Hopefully that will help make this easier to understand. Let me know if you have any questions.

Post a Comment for "Sqlite Access In Javascript"