All geek questions in one place

Check if the entry exists or not, if it exists, update it, otherwise create a new one in PhoneGap?

I wrote code that inserts the record values ​​into the SQLite database and displays it.

Now, what I want to do is check if the record I'm inserting is present or not?

If the entry is present, update it, create a new one.

How can I do this in PhoneGap? Below is the code I 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>
+4
source share
1 answer

In general, you need to search for a record:

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

and when you return the result, check if you have something or not and do INSERT or UPDATE respectively:

 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 if inserting a record will conflict otherwise. This requires that you have a UNIQUE constraint so that a 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('...', '...')

Note that you should never try to put strings directly in the SQL command to avoid formatting problems (what if the password contains " ?) And SQL injection - what happens when Bobby Tables tries to use your application? Use parameters instead; something like that:

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

Source: https://habr.com/ru/post/1440742/

More articles:


All Articles