Beginning Web Development for Smartphones – Developing Web Applications With PHP, MySQL and JQTouch


Creating a Client-Side Database and Tables

In this example, we'll be using a JavaScript database API for creating 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 = 'tmpCart';
var version = '1.0';
var displayName = 'tmpCart';
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


Used to hold the reference to the database connection when it is established.

Stores the name of the database we will be creating on the client side.

Stores the version number assigned to the database. The version number is often needed for upgrades or changes to the database.

Stores the database name available to the user.

Stores the expected size of our database in kilobytes. Should the size exceed the limit in this variable, we will be informed 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 databvariable.

The code fragment can also be written as:

datab = openDatabase ('tmpCart', '1.0', 'tmpCart', 200000);

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 (
function (transaction) {
transaction.executeSql (
'cart_sess varchar (50), cart_isbn varchar (30), cart_item_name
varchar (100), cart_qty integer, cart_price float); '

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 a 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 does not already exidst. The table has four fields: id, cart_sess, cart_ISBN, cart_item_name, cart_qty and cart_price. Theidfield is the primary key. It's value can not 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 variablelessid, 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 (
function (transaction) {
transaction.executeSql (
'INSERT INTO shopcart (cart_sess, cart_isbn, cart_item_name, cart_qty,
cart_price) VALUES (?,?,?,?,?); ',
[sid, isbn, title, qty, price],
function () {

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 do not 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 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 displayerrormessageusage:

function displayerrormessage (transaction, error) {
alert ('Error:' + error.message + 'has occurred with Code:' + error.code);
return true;

Two parameters are passed to the displayerrormessagefunction: the transaction object and the error object. The errorobject 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. Thedisplayerrormessage 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
Table2 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

The transaction failed for other non-database-related error.

The transaction failed for other database-related error.

The transaction failed because the version of the database did not match the one user requested.

The transaction failed because the data returned from the database was too large.

The transaction failed because either there was not enough storage space left or the user did not want the database to grow beyond the existing limit.

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.

The transaction failed because of constraint failure, for example, assigning two rows the same value in the primary field.

If we are not 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 (
function (transaction) {
transaction.executeSql (
'INSERT INTO shopcart (cart_sess, cart_isbn, cart_item_name, cart_qty,
cart_price) VALUES (?,?,?,?,?); ',
[sid, isbn, title, qty, price]

Source EzineArticles by Bintu Harwani

Be the first to comment

Leave a Reply

Your email address will not be published.