Creating SQL lite database using PhoneGap


1. Create a new android mobile application using Jquery mobile and PhoneGap.
 The openDatabase() method used for creating database in mobile.
                Syntax:
        var dbShell = window.openDatabase(name, version, display_name, size);
 Description :
                This method will create a new SQL Lite database and return a new database object. You can  use this database for performing sql operation.       
                Parameters
1.       Name – The name of the database
2.       Version – The version of the database
3.       Display name – The display name of the database
4.       Size – The size of the database in bytes.
2. Replace the index.html with following code.

<!DOCTYPE HTML>
<html>
  <head>
    <meta name="viewport" content="width=320; user-scalable=no" />
    <meta http-equiv="Content-type" content="text/html; charset=utf-8">
    <title>PhoneGap Demo With JQuery Mobile</title>
      <link rel="stylesheet" href="jquery.mobile/jquery.mobile-1.0rc2.css" type="text/css" charset="utf-8" />
        <link rel="stylesheet" href="pgandjqm-style-override.css" type="text/css" charset="utf-8" />
        <script type="text/javascript" src="jquery.mobile/jquery-1.6.4.min"></script>
        <script type="text/javascript" charset="utf-8" src="phonegap-1.1.0.js"></script>
        <script src="jquery.mobile/jquery.mobile-1.0rc2.js"></script>
        <script type="text/javascript" charset="utf-8" src="main.js"></script>
  </head>
  <body onload="init();">
    <div data-role="page" data-theme="b">
            <div data-role="header">
            <h4>PhoneGap with JQuery Mobile</h4>
            </div> <!-- end jqm header -->
      <div data-role="content">
            <a href="#" data-role="button" onclick="displayDatabaseDetails()">DB Details</a>
            <span id="databaseDetails">
                 
            </span>
      </div> <!-- end jqm content -->
      <div data-role="footer">
            <h4>Footer content</h4>
      </div> <!-- end jqm footer -->
    </div><!-- end jqm page -->
  </body>
</html>

3. Open the main.js and replace with following code.

 
var localDatabase = null;
function init() {
      //Creating SQL Lite database..
      localDatabase = window.openDatabase("TestDB","1.0","PhoneGap TestDB ",300000);
      localDatabase.transaction(populateDatabase,errorCreateDatabase,successCreateDatabase);
}

function populateDatabase(db) {
      db.executeSql("DROP TABLE IF EXISTS DEMO");
      db.executeSql("CREATE TABLE IF NOT EXISTS DEMO(id unique,name)");
      //Inserting some dummy records into demo table.
      db.executeSql("INSERT INTO DEMO(id,name) VALUES (1,'xxxxxxx')");
      db.executeSql("INSERT INTO DEMO(id,name) VALUES (2,'yyyyyyy')");
      db.executeSql("INSERT INTO DEMO(id,name) VALUES (3,'zzzzzzz')");
}

function errorCreateDatabase(err) {
      alert("Error in creation database function...! " + err.code);
}

function successCreateDatabase() {
      //alert("Database created successfully....!");
}

function displayDatabaseDetails() {
      localDatabase.transaction(executeQuery,errorTransaction,successTransaction);
}

function errorTransaction(err) {
      alert("Error in transaction....!");
}

function successTransaction() {
      alert("Success transaction....!");
}

function executeQuery(db) {
      db.executeSql("SELECT * FROM DEMO",[],querySuccess,queryFailure);
}

function querySuccess(tx,results) {
      //alert("Query executed successfully....!");
      //Getting row length...
var rowLength = results.rows.length;
      var str = "ID     Name<br/>";
      for(var i=0;i<rowLength;i++) {
            //Getting id and name value using item() method
            str += results.rows.item(i).id + "     " + results.rows.item(i).name + "<br/>";
      }
      $("#databaseDetails").html(str);
}

function queryFailure() {
      alert("Query execution failed....!");
}

Here the  executeSql()method was used for executing sql query.

4. Now run the application .
                    
               

5. Click the DB Details button, you can able to see the all the inserted records.
                


Comments