Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Web and WebWorks Development

Reply
Contributor
Nibedita
Posts: 22
Registered: ‎04-06-2011
My Device: 9800
My Carrier: airtel
Accepted Solution

creating or opening sqlite db in webworks for playbook

I've a sqlite db. I want to open the db using javascript. Also i've to keep the db locally and package it. Can anyone let me know with a sample code how to do the CRUD functionalities in sqlite db using JS. Please reply soon if anyone has any idea.

Please use plain text.
BlackBerry Development Advisor
tneil
Posts: 3,708
Registered: ‎10-16-2008
My Device: Z10
My Carrier: Rogers

Re: creating or opening sqlite db in webworks for playbook

On BlackBerry Smartphones and PlayBook, the database used by a WebWorks application must be created through JavaScript.

 

There isn't a way to embed a SQLite DB as a resource of your application and use it.  If you have pre-determined data, you will have to run an initializer script that will create and populate your data.

 

Some people have packaged JSON or an XML file to load this data.

Tim Neil
Director, Application Platform & Tools Product Management
Follow me on Twitter
Please use plain text.
Contributor
Nibedita
Posts: 22
Registered: ‎04-06-2011
My Device: 9800
My Carrier: airtel

Re: creating or opening sqlite db in webworks for playbook

I appreciate your quick response Neil. Could you please write a few goes of code in javascript which will create and update the DB. I went through the WebCast series and did run the db demo in WebCast 3 but it says "window.openDatabase is not a function".Please guide me to work this out.

Please use plain text.
Developer
ddluk
Posts: 212
Registered: ‎10-02-2010
My Device: Z10 and Z30
My Carrier: Play and Plus

Re: creating or opening sqlite db in webworks for playbook

Full working code is available here:

 

https://github.com/ddluk/WebWorks-Database

 

Fell free to ask questions :smileyhappy:

Please use plain text.
Contributor
Nibedita
Posts: 22
Registered: ‎04-06-2011
My Device: 9800
My Carrier: airtel

Re: creating or opening sqlite db in webworks for playbook

I tried this as well. :smileysad:

 

db = openDatabase("databasetest", '1.0', 'Test', 50 * 1024);

the above line s not getting executed

 

is dat a browser problem?????

 

 

Please use plain text.
Developer
ddluk
Posts: 212
Registered: ‎10-02-2010
My Device: Z10 and Z30
My Carrier: Play and Plus

Re: creating or opening sqlite db in webworks for playbook

About what browser you are talking about?
Please use plain text.
Contributor
Nibedita
Posts: 22
Registered: ‎04-06-2011
My Device: 9800
My Carrier: airtel

Re: creating or opening sqlite db in webworks for playbook

