Updating records using CUD with jQuery and JDE project
TweetRemember, in previous article we mentioned that upon deploying CUD, the embed code page displays nothing but a link code for generated scripts.
In this article we’ll give you basic instructions on how to use CUD to update records with plain HTML and jQuery.
Please note that CUD does not produce script for reading records so we also built one JDE project for this example. To learn more about JDE, please read this article.Compared to code in article mentioned above we have slightly changed function loadJSON() and added new function EditRec() that performs the update. Please note that this CUD does not use authentication for updating records, which is NOT recommended. In this exceptional case we can do it because all the harm that can be done by malicious visitor is to mess up number of votes for any particular book (and that makes no difference for this demo).
On the other hand, although we do not use script for adding records, we did protect it with authentication file, so even if visitor knows the URL of the script, he/she will not be able to add anything to our database without proper credentials.
We will provide more info about password protection later in this article.
Now, let’s see the code for functions loadJSON() and EditRec():
function loadJSON(){
var qs=$("#qs").val();
$.getJSON('https://thyme.dbbee.com/u/SGCEQ5WZFZ/BiblioProJSONjsonapi.wbsp?dbbee_sr='+startrec+'&dbbee_mr='+maxrec+'&dbbee_qs='+qs, function (data) {
var tHTML="The search returned "+data.BooksCount + " records<br/>";
rc=parseInt(data.BooksCount);
for(var i in data.Books){
tHTML +="<div class='content-wrap'>" + data.Books[i].Image+ "<h1>" +
data.Books[i].Title +
"</h1><h2>" +
data.Books[i].Author +
"</h2><h2>" +
data.Books[i].Publisher +
"</h2><h3>" +
data.Books[i]['Year Published'] +
"</h3><span class='green-color'>Votes </span><span class='green-color' id='votes_"+data.Books[i].dbBeeUniqueIdentifier+"'>"+data.Books[i].Votes+"</span>"+
"<div class='clear'></div><div id='button-wrap'><input type='button' value='+' onclick='EditRec(\""+data.Books[i].dbBeeUniqueIdentifier+"\",1);'/>"+
"<input type='button' value='-' onclick='EditRec(\""+data.Books[i].dbBeeUniqueIdentifier+"\",-1);'/></div>"+
"</div>";
}
$("#demo").html(tHTML);
MakeNavigation();
});
}
function EditRec(recId,VoteValue){
var newVoteVal=VoteValue+parseInt($("#votes_"+recId).html());
$.ajax({
method: "POST",
url: "https://thyme.dbbee.com/u/SGCEQ5WZFZ/BiblioProCUDcudupd.wbsp",
data: { dbbee_fld_dbBeeUniqueIdentifier: recId, dbbee_fld_Votes: newVoteVal},
cache: false
})
.done(function( retVal ) {
if(retVal=="OK")$("#votes_"+recId).html(newVoteVal);
});
}
The code marked with red color contains the important change compared to loadJSON() function described in above mentioned article.
First (and most important) we read the field dbBeeUniqueIdentifier to identify each book. We had to include this field in our JDE project, and we defined it as UID in our CUD project. Next, we added two buttons (for up-vote and down-vote) and both call function EditRec, passing the value of dbBeeUniqueIdentifier and number of votes (1 for up-vote, -1 for down-vote). And finally, we added a SPAN element to hold number of votes for each book (each SPAN has unique ID based on dbBeeUniqueIdentifier field).
Function EditRec does all the work. Let’s analyze its code.
In first line we calculate the number of votes to save to database, and then use single jQuery $ajax function passing the URL of dbBee CUD update script as url parameter.
As data parameter we send a unique ID for each book as dbBee field dbbee_fld_dbBeeUniqueIdentifier and total number of votes as dbBee field dbbee_fld_Votes.
In success callback (.done) we check if dbBee CUD script returned value "OK” (without quotation marks – value we defined in CUD wizard) and if so, we update the particular SPAN element that holds number of votes for particular book.
So once the host page is ready, jQuery function $. getJSON will fetch JSON from specified URL. When document is loaded our custom function will process the key "BookcCount”, loop through array "Books”, create HTML code using JSON data stored in each element and append HTML code to div element with id "demo”. When visitor clicks a button for up-vote or down-vote, function EditRec will use $ajax to send data to CUD update script and to save new value to the database.
See live demo here
As we already mentioned this is the rare case when you do not have to use password protection for updating records. We strongly recommend that you always protect your CUD projects with authentication and to prompt your visitors for user name and password. To initiate prompt, add withCredentials: true to your $ajax function (marked with red color in code below):
function EditRec(recId,VoteValue){
var newVoteVal=VoteValue+parseInt($("#votes_"+recId).html());
$.ajax({
method: "POST",
xhrFields: {
withCredentials: true
},
url: "https://thyme.dbbee.com/u/SGCEQ5WZFZ/BiblioProCUDcudupd.wbsp",
data: { dbbee_fld_dbBeeUniqueIdentifier: recId, dbbee_fld_Votes: newVoteVal},
cache: false
})
.done(function( retVal ) {
if(retVal=="OK")$("#votes_"+recId).html(newVoteVal);
});
}
Sometimes you will need to do update based on actions of anonymous visitors (like in our demo), but you still should not use CUD script without authentication. And of course, we do not recommend trying to hide or obfuscate your credentials in your front-end code - any visitor with average experience in JavaScript will find it in no time. However, you can hide your credentials in server-side script that can accept values, and pass them to CUD script in a safe way. Here’s the example:
function EditRec(recId,VoteValue){
var newVoteVal=VoteValue+parseInt($("#votes_"+recId).html());
$.ajax({
method: "POST",
url: "safevote.php",
data: { id: recId, votes: newVoteVal},
cache: false
})
.done(function( retVal ) {
if(retVal=="OK")$("#votes_"+recId).html(newVoteVal);
});
}
And here's the code for safevote.php
<?php
$recid=$_POST["id"];
$voteval=$_POST["votes"];
$dbBee = file_get_contents('https://thyme.dbbee.com/u/SGCEQ5WZFZ/BiblioProCUDcudupd.wbsp?dbbee_fld_dbBeeUniqueIdentifier=' . $recid . '&dbbee_fld_Votes=' . $voteval . '&dbbee_user=dummyusername&dbbee_password=dummypassword');
echo $dbBee;
?>
In the example above we changed the CUD update script URL with local script written in PHP. That script has no other purpose but to hide user name and password required by CUD update script. Few additional lines can make sure there’s no ampersand sign in posted values (so visitor cannot inject code for some other dbBee field). Also it is possible to expand this PHP script with code to validate posted values, build digest response (in case when CUD uses authentication file with digest authentication method), etc.
Although we used PHP for this sample script, same can be achieved with Ruby, Python, ASP.NET, Node.js, WhizBase, Perl, JSP, etc.
As you can see, it is very simple and easy to use CUD to modify the data stored in your Excel workbook or Access database. Combined with JDE or XDE projects, CUD can easily be used to build any kind of front-end application without single line of back-end code. Even when back-end coding makes sense, it is far simpler than regular CRUD programming.
