CODEWITHSUNDEEP
androidsqlitecordovacrud
user818671
user818671

Reputation: 399

Check whether a record exists or not, if it exists update it else create new one in PhoneGap?

I have written a code that inserts values of a record into SQLite database and displays it.

Now what I want to do is to check whether the record I am inserting is present or not?

If the record is present then update it else create a new one.

How can I do it in PhoneGap? Below is the code I have tried:

<!DOCTYPE html>
<html>
    <head>
        <title>Contact Example</title>

        <script type="text/javascript" charset="utf-8" src="cordova-1.5.0.js"></script>
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
        <script type="text/javascript" charset="utf-8">

            //add listener when device ready
            document.addEventListener("deviceready", onDeviceReady, false);
            var db = window.openDatabase("Dummy_DB", "1.0", "Just a Dummy DB", 200000); //will create database Dummy_DB or open it

            //function will be called when device ready
            function insertdata(){
                //alert("hi");
                var name = $('#name').val();
                var passwd = $('#pwd').val();

                if(name == "")
                {
                    alert("Please enter name");
                }

                if(passwd == "")
                {
                    alert("Please enter Password");
                }
                if(name != "" && passwd != ""){
                db.transaction(populateDB, errorCB, successCB);
                }
            }

            function onDeviceReady(){
                document.getElementById("submit").addEventListener("click", insertdata, false);

            }

            //create table and insert some record
            function populateDB(tx) {
                tx.executeSql('CREATE TABLE IF NOT EXISTS Login (id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Password TEXT NOT NULL)');
                //tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Alexandre Pato", "AC Milan")');
               // tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Van Persie", "Arsenal")');
                //tx.executeSql('DELETE FROM SoccerPlayer');
                var name = $('#name').val();
                var passwd = $('#pwd').val();



                tx.executeSql('INSERT INTO Login (Name,Password) VALUES ("'+name+'","'+ passwd +'")');
                $('#name').val('');
                $('#pwd').val('');

            }

            //function will be called when an error occurred
            function errorCB(err) {
               // alert("Error processing SQL: "+err.code);
            }

            //function will be called when process succeed
            function successCB() {
                alert("success!");
                db.transaction(queryDB,errorCB);
            }

            //select all from SoccerPlayer
            function queryDB(tx){
                tx.executeSql('SELECT * FROM Login',[],querySuccess,errorCB);
            }


            function querySuccess(tx,result){
                $('#SoccerPlayerList').empty();
                $.each(result.rows,function(index){
                       var row = result.rows.item(index);
                       $('#SoccerPlayerList').append('<li><a href="#"><h3 class="ui-li-heading">'+row['Name']+'</h3></a></li>');
                       });

                $('#SoccerPlayerList').listview();
            }


            </script>
    </head>
    <body>
        <div data-role="page">
                  <div data-role="header" data-position="fixed" data-theme="b">
                    <h1>Login</h1>
                  </div>
            <div>Name: <input type="text" id="name"></div>
            <div>Password: <input type="password" id="pwd"></div>
            <input type="submit" id="submit">
              <div data-role="content">
                     <ul id="SoccerPlayerList">
                            </ul>
                      </div>
                </div>
    </body>
</html>

Upvotes: 4

Views: 2609

Answers (1)

CL.
CL.

Reputation: 180070

In general, you have to search for the record:

SELECT 1 FROM Login WHERE Name = '...'

and when you get the result back, check whether you got something or not, and do an INSERT or UPDATE accordingly:

INSERT INTO Login(Name, Password) VALUES('...', '...')
-- or
UPDATE Login SET Password = '...' WHERE Name = '...'

However, SQLite has an extension that allows you to replace a record when inserting a record would create a conflict otherwise. This requires that you have a UNIQUE constraint so that the conflict can be detected:

CREATE TABLE Login(..., Name TEXT NOT NULL UNIQUE, ...)

Then, you can use the following command:

INSERT OR REPLACE INTO Login(Name, Password) VALUES('...', '...')

Please note that you should never try to put strings directly into the SQL command to avoid formatting problems (what if the password contains "?) and SQL injection attacks – what happens when Bobby Tables tries to use your app? Instead, use parameters; something like this:

db.transaction(function(tx) {
    var name = ...;
    var passwd = ...;
    tx.executeSql('INSERT OR REPLACE INTO Login(Name, Password) VALUES(?, ?)',
                  [name, passwd]);
});

Upvotes: 3

Related Questions