Problem
You wold like for your HTML5 application to persist user data in a SQL structure.
Solution
You can use the SQLite datastore available to HTML5 browsers.
Detailed explanation
The following example requires an HTML5 browser. It will create a SQLite database and persist data stored in a table structure.
Let's review the code below.
The page contains some form input fields to hold some information about the user. This includes the firstName, lastName, and phone. There are also buttons to perform the functions of reset, update, insert, and drop. There is also a div with the id or results to display the data.
Now, lets review the process.
Within the JavaScript block, there are variables to hold references to the form elements and the results div. Next, the SQL strings are defined which will be used to interact with the database.
The db variable is defined using the openDatabase function. The database is named AddressBook.
Next, the createTable function is called. Within the createTable function the createStatement is executed with no arguments passed in which will create the table Contacts if it doesn't currently exist. The showRecords method is called when the statement executes successfully. If not, the onError method is called if the statement fails.
Within the showRecords function the results.innerHTML is set to empty string. The selectAllStatament is executed with no variables passed in. On success, the result.rows array is set to the dataset class variable. The dataset is then looped over and the results.innerHTML is written with the lastname, firstname and two links for editing and deleting the contacts.
At this point the page is fully loaded, the AddressBook database and the Contacts table have been created. (See figure 1)
When the user fills out the form and clicks the Insert button, the insertRecord function is called. Within this function the insertStatement is called with the firstName.value, lastName.value, and phone.value arguments passed in. On success the loadAndReset function is called which will reset the form and call the showRecords function which will display the new record within the results div. (See figure 2)
When the edit links is clicked, the loadRecord function is called and the form is populated.
When the delete link is clicked, the deleteRecord function is called. Within the deleteFuction the deleteStatement is executed with the id argument passed in. On success, the showRecords function is called which refreshes the results div.
Finally, there is a drop button which when clicked calls the dropTable function. Within the dropTable function the dropStatement is executed with no arguments passed in. On success, the showRecords function is called which refreshes the results div.
HTML CODE+JAVASCRIPT
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>SQL Storage</title>
<body>
<br/><br/>
<div align="center">
<input type="hidden" id="id"/>
First name:<input type="text" id="firstName"/><br/>
Last name:<input type="text" id="lastName"/><br/>
Phone: <input type="text" id="phone"/><br/>
<button onClick="resetForm()">Reset Form</button>
<button onClick="updateRecord()">Update</button>
<button onClick="insertRecord()">Insert</button>
<button onClick="dropTable()">Drop Table</button>
<div id="results"></div>
</div>
</body>
<script>
var results = document.getElementById('results');
var id = document.getElementById('id');
var firstName = document.getElementById('firstName');
var lastName = document.getElementById('lastName');
var phone = document.getElementById('phone');
var createStatement = "CREATE TABLE IF NOT EXISTS Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, phone TEXT)";
var selectAllStatement = "SELECT * FROM Contacts";
var insertStatement = "INSERT INTO Contacts (firstName, lastName, phone) VALUES (?, ?, ?)";
var updateStatement = "UPDATE Contacts SET firstName = ?, lastName = ?, phone = ? WHERE id = ?";
var deleteStatement = "DELETE FROM Contacts WHERE id=?";
var dropStatement = "DROP TABLE Contacts";
var db = openDatabase("AddressBook", "1.0", "Address Book", 200000);
var dataset;
createTable();
function onError(tx, error) {
alert(error.message);
}
function showRecords() {
results.innerHTML = '';
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);
results.innerHTML +=
'<li>' + item['lastName'] + ' , ' + item['firstName'] + ' <a href="#" onclick="loadRecord('+i+')">edit</a> ' +
'<a href="#" onclick="deleteRecord('+item['id']+')">delete</a></li>';
}
});
});
}
function createTable() {
db.transaction(function(tx) {
tx.executeSql(createStatement, [], showRecords, onError);
});
}
function insertRecord() {
db.transaction(function(tx) {
tx.executeSql(insertStatement, [firstName.value, lastName.value, phone.value], loadAndReset, onError);
});
}
function loadRecord(i) {
var item = dataset.item(i);
firstName.value = item['firstName'];
lastName.value = item['lastName'];
phone.value = item['phone'];
id.value = item['id'];
}
function updateRecord() {
db.transaction(function(tx) {
tx.executeSql(updateStatement, [firstName.value, lastName.value, phone.value, id.value], loadAndReset, onError);
});
}
function deleteRecord(id) {
db.transaction(function(tx) {
tx.executeSql(deleteStatement, [id], showRecords, onError);
});
resetForm();
}
function dropTable() {
db.transaction(function(tx) {
tx.executeSql(dropStatement, [], showRecords, onError);
});
resetForm();
}
function loadAndReset(){
resetForm();
showRecords();
}
function resetForm(){
firstName.value = '';
lastName.value = '';
phone.value = '';
id.value = '';
}
</script>
</html>
figure1:
figure2:
