HTML5 Web SQL Database

Posted by on 

 1 <html>
 2 <head>
 3 <meta name="expires" content="0" />
 4 <title>:: Web Developer :: </title>
 5 </head>
 6 <body>
 7 <h3><a href="#">:: Web Developer :: </a></h3>
 8 <ul id="results"></ul>
 9 <div id="logs"></div>
10 Record: <input type="text" id="items" />  <br /> <br />
11 <button onClick="createRecord()">Create Record</button> 
12 <button onClick="createTable()">Create Table</button> 
13 <button onClick="dropTable()">Drop Table</button> <br /> <br />
14 Notes: Update by clicking the desired item then overwriting the text. <br />
15 In Google Chrome, see the generated database: Developer > Developer Tools > Storage <br />
16 or by simply pressing CTRL + SHIFT + I <br /> <br />
17 Here's the code: <br /> <br />
18 </body>
19 <script>
20 // Arguments: Database Name, Version, Display Name, Estimated Size (bytes)
21 var database = openDatabase("TEST_DATABASE", "1.0", "HTML5 Web SQL Database", 300000);
22 var log = document.querySelector("#logs");
23 
24 // Read records stored
25 function readRecord() {
26  document.querySelector("#results").innerHTML = '';
27  database.transaction(function(tx) {
28     tx.executeSql("SELECT * FROM TEST_TABLE", [], function(tx, result) {
29       for (var i = 0, item = null; i < result.rows.length; i++) {
30         item = result.rows.item(i);
31         document.querySelector("#results").innerHTML += 
32             '<li><span contenteditable="true" onkeyup="updateRecord('+item['id']+', this)">'+
33             item['text'] + '</span> <a href="#" onclick="deleteRecord('+item['id']+')">[Delete]</a></li>';
34       }
35     });
36   });
37 }
38 
39 // Update record on the fly
40 function updateRecord(id, textEl) {
41   database.transaction(function(tx) {
42     tx.executeSql("UPDATE TEST_TABLE SET text = ? WHERE id = ?", [textEl.innerHTML, id], null, onError);
43   });
44 }
45 
46 // Delete selected record 
47 function deleteRecord(id) {
48   database.transaction(function(tx) {
49     tx.executeSql("DELETE FROM TEST_TABLE WHERE id=?", [id],
50         function(tx, result) { readRecord() }, 
51         onError);
52   });
53 }
54 
55 // Add record with random values
56 function createRecord() {
57   var num = Math.round(Math.random() * 10000); // Random data
58   database.transaction(function(tx) {
59     tx.executeSql("INSERT INTO TEST_TABLE (id, text) VALUES (?, ?)", [num, document.querySelector('#items').value],
60         function(tx, result) {
61           log.innerHTML = '';
62           readRecord();
63         }, 
64         onError);
65   });
66 }
67 
68 // Create table 
69 function createTable() {
70   database.transaction(function(tx) {
71     tx.executeSql("CREATE TABLE TEST_TABLE (id REAL UNIQUE, text TEXT)", [],
72         function(tx) { log.innerHTML = '<p>TEST_TABLE created!</p>'; },
73         onError);
74   });
75 }
76 
77 // Delete table from database
78 function dropTable() {
79   database.transaction(function(tx) {
80     tx.executeSql("DROP TABLE TEST_TABLE", [],
81         function(tx) { readRecord(); log.innerHTML = '<p>TEST_TABLE dropped!</p>'; }, 
82         onError);
83   });
84 }
85 
86 // Log errors
87 function onError(tx, error) {
88   log.innerHTML += '<p>' + error.message + '</p>';
89 }
90 </script>
91 </html>

Comments

  1. Unknown22 May 2013 at 03:13

    Hi,

    This is a nice article. Thanks for sharing your knowledge. There are few links that also described "HTML5 Web SQL Database" in a good way.

    http://www.tutorialspoint.com/html5/html5_web_sql.htm
    http://www.mindstick.com/Blog/379/HTML5%20Web%20SQL%20Database
    http://www.w3schools.com/html/html5_webstorage.asp

    ReplyDelete

Post a Comment