HTML 5. Work with Web SQL database

HTML 5 has many new features that allow web developers to create more powerful and rich applications. These features include new ways of storing data on the client, such as web storage (supported in IE8) and a web SQL database.

Moreover, if web storage is focused on storing key-value pairs, then in the case of a web SQL database we have a full-fledged sqlite (in all current implementations this particular database engine is used, which is a problem with standardization).

Further I will tell how to work with web SQL database. In this case, the examples will naturally be in JavaScript. In addition, it is worth noting that with the browsers support of this whole economy things are not very good, but things are gradually changing for the better and, say, in Opera 10.50 there will be support, and browsers on the WebKit engine already have it. In more detail about what browser that supports you can find out by clicking on the link .
')

Connection to the database.

Connecting to a database is easy:

db = openDatabase("ToDo", "0.1", "A list of to do items.", 200000);

This code creates an object representing the database, and if a database with the same name does not exist, then it is also created. In this case, the arguments indicate the database name, version, display name and approximate size. Also, it is important to note that the approximate size is not a limitation. The actual size of the database may vary.

The success of the connection to the database can be assessed by checking the db object for null:

if(!db){alert("Failed to connect to database.");}

Always undertake this check, even if the connection to the database for this user has already been made in the past, and was successful. Security settings may change, the disk space will run out (say, if the user is using a smartphone) or the moon phase is unsuitable.

Execution of requests.

To execute queries to the database, you first need to create a transaction by calling the database.transaction () function. It has one argument, namely, another JavaScript function that accepts a transaction object and makes requests to the database.

The actual SQL query itself can be executed by calling the executeSql function of the transaction object. It takes 4 arguments:

• SQL query string
• array of query parameters (parameters are substituted for question marks in SQL query)
• function called upon successful execution of the request
• function called in case of a query execution error

An example of the function executeSql below:

db.transaction(function(tx) {
tx.executeSql("SELECT COUNT(*) FROM ToDo", [], function(result){}, function(tx, error){});
});

Let's now change the code so that if it is impossible to select from the “ToDo” table (which does not exist yet), this table will be created.

db.transaction(function(tx) {
tx.executeSql("SELECT COUNT(*) FROM ToDo", [], function (result) { alert('dsfsdf') }, function (tx, error) {
tx.executeSql("CREATE TABLE ToDo (id REAL UNIQUE, label TEXT, timestamp REAL)", [], null, null);
})});


Insert data.

Let's insert a new row into the “ToDo” table. For those familiar with the SQL syntax, the example below will seem very familiar:

db.transaction(function(tx) {
tx.executeSql("INSERT INTO ToDo (label, timestamp) values(?, ?)", [" iPad HP Slate", new Date().getTime()], null, null);
});

The first question mark in the SQL query is replaced with “Buy iPad or HP Slate”, and the second one with a time stamp. As a result, the following query will be executed:
INSERT INTO ToDo (label, timestamp) values (" iPad HP Slate", 1265925077487)


Work with query results.

The result of the query for retrieving data contains a set of rows, and each row contains the values ​​of the table columns for this row.

You can access any result line by calling the function result.rows.item (i), where i is the index of the line. Next, to get the desired value, you need to refer to a specific column by name - result.rows.item (i) ["label"].

The following example displays the result of a database query on a page:

db.transaction(function(tx) {
tx.executeSql("SELECT * FROM ToDo", [], function(tx, result) {
for(var i = 0; i < result.rows.length; i++) {
document.write('<b>' + result.rows.item(i)['label'] + '</b><br />');
}}, null)});


Conclusion

Using the web SQL database provides powerful features, but do not get carried away. If the problem can be solved using web storage, it is better to use it.

You can find more information on this topic in the relevant section of the site w3c consortium .
Also for the web SQL database have already begun to develop ORM libraries. An example of such a library here .