Click on any phrase to play the video from that point.
[metallic humming]
[ADOBE DEVELOPER CONNECTION]
Hi. I am Ray Camden, a Developer Evangelist for Adobe.
Today I'll be talking about the database support in PhoneGap.
When I say database support, I'm not talking about something like SQL Server.
This is a small, embedded, 1 user only DB system
that's not really meant for super powerful actions,
but it's really, really handy for mobile applications.
First off, it runs offline.
So if your user is not connected, not within WiFi, cell tower range, etc,
it still works.
If you don't actually want to pay licensing costs for SQL Server
or Oracle or anything like that, you have the power of a somewhat small
but nicely built SQL engine within your own application.
It's really especially useful if the user is creating content on your application.
Having a SQL engine allows you to perform really nice searches against that SQL
without relying on basic JavaScript string type matches.
So as long as you kind of keep in mind that it is single user,
it's not a full-blown SQL Server, you can be very, very powerful
and very, very happy with having this feature built right in to your PhoneGap applications.
The API is relatively simple.
You have a call to open the DB, and that gives you a handle.
You have an API to run a transaction.
Within your transactions you also have an API to execute SQL.
Everything you do will be based around those 3 functions.
When you do execute SQL,
if you write any SQL that returns something,
you get a really nice array of structures back.
So it's really simple within JavaScript then to work with that result set
and do whatever you want to do with it.
So I built a simple application that illustrates working with DBs. Let's take a look at it.
My application begins with some really simple HTML.
All I have here is 3 buttons and then a div that's going to act like a simple console.
I'll be logging all of my results there so we can see what's going on.
Most of the work is happening within my main.js file.
So once again, it's very important to not do anything special with the PhoneGap app
until PhoneGap is actually ready to go.
There's a special event for that, deviceready.
So almost every application is going to listen for that
and do most of its work after that event has fired.
You can see I'm doing that here.
In my deviceready handler, the first thing to do is run the openDatabase call.
The first argument is a simple name.
The second one is a version, and you can put whatever number you want to there.
The third argument is a separate name.
You could be a bit more verbose there and give it a longer name.
And the fourth argument is the preferred size.
Don't worry too much about there. Just put a simple number there.
It can get bigger than that.
Now, typically, a database is going to have to start with a few tables.
You can't easily ship a database with your PhoneGap application.
You have to actually create it when the application runs.
Luckily, that's pretty easy.
You can see the first thing I do after my DB is open is run a transaction
that calls a function called setup.
Within that setup function I have a transaction where I can execute SQL.
So what I've done here is used SQL to say "create table if not exist".
So on the very first time this application is run,
that SQL will actually create my table.
After that, it won't do anything at all.
What I'm using here is a simple table with just 3 columns:
an IB--that's an auto number--a simple text field, and a date field.
If I had more tables, I could actually just keep adding more execute SQL API calls in there
and basically have my entire setup routine handled within that one transaction.
Once this has fired, we are ready to go.
If you remember the front end, we had 3 main functions.
We had the ability to add, the ability to delete, and the ability to list everything.
Let's take a look at that list operation first.
That's within my testButton on touchStart function.
You can see I begin a transaction and then run my SQL.
And if you've done any server side work at all,
the SQL should be a bit familiar to you.
What it allows me to do is to run my SQL and then say,
"When you're done, fire this method."
I have a gotLog method that's going to get the result back from that SQL call.
Like I mentioned, it's a simple array, so I can do, for example,
results.rows.length to see if I actually have anything in there.
If I do have something, then I can do whatever business logic makes sense.
In this case, all I'm doing is taking everything and writing it out to screen. Okay?
The add operation is where we actually do our insert.
Let's take a look at that.
You can see my SQL here on line 28.
Notice the use of question marks. Those are bound parameters.
It makes your SQL a bit more secure and a bit more performant.
It allows me to take the dynamic aspects of my SQL and put it into an array of values.
My log table just stores a simple message--in this case, log it--
and the date that it was created.
So you can see my string being passed in as well as the current time.
When that's done, I fire a simple message to say that it's been added.
My delete operation just runs a simple delete from log.
Let's look at this in the emulator and see how it runs.
Here are my buttons.
If I hit Test, I should have nothing.
I will go ahead and hit Add a few times--1, 2, 3--
and then let's go ahead and take a look at that data.
And you can see there is my database right there.
And then if I wanted to clear it all, I can do like so
and then click Test again to get my data.
In this case, it's all gone.
So you can see working with SQL is very easy within PhoneGap.
[PhoneGap API reference - docs.phonegap.com]
It provides a lot of power to your applications, especially in terms of search.
I hope this has been helpful. My name is Ray Camden, and thank you for watching.
[ADOBE DEVELOPER CONNECTION]
[metallic humming]
