Web SQL Database: Creating and Querying Databases

This tutorial demonstrates how you can use SQL databases in Tizen applications.

This feature is supported in mobile applications only.

Warm-up

Become familiar with the Web SQL Database API features by learning about:

Asynchronous operations
- Opening a Database Asynchronously
  Learn how to create and open a database asynchronously.
- Executing SQL Statements Asynchronously
  Learn how to execute SQL statements asynchronously.
- Accessing SQL Results Asynchronously
  Learn how to access SQL statement results asynchronously.
- Handling a Syntax Error Asynchronously
  Learn how handle SQL database-related syntax errors asynchronously.
Synchronous operations
- Opening a Database Synchronously
  Learn how to create and open a database synchronously.
- Executing SQL Statements Synchronously
  Learn how to execute SQL statements synchronously.
- Accessing SQL Results Synchronously
  Learn how to access SQL statement results synchronously.
- Handling Errors Synchronously
  Learn how handle SQL database-related errors synchronously.

Opening a Database Asynchronously

To provide users with SQL database features, you must learn to create and open an SQL database asynchronously:

Use the openDatabase() method to access a database. If the database does not exist, the method first creates it and then opens it:

var db;
var version = 1.0;
var dbName = "tizendb";
var dbDisplayName = "tizen_test_db";
var dbSize = 2 * 1024 * 1024;
try 
{
   db = openDatabase(dbName, version, dbDisplayName, dbSize, function(database) 
   {
      alert("database creation callback");
   });
}

The method takes the following arguments: unique name of the database, expected version of the database to be opened (if an empty string is given any version can be loaded), display name, the estimated size of database (number of bytes), and, optionally, the database creation event handler.

Note
The creation event handler is invoked only once if the database does not exist. There is no event handler for the database opened event.

Source Code

For the complete source code related to this use case, see the following file:

websqldatabase_example.html

Executing SQL Statements Asynchronously

To provide users with SQL database features, you must learn to execute SQL statements asynchronously:

In the Web SQL Database API, each SQL statement must be executed under a transaction. To create a transaction, use either transaction() or readTransaction() method returned by the openDatabase() method:
```
db.transaction(function(t)
{
   /* Place SQL statements here */
}, function() 
{
   alert("SQL statements were executed successfully.");
});
```
The difference between the transaction() and readTransaction() methods is that the latter cannot be used with SQL statements that change the database (such as INSERT, UPDATE, DELETE, or CREATE).

Note

When possible, use the readTransaction() to obtain better execution performance of SQL statements.

Note
When possible, use the readTransaction() to obtain better execution performance of SQL statements.

To execute a SQL statement, use the executeSql() method. The SQL statement is the first parameter of the method and cannot contain SQL transaction statements (such as BEGIN, END, or ROLLBACK):

t.executeSql("CREATE TABLE tizenTable (id INTEGER PRIMARY KEY, title TEXT, content TEXT, insertDay DATETIME)", 
                          [], function(sqlTransaction, sqlResultSet) 
{
   alert("Table has been created.");
}, function(sqlTransaction, sqlError) 
{
   /* Error handling */
});

Pass arguments to the SQL statement:
```
sqlTransaction.executeSql("SELECT * FROM tizenTable WHERE id=?", [value]);
```
Note

Use an array to pass the arguments to secure SQL statements from SQL injection attacks.

Source Code

For the complete source code related to this use case, see the following file:

websqldatabase_example.html

Accessing SQL Results Asynchronously

To provide users with SQL database features, you must learn to access SQL statement results asynchronously:

When a SQL statement is executed, its event handler is invoked and returns the result as a sqlResultSet object:

The result object of the INSERT statement contains the insert ID, which stores the identifier of the added record. If multiple records were inserted, the insert ID contains the ID of the last inserted record:

sqlTransaction.executeSql("INSERT INTO tizenTable(title, content, insertDay) VALUES (?, ?, ?)", 
                          [title, context, day], 
                          function(sqlTransaction, sqlResultSet) 
{
   alert("The 'id' of the new record  is " + sqlResultSet.insertId);
});

The result object of the SELECT statement stores also the number of records that were inserted, changed, or deleted. It contains the number of selected rows (length field) and the item() method. Use the method with the index argument (integer value from 0 to rows.length - 1) to get individual records:

sqlTransaction.executeSql("SELECT id, title, author FROM books", [], 
                          function(sqlTransaction, sqlResultSet) 
{
   var book, i, booksNumber = sqllResultSet.rows.length;
   for (i = 0; i < booksNumber; i++) 
   {
      book = sqlResultSet.rows.item(i);
      alert("id: " + book.id + ", title: " + book.title 
            + ", author: " + book.author);
   }
});

