public
Last active

PhoneGap sqlite working example

  • Download Gist
gistfile1.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209
Hi
I hope this helps you all get started with phonegap and sqlite in your
application
 
Dean-O
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
 
<!--
This is an example that shows how to create an application that uses
an embedded sqlite database
in a mobile device (iphone,ipod,ipad,android using phonegap, jquery
and sqlite.
 
Your phonegap project will already contain the phonegap.js.
 
You will need to download and add to your project the jquery.min.js
file
 
The application will create a database called WebSqlDb with a
table in it called User, which contains three fields UserId,
FirstName and LastName
 
When the application is run the firsttime, if the local database does
not exist, the application
will create the database and the table.
 
The application shows two text boxes which you can use to add values
to the database using the add record button
 
The application also has a refresh button which will get the data from
the database and show it on the screen
 
When the application is run on the device the onBodyLoad() function is
called, which sets up the database and table
 
The Add Record button calls the AddValueToDB() function
 
The Refresh button calls the ListDBValues() function
 
There are a few alert statements in this application, which are only
there for debuggin purposes. They look like this
alert("DEBUGGING: followed by some text");
 
These are only in the application to indicate where the application is
at when it is processing functions, etc
 
You will need to comment these out before you deploy/sell your
application
 
-->
 
<!-- Change this if you want to allow scaling -->
<meta name="viewport" content="width=default-width; user-
scalable=no" />
<meta http-equiv="Content-type" content="text/html;charset=utf-8">
 
<title>Embedded Sql Example</title>
 
<!-- include the next line to use phonegap javascript functions -->
<script type="text/javascript" charset="utf-8" src="phonegap.js"></
script>
 
<!-- include the next line to use jquery functions in your application
you must download this and include the directory your html file is in
-->
<script type="text/javascript" charset="utf-8" src="jquery.min.js"></
script>
 
<!-- main scripts used in this example -->
<script type="text/javascript" charset="utf-8">
 
// global variables
var db;
var shortName = 'WebSqlDB';
var version = '1.0';
var displayName = 'WebSqlDB';
var maxSize = 65535;
 
// this is called when an error happens in a transaction
function errorHandler(transaction, error) {
alert('Error: ' + error.message + ' code: ' + error.code);
 
}
 
// this is called when a successful transaction happens
function successCallBack() {
alert("DEBUGGING: success");
 
}
 
function nullHandler(){};
 
// called when the application loads
function onBodyLoad(){
 
// This alert is used to make sure the application is loaded correctly
// you can comment this out once you have the application working
alert("DEBUGGING: we are in the onBodyLoad() function");
 
if (!window.openDatabase) {
// not all mobile devices support databases if it does not, the
following alert will display
// indicating the device will not be albe to run this application
alert('Databases are not supported in this browser.');
return;
}
 
// this line tries to open the database base locally on the device
// if it does not exist, it will create it and return a database
object stored in variable db
db = openDatabase(shortName, version, displayName,maxSize);
 
// this line will try to create the table User in the database just
created/openned
db.transaction(function(tx){
 
// you can uncomment this next line if you want the User table to be
empty each time the application runs
// tx.executeSql( 'DROP TABLE User',nullHandler,nullHandler);
 
// this line actually creates the table User if it does not exist
and sets up the three columns and their types
// note the UserId column is an auto incrementing column which is
useful if you want to pull back distinct rows
// easily from the table.
tx.executeSql( 'CREATE TABLE IF NOT EXISTS User(UserId INTEGER NOT
NULL PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL)',
[],nullHandler,errorHandler);
},errorHandler,successCallBack);
 
}
 
// list the values in the database to the screen using jquery to
update the #lbUsers element
function ListDBValues() {
 
if (!window.openDatabase) {
alert('Databases are not supported in this browser.');
return;
}
 
// this line clears out any content in the #lbUsers element on the
page so that the next few lines will show updated
// content and not just keep repeating lines
$('#lbUsers').html('');
 
// this next section will select all the content from the User table
and then go through it row by row
// appending the UserId FirstName LastName to the #lbUsers element
on the page
db.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM User;', [],
function(transaction, result) {
if (result != null && result.rows != null) {
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows.item(i);
$('#lbUsers').append('<br>' + row.UserId + '. ' +
row.FirstName+ ' ' + row.LastName);
}
}
},errorHandler);
},errorHandler,nullHandler);
 
return;
 
}
 
// this is the function that puts values into the database using the
values from the text boxes on the screen
function AddValueToDB() {
 
if (!window.openDatabase) {
alert('Databases are not supported in this browser.');
return;
}
 
// this is the section that actually inserts the values into the User
table
db.transaction(function(transaction) {
transaction.executeSql('INSERT INTO User(FirstName, LastName)
VALUES (?,?)',[$('#txFirstName').val(), $('#txLastName').val()],
nullHandler,errorHandler);
});
 
// this calls the function that will show what is in the User table in
the database
ListDBValues();
 
return false;
 
}
 
