Here is a working phonegap sqlite application example/tutorial for you to use
35,592 views
Skip to first unread message
Dean-O
Feb 20, 2011, 2:10:37 PM2/20/11
to phonegap
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>
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>
bitfool
Apr 11, 2011, 12:57:19 PM4/11/11
to phonegap
Thanks for posting, this was a good starting point for me.
Paul
Paul
Steven Benjamin
Jun 19, 2012, 4:57:06 PM6/19/12
to phon...@googlegroups.com
Thank, I appreciate the time you took to post this.
Junio Vitorino
Jun 19, 2012, 5:58:08 PM6/19/12
to phon...@googlegroups.com
Dean-O i've a pre-populated database in sqlite, how i could change your sample to reading it?
Rah manto
Jun 24, 2012, 10:20:07 AM6/24/12
to phon...@googlegroups.com
and hi vito
u might want to check these line:
db = openDatabase(shortName, version, displayName,maxSize);
this syntax is used for opening a database, means u can use ur prepopulated database without a problem
hope its helps
u might want to check these line:
// 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);
}
hope its helps
Rah manto
Jun 24, 2012, 12:38:58 PM6/24/12
to phon...@googlegroups.com
Okay this apps Works good ^^v
thank you so much Dean!!
ima student at EEPIS
i wanna make friends with u guys as phonegap/jquerymobile developer
add me at http://www.facebook.com/uSeulpeunTajim
On Friday, June 22, 2012 7:29:54 AM UTC-7, Rah manto wrote:
thank you so much Dean!!
ima student at EEPIS
i wanna make friends with u guys as phonegap/jquerymobile developer
add me at http://www.facebook.com/uSeulpeunTajim
On Friday, June 22, 2012 7:29:54 AM UTC-7, Rah manto wrote:
thats mean we donot need doing any changes to the device??
in example:
1. bought new phone with android gingerbread as the OS
2. i compile this code using phonegap via eclipse
3. i run/install this apk to the phone
The question is:
the apps running well on the phone? and the database created by apps when it first run on the phone?
please bear with me
ima newbie ><
balaji mogadali
Jun 26, 2012, 3:09:16 PM6/26/12
to phon...@googlegroups.com
hi ,
--
With Regards,
This problem found if you store your database in local storage ,
in order to reduce the problem you can use WebSql Storage which is not effected until u try to remove it by Sql
i felt this is solution to your problem
On Tue, Jun 26, 2012 at 11:28 PM, Mark Altman <marka...@gmail.com> wrote:
Thanks for this. Will the data in this database be deleted if the user clears their phone's cache (my Android device setting reads "Clear locally cached content and databases)? For example, if I created an app that let users save appointment date/time/details would this storage option be the wrong choice because they might lose their data when they clear their cache? If so, what would we use if we wanted to store the data with no danger of losing it?Mark
--
-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
For more info on PhoneGap or to download the code go to www.phonegap.com
To compile in the cloud, check out build.phonegap.com
With Regards,
M.Balaji
Raj Gurung
Aug 7, 2012, 3:15:44 PM8/7/12
to phon...@googlegroups.com
hi!
does the phonegap.js files needs to be changed to crodova 1.5.0? and i can not find jquery.min.js files! Is it now jquery.mobile-1.1.0.min.js ? When this file is ran on x-code 4, upon successful adding files, will it show the data being added when refresh button is clicked?
Please reply ASAP, as I am doing this for my dissertation and I have very limited time.
Thank you very much in advance for any help offered!
Regards,
R.
Nathan E.
Aug 14, 2012, 10:36:43 PM8/14/12
to phon...@googlegroups.com, balajim...@gmail.com
We're trying to wein everyone off WebSql, it's deprecated, nasty, and limited...
Srikanth Reddy
Feb 10, 2013, 8:56:17 AM2/10/13
to phon...@googlegroups.com
Hi i am getting
02-10 17:08:29.482: ERROR/Web Console(23604): ReferenceError: Can't find variable: onBodyLoad at file:///android_asset/www/index.html:113
error, from the above code will u please help me to retify this. am new to this technology.
WebSteve
Mar 8, 2013, 6:08:17 PM3/8/13
to phon...@googlegroups.com
I made a prepopulated database like this:
On Thursday, March 7, 2013 10:59:24 PM UTC-8, Ben Sebastian wrote:
On Thursday, March 7, 2013 10:59:24 PM UTC-8, Ben Sebastian wrote:
i already have the sqlitedatabse in my hand. i paste directly to assest folder. now how i populate this db to my app using phonegap
On Monday, 21 February 2011 00:40:37 UTC+5:30, Dean-O wrote:
Neerav Shah
Jun 5, 2013, 1:37:41 AM6/5/13
to phon...@googlegroups.com
hey can you give and example how to store a data from list selected. i had created a list which displays a data from the database know what i want to do is to store that data in variable and store it in new table.
Message has been deleted
martin lloyd manlapig
Oct 6, 2013, 10:45:50 PM10/6/13
to phon...@googlegroups.com
how can i run it with out phonegap?
Message has been deleted
Rohmad Dwi Jayanto
Nov 2, 2013, 1:33:26 AM11/2/13
to phon...@googlegroups.com
Thanks, i hope this code to start my program :)
chetan sharma
Nov 28, 2013, 5:03:03 AM11/28/13
to phon...@googlegroups.com
On Wednesday, 17 April 2013 17:54:05 UTC+5:30, Murat Aydogdu wrote:
If you learn it how, could you tell me ?ThanksMurat
8 Mart 2013 Cuma 08:59:24 UTC+2 tarihinde Ben Sebastian yazdı:
i already have the sqlitedatabse in my hand. i paste directly to assest folder. now how i populate this db to my app using phonegap
On Monday, 21 February 2011 00:40:37 UTC+5:30, Dean-O wrote:
code is not working on my ios device. is it for android
chetan sharma
Nov 28, 2013, 5:04:13 AM11/28/13
to phon...@googlegroups.com
not working on my ios device and simulater to.
On Monday, 21 February 2011 00:40:37 UTC+5:30, Dean-O wrote:
AP
Dec 28, 2013, 6:29:18 AM12/28/13
to phon...@googlegroups.com
I am testing this code on BB10 device with cordova 3.1 but its not working.
Should device required SDCard in it.
Thank you.
dan...@drappenheimer.com
Dec 31, 2013, 5:38:49 AM12/31/13
to phon...@googlegroups.com
It is important to note that Web SQL is an abandoned specification - see http://www.w3.org/TR/webdatabase/ - and poorly documented to boot!
Though it can be extremely useful and I touch on the subject in my step-by-step tutorial (which is at https://github.com/drappenheimer/phonegap-by-dissection ).
Thanks for the topic and the info!
Though it can be extremely useful and I touch on the subject in my step-by-step tutorial (which is at https://github.com/drappenheimer/phonegap-by-dissection ).
Thanks for the topic and the info!
On Sunday, February 20, 2011 7:10:37 PM UTC, Dean-O wrote:
Nehul Agrawal
Apr 24, 2014, 3:19:56 PM4/24/14
to phon...@googlegroups.com
Hello, Dan i tried the way you told here, but i modified it a little bit, but there is one problem i am facing when i trying to INSERT data in loop. Its not saving individually just the last post is getting saved and retrieved.
Please check this link to get an idea.
http://stackoverflow.com/questions/23275621/displaying-same-result-again-and-again-in-javascript-phonegap
Please let me know if you have any solution or any recommendation where i am missing.
Thank you!
On Monday, February 21, 2011 12:40:37 AM UTC+5:30, Dean-O wrote:
Vinod Shekha
Jun 2, 2015, 11:28:51 AM6/2/15
to phon...@googlegroups.com
Hi-
I tried this code but the database WebSqlDb is not creating.
so how to create the database
Kerry Fang
Aug 30, 2016, 2:00:08 AM8/30/16
to phonegap
It doesn't work for me. When I click Add records or Refresh nothing gets add to the database.
Steve Husting
Aug 30, 2016, 10:55:56 AM8/30/16
to phonegap
That post is dated 2011. Cordova has come a long way since then.
Reply all
Reply to author
Forward
0 new messages