HTML 5. Working 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 to store data on the client, such as web storage (supported in IE8) and web SQL database.

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

Next, I’ll show you how to work with web SQL database. In this case, the examples will naturally be in JavaScript. In addition, it is worth noting that things are not very good with browser support for this whole economy, but everything is gradually changing for the better and, say, in Opera 10.50 there will be support, and there are already browsers on the WebKit engine. More information about which browser that supports can be found by clicking on the link .

Connection to the database.

Connecting to the database is very simple:

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

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

The success of connecting to the database can be estimated by checking the db object for null:

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

Always do this check, even if the connection to the database for this user has already been done in the past, and was successful. Security settings may change, disk space will end (say, if the user uses a smartphone), or the moon phase will be inappropriate.

Fulfillment 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 takes a transaction object and makes database queries.

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

• SQL query string
• an array of query parameters (parameters are substituted in place of question marks in the 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: Let's change the code so that if it is impossible to select from the “ToDo” table (which does not exist yet), this table is created.

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



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 in the ToDo table. For those familiar with SQL syntax, the example below will seem very familiar: The first question mark in the SQL query is replaced with "Buy iPad or HP Slate", and the second with a timestamp. As a result, approximately the following request will be executed:

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


INSERT INTO ToDo (label, timestamp) values ("Купить iPad или HP Slate", 1265925077487)


Work with query results.

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

You can access any row of the result by calling the function result.rows.item (i), where i is the row index. Next, to get the required 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('' + result.rows.item(i)['label'] + '
');
}}, null)});


Conclusion

Using 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 additional information on this topic in the corresponding section of the w3c consortium website .
Also for the web SQL database, ORM libraries have already begun to be developed. An example of such a library is here .