</script>
</head>
<body onload="onBodyLoad()">
<h1>WebSQL</h1>
<input id="txFirstName" type="text" placeholder="FirstName">
<input id="txLastName" type="text" placeholder="Last Name">
<input type="button" value="Add record" onClick="AddValueToDB()">
<input type="button" value="Refresh" onClick="ListDBValues()"> <br>
<br>
<span style="font-weight:bold;">Currently stored values:</span>
<span id="lbUsers"></span>
</body>
</html>

Hey thnx............it's working

Finally after loads of pain, found one working example. Thanks a lot!

not working :((

using xcode with phone gap. and note that the phonegap.js --> cordovaXY.js (xy is the version) and i already download query.min.js ..

http://inwebsite.wordpress.com say: waw! thank's
first this code not work. Then,
I read this code per line, i found that sentence comment when i copy paste was entered so i put it back then tara... it works

Hi ! thank's for this. But i work on windows phone 8 devices. Have you got an idea ? I check on the net to resolve my problem but i didn't find a good solution. I will try nova framwork....

Thank's a lot if someone have a solution to asking database with sqlite (Phone Gap) on WP8.

I am looking for a PhoneGap Build solution for PhoneGap Storage on a Windows 8 phone.... If anyone know, I welcome feedback. Thanks!

Thanks.. good example!

Uow!!! Perfect! Thanks!!

I have been looking for a good working example! thanks so much!

Awesome....! after a lot of google search ... found this gr8 tutorial..! Thank you :)

I have tried the above mentioned sqlite code but after compiling, log cat shows AddValuesToDB() and ListDBValues are not defined. how to solve this? Please help me...Thanks

My problem is the same as varae; LogCat shows; Can't find variable onBodyLoad, AddValuesToDB() and ListDBValues ...

Finally it works,.. everythink was because you have to bind in the same line sentences like:

tx.executeSql( 'CREATE TABLE IF NOT EXISTS User(UserId INTEGER NOT
NULL PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL)',
[],nullHandler,errorHandler);
},errorHandler,successCallBack);

It was my fault because my javascript level is null...

Very nice post, It will be very useful. Otherwise, I have no clear where the database that is created by phonegap is stored? The database is stored at device File System or at memory?

Reading all comments I have notice that Phonegap have problems with using sqlitedb under Windows 8. Is this true? Our customer need an a mobile app that runs on different platforms (Android, Windows 8 and iOS) but it seems phonegap does not cover access to the database for all these platforms.

Kind Regards,
Jordi.

i getting error in null handler not define . plz any one can tell what variables to put in error handler..

Hi,Thank you so much for this nice post.Its working fine.

Hi, I have this error:
01-09 13:30:57.512: E/Web Console(334): ReferenceError: Can't find variable: ListDBValues at file:///android_asset/www/dodaj.html:198

please help me.

Its not working with IOS

Not working on my Windows XP + Eclipse + Android:

ReferenceError: Can't find variable: $ at file:///android_asset/www/index.html:130

its seems it doesn't recognize this: $('#lbUsers').html('');

Does somebody knows why?
(I just copied and pasted it)

@joancatala

seems like it doesnt find your jquery library .

From code:
include the next line to use jquery functions in your application
you must download this and include the directory your html file is in

    <script type="text/javascript" charset="utf-8" src="jquery.min.js"></

Works perfectly! Thank you so much!!! Been looking for a simple tutorial on using SQL inside a PhoneGap app. This is exactly what I needed.

To anyone who wants to use this code -- a word of warning:
The way the text is formatted on this page, some of the text is being word-wrapped to the next line. You have to put it into a text-editor and fix the bad line-breaks in the comments so they don't interfere with the code. (Lines 105/106, 113/114, 121/122 are an example of this, although there are several more.)

There are also a couple of bad line-breaks in the JavaScript code itself. For example, lines 130, 131, and 132 should all be on a single line with no line-breaks. Lines 70/71 have the same problem. There are several other examples of this problem as well.

So, because of the way the text is formatted on this site, you can't simply copy/paste and expect it to run. Take your time and go through it to fix all the weird line-breaks, and I promise you it will run. (Oh, also don't forget to download the proper jQuery-min.js file at jQuery.com. Then it will run. I promise.) :-)

Works great !!
Had to merge broken lines especially fix "INSERT INTO User..." line so that the sql will be on the same line

Works great! thank you! the only thing is that when you paste it from here make sure you check all lines once to see if the comments are fine and there are not additional enters. just fix them and it works great!

It's Working After the code review. One line of code is comment (delete tableif already exist). Than I use phonegap1.4 and jqm1.3.2.min.js.

Working code snippet:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">


Embedded Sql Example


WebSQL








Currently stored values:


working in eclise-phonegap-andropid

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.