Tuesday 3 July 2012

Database in HTML5

Hello Guys,

Have you thinking of Creating | Inserting | Fetching of data in HTML5. If yes then this
Blog will helps you a lot.
Yesterday I worked on a phonegap Application where my need is to provide offline
Access to my mobile Apps. Its very Simple using HTML5 offline access to local
Database .

Here , I am sharing with a sample code:

1. Creating Database and Table in HTML5:
         This is, just a simple form. Of course, when you have a form, you want to capture the form submission somehow. In our case, we’ll use the new HTML5 local SQL database. We don’t need any servers or HTTP requests or anything except a compatible browser .
Here’s how we initialize the database:

Below can see that all we did here was add an openDatabase call and some SQL statements to create tables. These are just standard SQL statements (the reference SQL is from SQLite). 

<!DOCTYPE html> 
<html>  
  <head>
    <title>Offline Storage</title>
    <script src="http://www.google.com/jsapi"></script>
    <script>
      google.load("jquery", "1.4.1");
    </script>
    <script>
      var db = window.openDatabase("Student", "", "Previous Course", 1024*1000);
      $(document).ready(function() {
        db.transaction(function(tx) {
          tx.executeSql('CREATE TABLE IF NOT EXISTS Course(id INTEGER PRIMARY KEY, course_id
              INTEGER, subject_one TEXT, subject_two TEXT, email TEXT)', []);
        });
      });
    </script>
  </head>
  <body>
    <form method="get" id="course_form">
      <div>
        <label for="1">Subject 1</label>
        <input type="text" value="" id="subject1" name="subject1" placeholder="subject"/>
      </div>
      <div>
        <label for="2">Subject 2</label>
        <input type="text"  value="" id="subject2" name="subject2" placeholder="subject" />
      </div>
      <div>
        <input type="email" id="email" placeholder="Enter your email address" size="40"/>
      </div>
      <div>
        <input type="submit" value="Upload Data" />
      </div>
    </form>
  </body>
</html> 


2. Inserting Data into the Table:
               Now we’ll write a couple functions to help us insert subject into this table:

<!DOCTYPE html>
<html>
<head>
<title>OffLine Storage</title>
<script src="http://www.google.com/jsapi"></script>
<script>
      google.load("jquery", "1.4.1");
</script>
<script>
      var db = window.openDatabase("Student", "", "Previous Course", 1024*1000);

      function insertSubject(subject_one, subject_two, course_id, email) {
       db.transaction(function(tx) {
          tx.executeSql('INSERT INTO Course (course_id, subject_one, subject_two, email)
            VALUES (?, ?, ?, ?)', [course_id, subject_one, subject_two, email]);
       });
      }

      $(document).ready(function() {
        db.transaction(function(tx) {
          tx.executeSql('CREATE TABLE IF NOT EXISTS Course(id INTEGER PRIMARY KEY, course_id
           INTEGER, subject_one TEXT, subject_two TEXT, email TEXT)', []);
        });

        $('#course_form').submit(function() {
         course = { 1: $('#subject1').val(), 2: $('#subject2').val() };
          
          insertSubject($('#subject1').val(), $('#subject2').val(), 1, $('#email').val());

          return false;
        });
      });
    </script>
</head>
<body>
<form method="get" id="course_form">
<div>
<label for="1">Subject 1</label> <input type="text" value=""
id="subject1" name="subject1" placeholder="subject" />
</div>
<div>
<label for="2">Subject 2</label> <input type="text" value=""
id="subject2" name="subject2" placeholder="subject" />
</div>
<div>
<input type="email" id="email" placeholder="Enter your email address"
size="40" />
</div>
<div>
<input type="submit" value="Upload Data" />
</div>
</form>
</body>
</html>

3. Displaying | Fetching of data from the table :
       Getting data inserted into our database was trivial, but now we want to show previously submitted data, right? This is easy, too. We’ll start with two changes: (1) show ALL previously submitted data upon loading the page, and (2) update this list whenever new data are submitted.




<!DOCTYPE html>
<html>
<head>
<title>OffLine Storage</title>
<script src="http://www.google.com/jsapi"></script>
<script>
      google.load("jquery", "1.4.1");
</script>
<script>
      var db = window.openDatabase("Student", "", "Previous Course", 1024*1000);

      function insertSubject(subject_one, subject_two, course_id, email) {

       db.transaction(function(tx) {
          tx.executeSql('INSERT INTO Course (course_id, subject_one, subject_two, email)
VALUES (?, ?, ?, ?)', [course_id, subject_one, subject_two, email]);
       });
      }

      function renderResults(tx, rs) {
          e = $('#previous_course');
          e.html("");
          for(var i=0; i < rs.rows.length; i++) {
            r = rs.rows.item(i);
            e.html(e.html() + 'id: ' + r['id'] + ', subject_one: ' + r['subject_one'] + ',
  subject_two: ' + r['subject_two'] + ', email: ' + r['email'] + '<br />');
          }
        }
        function displayData(email) {
          db.transaction(function(tx) {
            if (!(email === undefined)) {
              tx.executeSql('SELECT * FROM Course WHERE email = ?', [email], renderResults);
            } else {
              tx.executeSql('SELECT * FROM Course', [], renderResults);
            }
          });
        }
        $(document).ready(function() {

         db.transaction(function(tx) {
                 tx.executeSql('CREATE TABLE IF NOT EXISTS Course(id INTEGER PRIMARY KEY,
course_id INTEGER, subject_one TEXT, subject_two TEXT, email TEXT)', []);
               });
               $('#course_form').submit(function() {
                course = { 1: $('#subject1').val(), 2: $('#subject2').val() };
                insertSubject($('#subject1').val(), $('#subject2').val(), 1, $('#email').val());
                displayData();
            return false;
          });
               displayData();
        });
    </script>
</head>
<body>
<form method="get" id="course_form">
<div>
<label for="1">Subject 1</label> <input type="text" value=""
id="subject1" name="subject1" placeholder="subject" />
</div>
<div>
<label for="2">Subject 2</label> <input type="text" value=""
id="subject2" name="subject2" placeholder="subject" />
</div>
<div>
<input type="email" id="email" placeholder="Enter your email address"
size="40" />
</div>
<div>
<input type="submit" value="Upload Data" />
</div>
</form>
<div>
<h2>Previous Course</h2>
</div>
<div id="previous_course"></div>
</body>
</html>

Hope this will helps some one.....
Enjoy Coding  :)

Mukesh Kumar

Hi Guys I am from Delhi working as Web/Mobile Application Developer(Android Developer), also have knowledge of Roboelctric and Mockito ,android test driven development... Blogging has been my passion and I think blogging is one of the powerful medium to share knowledge and ideas....

3 comments:

  1. Excellent post. I just copy and paste the code in different file like html1,html2,html3 for create,insert and fetch data. It's working.

    Keep it up.Best regards for ur future posts.

    ReplyDelete
  2. Is there a way to preload the database from an existing flat file>

    ReplyDelete

 

Copyright @ 2013 Android Developers Blog.