Folks who have used Adobe AIR before already know (or hopefully know) about it's cool, built-in database support. Any AIR application (whether written in Flex or HTML - you do remember you can do AIR with HTML, right?) can make use of an embedded SQLite database. This is incredibly useful for applications and pretty darn easy to use as well. PhoneGap also has built in support for SQLite via their storage support. In this blog entry I'll walk you through a sample application I've created that makes use of the feature. I'll also point out some of the mistakes I made while writing so that hopefully you can avoid the issues I ran into.
First - it is important to carefully read PhoneGap's storage documentation. I don't know why - but the biggest issue I've had with PhoneGap so far has been around my incorrect assumptions not matching up with reality. I guess I shouldn't be surprised there, but with PhoneGap I've found it really critical to carefully read the docs. It isn't that PhoneGap is brittle per se, but you really need to ensure you know what you're doing. Consider yourself warned.
Along with PhoneGap's docs, you want to read the W3C spec for Web SQL Database support. It definitely helped fill in some things that the PhoneGap docs didn't cover. As an example, the PhoneGap docs enumerate the error codes for the SQLError object, but don't tell you the actual values that go with them. The W3C docs for SQLError though have both the labels and the codes.
For folks who are familiar with SQLite in AIR, there are two main differences I've found between the AIR and PhoneGap platforms.
First - it seems (although I've not confirmed this) that PhoneGap only supports asynchronous connections. That's not a bad thing typically, but I tend to prefer synchronous connections since they are a lot simpler to work with. They can lock your application up if you need to perform a particularly long operation, but I like having the option at least. I'll typically use two connections in an AIR app. I'll have a synchronous one for setup and then an asynchronous one to handle post-launch operations. Again - with PhoneGap you only have the asynchronous option.
Second - while both AIR and PhoneGap support transactions through SQLite, PhoneGap requires all SQL transactions to go through a transaction. This adds a level of complexity to your code that may be a bit confusing at first, but once you've worked through a couple of examples it really isn't that big of a deal. It tended to bite me in the rear because I made the mistake of running "executeSQL" on my transaction object instead of "executeSql". The application reported it as a database error because the transaction callback was executing. This was confusing and hard to debug.
Ok - so let's talk about the application. My demo application is called SimpleNotes. It's a very poor, very limited note taking application. It's got a grand total of two screens. The first is a list of your current notes.

You can click a note to edit it, or simply click Add Note.

So how does it work? I've got an event handler that listens in for the deviceready event. This event is fired by PhoneGap when the hardware is ready for business time. I began by having it call a function named phoneReady:
function phoneReady() {
//First, open our db
dbShell = window.openDatabase("SimpleNotes", 2, "SimpleNotes", 1000000);
//run transaction to create initial tables
dbShell.transaction(setupTable,dbErrorHandler,getEntries);
}
In case your curious why I named the database twice - the first string is the database name while the second one is the display name. Much like my name is Raymond but I write it as G-Megabyte. (Now you know...) The second argument is the version. This can be used to shift the application from one database version to another. Normally you would update a database with SQL though so I'm not sure that folks would change this often in production. The last argument is the size. I picked 1000000 because I thought it looked nice on paper. Notice then we have a transaction to run setupTable. This is where I'll do my initial definitions.
//I just create our initial table - all one of em
function setupTable(tx){
tx.executeSql("CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY,title,body,updated)");
}
As you can see, I use "IF NOT EXISTS" in my creation script. That makes it safe to run more than once. For information on valid SQL syntax for SQLite, check their docs. Ok, now if you remember the original transaction call, it had 2 more arguments. The second was a generic error handler, and the third was getEntries. First let's look at the error handler:
function dbErrorHandler(err){
alert("DB Error: "+err.message + "\nCode="+err.code);
}
Most likely, and especially for an application like this, any error should be logged, possibly emailed, and also result in the termination of the app itself. My application is pretty useless if it can't do what it needs to with the database. But how you handle your errors will be up to the individual application. Let's more on then to getEntries.
//I handle getting entries from the db
function getEntries() {
dbShell.transaction(function(tx) {
tx.executeSql("select id, title, body, updated from notes order by updated desc",[],renderEntries,dbErrorHandler);
}, dbErrorHandler);
}
So compared to the earlier transaction call I got a bit fancier here. I've embedded the function within the transaction call itself. It may be a bit hard to read there so let me rewrite this in pseudo-code so it is clear:
Start a transaction:
execute my sql, and on a good result, run renderEntries, and on a bad result, run dbErrorHandler
If the transaction fails: run dbErrorHandler
I sometimes tried to put the result handler in the transaction call, but if you do that, you don't get passed the result of the SQL execution. So... don't do that. The second argument to executeSql (the empty array) is how bound parameters are handled. Since I have none I just pass it as an empty array. Now let's look at renderEntries.
function renderEntries(tx,results){
doLog("render entries");
if (results.rows.length == 0) {
$("#mainContent").html("<p>You currently do not have any notes.</p>");
} else {
var s = "";
for(var i=0; i<results.rows.length; i++) {
s += "<li><a href='edit.html?id="+results.rows.item(i).id + "'>" + results.rows.item(i).title + "</a></li>";
}
$("#noteTitleList").html(s);
$("#noteTitleList").listview("refresh");
}
}
So a good portion of this is generic DOM manipulation and jQuery Mobile related, but focus on how I use the results attribute. I can easily check the length and handle an empty database. I can also easily loop over them and access the data. Pretty simple, right? Let's look at one more function. When saving a note, I'm either saving an existing note or editing a new one. I've got code in there to handle that, but let's focus on the actual save operation.
function saveNote(note, cb) {
//Sometimes you may want to jot down something quickly....
if(note.title == "") note.title = "[No Title]";
dbShell.transaction(function(tx) {
if(note.id == "") tx.executeSql("insert into notes(title,body,updated) values(?,?,?)",[note.title,note.body, new Date()]);
else tx.executeSql("update notes set title=?, body=?, updated=? where id=?",[note.title,note.body, new Date(), note.id]);
}, dbErrorHandler,cb);
}
So before we start - I'll say that the note argument is a simple JavaScript object. It's got values for title, body, and possibly the ID. The cb argument is simply a call back to execute. Notice how I handle insert versus update logic. Also note how i handle the bound parameters. Very much like AIR. It looks like you can't do named parameters like you can in Adobe AIR, so make your get your positions right.
And that's pretty much it. There's a lot more logic around to handle my form editing and the like, but you can see that yourself in the attached zip. The zip contains an unsigned APK as well as a copy of the code. Note - when saving a note I've got a weird page display issue going on. Anyone who fixes that gets mega bonus points from the pool.
Edit: For folks downloading the code, please see Anne's comment here.
Archived Comments
Curious... In terms of security is it better to store sensitive data in this manner or to use the HTML 5 web storage? I am concerned about someone being able to look at the data outside the application.
Thanks,
--Dave
Hmm. This is a guess:
SQLite uses a 'real' file. Therefore, someone should be able to find the file and get to it. In AIR you can encrypt the DB. I do not see that option here.
For LocalStorage, it is tied to the browser instance and the domain. LocalStorage should still work fine w/n PhoneGap, and I'd _assume_ be tied to the Application name (since there is no domain). My gut says it should be more secure - but I wouldn't put any money on it.
Great timing Raymond, as usual.
In my experience SQLite in Adobe AIR (Apollo) was one of highlights in Adobe System aspirations to access the desktop, I remember buying a book on Amazon to start learning (Beginning Adobe AIR). Now with PhoneGap I hope to have the time to use these products!!!
Hello Sir, thank you so much for this tut, it did hel me a lot, but i am wondering where the database is? i can't find it, even after opening the DDMS perspective, on Eclipse, and i navigate to the app folder, but i am enable to find it,
Please help, i need to find that database
See this: http://gauravstomar.blogspo...
Thank's a lot for the reply, except, that in my case, and after that i did implemented the example from your blog, i'am not able to generate the DataBase, even if the interface shwos that notes are added, but i just can't find the generated dataBase.
thank you
Well, for now, I'd say let's ignore your inability to find the database. Let's focus on your inability to use dbs at all.
So your saying that when you work with a db, it shows notes being saved, but when you restart the app you see no notes?
hi again,
i am able to add notes on the graphic interface, and when in restart the app i do find the saved notes, but the problem is that i can't find the database(i want to see, and consult the saved data)
Thank you .
Oh, ok, so then I'm out of luck. That blog entry I posted is the only one I know discussing the physical location of the db.
all right then, but thanks so much an i keep looking for a solution,
Thank you :)
I encountered a problem saving the notes, can i copy your version of phonegap.jar im using cordova1.5.jar. here's the error:
Web Console(544): Uncaught TypeError: Cannot call method 'transaction' of undefined at file:///android_asset/www/js/main.js:56
Are you using the exact same version, except you used cordova1.5.jar instead of phonegap.jar?
I want to use database file as such in the code because data is huge and adding that data to database is hard. I implemented same in Flex application but here i get "no such table" error. My code is:
db = openDatabase("testdata.db","1.0","Apps Sample Database",5*1024*1024);
db.transaction(function(tx){
tx.executeSql("select * from test",[],function(t,res){
var len=res.rows.length;
for(var i=0;i<len;i++){
$('#select-choice-b').append('<option val='+res.rows.item(i)['a']+"'>"+res.rows.item(i)['b']+"</option>");
}
}, function(tx,error)
{
alert("database error:" +error.message);
});
here testdata.db is altready ctreated and have table test with data in it.
I believe you want to follow this: http://gauravstomar.blogspo...
could you explain me with sample code for connect the MySQl database in phonegap for android project
Thanks in Advance
You can't run MySQL on a device. It would need to be on a remote server. You can then use JavaScript/Ajax to hit it via something on the server, like ColdFusion, to handle the connection.
Than you for your response!Actually i trying for an android app using phone gap which one used for the tracking the bus routes.so for that purpose i have to connect the database or any existing server.i tried with java script and php .but i did n't get success .could you please suggest with some useful code to me.its very use full me.
A great thanks in advance
Well, it depends on what went wrong. :)
FIrst off - are you sure your PHP code is right? I'd test that without PhoneGap. You need a PHP script that can respond to remote requests and return database information in JSON format. You can do this w/o touching PhoneGap at all and it will make testing easier.
At that point, you switch back to your PhoneGap app and see if the Ajax calls work now.
HI.i really lot of things learn t from this blog.thank you providing this extraordinary blog for us. i am new to phone gap.and i want use web services in android app.can any one give detail information about it and any .js or jar files to copy into project?and if you give sample code to me a grateful thanks to you.
Are you talking about the formal definition of Web Services, ie those that use SOAP? If so, you will need to find a JavaScript library that works well with it. SOAP is just XML, but it is very verbose and very stringent on how you use it. If at all possible, I'd recommend a service that uses JSON instead.
Hi, I´ve been working with phonegap retrieven a ws user data, but for some weird reason when they update info, or anything in the ws response, the app keeps on sending the old response; the only way to get the new response is by uninstalling and installing again the app.
Any ideas why this could be happening?
I'd have to see more of your code to figure out why it isn't working. SInce your issue really isn't on topic for this blog post, please use my Contact form to send me some details.
Nice tutorial, Thanks a lot. Ive got a question.
Do i have to write this:
var db = window.openDatabase("DDB", "1.0", "DDB", 200000);
everytime i want to do a query to the database?
Because when i try to do a query from another html i got nothing I think
I did it no worries to answer the prevoius question.
Thanks
This was not working in IOS because the code
$("#editPage").live("pageshow", function() {
var loc = window.location.hash;
was not retrieving the url parameter. I found another of your blogs (http://www.raymondcamden.co... which used the data attribute and that fixed the code for me. Just change the var loc line.
$("#editPage").live("pageshow", function() {
var loc = $(this).data("url");
Perhaps this warrants an update to the code?
Thanks Anne. I added a small note towards the end.
my error is "Uncaught TypeError: Cannot call method 'transaction' of undefined at file:///android_asset/www/js/main.js:60". so help me... how to slove this error..
@Ad: I'd need more data. It seems like your variable isn't being used correctly. Can you please post your version of main.js to gist.github.com or pastebin?
i am new in phonegap.. so i can't get the version of main.js but i am use phonegap-1.1.0.js. i will post my hole main.js to you..
Thank you to reply me...
Thank you for this tutorial Sir.
I am just curious as to will it affect performance if we have to execute lots of queries in the table and get their results on app initialization.
If is it so, is there a better way to counter that.
I am looking at SQLLite, database.com and PhoneGap storage. Trying to figure out which one suits the best when size of database might cause issues.
Do you really need to execute all those queries on the first load? Maybe some could wait until later - like when some other part of the app is loaded for the first time. That's what I'd do first.
I was thinking on how those jokes/ meme app were created. They get random jokes. Hence, if they have 1000 jokes in record they may be fetching it from DB.
So, if something on same lines have to be thought for Phonegap, either we will create a DB with those 1000 entries at some point in our application or fetch from already created DB. (Hope I'm assuming right).
In case if we are loading wouldn't that slow down the mobile app for user, else we are getting from DB, do you have any suggestions on which one works best.
My apologies for the long comment.
Well, you could try shipping the data with the app (http://www.raymondcamden.co.... Also keep in mind that 1k entries would probably insert pretty darn quickly.
Thank you Sir for that wonderful entry.
I will use that plugin to work on some demo examples and will see if I could update DB based on user inputs accordingly...
I think you can fake a synchronous executeSql by using a setTimeout loop and have the success and error callbacks do a clearTimeout. Maybe setInterval would even make sense in this scenario because you're just waiting 1 of the 2 callbacks to be called.
Not sure I'd want to do that.
Hi raymond,
i am new to phonegap. i am using phonegap's cloud build. As u suggest i went through docs about storage. I just want to know, how to give permissions and get started with cloud build ? One Example will be fine.
Sorry, are you about PhoneGap Build? If so, you don't need 'permissions', you just need to register for an account on the site. To get started, read this guide:
https://build.phonegap.com/...
Can you tell me what is the max size of database I can create on IOS device? I read on something like 50MB. Is that correct?
I'm not sure actually. I can say that if you are wanting to store large things, you may wish to use the file system API instead.
Hi, i try to work with phonegap and android sdk, but i have a problem. when i launch my application to my device, samsung Galaxy ace, my app trough a exception.... Uncaught TypeError: Transaction error callback must be of valid type.
my framework said that the problem occur in the line 75 of my index.html and in this line i do this.
transaction: function(fn,err,suc){
if(self.connection==null){
self.openDataBase();
}
self.connection.transaction(fn,err,suc);
}
connection is my var where i assig to the database connection.
All I can think of is to ensure that err is really a function.
is that right,
self.connection.transaction(fn,err,suc)......in this invocation i have three function that will pass when used the transaction... i dont know hwat happen, but whe i try the app with the browser, all is right, i can open data base, i can do a select, but when i install the app in my galaxy samsug ace i found this problem, Uncaught TypeError: Transaction error callback must be of valid type.
do Raymond Camden # have some idea, what is the problem?
thanks for all :) i found the error is my fault
Hi,
We are using a set of insert query transactions during a phonegap application launch. Which Takes considerable amount of time. Since all data are in server and that gets frequently updated, we can go for the choice to providing local database file along with the app
Hi
We are using a set of insert query transactions during a phonegap application launch. Which Takes considerable amount of time. Since all data are in server and that gets frequently updated, we can go for the choice to providing local database file along with the app
Is there any better solution that the this? Please help
Hi ! thanks for this post !
I'd like you could write a tutorial only about handle db transaction from one version to one other.
Example: after 1 year of development, and in-production, I need to add a column to a table. So my db now is v.2, before was v.1. I'm not able to understand how to handle this ..
- What happens if my customer download the app from the first time? I suppose I need to init the db directly with v2 code
- What I a customer had the v1 and after update the db need to be updated to v2 ?
I was writing an answer and this turned out to be more complex then I imagined. Working up a demo for you in a new blog post.
Please see my follow up here: http://www.raymondcamden.co...
If you have any questions, please post the comment there, not here.
Hello Sir, am new to phonegap but i can use dreamweaver, mysql, php and jquery please Sir i need yur advice on the tools i will need if i want to develop a mobile app to compile using phonegape. infact my major problem is the DBMS engine to use and the server script language kindly provide me with some tips thank you.
Idris, I think you are asking about the tools you need to use PhoneGap. To be clear, there is nothing you *have* to use. You can use Dreamweaver, Notepad, Brackets, etc. Whatever editor helps you write code is fine for PhoneGap.
As for the DBMS and server side, PhoneGap doesn't care. My primary experience is with ColdFusion so I've done plenty of blog posts showing CF as the back end, but as I said, PhoneGap doesn't care.
Thanks Sir.
Great article; I get an "error loading page" when I submit (save) a note; did anybody fix this issue? thanks.
If you test in Chrome, do you see a better error message?
thanks Raymond; I will take a look at it.
I encountered the same problem because there was an error in my javascript. Copy and paste your code to Dreamweaver to check you syntax.
How can I use the results of an executeSql SELECT and Insert them to another executeSql within the same function? I've tried two db.transactions but it seem that the results are not yet known by the last db.transactions even if I assign the results in global variables.
here's my code:
function addme(){
var reni_energy=0;
var reni_iron=0;
var db = window.sqlitePlugin.openDatabase('des', '2.0', 'DES DB', 2 * 1024 * 1024);
var sql = "SELECT * FROM reni WHERE groupo='" + group + "'";
db.transaction(function (tx) {tx.executeSql(sql, [], function (tx, results) {
len = results.rows.length;
if(len==1){
reni_energy = results.rows.item(0).energy;
reni_iron =results.rows.item(0).iron;
}
})
});
var db = window.sqlitePlugin.openDatabase('des', '2.0', 'DES DB', 2 * 1024 * 1024);
db.transaction( function(tx) {
var query="INSERT INTO fooditems (energy,iron) VALUES (?,?);";
tx.executeSql(query,[reni_energy,reni_iron],[],[]);
});
}
The callbacks are asynchronous which is why it isn't working for you. You would need to do the insert call inside the callback handler of the select.
Thank u for ur nice tutorial.But i get Following error when i run this programme
10-24 18:10:52.162: E/Web Console(2974): Uncaught TypeError: Cannot call method 'transaction' of undefined at file:///android_asset/www/js/main.js:60
Keeping in mind this post is now 2 years old - are you using the latest PG? What is line 60 for you?
Yeah i use phone gap 2.9.0.(Codrova 2.9.0).
Line 60= dbShell.transaction(function(tx) {
if (note.id == "")
tx.executeSql("insert into notes(title,body,updated) values(?,?,?)", [note.title, note.body, new Date()]);
else
tx.executeSql("update notes set title=?, body=?, updated=? where id=?", [note.title, note.body, new Date(), note.id]);
}, dbErrorHandler, cb);
it always shows "cannot call method 'transaction' of undefined" in saveData and getEntries.
i spend full day but this is not solved.
Aravinth: That means the db variable wasn't set right. I'd look into that.
Hi Raymond ,
Thanks for your reply and nice tutorial . i solved that issue it is my fault phonegap version problem so i got that error.
But now i got another one problem when i click the list view for edit it always empty that is the " window,location.hash" shows empty . Can you tell me what can i do.
Hi Raymond ,
I am new in phonegap . I use your example in my application. when I tested on device "add notes" is working fine but I try to edit existing notes its shows a message "error loading page"
Please help me.
Thanks in advance.
@Aravinth: See if this comment helps: http://www.raymondcamden.co...
@Suraj: You will need to debug to see what the issue is. Try using Weinre and get the line number of the error.
hi sir,
am developing an phonegap app,the problem am facing is database is created and inserted in all ios and android devices,but not in android 2.3 gingerbread version.is any other possible way to create it?please help me.
am wating for your reply.
What error do you see?
hi sir,
am having the following error in android gingerbeard.And also i tried the storage example from the phonegap docs.
E/Web Console(11914): Uncaught TypeError: Transaction callback must be of valid type. at file:///android_asset/www/index.html:531
looking for your feed back sir.
Can you show us that line, 531?
sir,
following is the line which showned in line 531.
here i pass two parameters for the function parseval which is going to hit api.
db.transaction(parseval(results.rows.item(k).currentstatus,tx));
That doesn't look valid to me. The first argument in transaction should be an inner function. Please see the WebSQL documentation (http://www.w3.org/TR/webdat....
I have doubt in passing the query result to a variable and displaying that in text box .
Sorry, do you have a question?
Thanks Raymond Camden ,
This time its work perfectly . I need one more help from you .
I want to know how can I run phonegap application in the background when I click back button.
Please help me.
Um - I honestly don't know. I think the OS handles putting an app to sleep based on its logic of how much priority an app needs. I know you have event handlers in PhoneGap you can use (pause/resume), but that's about it.
I want to use the UPDATE-Statement in my phonegap application.
But it doesn't work, i don't know why:
transaction.execSql("UPDATE PROFILES SET name='SandroidXXX' WHERE id='2'");
Error 0; The SQLTransactionCallback was null or threw an exception.
Do you know what the cause of this could be?
Not off hand - can you show a bit more of your code?
Hi raymond i have one doubt i googled but didn't find any solution . Please tell me if you know.
// main.js
function onDeviceReady(){
var query = "select * from DEMO";
transction(query,processPersonsResponse);
}
function processPersonsResponse(response){
alert(response);
}
i called the transaction from another JS file like above and i want the return results.
my another JS file is like:
function transction(query,a){
db = window.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);
db.transaction(
function(tx){
tx.executeSql(query,[],successCB);
},errorCB);
}
function errorCB(e){
alert("error"+e.message);
}
function successCB(tx,results){
alert(results.rows.length+" db ");
a(results);
}
but i got error like "the statement callback raised anexception or statement error call back".
Please tell me any idea how to do this.
Thank you.
I'd have to see it completely to help. Nothing sticks out really.
How could I know if my update statement success or not find the condition and no records updated.
example:
UPDATE CUSTOMERS SET customerName= "Tom" WHERE customerId= 1;
if there is no customerId=1 there will not be any rows affected.
How could I know the affected rows number in sqlite phonegap cordova 3.4?
The SQLResultSet object supports a property (insertId) for a new ID (if doing an insert) as well as records affected property (rowsAffected). You could use this to determine if data was updated.
could you give me example with update statement ?
The executeSql command lets you specify the call back function to run and the SQLResetSet is passed to it. That should be enough to get you going.
How to display the value of the max id.
var sql = 'SELECT max(id) FROM CONTACTS';
var db = window.openDatabase("Database", "1.0", "Demo", 200000);
db.transaction(function(tx){
tx.executeSql(sql,[],function(ax,res){
maxId= res.rows.length;
}
it alerts "1" and in table the maximum id =5
What is the error?
You used the number of rows. It will always be 1 because your SQL returns one row. You want the value of that row, not the number of rows. Try using select max(id) as topId. That will give a name to your column.
could you explain more by my example
I told you what to do. I'm not sure what else I can tell you.
OK i got it
Many Thanks
hi, i download you app and works fine, but i need the config.xml file for compile my version for testing, you cand publish it? (I form argentina, sorry for my bad english)
Why do you think you need that?
Hi Raymond,
Thanks for nice tutorial. I tried your code in my android Samsung tab it's not working it was not executing .dbShell = window.openDatabase("SimpleNotes", 2, "SimpleNotes", 1000000);
Can you please guide it.
What error do you get?
Hey Raymond. Thanks for your great tutorial. I tried your example and beside that weird error you've mentioned, after I click on "Save Note" button and getting that error. I don't see my added note in the ListView.
@Alex: "that error" - what error?
Please tell me required files for this example, i want use database.
The download link was broken - it is fixed now.
link dead
Fixed.
download link is not working.. when i click on that.. it gives page not found.. plz check and update link.. Thanks
Please use this new download link: http://static.raymondcamden...
APP NOT WORKING -.-
OK
example is not working
You mean the download code or something else? And how is it not working?
download link shows 404 error
You can find it here: https://static.raymondcamde....
Thank you
Thanks and Regards ,
*SUBIN BABU*
p : +1 (703) 972-2401
m: +91-9895657271
e : subin@artificerstechnologies.com
w : www.artificerstechnologies.com
This Artificer's Technologies email, along with any attachments, is considered confidential or otherwise protected from disclosure. If you have received it in error, you are on notice of its status, please notify us immediately by reply email and delete this email and its attachments from your system. Dissemination, distribution or copying of any erroneously received Artificer's Technologies email, along with any attachments, use of it for any purpose, or disclosure of its contents to any person(s) other than the person(s) to whom it was intended is prohibited. Thank you for your cooperation.