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

Posts: 114
Registered: ‎06-14-2010
My Device: Passport
My Carrier: Virgin Mobile Canada

I'd appreciate a BBUI.js compatible code sample processing complex Web database query result

Hi; I'm still struggling with getting Web database code to be "done" enough for my javascript to work with the result.

The first time the app is run, it needs to run a series of SQL "Create" queries to populate the Web Database tables (lookup tables, sample data etc.)

For this, I have all the "Create" queries and "Insert" queries in a file and they are run one at a time asynchronously... it's alright if they just finish when they finish... but to clear "first run" I do want to do checks that the table structures are correct and have been populated properly: I would do this by running "Select" statements on some of the tables to see that the create and insert queries all worked and completed successfully... but I'm having the problem stalling the "check" functions long enough for the "create" and "insert" statements to finish... but for now, I'm having to use "debug" functions to reset the "the first run is complete" flag and then reload the app so that the first run routines will keep plugging away at the "create" and "insert" queries until they're all done.

The bigger problem is when the app is in use by an end user. One of the screens shows a list of records that have sub-records from another table: for each rendered "main item" I want to render all the 'sub items' before the next "main item". The result with the "main items" is a query with lots of INNER JOINs and the sub-items have another set of INNER JOINs that make it impractical to make one super mega query with a record for every one of the sub records.

I have tried to run the SQL statements all in the "onScreenReady" event and put all the results into javascript array objects, but that hasn't worked out too well for me either, and there's still the issue of trying to hold off on executing the "onDomReady" functions until all the queries are done and the results are safely ensconsed in javascript array objects. Right now, I'm having to forego any automatic population of the screen with a list of items and the user has to push a "show records" button and then it will run the functions and render the items and subitems properly: so I'm able to get the data out of the database just fine, and render it on screen, but not automatically when the screen loads.

I guess this is a 'plea' for sample code that reads data from multiple tables from a database and renders the data from both to screen without potentially failing due to an asynchronous database statement not having returned its result yet.

I've reviewed the sample code distributed with BBUI.js, but not found anything that I could adapt... if I just need to look harder and what I'm trying to do is practical and possible in BBUI.js / Webworks, I could really use a few pointers or maybe just a strong reassurance that its not hopeless... it feels that way right now.

New Contributor
Posts: 7
Registered: ‎01-25-2014
My Device: z10
My Carrier: simulator

Re: I'd appreciate a BBUI.js compatible code sample processing complex Web database query result

First of all, if you want to make an app only for bb10, I would highly recommend checking out indexedDB (https://developer.mozilla.org/en/docs/IndexedDB).


Carrying on:

You could always set callbacks to run when database transactions are complete (for indexedDB: each request has "onsuccess" and "oncomplete" values you can set and each transaction as "oncomplete" and "onerror" values, for WebSQL, you can set call backs in the parameters of the "executeSQL" and "transaction" methods). The checks can be run after the last callback.


Also it's good practice to run multiple queries of the same type inside request callbacks one after the other in one transaction, this way queries depending on one piece of data won't cause conflicts (for example to run multiple create queries: 


_runCreate = function(trans){
     ...//generate create query string
     trans.executeSQL(createQueryString, [], _runCreate);

db.transaction(_runCreate, onErrorFunction, onSuccessFunction);

 ). That way you can chain multiple call backs and have some variable outside the function to indicate when the last callback is complete and start your check queries.


For the first time running, if you're using webSQL the last argument in the openDatabase method is a callback to run if the database is created for the first time (http://www.w3.org/TR/webdatabase/#asynchronous-database-api). 


As for loading the list on the screen you could always show a loading screen (i.e. hide the list and show a large activity indicator in the middle) and run the database requests in the ondomready function. The final callback for the request should hide the loading screen (hide the activity indicator and show the list). Its best to run these functions in ondomready as opposed to onscreenready because onscreenready is meant to run quick actions. Also if you run the queries in onscreenready, you don't know if the screen is loaded on the DOM or not after the async queries are done and you have to load the data on the list.


Hope this helps!