First of all thanks for viewing my thread. also i have see and tries many SO post on this issue and found that most of these are suggesting changes like utf, substring etc.But my problem is not my app code. Following is my problem statement:
I have an under developing android app which shows tourist spots based on search and suggestion shown during search made by user. When user start typing search Keyword e.g. "Princep" then he sees suggestions which includes 'Princep Ghat, Kolkata". How search workds in two ways. when user selects suggestion then php page is being called with query with tourist spot id (LinkId). He gets only one result of above said tourist spot.
if User do not select suggestion and search instead of keyword "Princep" and press search button then he should see many result where this keyword is found.
For background work i have two php pages. One for each search (Single search with LinkId (bigint) and Other is with Contains, of sql server. I am using PDO driver for connecting to MS SQL SERver.
PROBLEM I AM FACING: when i search with LinkId( query with where clause) then my app (Java) code works well and there is no issue of string to JSON conversion. but when i receive same result from Contains query working on metadata column then i face issue. FYI, my metadata column is varchar and have name, city name etc as keywords.
i have seen that variable shows returned result when i debug application and that is same for both searches.
I am assuming that my java code is fine as it is working for case and string is converting into Json. but when same record is getting returned using contains query then it shows error in converting to Json.
Following is my Java code for reference.
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setReadTimeout(50000);
conn.setConnectTimeout(15000);
conn.setRequestMethod("POST");
conn.setDoInput(true);
conn.setDoOutput(true);
OutputStream os = conn.getOutputStream();
BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(os, "UTF-8"));
writer.write(getQuery(params));
writer.flush();
writer.close();
os.close();
conn.connect();
BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));
String line = reader.readLine();
JSONObject jsonResponse = new JSONObject(line); //Problem comes Here
xLab.clear();
JSONArray jsonArray = jsonResponse.getJSONArray("posts");
PHP file which Works
<?php
/*
Our "config.inc.php" file connects to database every time we include or require
it within a php script. Since we want this script to add a new user to our db,
we will be talking with our database, and therefore,
let's require the connection to happen:
*/
require("config.inc.php");
if (!empty($_POST)) {
$query ="select TOP 50 LinkId,LinkHeading,AttractionName,iconpath, MetaData,countryName,MainCategoryNAme,MainSubCategoryName,latitude,longitude,MainSubCategoryId,ROW_NUMBER() OVER (ORDER BY LinkId) AS RowNum from dbo.trlinks_view where LinkId =:LinkId";
$query_params = array(
':LinkId' => $_POST['LinkId']
);
//execute query
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {
$response["success"] = 0;
$response["message"] = "Database Error!";
die(json_encode($response));
}
// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows) {
$response["success"] = 1;
$response["message"] = "Post Available!";
$response["posts"] = array();
foreach ($rows as $row) {
$post = array();
$post["LinkId"] = $row["LinkId"];
$post["LinkHeading"] = $row["LinkHeading"];
$post["IconPath"]=$row["iconpath"];
$post["MainSubCategoryName"]=$row["MainSubCategoryName"];
$post["latitude"]=$row["latitude"];
$post["longitude"]=$row["longitude"];
$post["MainSubCategoryId"]=$row["MainSubCategoryId"];
//update our repsonse JSON data
array_push($response["posts"], $post);
}
// echoing JSON response
echo json_encode($response);
} else {
$response["success"] = 0;
$response["message"] = "No Post Available!";
die(json_encode($response));
}
}else{
?>
<h1>Add Comment</h1>
<form action="touristspot_linkid.php" method="post">
Username:<br />
<input type="text" name="LinkId" placeholder="username" />
<br /><br />
<input type="submit" value="Get " />
</form>
<?php
}
?>
PHP file which do not work
<?php
require("config.inc.php");
if (!empty($_POST)) {
$query ="select TOP 50 LinkId,LinkHeading,AttractionName,iconpath, MetaData,countryName,MainCategoryNAme,MainSubCategoryName,latitude,longitude,MainSubCategoryId,ROW_NUMBER() OVER (ORDER BY LinkId) AS RowNum from dbo.trlinks_view where contains(*,:MetaData) and latitude is not null and longitude is not null";
$query_params = array(
':MetaData' => $_POST['MetaData']
);
//execute query
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {
$response["success"] = 0;
$response["message"] = "Database Error!";
die(json_encode($response));
}
// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows) {
$response["success"] = 1;
$response["message"] = "Post Available!";
$response["posts"] = array();
foreach ($rows as $row) {
$post = array();
$post["LinkId"] = $row["LinkId"];
$post["LinkHeading"] = $row["LinkHeading"];
$post["IconPath"]=$row["iconpath"];
$post["MainSubCategoryName"]=$row["MainSubCategoryName"];
$post["latitude"]=$row["latitude"];
$post["longitude"]=$row["longitude"];
$post["MainSubCategoryId"]=$row["MainSubCategoryId"];
//update our repsonse JSON data
array_push($response["posts"], $post);
}
// echoing JSON response
echo json_encode($response);
} else {
$response["success"] = 0;
$response["message"] = "No Post Available!";
die(json_encode($response));
}
}else{
?>
<h1>Add Comment</h1>
<form action="touristspot_metadata.php" method="post">
Username:<br />
<input type="text" name="MetaData" placeholder="username" />
<br /><br />
<input type="submit" value="Get " />
</form>
<?php
}
?>
Value of variable 'line' after readline statement(using logcat) after both cases
04-02 07:06:52.260 7119-8577/com.Tourism.CityTours I/lineMessage: {"success":1,"message":"Post Available!","posts":[{"LinkId":"61331","LinkHeading":"Princep Ghat, Kolkata","IconPath":"http:\/\/localhost.com:80\/Images\/default_image.jpg","MainSubCategoryName":"Kolkata","latitude":"22.555327295826448","longitude":"88.33177328109741","MainSubCategoryId":"1092"}]}
04-02 07:11:12.806 7119-8412/com.Tourism.CityTours I/lineMessage: {"success":1,"message":"Post Available!","posts":[{"LinkId":"61331","LinkHeading":"Princep Ghat, Kolkata","IconPath":"http:\/\/localhost.com:80\/Images\/default_image.jpg","MainSubCategoryName":"Kolkata","latitude":"22.555327295826448","longitude":"88.33177328109741","MainSubCategoryId":"1092"}]}
