VoiceGap

Filling the space between You and Your Apps

How to Store Objects in a Remote Database

In this post we are going to look at storing VoiceGap,PhoneGap, or WepApp application data in a remote database. We will borrow some ideas from REST and NOSQL. First we’ll start with a simple WebApp with no mobile components starting with the old Expendable Part 2 code base. Then discover how this can be added to our VoiceGap Mobile code. The back end for this tutorial is PHP; but could be almost anything. In a future post, we will add local SQL Lite tables to the device; allowing us to store information across devices, participants, and applications.

Looking at our good old “work” function.

function work() {

		var s = $.trim($("#searchField").val());
		alert("going to search for "+s);

		$.getJSON("http://api.search.live.net/json.aspx?Appid="+appid+"&query="+escape(s)+"&sources=image&image.count=5", {}, function(res) {

			var results = res.SearchResponse.Image.Results;
			if(results.length == 0) {
				$("#tab-3").html("No results!");
				return;
			}
			var s = "<hr/><h3>Search Results</h3>";
for(var i=0; i<results.length; i++) {

     s+= "<p><img src='"+results[i].Thumbnail.Url+"'><br/>";
     s+= "<a href='"+results[i].Url+"'>";
     s+=  results[i].DisplayUrl+"</a></p>";
			}
			$("#tab-3").html(s);
			$("#bar-3").click();
            $("#foot").listview('refresh');

		});

}

at first we’re just going to change the click action, so that it stores the picture in our remote database. And test out how that works.

function work() {

		var s = $.trim($("#searchField").val());
		alert("going to search for "+s);

		$.getJSON("http://api.search.live.net/json.aspx?Appid="+appid+"&query="+escape(s)+"&sources=image&image.count=5", {}, function(res) {

			var results = res.SearchResponse.Image.Results;
			if(results.length == 0) {
				$("#tab-3").html("No results!");
				return;
			}
			var s = "<hr/><h3>Search Results</h3>";
for(var i=0; i<results.length; i++) {

     s+= "<p><img src='"+results[i].Thumbnail.Url+"'><br/>";
     s+= "<a href=\"javascript:remote_save('"+escape(JSON.stringify(results[i]))+"');\">";
     s+=  results[i].DisplayUrl+"</a></p>";

			}
			$("#tab-3").html(s);
			$("#bar-3").click();
            $("#foot").listview('refresh');

		});

}

function remote_save(u) {
$.getJSON("http://www.cpe.net/stocks/puttuple.php?rel=mylist&appid=dev001|a1&key=test"+22+"&data="+u,{},{});
}

The Click action below will save the image for later retrieval.

javascript:remote_save('"+escape(JSON.stringify(results[i]))+"');

Someday we’re going to create a special search which we will name “history.” If Capt. Expendable hears “history” it will populate the images from the Remote Database instead of from an internet search.

To make all these things work, we have build some very specific ajax services. Namely:

  • gettuple.php
  • <?php
    include 'config.php';
    
    $sql = "select e.relation, e.user_key, e.user_data " . 
    		"from tuples e " .
    		"where e.app_id=:appid " .
                " and e.relation=:relation " .
                " and e.user_key=:key " .
                " order by e.relation, e.user_key";
    
    try {
    	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
    	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	$stmt = $dbh->prepare($sql);  
    	$stmt->bindParam("relation", $_GET[rel]);
    	$stmt->bindParam("appid", $_GET[appid]);
    	$stmt->bindParam("key", $_GET[key]);
    	$stmt->execute();
    	$employee = $stmt->fetchObject();  
    	$dbh = null;
    	echo '{"data":'. json_encode($employee) .'}'; 
    } catch(PDOException $e) {
    	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
    
    ?>
    
  • gettuples.php
  • <?php
    include 'config.php';
    
    $sql = "select e.relation, e.user_key, e.user_data " . 
    		"from tuples e " .
    		"where e.app_id=:appid " .
                " and e.relation=:relation " .
                " order by e.relation, e.user_key";
    
    try {
    	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
    	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	$stmt = $dbh->prepare($sql);  
    	$stmt->bindParam("relation", $_GET[rel]);
    	$stmt->bindParam("appid", $_GET[appid]);
    	$stmt->execute();
    	$employee = $stmt->fetchAll(PDO::FETCH_OBJ);  
    	$dbh = null;
    	echo '{"data":'. json_encode($employee) .'}'; 
    } catch(PDOException $e) {
    	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
    
    ?>
    
  • puttuple.php
  • <?php
    include 'config.php';
    
    $rel = $_GET[rel];
    $appid = $_GET[appid];
    $key = $_GET[key];
    $data = $_GET[data];
    
    $sql = "INSERT INTO cpenet_stocks.tuples (relation, app_id, user_key, user_data) " . 
    		"VALUES ('". $rel ."','". $appid ."','". $key ."','". $data ."') " .
                    "ON DUPLICATE KEY UPDATE user_data='". $data ."'";
    
    	
    try {
    //        echo $sql;
    	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);	
    	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	$stmt = $dbh->prepare($sql);  
    	//$stmt->bindParam(":rel", $_GET[rel]);
    	//$stmt->bindParam(":appid", $_GET[appid]);
    	//$stmt->bindParam(":key", $_GET[key]);
    	//$stmt->bindParam(":data", $_GET[data]);
    	$stmt->execute();
         //   echo $sql;
    //	$employee = $stmt->fetchObject();  
    //	$dbh = null;
    	echo '{"key":'. json_encode($key) .'}'; 
    } catch(PDOException $e) {
    	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
    
    ?>
    

The parameters that are available are

  • rel (for relation)
  • key (for the relation/table key)
  • appid (app id, made up of DevId|AppId|UserId)
  • data (Json formatted string)
  • token (security token to access database)

<sample>

function remote_save(u) {
$.getJSON("http://www.cpe.net/stocks/puttuple.php?rel=mylist&appid=dev001|a1&key=test"+22+"&data="+u,{},{});
}

Returns

{"data":{"relation":"mylist","user_key":"test","user_data":""}}

<output>

Here is the full code listing. Keep in mind this is the WebApp version and not the Mobile code, but you could have made the changes to either. The PHP code shown here is obviously for demonstration purposes and not production code. In the next Article we will look at MongoDB as a production NOSQL remote database.

Comments are currently closed.