Source Code

For the complete source code related to this use case, see the following file:

websqldatabase_example.html

Handling a Syntax Error Asynchronously

To provide users with SQL database features, you must learn to handle SQL database-related errors asynchronously:

Handle a syntax error in the sqlError object:

sqlTransaction.executeSql("SELECT * FROM notExistingTable", [], 
                          function(sqlTransaction, sqlResultSet) {}, 
                          function(sqlTransaction, sqlError) 
{
   switch (sqlError.code) 
   {
      case sqlError.SYNTAX_ERR:
         alert("Syntax error has occurred. " + sqlError.message);
         break;
      default:
          alert("Other error");
   }
});

Other types of errors that can occur are exceptions. The sqlException object has the same fields as the sqlError object but it must be handled in the try - catch block.

Opening a Database Synchronously

To provide users with SQL database features, you must learn to create and open an SQL database synchronously:

Use the openDatabaseSync() method to access a database. The method can only be used in the Web Worker context. If the database does not exist, the method first creates it and then opens it:

var databaseSync = null;
try 
{
   databaseSync = openDatabaseSync("dbName", "1.0", "display database name", 1024 * 1024, function(databaseSync) 
   {
      alert("database creation callback");
   });
}

Note
The creation event handler is invoked only once if the database does not exist. There is no event handle for the database opened event but, in the synchronous database API, no other code is run until the database creation operation is completed.

Executing SQL Statements Synchronously

To provide users with SQL database features, you must learn to execute SQL statements synchronously:

In the Web SQL Database API, each SQL statement must be executed under a transaction. To create a transaction, use either transaction() or readTransaction() method returned by the openDatabaseSync() method:
```
database.transaction(function(sqlTransactionSync) 
{
   /* Place SQL statements here */
}, function() 
{
   alert("SQL statements were executed successfully.");
});
```
The difference between the transaction() and readTransaction() methods is that the latter cannot be used with SQL statements that change the database (such as INSERT, UPDATE, DELETE, or CREATE).

Note

When possible, use the readTransaction() to obtain better execution performance of SQL statements.
To execute a SQL statement, use the executeSql() method. The SQL statement is the first parameter of the method and cannot contain SQL transaction statements (such as BEGIN, END, or ROLLBACK):
```
var sqlResultSet = sqlTransactionSync.executeSql(
   "CREATE TABLE IF NOT EXISTS books(id INTEGER PRIMARY KEY, title TEXT, author TEXT)", []);
```

Note
When possible, use the readTransaction() to obtain better execution performance of SQL statements.

Pass arguments to the SQL statement:

var sqlResultSet = sqlTransactionSync.executeSql(
   "SELECT id FROM books WHERE title=? AND author=?", ["Ulysses", "James Joyce"]);

Note
Use an array to pass the arguments to secure SQL statements from SQL injection attacks.

Accessing SQL Results Synchronously

To provide users with SQL database features, you must learn to access SQL statement results synchronously:

When a SQL statement is executed, its event handler is invoked and returns the result as a sqlResultSet object:
- The result object of the INSERT statement contains the insert ID, which stores the identifier of the added record. If multiple records were inserted, the insert ID contains the ID of the last inserted record:
```
var sqlResultSet = sqlTransactionSync.executeSql("INSERT INTO books (id, title, author) VALUES(NULL, ?, ?)", 
                                                 ["Ulysses", "James Joyce"]);
alert("The 'id' of the new record  is " + sqlResultSet.insertId);
```
- The result object of the SELECT statement stores also the number of records that were inserted, changed, or deleted. It contains the number of selected rows (length field) and the item() method. Use the method with the index argument (integer value from 0 to rows.length - 1) to get individual records:
```
var sqlResultSet = sqlTransactionSync.executeSql("SELECT id, title, author FROM books");
var book, i, booksNumber = sqlResultSet.rows.length;
for (i = 0; i < booksNumber; i++) 
{
   book = sqlResultSet.rows.item(i);
   alert("id: " + book.id + ", title: " + book.title + ", author: " + book.author);
}
```

Handling Errors Synchronously

To provide users with SQL database features, you must learn to handle SQL database-related errors synchronously:

Handle errors in the sqlException object:

try 
{
   databaseSync.transaction(function(sqlTransactionSync) 
   {
      var sqlResultSet = sqlTransactionSync.executeSql("DELETE FROM books WHERE id=?", [id]);
   });
   /* Instructions if the above SQL statement is executed successfully */
}
catch (sqlException) 
{
   postMessage("An error has occurred during deleting the book from the table! 
               Error code: " + sqlException.code + " (" + sqlException.message + ").");
}

Note
In the synchronous database API, the script execution is stopped until the transaction is completed.