Create SQLite Off-Line Database and Insert,Update,Delete,Drop Operations in SQLite using JQuery ,HTML5 Inputs.

December 29, 2011 by TEJAS R PATEL

Purpose : Create SQLite Off-Line Database and Insert,Update,Delete,Drop Operations in SQLite using JQuery ,HTML5 Inputs.

Example :

Steps :

Step 1 : Create HTML Page.

<!DOCTYPE html PUBLIC>

 <html>

<head>

    <title>Exercise 3</title>

    <link rel="Stylesheet"  href="../Styles/ExercisePage3StyleSheet.css"/>

     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>

<body>

<h1>CONTACT FORM</h1>

              <form id="mycontact">

                     <fieldset>

                           <legend>Your details</legend>

                           <ol>

                                  <li>

                                         <label for="username">Name</label>

                                         <input id="username" type="text" placeholder="First and last name" required autofocus pattern=" ">

                        <input type="hidden" id="id"/>

                                  </li>

                                  <li>

                                         <label for="useremail">Email</label>

                                         <input id="useremail" type="text" placeholder="example@domain.com" required pattern=" ">

                                  </li>

                           </ol>

                     </fieldset>

                <button id="btnReset" type=submit>Reset</button>

                     <button id="submitButton" type="submit">SAVE</button>

                <button id="btnUpdate" type=submit>UPDATE</button>

                <button id="btnDrop" type=submit>DROP</button>

              </form><br />

        <div id="results"></div>

</body>

 <script type="text/javascript" src="../Scripts/ExercisePage3JS.js"> </script>

</html>

<span style="font-family: Calibri; font-size: small;">

Step 2 : Make a Style to a Contact Form in CSS file ( Ex. ExercisePage3StyleSheet.css)

html, body, h1, form, fieldset, legend, ol, li

{

    margin: 0;

    padding: 0;

}

body

{

    background: #ffffff;

    color: #111111;

    font-family: Georgia, "Times New Roman", Times, serif;

    padding : 20px;

}

form#mycontact

{

    background: #9cbc2c;

    -moz-border-radius: 5px;

    -webkit-border-radius: 5px;

    padding: 20px;

    width: 400px;

    height: 150px;

}

form#mycontact fieldset

{

    border: none;

    margin-bottom: 10px;

}

form#mycontact fieldset:last-of-type

{

    margin-bottom: 0;

}

form#mycontact legend

{

    color: #384313;

    font-size: 16px;

    font-weight: bold;

    padding-bottom: 10px;

}

form#mycontact > fieldset > legend:before

{

    content: "Step " counter(fieldsets) ": ";

    counter-increment: fieldsets;

}

form#mycontact fieldset fieldset legend

{

    color: #111111;

    font-size: 13px;

    font-weight: normal;

    padding-bottom: 0;

}

form#mycontact ol li

{

    background: #b9cf6a;

    background: rgba(255,255,255,.3);

    border-color: #e3ebc3;

    border-color: rgba(255,255,255,.6);

    border-style: solid;

    border-width: 2px;

    -moz-border-radius: 5px;

    -webkit-border-radius: 5px;

    line-height: 30px;

    list-style: none;

    padding: 5px 10px;

    margin-bottom: 2px;

}

form#mycontact ol ol li

{

    background: none;

    border: none;

    float: left;

}

form#mycontact label

{

    float: left;

    font-size: 13px;

    width: 110px;

}

form#mycontact fieldset fieldset label

{

    background:none no-repeat left 50%;

    line-height: 20px;

    padding: 0 0 0 30px;

    width: auto;

}

form#mycontact fieldset fieldset label:hover

{

    cursor: pointer;

}

form#mycontact textarea

{

    background: #ffffff;

    border: none;

    -moz-border-radius: 3px;

    -webkit-border-radius: 3px;

    -khtml-border-radius: 3px;

    font: italic 13px Georgia, "Times New Roman", Times, serif;

    outline: none;

    padding: 5px;

    width: 200px;

}

