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
Trusted Contributor
mobilvar
Posts: 119
Registered: ‎11-28-2011
My Device: 9930
My Carrier: Verizon
Accepted Solution

Using HTML5 to access a local database

I am working on an app that will need to access information from a database.

I would like to inculde a sqllite database file as part of my installation and use html5 to search this database file and present data in it to the user. Is it possible to deploy an app like this?

In order to improve user experience, I do not want to save the data to an xml file, parse that file and load that data into a local html5 database.

Please use plain text.
Developer
steve_web
Posts: 320
Registered: ‎05-07-2009
My Device: BlackBerry Z10, PlayBook, 9900
My Carrier: Telus

Re: Using HTML5 to access a local database

You can use HTML5 to store info in a SQLLite database.

 

The general HTML5 API documentation is here: https://bdsc.webapps.blackberry.com/html5/api/

 

And the specific database API's are here: https://bdsc.webapps.blackberry.com/html5/apis/Database.html

 

Let us know if you need any additional help getting it setup.

 

Cheers,

Steve

 

 


PlayBook Apps: DataMine, Dots + Boxes, 5 Marbles
BlackBerry SmartPhone Apps: 5 Marbles Soon: **** (D.Z.A), ****Quest, **** Challenge, VaultCracker, DevBrowser, Radial****
Please use plain text.
Trusted Contributor
mobilvar
Posts: 119
Registered: ‎11-28-2011
My Device: 9930
My Carrier: Verizon

Re: Using HTML5 to access a local database

Thank you for your quick respons!

I previously reviewed the documentation that you linked to.

My challenge is that I already have the data I need in a sqllite database file. I just want to access this database file from my app then manipulate and display the results to the users. I'm not finding a way to access an existing database file through html5...

Please use plain text.
Developer
steve_web
Posts: 320
Registered: ‎05-07-2009
My Device: BlackBerry Z10, PlayBook, 9900
My Carrier: Telus

Re: Using HTML5 to access a local database

Hi mobilvar,

 

There isn't a way to load a SQLLite db file directly into your app (or any HTML5 database for that matter).

 

What you can do is 3 easy steps on your first app run.

 

1.) Create the DB.

2.) Create the Tables you need

3.) Populate the data

 

It may seem a bit daunting at first, but its actually not that bad at all.  I'm doing this for my current app (1db, 4 tables) and it works very well and is extremely quick.


PlayBook Apps: DataMine, Dots + Boxes, 5 Marbles
BlackBerry SmartPhone Apps: 5 Marbles Soon: **** (D.Z.A), ****Quest, **** Challenge, VaultCracker, DevBrowser, Radial****
Please use plain text.
Trusted Contributor
mobilvar
Posts: 119
Registered: ‎11-28-2011
My Device: 9930
My Carrier: Verizon

Re: Using HTML5 to access a local database

Thanks again for your help. 

Do you have an example of a file parse routine that I can look at?

If the database is set up the first time an application is run, will the database need to be re-created when it is closed and reopened, or will that data persist on the phone even between application sessions?

Please use plain text.
Trusted Contributor
mobilvar
Posts: 119
Registered: ‎11-28-2011
My Device: 9930
My Carrier: Verizon

Re: Using HTML5 to access a local database

 

I have my data in a csv file. What could I use to parse this file and load the data into the SQLite database on the phone?

I see examples in my research so far of how to create the database and add data to it.

My hangup now is how to parse the data and pass the values to a routine that loads the data into a table on the phone.

Please use plain text.
Developer
steve_web
Posts: 320
Registered: ‎05-07-2009
My Device: BlackBerry Z10, PlayBook, 9900
My Carrier: Telus

Re: Using HTML5 to access a local database

There's several options, but I personally find this to be very handy.

 

In a JavaScript file, lets call it "data.js" I'd define the content you want to import.

 

var branchOffices = [
  {"id":135,"name":"Chicago","widgets":5493},
  {"id":217,"name":"Los Angeles","widgets":5443},
  {"id":491,"name":"Waterloo","widgets":9544},
  ...
  {"id":854,"name":"Orlando","widgets":2834}
];

 

Then when your page loads, store this data in the D

 

db.transaction(function(tx){
  var branch = null;
  for(var i=0;i<branchOffices.length;i++){
    branch = branchOffices[i];
    tx.executeSql("INSERT INTO branches (id, name, widgets) VALUES (?, ?, ?)",
      [branch.id, branch.name, branch.widget]
    );
  }
});

 

 

adjust according to your table/column names obviously, but I find a simple JSON-like format is the easiest to import from.

 

 

Cheers,

Steve

 

 

 


PlayBook Apps: DataMine, Dots + Boxes, 5 Marbles
BlackBerry SmartPhone Apps: 5 Marbles Soon: **** (D.Z.A), ****Quest, **** Challenge, VaultCracker, DevBrowser, Radial****
Please use plain text.
Trusted Contributor
mobilvar
Posts: 119
Registered: ‎11-28-2011
My Device: 9930
My Carrier: Verizon

Re: Using HTML5 to access a local database

Just a note to those who visit this thread later, before inserting into the table "branches", it must first be explicityly created.

Here is the code I used to do that:

 

db.transaction(function(tx) {
tx.executeSql("CREATE TABLE IF NOT EXISTS branches(id NUMBER, name TEXT, widgets TEXT)", [ ]);
});

 

Here is also some basic code to write out the contents of the SELECT query, in this case the "name" field;

 

db.transaction(
function(tx) {
tx.executeSql("SELECT * FROM branches", [ ],
function(tx, result) {
for(var q = 0; q < result.rows.length; q++) {
//document.write(result.rows.item(i));
document.write('<b>' + result.rows.item(q)['name'] + '</b>')
}
}, null);
}
);

Please use plain text.