HTML5, Chrome, and WebDatabase.

I started to get the HTML5 itch a few weeks ago so I started to look around the web to feed my appetite for all things HTML 5. Turns out info is pretty hard to come by. Not sure when your reading this but its been tough to find adequate examples of what IS and what ISNT implemented yet since it all depends on the browser your using. Anyway. So im going to jot down my notes on using HTML5’s Web Database here by creating a few examples.

Quick Agenda
1. What is HTML 5.
2. What you need to get started.
3. The tool to view the DB
4. How to create AND connect to a DB.
5. How to create a table.
6. How to insert/update/delete records into a table.
7. How to list records in a table.

Let get the ball rollin’ now…buhahah

What is HTML 5.
HTML 5 is a collection of new tags, (list here) and along with the new tags it provides a tool set to store data on the user’s machine using javascript, yes Javascript (Web database API here).

What you need to get started.
For the example you’ll need at least Chrome 4. Of course you can use any browser you wish but YOU NEED TO BE SURE HTML 5 WEB DATABASE IS SUPPORTED.. I strongly recommend you download and install Google’s Chrome browser, so far it’s great. Click here for download link.

Thats all you need, oh! and you’ll need some HTML and Javascript know-how.

Load the web page your going to test on. In my case its http://localhost then click on the “Control Page” document icon on the far-top-right corner of your browser . Select Developer > Developer Tools. (Figure 1.0)

You should now see a window as shown in Figure 1.2 (minus the DB). If you do not see the information displayed on Figure 1.2 click on “Storage” on the top menu. The “Storage” window contains a nice little DATABASE list on the left along with other items specific to that page. At the moment there’s no databases so you wont see anything. This is just the place where you’ll be able to work with the DB 🙂

How to create AND connect to the DB
Let’s create a database. Our database will have the name, “test_database” and will be our initial 1.0 version. (Note: the database name is case sensitive)

dbObj = null;
 
function connectToDB(){
  dbObj = openDatabase('test_database', '1.0', 
                                 'Test Database', 1024*1024*3);
}

connectToDB();

1. openDatabase(‘name of db’, ‘version’, ‘database description’, total bits table will use, call back function)

The callback function is used to create the table structure if present. In this example we’ll create the tables later. Also note that the size of the database is dependent on the amount of free space the user has available.

Once you run the above javascript on your sandbox, pull up the Chrome Developer tool, you should now see the database (Figure 1.2)

How to create a table.
Were going to add a table. We’re going open a connection to the database again as shown in the first example and then use the SQLTransation object to execute a CREATE statement.

dbObj = null;
 
function connectToDB()
{
   dbObj = openDatabase('test_database', '1.0', 
                                   'Test Database', 1024*1024*3);
}

connectToDB();

  //Create the table method
createTable = function()
{
   dbObj.transaction(function(SQLTransaction){
        SQLTransaction.executeSql(
        "CREATE TABLE userinfo (id INTEGER PRIMARY KEY, item1 TEXT)", [], 
        function(){ alert('I am a successfull callback function!'); }, 
        function(){ alert('Oh no! I am a sad error callback function'); } );
      });
}

createTable();

Copy the above javascript, place into your file, and refresh the page. You will now have the table ‘userinfo’ in our database. If you want to add more tables simple call the method once more with a different CREATE statement. (Figure 1.3).

Some explaining…
SQLTransaction has 2 methods, transaction() (Read/Write) and readTransaction() (Read only). We use the transaction method when we need to read AND write. The readTransaction() has only ‘read only’ functionality and best used for SELECT SQL statements.

To execute the SQL statements we use the executeSql() method. A parameter explanation is shown below.

executeSql(‘SQL Statement’, [], success callback function, error callback function) . The [] contains values for each ? used within the SQL statement. “INSERT INTO userinfo (item1) VALUES (?)” will replace the ? with the the content to save for the specific column. It’s also recommended to use this feature to code against SQL Injection attacks. (quote)

How to insert/update/delete records into the DB
At this point UPDATE, INSERT, as well as DELETE statements use the same process as the previous example. We use the SQLTransaction.transaction() method as well as the executeSql() method.

The next example will insert a record into our database table, ‘userinfo’, and we’ll verify the data entered using the Chrome Developer tool. Please note that the below code assumes you already created the table.

dbObj = null;
 
function connectToDB()
{
   dbObj = openDatabase('test_database', '1.0', 
				   'Test Database', 1024*1024*3);
}

connectToDB();


//Insert record into Table.
insertRecord = function(item1)
{
   dbObj.transaction(function(SQLTransaction){
      SQLTransaction.executeSql("INSERT INTO userinfo (item1) VALUES (?)", [item1], 
   function(){ alert('Record saved!'); }, 
   function(){ alert('Uh Oh! record not save!'); } );

   });

}
var item1 = "your not funny dude..really your not.";
insertRecord(item1);

How to List records
With data in our database, lets fetch the record we have in the table. To do so we use the read/write SQLTransaction method, readTransaction().

The following code assumes you have the database created, table created, and information stored in the table. If not go back and read the different sections.

dbObj = null;

function connectToDB()
{
   dbObj = openDatabase('test_database', '1.0', 
				   'Test Database', 1024*1024*3);
}

connectToDB();

fetchRecords = function(){

   dbObj.readTransaction(function(SQLTransaction){
      SQLTransaction.executeSql('SELECT id, item1 FROM userinfo', [], 
				function(SQLTransaction, data){
                                displayRecords(data); });	
		});

}

displayRecords = function(data){

   var a = document.getElementById("text");
   a.innerText = data.rows.item(0).item1;

}

fetchRecords();

In this example we query the table and display the results of the call. We use the SQLTransaction readTransaction() method, SQLTransaction executeSql() method passing in the SQL statement and a call back function displayRecords(). If the SQL SELECT statement successfully ran we pass a SQLResultSet object, ‘data’, to the displayRecords() method. The displayRecords() method fetches the HTMLElement node, ‘text’ and sets the text for the node to be the record fetched item1 value.

Fetching a specific value for a row.
To fetch the record we use the SQLResultSet object’s ‘rows’ attribute which returns a SQLResultSetRowList object. We then use the ‘item()’ method to fetch a specific row in the row list. In this case we fetch the first row and call the objects class property, ‘item1’. Note that each column in the table is represented but a class property.

Conclusion
We covered all the basic features of the HTML 5 web database. Inserting, creating a table, connecting to a DB, and fetching records. Give it a shot, if you have any questions feel free to leave a comment or refer to the W3C page 🙂

Armando Padilla