Can't get values from Sqlite DB using query

Question

I used sqlite to populate a DB with some Tables in it. I made a function at another javascript page that executes the database & selects some values from the table. The function is called at $(document).ready().

Javascript:

 //DB Population
function onDeviceReady() {
    var db = window.openDatabase("Database", "1.0", "SqliteTrial", 20000);
    db.transaction(populateDB, errorCB, successCB);
}

function populateDB(tx) {
tx.executeSql('DROP TABLE IF EXISTS Subjects');
tx.executeSql('CREATE TABLE IF NOT EXISTS Subjects (id unique, subjectname)');
tx.executeSql('INSERT INTO Subjects (id, subjectname) VALUES (1, "Math")');
tx.executeSql('INSERT INTO Subjects (id, subjectname) VALUES (2, "Science")');
}  

function GetSubjectsFromDB()
{
    console.log("");
    tx.executeSql('SELECT * FROM Subjects', [], queryNSuccess, errorCB);
}

function queryNSuccess(tx, results) {
    alert("Query Success");
    console.log("Returned rows = " + results.rows.length);
    if (!results.rowsAffected) {
        console.log('No rows affected!');
        return false;
    }
    console.log("Last inserted row ID = " + results.insertId);
}

function errorCB(err) {
    alert("Error processing SQL: "+err.code);
}

Is there some problem with this line?

tx.executeSql('SELECT * FROM Subjects', [], queryNSuccess, errorCB);

The queryNSuccess isn't called, neither is the errorCB so I don't know what's wrong.

This is how I call it at another page:

Javascript:

     $(document).ready(function () {
            DisplayData();
            GetSubjectsFromDB(tx);
        });

How is "tx" initialized? Are there any errors reported in the console? — Pointy
– Pointy, Commented Jul 7, 2012 at 15:16
Your example shows how you create and populate your db - but not how you execute the statement you consider faulty (GetSubjectsFromDB). Please show this code instead. — raina77ow
– raina77ow, Commented Jul 8, 2012 at 11:20
@LeeTaylor There's no 'your' database here: it's a client-side storage. — raina77ow
– raina77ow, Commented Jul 8, 2012 at 11:21
Could you just show the line in which GetSubjectsFromDB is called? — raina77ow
– raina77ow, Commented Jul 8, 2012 at 13:33

raina77ow · Accepted Answer · 2012-07-08 17:15:18Z

1

No, it doesn't work like that. tx variable is actually a parameter that will be sent into the specified callback function by db.transaction method. So you're probably want to do this instead:

$(document).ready(function () {
    ...
    db.transaction(GetSubjectsFromDB);            
});

... and rewrite this function definition as...

function GetSubjectsFromDB(tx) { ... something to do with tx ... }

But there's another problem actually, as I see it. Your db variable, which stores the connection handle (created by window.openDatabase call) is local to onDeviceReady function - in other words, it's not visible outside of this function.

The easiest way to solve this is to define this variable at the Global context:

var dbh; // the SQLite connection handle
function onDeviceReady() { ... dbh = window.openDatabase ... }
function GetSubjects() { ... dbh.transaction(getSubjectsFromDb) ... }
function getSubjectsFromDb(tx) { ... tx.executeSql(...) ... }

Here's a great presentation describing general usage of WebSQL DB. But I'd also like to add that WebSQL DB API is considered deprecated; it's recommended to use IndexedDB instead. Here's something to read about it.

edited Jul 8, 2012 at 17:15

answered Jul 8, 2012 at 17:01

raina77ow

107k16 gold badges205 silver badges240 bronze badges

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

1 Comment

Sana Joseph Over a year ago

Thanks a million @raina77ow ... Your Answer is really useful.

Collectives™ on Stack Overflow

Can't get values from Sqlite DB using query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related