Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to use SQLite database using Phonegap

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 899
    Comment on it

    In this article, you will learn about the SQLite database in Cordova for mobile application.
    SQLite is open source database which can be used in phonegap application. It has features as in standard relational database (SQL syntax, prepared statements, transactions). One of the advantage of using this is it takes less memory at runtime.

    To use it you need to download the plugin for SQLite in cordova from here:

    cordova plugin add cordova-sqlite-storage --save

    connect with database:
    create a database and its object which is used in the application.
     

    dbobj =window.sqlitePlugin.openDatabase({name:databasename});

    You can check this SQLite database in with this path:
     DDMS >> file explore >> data >> data >> project package >> databases >> FILENAME.
    As android and iOS have different syntax, you need to call device ready to get the platform of the device. you can use the following code:

    var dbobj;
    document.addEventListener("deviceready", onDeviceReady, false);
    
    if(device.platform=="Android"){
        dbobj = window.sqlitePlugin.openDatabase({name: "databasename"});
    }
    else{
        dbobj = window.openDatabase("databasename", "<version>", "<display_name>",'<size>');
      //dbobj = window.openDatabase("databasename", "4", "Cordova Demo",'');
    }

    Create required tables in database:
    transaction: runs a database transaction
    executeSql: executes SQL statement.

    dbobj.transaction(createSchema, errorInSchema, successInSchema);
     
    function createSchema(tx)
    {
        tx.executeSql('CREATE TABLE IF NOT EXISTS tablename(nID INTEGER PRIMARY KEY AUTOINCREMENT,sName TEXT)');
    }
     
    function errorInSchema()
    {
       alert(Error to create schema");
    }
     
    function successInSchema()
    {
        alert("Schema creation successful");
    }

    Insert records in the sqlite database:

    dbobj.transaction(insertRecord, errorDB, successDB);
     
    function insertRecord(tx)
    {
        tx.executeSql('INSERT INTO tablename (<em>sName</em>) VALUES(your name),[],SuccessInsert,errorInsert);
    }
     
    function SuccessInsert(tx,result){
              alert("Last inserted ID = " + result.insertId);
    }
     
    function errorInsert(error){
        alert("Error processing SQL: "+error.code);
    }

    Check update query to update existing record in the sqlite database:

    dbobj.transaction(updateRecord, errorDB, successDB);
    function updateRecord(tx){
        tx.executeSql('UPDATE tablename SET sName=your name where nID = 1', [],  successUpdate, errorUpdate);
    }
     
    function successUpdate(tx,result){
        alert("Last updated ID = " + result.insertId);
    }
     
    function errorUpdate(error){
        alert("Error processing SQL : "+error.code);
    }
    
    

    SQL select statement in the sqlite database:
     

    dbobj.transaction(selectRecords, errorDB, successDB);
     
    function selectRecords(tx)
    {
        tx.executeSql('SELECT * FROM tablename', [], successResults,errorInQuery);
    }
     
    function successResults(tx,results){
            //    alert(JSON.stringify(results));
            var sData = jquery.ParseJSON(results);
            var nLength = results.rows.length;
            for(var c=0;c<=len;c++){
             // execute or place your desired statement
            }
        }

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: