Introduction
In this article, we'll be using a JavaScript database API for the client-side database. This API provides a simple method of storing, retrieving, and marinating user-supplied information. In addition, it provides a relational database model, and so can easily handle complex data. We can use standard SQL statements to perform all database-related tasks, such as creating databases and tables, as well as inserting, fetching, deleting, and updating rows in the tables.
Let’s start off by creating and opening a database. The code for creating and opening the client-side database is shown below:
var datab;
var shortName = '</span />tmpCart'</span />;
var version = '</span />1.0'</span />;
var displayName = '</span />tmpCart'</span />;
var maxSize = 200000;
datab = openDatabase(shortName, version, displayName, maxSize);
The usage of the variables used in the code fragment is shown Table 1.
Table 1. Client-Side Database Variables
Variable | Usage |
datab | Used to hold the reference to the database connection when it is established. |
shortName | Stores the name of the database we will be creating on the client side. |
version | Stores the version number assigned to the database. The version number is often needed for upgrades or changes to the database. |
displayName | Stores the database name available to the user. |
maxSize | Stores the expected size of our database in kilobytes. Should the size exceed the limit in this variable, we will be notified and asked if the increase should be allowed or denied. |
The variable values defined in our code fragment say that we want to create a client-side database named tmpCart
with a version number of 1.0 and a size limit of 200000 KB. After assigning these variable values, we can pass them to the openDatabase
command, which actually creates the tmpCart
database and stores the connection in datab
variable.
The code fragment can also be written as:
datab = openDatabase('</span />tmpCart'</span />, '</span />1.0'</span />, '</span />tmpCart'</span />, 200000</span />);
That is, we can directly specify the parameter values in the openDatabase
function without using variables at all.
Now that we've created and opened a server-side database, let’s create a table in it.
Creating Client-Side Tables
In this example, we'll be using the client-side database for storing cart information. Let’s first create a table called shopcart
in our client-side database tmpCart
, as shown in this code fragment:
datab.transaction</span />(
function</span />(transaction</span />) {
transaction</span />.executeSql(
'</span />CREATE TABLE IF NOT EXISTS shopcart '</span /> +
'</span /> (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '</span /> +
'</span /> cart_sess varchar(50), cart_isbn varchar(30), cart_item_name
varchar(100), cart_qty integer, cart_price float );'</span />
);
}
);
The JavaScript database API supports SQL transactions and all database queries must take place in the context of a transaction. To execute a standard SQL query, we need to call the executeSql
method of the transaction object. To do this, we call the transaction
method of the datab
object and pass it an anonymous function. We then pass the transaction to the anonymous function so that we can call executeSql
method of the transaction object.
The executeSql
method creates a table called shopcart
if it doesn't already exist. The table has six fields: id, cart_sess, cart_ISBN, cart_item_name, cart_qty
and cart_price
. The id
field is the primary key. Its value cannot be null
and is unique for each row of the table. We set this field to AUTOINCREMENT
, so its value will increase by 1
for each table row added.
Inserting Rows in Client-Side Tables
Let’s assume that a user has dropped a book in the cart, so we now have some cart data to process—session id, ISBN, title, quantity, and price. This data is stored in the variables sid, ISBN, title, qty,
and price
respectively. We'll transfer this data to a row in the client-side shopcart
table, as shown in the following code fragment:
datab.transaction</span />(
function</span />(transaction</span />) {
transaction</span />.executeSql(
'</span />INSERT INTO shopcart (cart_sess, cart_isbn, cart_item_name, cart_qty,
cart_price) VALUES (?,?,?,?,?);'</span />,
[sid, isbn, title, qty, price],
function</span />(){
},
displayerrormessage
);
}
);
Note: Session id
is used for session management—
a mechanism used for tracking users visiting our web application. The Server generates a unique id for the current session.
First we use the executeSql
method of the transaction
object to execute a SQL query. We need to pass the data for five fields, or columns, to this method. The five question marks (?) in the VALUES
parentheses are placeholders and take on the values in the array [sid, ISBN, title, qty, price]
. The first question mark is replaced by the value in the sid
variable, the second question mark is replaced by the ISBN
value, and so on.
The array of values is followed by an anonymous function called the data callback function, which may contain statements to execute after successful execution of the SQL statement. For example, data callback
may contain calls to other functions that display a confirmation message or navigate to an other panel. If we don't want any action to take place after the successful execution of the SQL statement, we can leave this function empty, as we have actually done in the code fragment.
The last part of the fragment, displayerrormessage,
is a call to the error handling
function we want to evoke if the SQL statement fails. Here is an example of displayerrormessage
usage:
function</span /> displayerrormessage(transaction, error) {
alert('</span />Error: '</span />+error.message+'</span /> has occurred with Code: '</span />+error.code);
return</span /> true</span />;
}
Two parameters are passed to the displayerrormessage
function: the transaction
object and the error
object. The error
object displays the error message and the error code. The reason for passing transaction
object to the displayerrormessage
function is to allow more SQL statements to be executed from within the function, if desired. The displayerrormessage
function may return true
to halt the execution and roll back the entire transaction, or false
, in which case the transaction will continue.
Error Codes
Table 2 contains a quick look at the common error codes generated while executing a transaction on client-side database tables.
Table 2 Error Codes and Occurrence
Error Code | Occurs When |
0 | The transaction failed for other non-database-related error. |
1 | The transaction failed for other database-related error. |
2 | The transaction failed because the version of the database didn't match the one user requested. |
3 | The transaction failed because the data returned from the database was too large. |
4 | The transaction failed because either there was not enough storage space left or the user didn't want the database to grow beyond the existing limit. |
5 | The transaction failed because the transaction included a syntax error, number of parameters mismatch, statement modifying the database in a read-only transaction, and so on. |
6 | The transaction failed because of constraint failure, for example, assigning two rows have the same value in the primary field. |
If we aren't interested in capturing errors, we can omit both data callback and error handling callback functions. If we do that, the code fragment will appear as follows:
datab.transaction</span />(
function</span />(transaction</span />) {
transaction</span />.executeSql(
'</span />INSERT INTO shopcart (cart_sess, cart_isbn, cart_item_name, cart_qty,
cart_price) VALUES (?,?,?,?,?);'</span />,
[sid, isbn, title, qty, price]
);
}
);
For more information, refer to my book: “Beginning Web Development for Smartphones: Developing Web Applications with PHP, MySQL and jQTouch” available at Amazon.
History
- 30th September, 2010: Initial version