RSS

HTML5 SQL Database Tutorial

11 Aug
HTML5 Data Storage

HTML5 Data Storage

HTML5 supports client side data storage. There are several types of storing data with HTML5

1. local storage through localStorage object

2. session storage through sessionStorage object

3. SQL based database

This tutorial demonstrates how to manage a client side SQL based database with HTML5.

Demo

<!DOCTYPE html>
<html>
    <head>
        <meta name=viewport content="user-scalable=no,width=device-width" />
        <link rel="stylesheet" href="css/jquery.mobile-1.1.0.min.css" />
        <script src="js/jquery-1.7.2.min.js"></script>
        <script src="js/jquery.mobile-1.1.0.min.js"></script>
    </head>
    <body>
        <div data-role=page id=home>
            <div data-role=header>
                <h1>Home</h1>
            </div>

            <div data-role="content">
                <a href="#" data-role="button" id="create"> Create table </a>
                <a href="#" data-role="button" id="remove"> Delete table </a>
                <span> Item </span>
                <input type="text" id="item">
                <span> Quantity </span>
                <input type="text" id="quantity">
                <a href="#" data-role="button" id="insert">Insert item</a>
                <a href="#" data-role="button" id="list">List items </a>

                <ul data-inset="true" data-role="listview" id="itemlist"></ul>
            </div>
        </div>

        <script>
            var db = openDatabase ("itemDB", "1.0", "itemDB", 65535);
            $("#create").bind ("click", function (e)
            {
                db.transaction (function (transaction)
                {
                    var sql = "CREATE TABLE items " +
                        " (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                        "item VARCHAR(100) NOT NULL, " +
                        "quantity int(2) NOT NULL)"
                    transaction.executeSql (sql, undefined, function ()
                    {
                        alert ("Table created");
                    },error
                );
                });
            });

            $("#remove").bind ("click", function (e)
            {
                if (!confirm ("Delete table?", "")) return;;
                db.transaction (function (transaction)
                {
                    var sql = "DROP TABLE items";
                    transaction.executeSql (sql, undefined, success, error);
                });
            });

            $("#insert").bind ("click", function (event)
            {
                var item = $("#item").val ();
                var quantity = $("#quantity").val ();
                db.transaction (function (transaction)
                {
                    var sql = "INSERT INTO items (item, quantity) VALUES (?, ?)";
                    transaction.executeSql (sql, [item, quantity], function ()
                    {
                        alert ("Item created!");
                    }, error);
                });
            });

            $("#list").bind ("click", function (event)
            {
                $("#itemlist").children().remove()
                db.transaction (function (transaction)
                {
                    var sql = "SELECT * FROM items";
                    transaction.executeSql (sql, undefined,
                    function (transaction, result)
                    {

                        if (result.rows.length)
                        {
                            for (var i = 0; i < result.rows.length; i++)
                            {
                                var row = result.rows.item (i);
                                var item = row.item;
                                var quantity = row.quantity;
                                $("#itemlist").append("<li>" + item + " - " + quantity + "</li>");
                            }
                        }
                        else
                        {
                            $("#itemlist").append("<li> No items </li>");
                        }
                    }, error);

                });

            });

            function success ()
            {
            }

            function error (transaction, err)
            {
                alert ("DB error : " + err.message);
                return false;
            }
        </script>
    </body>

</html>

Please note that Firefox does not support this type of web SQL databases. If you try this in a FF browser, it will throw “ReferenceError: openDatabase is not defined”.

About these ads
 
2 Comments

Posted by on August 11, 2013 in HTML, HTML5, jQueryMobile

 

Tags:

2 responses to “HTML5 SQL Database Tutorial

  1. naga

    November 29, 2013 at 3:56 pm

    IT Is not working in Phonegap

     
  2. p

    September 16, 2014 at 10:28 pm

    I was very happy to uncover this website. I want to to
    thank you for ones time for this particularly wonderful read!!
    I definitely savored every bit of it and i also have you book marked to look
    at new information on your website.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: