SQL Database in Apache Cordova/PhoneGap

Introduction

In Apache Cordova/PhoneGap their are various database objects. In this article I will explain the SQL objects. Some of them are SQLTransaction, SQLResultSet, SQLResultSetList and SQLError; they are described in this article.

SQLTransaction:  It contains the methods that allow the user to execute SQL statements against the Database. The method we use to execute the SQL statement is executeSql. The user can create a database transaction by calling the executeSql method multiple times. The supported platforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width, height=device-height, initial-scale=1.0, maximum-scale=1.0, user-scalable=no;" />

    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />

    <title>ExecuteSql Method</title>

    <link rel="stylesheet" href="master.css" type="text/css" media="screen" title="no title"

        charset="utf-8" />

    <script type="text/javascript" charset="utf-8" src="cordova-1.9.0.js"></script>

    <script type="text/javascript">

        // Wait for Cordova to load    

        document.addEventListener("deviceready", onDeviceReady, false);

        // Cordova is ready      

        function onDeviceReady() {

            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

            db.transaction(populateDB, errorCB, successCB);

        }

        // Populate the database     

        function populateDB(tx) {

            tx.executeSql('DROP TABLE IF EXISTS DEMO');

            tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

        }

        // Transaction error callback

        function errorCB(err) {

            alert("Error processing SQL: " + err);

        }

        // Transaction success callback

        function successCB() {

            alert("success!");

        }

    </script>

</head>

<body>

    <h1>

        Example</h1>

    <p>

        SQLTransaction</p>

</body>

</html>

SQLResultSet:  When we work with the executeSql method of the SQLTransaction it will invoke its callback with a SQLResultSet object. This object has the three properties, the first one is the insertId that will used to return the row number of the successful SQL insert statement; if the SQL does not insert any row then the insertId is not set. The second property of SQLResultSet is rowsAffected; it is always 0 for the SQL select statement, for insert and update it will return the number of rows that have been modified. The third and the last property of SQLResultSet is the rows that represents the rows returned. If no row is selected then the object will be empty. The supported plateforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example
 

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width, height=device-height, initial-scale=1.0, maximum-scale=1.0, user-scalable=no;" />

    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />

    <title>ExecuteSql Method</title>

    <link rel="stylesheet" href="master.css" type="text/css" media="screen" title="no title"

        charset="utf-8" />

    <script type="text/javascript" charset="utf-8" src="cordova-1.9.0.js"></script>

    <script type="text/javascript">

        // Wait for Cordova to load     

        document.addEventListener("deviceready", onDeviceReady, false);

        // Populate the database        

        function populateDB(tx) {

            tx.executeSql('DROP TABLE IF EXISTS DEMO');

            tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

        }

        // Query the database   

        function queryDB(tx) {

            tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);

        }

        // Query the success callback 

        function querySuccess(tx, results) {

            console.log("Returned rows = " + results.rows.length);

            // this will be true since it was a select statement and so rowsAffected was 0

            if (!results.rowsAffected) {

                console.log('No rows affected!');

                return false;

            }

            // for an insert statement, this property will return the ID of the last inserted row

            console.log("Last inserted row ID = " + results.insertId);

        }

        // Transaction error callback  

        function errorCB(err) {

            console.log("Error processing SQL: " + err.code);

        }

        // Transaction success callback      

        function successCB() {

            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

            db.transaction(queryDB, errorCB);

        }

        // Cordova is ready    

        function onDeviceReady() {

            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

            db.transaction(populateDB, errorCB, successCB);

        }

    </script>

</head>

<body>

    <h1>

        Example</h1>

    <p>

        Database</p>

</body>

</html>

SQLResultSetList:  It is one of the properties of the SQLResultSet that contains the rows returned from a SQL query. The only one property of the SQLResultSetList is length that represents the number of rows returned by the SQL query, and the method is the item that returns the rows at the specified index represented by a JavaScript object. The supported plateforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example
 

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width, height=device-height, initial-scale=1.0, maximum-scale=1.0, user-scalable=no;" />

    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />

    <title>ExecuteSql Method</title>

    <link rel="stylesheet" href="master.css" type="text/css" media="screen" title="no title"

        charset="utf-8" />

    <script type="text/javascript" charset="utf-8" src="cordova-1.9.0.js"></script>

    <script type="text/javascript">

        // Wait for Cordova to load       

        document.addEventListener("deviceready", onDeviceReady, false);

        // Populate the database        

        function populateDB(tx) {

            tx.executeSql('DROP TABLE IF EXISTS DEMO');

            tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

            tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

        }

        // Query the database     

        function queryDB(tx) {

            tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);

        }

        // Query the success callback      

        function querySuccess(tx, results) {

            var len = results.rows.length;

            console.log("DEMO table: " + len + " rows found.");

            for (var i = 0; i < len; i++) {

                console.log("Row = " + i + " ID = " + results.rows.item(i).id + " Data =  " + results.rows.item(i).data);

            }

        }

        // Transaction error callback

        function errorCB(err) {

            console.log("Error processing SQL: " + err.code);

        }

        // Transaction success callback      

        function successCB() {

            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

            db.transaction(queryDB, errorCB);

        }

        // Cordova is ready      

        function onDeviceReady() {

            var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

            db.transaction(populateDB, errorCB, successCB);

        }

    </script>

  </head>

  <body>

    <h1>Example</h1>

    <p>Database</p>

  </body>

</html>

SQLError:  When an error occurs the SQLError object is thrown. The properties of the SQLError is code and message. The code is one of the predefined error codes and the message is the description of the error.

Summary : In this article I explained the various objects of SQL in Apache Cordova/PhoneGap.

Up Next
    Ebook Download
    View all
    Learn
    View all