How to compare QML string from a SQLight DataBase

Question

I've being trying to create a match string function which will read a SQLLite (javascript created) Database from qml and match the string (web address given to it in my case), this is my Database file code:

.pragma library

var db;
// opens database at launch
function openDB()
{
    db = openDatabaseSync("BookmarksDB","1.0","Bookmarks Database",1000000);
    createTable();
}

// creates table if it doesn't exist, otherwise ignores
function createTable()
{
    db.transaction(
       function(tx) {
          tx.executeSql("CREATE TABLE IF NOT EXISTS bookmarks (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, url TEXT, creationdate TEXT, modified DATETIME)");
       }
    )
}

// deletes table
function dropTable()
{
    db.transaction(
       function(tx) {
          tx.executeSql("DROP TABLE IF EXISTS bookmarks");
       }
    )
}

// creates a single bookmark record
function createBookmark(bookmarkItem)
{
    db.transaction(
       function(tx) {
          tx.executeSql("INSERT INTO bookmarks (title, url, creationdate, modified) VALUES(?,?,?,?)",[bookmarkItem.title, bookmarkItem.url, bookmarkItem.creationdate, bookmarkItem.modified]);
       }
    )
}

// updates a single bookmark record
function updateBookmark(bookmarkItem)
{
    db.transaction(
       function(tx) {
          tx.executeSql("UPDATE bookmarks SET title = ?, url = ?, creationdate = ?, modified = ? WHERE id = ?",
                        [bookmarkItem.title, bookmarkItem.url, bookmarkItem.creationdate, bookmarkItem.modified, bookmarkItem.id]);
       }
    )
}

// deletes a single bookmark record
function deleteBookmark(id)
{
    db.transaction(
       function(tx) {
          tx.executeSql("DELETE FROM bookmarks WHERE id = ?", [id]);
       }
    )
}
// read list of bookmarks
function readBookmarkList(model)
{
    model.clear();
    var sqlstring = "SELECT id, title, url, creationdate FROM bookmarks";
    db.readTransaction(
       function(tx) {
          var rs;
          rs = tx.executeSql(sqlstring);
             for (var i = 0; i < rs.rows.length; i++) {
                model.append(rs.rows.item(i))
             }
       }
    )
}



// read a single bookmark item
function readBookmarkItem(id) {
    var data = {}
    db.readTransaction(
       function(tx) {
          var rs = tx.executeSql("SELECT * FROM bookmarks WHERE id=?", [id])
          if(rs.rows.length === 1) {
             data = rs.rows.item(0)
          }
       }
    )
    return data;
}

// create a default bookmark item
function defaultItem()
{
    return {title: "", url: "", creationdate: new Date(), modified: new Date()}
}

I was wanting to create something like, function checkUrl(url){

if(dbvalues == url) { return true } else{return false}

} }

But I haven't a clue how to read all the data from the tables and get it to compare with the url given in the function.

Can somebody please help me out?

I'm a complete noob with SQL stuff

Using Qt Quick 1.1 on Symbian

Answer 1

Why don't you try something like

function checkURL(url) {
    var exists
    db.readTransaction(function(tx) {
        var sql = "SELECT url FROM bookmarks WHERE url=? LIMIT 1";
        var rs = tx.executeSql(sql, [url])
        exists = rs.rows.length > 0
    })
    return exists
}

LIMIT 1 clause will tell DB engine to stop searching when it finds the first row matching your criteria.

Haven't tried this myself, but it should work.