What is the SQLite?
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine for store our data.
Now why we used in our application?
Every mobile app with minimum data storage needs to use a database. Since the mobile devices do not offer the same memory and processing capacity as a computer, we need to use specially designed systems for those environments so we used SQLite client-server systems.
Step 1 : Just Open command prompt and Create the App
Go to the directory where you maintain your source code, and run a command such as the following:
cordova create hello com.example.hello HelloWorld
Step 2 : Add Platform
All subsequent commands need to be run within the project's directory, or any subdirectories within its scope:
Before you can build the project, you need to specify a set of target platforms. Your ability to run these commands depends on whether your machine supports each SDK, and whether you have already installed each SDK.
cordova platform add android
{
(optional with working multi platforms)
Run this to check your current set of platforms:
cordova platforms ls
Run either of the following synonymous commands to remove a platform:
cordova platform rm android
}
Step 3 : Just Open command prompt and go to your project path and install plugin using CLI
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git
NOTE : Using CLI its very easy to install because we do not need to install any file manually all file will be placed automatically on folder.
Step 4 : Create or Open Database
// Wait for Cordova to load
document.addEventListener("deviceready", onDeviceReady, false);
var db;
function onDeviceReady() {
db = window.sqlitePlugin.openDatabase({name: "DB"});
}
document.addEventListener("deviceready", onDeviceReady, false);
var db;
function onDeviceReady() {
db = window.sqlitePlugin.openDatabase({name: "DB"});
}
Now here is the Example of Each SQL Transation
Create Table
db.transaction(function(tx) {
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
});
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
});
Insert Into Table
db.transaction(function(tx) {
tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
console.log("insertId: " + res.insertId + " -- probably 1");
console.log("rowsAffected: " + res.rowsAffected + " -- should be 1");
});
console.log("insertId: " + res.insertId + " -- probably 1");
console.log("rowsAffected: " + res.rowsAffected + " -- should be 1");
});
});
Execute Other Queries
db.transaction(function(tx) {
tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
console.log("res.rows.length: " + res.rows.length + " -- should be 1");
console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
});
console.log("res.rows.length: " + res.rows.length + " -- should be 1");
console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
});
});
this way you can use database in your app
NOTE : make sure db variable should be global accessible
Example :
index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="format-detection" content="telephone=no" />
<meta name="msapplication-tap-highlight" content="no" />
<!-- WARNING: for iOS 7, remove the width=device-width and height=device-height attributes. See https://issues.apache.org/jira/browse/CB-4323 -->
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width, height=device-height, target-densitydpi=device-dpi" />
<link rel="stylesheet" type="text/css" href="css/index.css" />
<title>Hello World</title>
</head>
<body>
<div class="app">
<h1>Apache Cordova</h1>
<div id="deviceready" class="blink">
<p class="event listening">Connecting to Device</p>
<p class="event received">Device is Ready</p>
</div>
</div>
<script type="text/javascript" src="cordova.js"></script>
<script type="text/javascript" src="js/index.js"></script>
</body>
</html>
index.js
// Wait for Cordova to load
document.addEventListener("deviceready", onDeviceReady, false);
// Cordova is ready
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
// var db = window.openDatabase({name: "my.db"});
alert("conncting");
db.transaction(function(tx) {
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
alert("inserting");
db.transaction(function(tx) {
tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
alert("record"+res.rows.item(0).cnt);
});
});
}, function(e) {
alert(e.message);
});
});
}
Than You Sir..!
ReplyDeleteI have been looking for such straight solutions .. thank you very much :)
Thanks to your interest... :)
ReplyDeleteHello!
ReplyDeleteFirst, let me thank you for this write-up. I have been trying to learn how to use this plugin, and unfortunately documentation for it is rather sparse. This is greatly appreciated. If I may ask one question though, how would I work with an already existing sqlite database? For example, let's say I have a database that can be found in this location:
/www/db/example.db
How would I access this database versus creating a new one? And I apologize if I double posted, but the page refreshed when I first tried to submit this comment.
Thanks again!
Derik
Thank you for your interest...:)
ReplyDeletePlease put your db file in www directory not in /www/db/example.db like /www/example.db and follow the my next write-up steps.
http://santoshshinde2012.blogspot.in/2015/03/open-existing-sqlite-file-in-phonegap.html
Thank you!
I greatly appreciate you breaking down how to use this plugin. As I mentioned previously, I had been trying to teach myself how to use it but to no avail, so this information is most beneficial. Thank you very much!
ReplyDeleteThank you ..:)
ReplyDeleteHow to use database file when we create using sqlite manager and want to use in phonegap.
ReplyDeletefor example : i create a database using sqlite manager and then i want to use that test.sqlite database file in phonegap, how?
Please check this one http://santoshshinde2012.blogspot.in/2015/03/open-existing-sqlite-file-in-phonegap.html
ReplyDelete