form#mycontact input:not([type=submit]):focus,

form#mycontact textarea:focus

{

    background: #eaeaea;

}

form#mycontact button

{

    background: #384313;

    border: none;

    float:left;

    -moz-border-radius: 20px;

    -webkit-border-radius: 20px;

    -khtml-border-radius: 20px;

    border-radius: 20px;

    color: #ffffff;

    display: block;

    font: 14px Georgia, "Times New Roman", Times, serif;

    letter-spacing: 1px;

    margin: 7px 0 0 5px;

    padding: 7px 20px;

    text-shadow: 0 1px 1px #000000;

    text-transform: uppercase;

}

form#mycontact button:hover

{

    background: #1e2506;

    cursor: pointer;

}

Step 3 : Write the code in JS File.

//  Declare SQL Query for SQLite

var createStatement = "CREATE TABLE IF NOT EXISTS Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, useremail TEXT)";

var selectAllStatement = "SELECT * FROM Contacts";

var insertStatement = "INSERT INTO Contacts (username, useremail) VALUES (?, ?)";

var updateStatement = "UPDATE Contacts SET username = ?, useremail = ? WHERE id=?";

var deleteStatement = "DELETE FROM Contacts WHERE id=?";

var dropStatement = "DROP TABLE Contacts";

 var db = openDatabase("AddressBook", "1.0", "Address Book", 200000);  // Open SQLite Database

var dataset;

var DataType;

 function initDatabase()  // Function Call When Page is ready.

{

    try {

        if (!window.openDatabase)  // Check browser is supported SQLite or not.

        {

            alert('Databases are not supported in this browser.');

        }

        else {

            createTable();  // If supported then call Function for create table in SQLite

        }

    }

    catch (e) {

        if (e == 2) {

            // Version number mismatch. 

            console.log("Invalid database version.");

        } else {

            console.log("Unknown error " + e + ".");

        }

        return;

    }

}

function createTable()  // Function for Create Table in SQLite.

{

    db.transaction(function (tx) { tx.executeSql(createStatement, [], showRecords, onError); });

}

function insertRecord() // Get value from Input and insert record . Function Call when Save/Submit Button Click..

{

        var usernametemp = $('input:text[id=username]').val();

        var useremailtemp = $('input:text[id=useremail]').val();
        db.transaction(function (tx) { tx.executeSql(insertStatement, [usernametemp, useremailtemp], loadAndReset, onError); });

        //tx.executeSql(SQL Query Statement,[ Parameters ] , Sucess Result Handler Function, Error Result Handler Function );

}

function deleteRecord(id) // Get id of record . Function Call when Delete Button Click..

{

    var iddelete = id.toString();

    db.transaction(function (tx) { tx.executeSql(deleteStatement, [id], showRecords, onError); alert("Delete Sucessfully"); });

    resetForm();

}

function updateRecord() // Get id of record . Function Call when Delete Button Click..

{

    var usernameupdate = $('input:text[id=username]').val().toString();

    var useremailupdate = $('input:text[id=useremail]').val().toString();

    var useridupdate = $("#id").val();

    db.transaction(function (tx) { tx.executeSql(updateStatement, [usernameupdate, useremailupdate, Number(useridupdate)], loadAndReset, onError); });

}

function dropTable() // Function Call when Drop Button Click.. Talbe will be dropped from database.

{

    db.transaction(function (tx) { tx.executeSql(dropStatement, [], showRecords, onError); });

    resetForm();

    initDatabase();

}

function loadRecord(i) // Function for display records which are retrived from database.

{

    var item = dataset.item(i);

    $("#username").val((item['username']).toString());

    $("#useremail").val((item['useremail']).toString());

    $("#id").val((item['id']).toString());

}

function resetForm() // Function for reset form input values.

{

    $("#username").val("");

    $("#useremail").val("");

    $("#id").val("");

}

