Back to Top

How to use SQLite Database in PhoneGap?

SQLite statements used in Android/iOS using Phonegap

In previous article we have covered the To setup SQLite for Android-Phonegap application. In this article, we are about to explore the SQLIte statements used in Android/iOS using PhoneGap.

Let’s start to create database and database object which will be used entire application.

iOS and Android both have different syntax for connecting with the database so you can use following code with a condition.

NOTE: If you are using above conditional code, you need to call code into device ready otherwise you will not get device.platform value.

Read about: Disable zoom-in/out effect in Android/iOS

Next is to create required tables into the database.Here we will use database object we have created above.

Here, In above code transaction used to runs a database transaction and executeSql method executes an SQL statement.

Read:Ipad Orientation Detection

Next, We will see how to insert records in the sqlite database using PhoneGap.

Now, It’s time to check update query to update existing record in the sqlite database using PhoneGap.

Here, you can use REPLACE query, if you want to update the record without using SELECT query. REPLACE query is required a primary key value to replace record.

Here, INSERT OR REPLACE would insert if the row does not exist or replace the values if it exists.

Well, It’s time to understand most important SQL select statement in the sqlite database using Phonegap syntax.

We’re done!I hope that you like this article and find many uses for your application.

As always, thanks for reading. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Comments (13)

  1. not working 🙁

    1. Did you get any error?

  2. There must be a link for complete code

  3. Looks interesting, but it’s pretty useless without the complete working code. Could you provide that, please? Thanks.

    1. This is the post to explain how you can use SQLite with PhoneGap. Also, I have given insert/update/delete/select examples too. What exactly you want? Any specific example? Thanks

      1. Hi i guess he would like an example on how to use it, like in html page how to call the functions. Thanks for your tutorizl

  4. Ibrahim Aboelsuod says:

    nice work, exactly what I was looking for. thanks ^^

  5. It’s nice! For me it’s working! Thank You! How you controle the data base version? Like the first time you install the app and make update schema on further versions?

  6. dont work
    i take mistake
    SQLitePlugin.js:25 Uncaught Error: Database location or iosDatabaseLocation value is now mandatory in openDatabase call

  7. hii, i am working on sqlite with angularjs but facing error in opening same database for multiple times, it waiting to connection and showing database is already open.Is there have any solution to check if database is already than no need to open it again and execute the transaction.

  8. this is the factory that i have created,and using that factory method for multiple times…

    .factory(‘dbCache’, function ($q) {
    return {
    getItem: function (a, key) {
    var db, deferred;

    var table;
    if (!isNaN(a))
    {

    table = “tab” + a;

    } else
    {
    table = a;
    }
    deferred = $q.defer();

    db = window.sqlitePlugin.openDatabase({name: “SQLiteTest.db”, location: ‘../../../../storage/emulated/0/Bharat’});
    db.transaction(function (tx) {

    tx.executeSql(‘create table if not exists ‘ + table + ‘(id integer primary key, data text)’);

    return tx.executeSql(“select * from ” + table, [], function (tx, res) {
    sqlite(‘selected data from get Item ‘ + res.rows.length);
    if (res.rows.length > 0) {
    sqlite(‘return success in get ‘ + res)
    return deferred.resolve(res);
    } else {
    sqlite(‘return false in get ‘)
    return deferred.resolve(false);
    }
    }, function (error)
    {
    sqlite(‘error in select query ‘ + table)
    });
    }, function (error)
    {
    sqlite(‘error inn getItem transaction factory ‘ + table)
    });
    return deferred.promise;
    },
    setItem: function (a, data1) {
    var table;
    if (!isNaN(a))
    {

    table = “tab” + a;

    } else
    {
    table = a;
    }

    printConsole(‘data param ‘ + JSON.stringify(data1));
    var data = JSON.stringify(data1);

    var db;

    db = window.sqlitePlugin.openDatabase({name: “SQLiteTest.db”, location: ‘../../../../storage/emulated/0/Bharat’});
    sqlite(‘db data ‘ + JSON.stringify(db))
    db.transaction(function (tx) {

    tx.executeSql(‘create table if not exists ‘ + table + ‘ (id integer primary key ,data text)’);

    return tx.executeSql(“select * from ” + table, [], function (tx, res) {

    sqlite(‘select query —‘ + res.rows.length);
    if (res.rows.length > 0) {
    printConsole(‘rows length is greater 1’ + data);

    return tx.executeSql(“UPDATE ” + table + ” SET data = ‘” + data + “‘”, [], function (tx, res) {

    return true;
    }, function (error) {

    });
    } else {

    return tx.executeSql(“INSERT INTO ” + table + ” (data) VALUES (?)”, [data], function (tx, res) {

    return true;
    });
    }
    });
    }, function (error)
    {

    });
    return false;
    },
    removeItem: function (a) {

    var table;
    if (!isNaN(a))
    {

    table = “tab” + a;

    } else
    {
    table = a;
    }
    document.addEventListener(“deviceready”, onDeviceReady, false);
    var db = null;
    function onDeviceReady() {
    sqlite(“inside ondevice “);
    db = window.sqlitePlugin.openDatabase({name: “SQLiteTest.db”, location: ‘../../../../storage/emulated/0/Bharat’});

    db.transaction(function (transaction) {

    return transaction.executeSql(“DROP TABLE IF EXISTS ” + table, [], function (tx, result) {

    return true;
    }, function (error) {
    sqlite(‘Something went Wrong in dron query ‘ + table);
    });
    });
    return false;

    }
    printConsole(‘after device ready’);

    }
    }
    });

  9. Hi Bhumi,

    Is there any way , we can search fast from 100k record sotre in sql lite record

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

Basic Github commands

Posted on 3 years ago

Bhumi

Two years blogiversary

Posted on 5 years ago

Bhumi

How to Add Tag in WordPress

Posted on 6 years ago

Bhumi

How To Turn On Output Buffering

Posted on 6 years ago

Bhumi