<head>
<html>
    <title> HTML5 Web DB </title>
    <meta id="viewport" name="viewport" content="width=device-width" />
    <link href="/styles.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
        th { text-align: left; font-weight: bold; background-color: #CCC; padding: 0.25em; font-size: 1.25em; font-variant: small-caps; }
    </style>

    <script type="text/JavaScript">
        var db;
        
        /**
         * Helper functions
         */        
        function success(msg) {
            console.log(msg);
        }
        function error(msg) {
            var ele = document.getElementById("output");
            if (ele) {
                ele.innerHTML += "<div class='error'>" + msg + "</div>" ;
            }
        }
        function getSQLErrorName(err)
        {
            if (err === null)
            {
                return "";
            }
            switch(err.code)
            {
                case err.DATABASE_ERR:
                    //The statement failed for database reasons not covered by any other error code.
                    return "DATABASE";
                case err.VERSION_ERR:
                    //The operation failed because the actual database version was not what it should be.
                    //    For example, a statement found that the actual database version no longer matched the
                    //    expected version of the Database or DatabaseSync object, or the Database.changeVersion()
                    //    or DatabaseSync.changeVersion() methods were passed a version that doesn't match the actual database version.
                    return "DATABASE VERSION";
                case err.TOO_LARGE_ERR:
                    //The statement failed because the data returned from the database was too large. The
                    //    SQL "LIMIT" modifier might be useful to reduce the size of the result set.
                    return "RESULT TOO LARGE";
                case err.QUOTA_ERR:
                    //The statement failed because there was not enough remaining storage space, or the storage
                    //    quota was reached and the user declined to give more space to the database.
                    return "QUOTA EXCEEDED";
                case err.SYNTAX_ERR:
                    //The statement failed because of a syntax error, or the number of arguments did not match
                    //    the number of ? placeholders in the statement, or the statement tried to use a statement
                    //    that is not allowed, such as BEGIN, COMMIT, or ROLLBACK, or the statement tried to use a
                    //    verb that could modify the database but the transaction was read-only.
                    return "SYNTAX";
                case err.CONSTRAINT_ERR:
                    //An INSERT, UPDATE, or REPLACE statement failed due to a constraint failure. For example,
                    //    because a row was being inserted and the value given for the primary key column duplicated
                    //    the value of an existing row.
                    return "CONSTRAINT";
                case err.TIMEOUT_ERR:
                    //A lock for the transaction could not be obtained in a reasonable ti
                    return "TIMEOUT";
                default:
                    //The transaction failed for reasons unrelated to the database itself and not covered by any
                    //    other error code.
                    return "UNKNOWN";
            }
        }
        
        //Two types of error events can occur: transaction errors and SQL statement errors.
        
        /**
         * SQLTransactionErrorCallback - method raised by the db.transaction() or db.readTransaction() or db.changeVersion() methods when an error occurs within a transaction event.
         *          http://www.w3.org/TR/webdatabase/#sqltransactionerrorcallback
         * @param err (SQLError) has two parameters: code (unsigned short), message (string) and constants
         *          http://www.w3.org/TR/webdatabase/#sqlerror
         */
        function handleTransactionError(err)
        {
            error("SQLTransactionError " + err.code + " [" + getSQLErrorName(err.code) + "] " + err.message);
        }
        /**
         * SQLStatementErrorCallback - method raised by the tx.executeSql() method when an error occurs within an SQL statement.
         *          http://www.w3.org/TR/webdatabase/#sqlstatementerrorcallback
         * @param tx (SQLTransaction) has a single method: void executeSql(string sqlStatement, optional array args, optional SQLStatementCallBack callback, optional SQLStatementErrorCallback errorCallback)
         *          http://www.w3.org/TR/webdatabase/#sqltransaction
         * @param err (SQLError) has two parameters: code (unsigned short), message (string) and constants
         *          http://www.w3.org/TR/webdatabase/#sqlerror
         */
        function handleSQLError(tx, err)
        {
            //The tx parameter can be used to run another SQL statement (e.g. log a message to an error table)
            error("SQLStatementError " + err.code + " [" + getSQLErrorName(err.code) + "] " + err.message);
        }

        
        
        /**
         * The following are SQLStatementCallback methods raised after a records are inserted, updated, deleted selected from the DB.
         * @param tx (SQLTransaction) has a single method: void executeSql(string sqlStatement, optional array args, optional SQLStatementCallBack callback, optional SQLStatementErrorCallback errorCallback)
         *          http://www.w3.org/TR/webdatabase/#sqltransaction
         * @param result (SQLResultSet) contains three attributes: insertId (readonly long), rowsAffected (readonly long), rows (readonly SQLREsultSetRowList)
         *          http://www.w3.org/TR/webdatabase/#sqlresultset
         */
        function insertComplete(tx, result)
        {
            //The insertId attribute contains the ID of the row that was inserted into the database.
            //If a single statement inserted multiple rows, the ID of the last row is returned.
            success("insertComplete() - " + result.rowsAffected + " row(s) added (rowId =" + result.insertId + ")");
        }
        function updateComplete(tx, result)
        {
            //The rowsAffected attribute contains number of rows that were changed by the SQL statement.
            // SELECT statements do not modify rows, and therefore have a rowsAffected value of 0.
            success("updateComplete() - " + result.rowsAffected + " row(s) updated");
        }
        function deleteComplete(tx, result)
        {
            success("deleteComplete() - " + result.rowsAffected + " row(s) deleted");
        }
        function selectComplete(tx, result)
        {
            //The rows attribute is a SQLResultSetRowList object containing one paramter length (int) and one method .item(index)
            //  The same object must be returned each time. If no rows were returned, then the object will be empty (its length will be zero).
            //    http://www.w3.org/TR/webdatabase/#sqlresultsetrowlist
            var size = result.rows.length;
            success("selectComplete() - " + size + " row(s) returned");
        }

        
        
        
        /**
         * SQLStatementCallback methods raised after a SELECT statement is called.  Display results to the page.
         * @param tx (SQLTransaction) has a single method: void executeSql(string sqlStatement, optional array args, optional SQLStatementCallBack callback, optional SQLStatementErrorCallback errorCallback)
         *            http://www.w3.org/TR/webdatabase/#sqltransaction
         * @param result (SQLResultSet) contains three attributes: insertId (readonly long), rowsAffected (readonly long), rows (readonly SQLREsultSetRowList)
         *            http://www.w3.org/TR/webdatabase/#sqlresultset
         */
        function displayMessagesResults(tx, result)
        {
            var ele = document.getElementById("contents");
            var output = "";
            var size = result.rows.length;
            
            if (size === 0)
            {
                output += "<i>Empty</i>";
            }
            else {
                output += "<table cellspacing='0' cellpadding='0' border='0' width='100%'>";
                output += "<tr><th>id</th><th>message</th><th>created</th><th></th></tr>";
                for (var i = 0; i < size; i++)
                {
                    var item = result.rows.item(i);
                    var dt = new Date(item.created);
                    output += "<tr><td>" + item.id + "</td><td>" + item.message + "</td><td>" + dt.toLocaleDateString() + " " + dt.toLocaleTimeString() + "</td><td><a href='#' onclick=\"deleteRow('" + item.id + "')\">Delete</a></td></tr>";
                }
                output += "</table>";
            }
            
            if (ele)
            {
                ele.innerHTML = output;
            }
        }
        
        /**
         * Make the following logic its own method, so it can be called from various sources.
         */
        function displayMessages()
        {
            if (db)
            {
                db.transaction(function(tx) {
                                            tx.executeSql('SELECT id, message, created FROM Messages', [], displayMessagesResults, handleSQLError);
                                            }, handleTransactionError);
            }
        }
        
        /**
         * Called when the user clicks on the 'Add Message' button.
         */
        function addMessage()
        {
            var created = new Date().getTime();
            var message = document.getElementById("txtMessage").value;
            if (message === "")
            {
                error("Enter a message");
            }
            else {
                if (db)
                {
                    db.transaction(function(tx) {
                                                tx.executeSql("INSERT INTO Messages (message, created) VALUES (?, ?)", [message, created], insertComplete, handleSQLError);
                                                displayMessages();
                                                }, handleTransactionError);
                }
            }
        }
        /**
         * Called when the user clicks on the 'Delete' hyperlink.
         */
        function deleteRow(id)
        {
            if (db)
            {
                db.transaction(function(tx) {
                                            tx.executeSql('DELETE FROM Messages WHERE id = ?', [id], deleteComplete, handleSQLError);
                                            displayMessages();
                                            }, handleTransactionError);
            }
        }
        
        
        /**
         * SQLStatementCallback methods raised after the first table was created.  Add test data.
         * @param tx (SQLTransaction) has a single method: void executeSql(string sqlStatement, optional array args, optional SQLStatementCallBack callback, optional SQLStatementErrorCallback errorCallback)
         *     http://www.w3.org/TR/webdatabase/#sqltransaction
         * @param result (SQLResultSet) contains three attributes: insertId (readonly long), rowsAffected (readonly long), rows (readonly SQLREsultSetRowList)
         *     http://www.w3.org/TR/webdatabase/#sqlresultset
         */
        function firstCreateComplete(tx, result)
        {
            try
            {
                //Do not need to begin another transaction.  Since the active transaction object was
                //    provided as a parameter, it can now be reused to insert some test data:
                var created = new Date().getTime();
                tx.executeSql("INSERT INTO Messages (message, created) VALUES (?, ?)", ["Hello World A", created], insertComplete, handleSQLError);
                tx.executeSql("INSERT INTO Messages (message, created) VALUES (?, ?)", ["Hello World B", created], insertComplete, handleSQLError);
                tx.executeSql("INSERT INTO Messages (message, created) VALUES (?, ?)", ["Hello World C", created], insertComplete, handleSQLError);
                
                //Finally, display all rows in the table.
                displayMessages();
            }
            catch(ex) {
                error("exception (firstCreateComplete): " + ex);
            }
        }
        
        /**
         * DatabaseCallback method invoked when the Database is first created. Designed to initialize the schema by creating necessary table(s).
         *     http://www.w3.org/TR/webdatabase/#databasecallback
         * @param database (Database) - reference to the DB object that was creatd
         *     http://www.w3.org/TR/webdatabase/#database
         */
        function createTableOnNewDatabase(database)
        {
            try
            {
                if (database)
                {
                    database.transaction(function(tx) {
                                                      //The following method is asyncronous, perform record insert statements within the callback method after table has been created successful
                                                      tx.executeSql("CREATE TABLE IF NOT EXISTS Messages (id INTEGER PRIMARY KEY, message TEXT, created TIMESTAMP)", [], firstCreateComplete, handleSQLError);
                                                      }, handleTransactionError);
                }
            }
            catch(ex) {
                error("exception (createTableOnNewDatabase): " + ex);
            }
        }


        
        /**
         * Called by page load event.  Opens DB reference and displays contents of Messages table
         */
        function initPage()
        {
            try
            {
                
                //Assign 2MB of space for the database
                var dbSize = 2 * 1024 * 1024;    
                db = window.openDatabase("WebDBSample", "1.0", "HTML5 Database API example", dbSize, createTableOnNewDatabase);
                
                if (db != null)
                {
                    displayMessages();
                }
            }
            catch(e) {
                error("exception (initPage): " + e);
            }
        }
    </script>
</head>

<body onload="initPage()">
    <a href="/index.html" class="home">home</a>
    
    <h1>Database</h1>
    
    <div class="bottom demo">
        <p id="userInput">
            Add a message to this domain's offline storage container: <br/>
            <textarea id="txtMessage" rows="3" cols="30"></textarea> <br/>
            <button id="btnSave" onclick="addMessage()">Add Message</button>
        </p>
        <div id="contents"></div>
    </div>

    <div id="output"></div>

</body>
</html>

 

 

This is what i tried running in a couple of browsers.

 

it says window.openDatabase is not a function

 

i downloaded ddluk-WebWorks-Database-23a5135 and tried running in playbook simulator but the application dint open.

 

I am not able to create database using HTML5

 

 

Please use plain text.
Developer
ddluk
Posts: 212
Registered: ‎10-02-2010
My Device: Z10 and Z30
My Carrier: Play and Plus

Re: creating or opening sqlite db in webworks for playbook

It will only work on two browsers  - Chrome and Safari. I just checked my example and it's working on Playbook simulator without any problems.

Please use plain text.
Contributor
Nibedita
Posts: 22
Registered: ‎04-06-2011
My Device: 9800
My Carrier: airtel

Re: creating or opening sqlite db in webworks for playbook

yeah its working!!!! I repackaged it its working in simulator.Thankz

Please use plain text.
New Developer
cicube
Posts: 6
Registered: ‎05-06-2009
My Device: Not Specified

Re: creating or opening sqlite db in webworks for playbook

When I try to load Database.bar on the Playbook (the actual device) it fails saying that it is unsigned.  What are the steps to re-sign Database.bar??

Please use plain text.