function loadAndReset() //Function for Load and Reset...

{

    resetForm();

    showRecords()

}

function onError(tx, error) // Function for Hendeling Error...

{

    alert(error.message);

}

function showRecords() // Function For Retrive data from Database Display records as list

{

    $("#results").html('')

    db.transaction(function (tx) {

        tx.executeSql(selectAllStatement, [], function (tx, result) {

            dataset = result.rows;

            for (var i = 0, item = null; i < dataset.length; i++) {

                item = dataset.item(i);

                var linkeditdelete = '<li>' + item['username'] + ' , ' + item['useremail'] + '    ' + '<a href="#" onclick="loadRecord(' + i + ');">edit</a>' + '    ' +

                                            '<a href="#" onclick="deleteRecord(' + item['id'] + ');">delete</a></li>';

                $("#results").append(linkeditdelete);

            }

        });

    });

}

$(document).ready(function () // Call function when page is ready for load..

{
;

    $("body").fadeIn(2000); // Fede In Effect when Page Load..

    initDatabase();

    $("#submitButton").click(insertRecord);  // Register Event Listener when button click.

    $("#btnUpdate").click(updateRecord);

    $("#btnReset").click(resetForm);

    $("#btnDrop").click(dropTable);

});

Step 4 : Run application into the browser..

SQLite Database look like,

Posted in HTML5 | Tagged delete, drop, html, insert, jquery, off line, off line storage, patel, sqlite, storage, table, tejas, update, wordpress | 15 Comments

15 Responses

on March 16, 2012 at 2:33 am | Reply Rob H.

Excellent! One of a very few demos that is complete and actually works.
Thank You.
on September 6, 2012 at 2:51 am | Reply subhasis

very good..i like this one..
on January 10, 2013 at 12:02 pm | Reply Manu

Works perfect. Thank you very much
on February 12, 2013 at 1:55 pm | Reply pavan

I love this really helped a lot…
on August 2, 2013 at 10:24 pm | Reply Glenn

This is very nice. Where did you go?
on October 23, 2013 at 6:00 am | Reply sowmiya

how can i view the sqlite database
- on November 20, 2013 at 6:46 am | Reply TEJAS R PATEL
  
  have you searched in “AppData” folder in win OS ?
on November 5, 2013 at 8:57 am | Reply Dileep Kumar

IS this using SQLite Database. If yes where the DB file is saved?
- on November 20, 2013 at 6:46 am | Reply TEJAS R PATEL
  
  have you searched in “AppData” folder in win OS ?
on November 18, 2013 at 4:41 am | Reply panupong

Thank you vary much
on November 18, 2013 at 2:44 pm | Reply Björn

This could be the solution i’ve been looking for for a long time. I have a question though. Where exactly is the database stored?? I’ve tried this example so the db and the table is created and working, but I can’t find the db-file in the sqlite-folder nor in the Chrome-folder, nor anywhere else. But it has to be stored locally somewhere. To gain the proper functionality I need to locate it for backup-purposes.

Thanks!
- on November 20, 2013 at 6:46 am | Reply TEJAS R PATEL
  
  have you searched in “AppData” folder in win OS ?
  - on November 20, 2013 at 9:48 am Björn
    
    There is no folder named “AppData” actually. However, I looked through every folder with similar name (and function I guess) but came up with nothing.
    
    Shouldn’t there be a db-file named “AddressBook” somewhere on the harddrive after running your example?? I’ve tried searching specifically for it, but again, nothing. Could it be embedded in one of the files from the example somehow? Sounds far fetched, but still…
  - on November 21, 2013 at 6:12 am TEJAS R PATEL
    
    “appdata” is hidden folder in win which is “applicationdata” in previous os…
on November 28, 2013 at 9:39 am | Reply Björn

I can only find att hidden folder named “ProgramData”, but I didn’t find anything there either.
Anyways, how do I get it to connect to att specific sqlite db?