Retrieving data from SQLite database

In our previous section, we learned how we can create a table in the SQLite database and how we can insert the data into the database table. In this section, we will learn about how we can fetch the data from the database and how it will be visible on the user interface.

We will use the select statement to fetch data from the table and use the unordered list as a list view where our data will be shown:

These are the following steps used to fetch data from the SQLite database:

1) Create index2.html file

We will create a new file index2.html with the same code as present in the index.html. We will make changes later in index2.html file not in index.html.

2) Creating a list view

Now, we will first create a list view using <ul></ul> tags. This list view will show all the result on the user interface. We will give an id to this list view and code in between the <body></body> tags as:

<body>
        <div data-role="page">
        <div data-role="header"><h1> SQLite database example </h1></div>
        <div data-role="main" class="ui-content">
        <label for="name"> Name </label>
        <input type="text" id="name" />
        <label for="email"> Email </label>
        <input type="text" id="email" />
        <button id="btnSave" type="submit"> Save </button>
        <ul id="members" data-role="listview">
        </ul>
        <script type="text/javascript" src="cordova.js"></script>
        </div><!-- main-->
        </div><!-- page -->
</body>

3) Create and execute the select statement

Now, we will go to our saveRecord function and do the modification here. We will add a select statement using the transaction.executeSql function. This function will also have four parameters, i.e., select statement array getSuccess callback function, and getError callback function. We will add the select statement in the following way:

transaction.executeSql("SELECT * FROM employeesList", [], getSuccess, getError);

4) Create getSuccess function

Now, we will create the getSuccess function in between the <script></script> tags. The getSuccess function will have two parameters, i.e., transaction and result. In this function, we will create a row object and create a loop for showing the name and email of each record. We will create a variable for both the name and the email, and then we will add this name and email to our list view in the following way:

        function getSuccess(tx, result)
        {
            var rows = result.rows;
            for(var x=0; x< rows.length; x++)
{
                var name = result.rows[x].Name; <!--Fetching name -->
                var email = result.rows[x].Email; <!?Fetching Email -->
                var out = "<li>" + name + "<br/>" + email + "</li>"; <!?Store data in list-->
                document.getElementById('members').innerHTML += out; <!?Adding list in the list view -->
            } 
            
            $('#members').listview('refresh'); <!--Refreshing list view -->
        }

5) Create getError function

The getError function will be used to show the error on the console if it is raised. The getError function will be created in the following way:

        function getError(e)
        {
            console.log(e);
        }

Complete Code:

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no, width=device-width">
        <title>SQLLite DB App</title>
        <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.min.css" />
        <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
        <script src="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.min.js"></script>
        <script>
        var db;
            
        window.onload=function()// it is a function call.
        {
            document.getElementById('btnSave').addEventListener('click', saveData);
            db = window.openDatabase("employees",  "1.0", "LearnToProgram", 200000);
        }
        
        function saveData(e)
        {
            db.transaction(saveRecord, onSuccess, onError);
        }
            
        function saveRecord(transaction)
        {
            var name= document.getElementById('name').value;
            var email = document.getElementById('email').value;
            transaction.executeSql('CREATE TABLE IF NOT EXISTS employeesList (id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Email TEXT NOT NULL) ');
            
            var sql= "INSERT INTO employeesList (Name,Email) VALUES ('" + name +"', '" + email + "')";
            console.log(sql);
            transaction.executeSql(sql);
            transaction.executeSql("SELECT * FROM employeesList", [], getSuccess, getError);
        }
        function getSuccess(tx, result)
        {
            var rows = result.rows;
            for(var x=0; x< rows.length; x++){
                var name = result.rows[x].Name;
                var email = result.rows[x].Email;
                var out = "<li>" + name + "<br/>" + email + "</li>";
                document.getElementById('members').innerHTML += out;
            }
            
            $('#members').listview('refresh');
        }
        function getError(e)
        {
            console.log(e);
        }
            
        function onSuccess()
        {
            console.log("Record Saved");
        }
            
        function onError(error)
        {
            console.log(error);
        }
        </script>
    </head>
    <body>
        <div data-role="page">
        <div data-role="header"><h1> SQLite Database Example</h1></div>
        <div data-role="main" class="ui-content">
        <label for="name">Name</label>
        <input type="text" id="name" />
        <label for="email">Email</label>
        <input type="text" id="email" />
        <button id="btnSave" type="submit">Save</button>
        <ul id="members" data-role="listview">
        </ul>
        <script type="text/javascript" src="cordova.js"></script>
        </div><!-- main-->
        </div><!-- page -->
    </body>
</html>

Output:

Click Here Download project

Next TopicObtaining device location

← prev next →

For Videos Join Our Youtube Channel: Join Now

Feedback

Send your Feedback to [email protected]

Help Others, Please Share

Learn Latest Tutorials

Splunk

SPSS

Swagger

Transact-SQL

Tumblr

ReactJS

Regex

Reinforcement Learning

R Programming

RxJS

React Native

Python Design Patterns

Python Pillow

Python Turtle

Keras

Preparation

Aptitude

Reasoning

Verbal Ability

Interview Questions

Company Questions

Trending Technologies

Artificial Intelligence

AWS

Selenium

Cloud Computing

Hadoop

ReactJS

Data Science

Angular 7

Blockchain

Git

Machine Learning

DevOps

B.Tech / MCA

DBMS

Data Structures

DAA

Operating System

Computer Network

Compiler Design

Computer Organization

Discrete Mathematics

Ethical Hacking

Computer Graphics

Software Engineering

Web Technology

Cyber Security

Automata

C Programming

C++

Java

.Net

Python

Programs

Control System

Data Mining

Data Warehouse

Javatpoint Services

JavaTpoint offers too many high quality services. Mail us on [email protected], to get more information about given services.

Website Designing
Website Development
Java Development
PHP Development
WordPress
Graphic Designing
Logo
Digital Marketing
On Page and Off Page SEO
PPC
Content Development
Corporate Training
Classroom and Online Training
Data Entry

Training For College Campus

JavaTpoint offers college campus training on Core Java, Advance Java, .Net, Android, Hadoop, PHP, Web Technology and Python. Please mail your requirement at [email protected]
Duration: 1 week to 2 week

^{Like/Subscribe us for latest updates or newsletter}