Issue parsing complex JSON retrieved from PHP/MySQL

[daruthe]

The scenario:

I download a JSON list of movies from a PHP/MYSQL, and populate a ListView with the titles and ids of each. This happens no problems. The program then allows the user to click on a movie and retrieve additional details.

A new activity is loaded from the ListView_ItemClick event and an int process global variable in the new activity is set to the id of the movie to load details for. The HTTP client then rushes off to to the details PHP with the specified movie id and grabs the JSON for it (example url = http://www.daruthe.com/android/ck4a/movielist_details_android.php?id=366)

Sub Process_Globals
  'These global variables will be declared once when the application starts.
  'These variables can be accessed from all modules.
  Dim DetailsDownloader As HttpClient
  Dim MovieDetailID As Int
  Dim MovieDetailsTaskID As Int
  
  MovieDetailsTaskID = 1
End Sub

'*****************************************

Sub Activity_Create(FirstTime As Boolean)
  Activity.LoadLayout("layoutMovieDetails")
  
  If FirstTime Then
    DetailsDownloader.Initialize("DetailsDownloader")
  End If
  
  LoadMovie(MovieDetailID)
End Sub

'*****************************************

Sub LoadMovie(AMovieID As Int)
  ProgressDialogShow("Fetching movie details...")
  Dim req As HttpRequest
  Dim tmpQuery As String
  
  If Main.URLBase.StartsWith("http://") Then
    tmpQuery = Main.URLBase & Main.WRMovieDetails & AMovieID
  Else
    tmpQuery = "http://" & Main.URLBase & Main.WRMovieDetails & AMovieID
  End If
  
  Log("Retrieving " & tmpQuery)
  
  req.InitializeGet(tmpQuery)
  DetailsDownloader.Execute(req, MovieDetailsTaskID)
End Sub

'*****************************************

Sub DetailsDownloader_ResponseSuccess (Response As HttpResponse, TaskId As Int)
  Dim res As String
  res = Response.GetString("UTF8")
  
  Log("Response from server for movie details: " & res)
  Dim parser, tmpDetailsParser, tmpCopiesParser As JSONParser
  parser.Initialize(res)
  Select TaskId
    Case MovieDetailsTaskID
     Dim tmpTopLoader As Map
     tmpTopLoader.Initialize     
     tmpTopLoader = parser.NextObject  
   
'     Dim tmpMovieDetails As Map
'      tmpMovieDetails.Initialize
          
     'tmpMovieDetails = tmpTopLoader.Get("Header")
     
     If tmpTopLoader.ContainsKey("Header") Then
      Log("header: "&tmpTopLoader.Get("Header"))
      'tmpDetailsParser.Initialize(tmpTopLoader.Get("Header"))
      Dim tmpMovieDetails As List
       tmpMovieDetails.Initialize
      tmpMovieDetails = tmpTopLoader.Get("Header")
       'tmpMovieDetails = tmpDetailsParser.NextObject
     End If

      'Dim tmpD As List
     
     'tmpD = tmpMovieDetails.GetValueAt(0)
          
     'If tmpD.IsInitialized Then
      ' lblOwner.Text = tmpD.Get("owner")
   '  End If
                  
     If tmpMovieDetails.IsInitialized Then
       lblOwner.Text = tmpMovieDetails.Get("owner")
     End If
     
     
      'Dim tmpCopiesList As List
      'tmpCopiesList = tmpTopLoader.Get(1)

      'For i = 0 To tmpCopiesList.Size - 1
       ' Dim m As Map
       ' m = tmpCopiesList.Get(i)
        'Dim tmpFormat, tmpOnLoan, tmpCost As String
        'tmpFormat = m.Get("format")
      'tmpOnLoan = m.Get("onloan")
      'tmpCost = m.Get("cost")
      'Next      
  End Select
  Response.Release
  ProgressDialogHide
End Sub

The JSON that is returned by the PHP/MYSQL page will look something along the lines of the following. The copies will have between 0 and X items

{
    "Header": [
        {
            "id": "366",
            "title": "10,000 Bc",
            "description": null,
            "owner": "Me"
        }
    ],
    "Copies": [
        {
            "format": "Blu-Ray",
            "onloan": "No",
            "cost": "0.00",
            "parentmovieid": "366"
        },
        {
            "format": "DVD",
            "onloan": "No",
            "cost": "0.00",
            "parentmovieid": "366"
        }
    ]
}

I have validated that JSON string against the following 3 sites:
JSONLint - The JSON Validator.
JSON Format - your online JSON Formatter
Collapsible JSON Formatter - view your json code in colors

The PHP used to generate the JSON is:

$j_details = json_encode($rows);
$j_copies = json_encode($copiesrows);
$j_combined = '{"Header":' . $j_details .',"Copies":' . $j_copies .'}';

header('Content-type: application/json; charset=UTF-8');
print $j_combined;

The log from when the details activity is loaded and onwards:

** Activity (main) Pause, UserClosed = false **
** Activity (actmoviedetails) Create, isFirst = true **

Retrieving http://www.daruthe.com/android/ck4a/movielist_details_android.php?id=366

** Activity (actmoviedetails) Resume **

Response from server for movie details: {"Header":[{"id":"366","title":"10,000 Bc","description":null,"owner":"Me"}],"Copies":[{"format":"Blu-Ray","onloan":"No","cost":"0.00","parentmovieid":"366"},{"format":"DVD","onloan":"No","cost":"0.00","parentmovieid":"366"}]}

header: [{owner=Me, id=366, description=null, title=10,000 Bc}]

actmoviedetails_detailsdownloader_responsesuccess (java line: 293)

java.lang.NumberFormatException: owner
   at org.apache.harmony.luni.util.FloatingPointParser.initialParse(FloatingPointParser.java:130)
   at org.apache.harmony.luni.util.FloatingPointParser.parseDouble(FloatingPointParser.java:281)
   at java.lang.Double.parseDouble(Double.java:287)
   at net.daruthe.dev.CollectionKeeperForAndroid.actmoviedetails._detailsdownloader_responsesuccess(actmoviedetails.java:293)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:521)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:145)
   at anywheresoftware.b4a.BA$3.run(BA.java:279)
   at android.os.Handler.handleCallback(Handler.java:587)
   at android.os.Handler.dispatchMessage(Handler.java:92)
   at android.os.Looper.loop(Looper.java:123)
   at android.app.ActivityThread.main(ActivityThread.java:4627)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:521)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
   at dalvik.system.NativeStart.main(Native Method)
java.lang.NumberFormatException: owner

I have read both the JSON and retrieving MYSQL data tutorials written by Erel as per posts:
http://www.b4x.com/forum/basic4android-getting-started-tutorials/6923-android-json-tutorial.html
http://www.b4x.com/forum/basic4andr...-connect-android-mysql-database-tutorial.html

What I am aiming for is to have the Headers as a Map and the Copies as a List of Maps

Any assistance would be greatly appreciated.

Version info:
Basic4Android = 1.92
JSON lib = 1.00
HTTP lib = 1.20
Core = 1.91

 

[Erel]

Run in Debug mode to see the exact error line.

This line is wrong:

 lblOwner.Text = tmpMovieDetails.Get("owner")

tmpMovieDetails is a List not a Map. List.Get expect an numeric index value.

 

[daruthe]

Run in Debug mode to see the exact error line.

This line is wrong:

 lblOwner.Text = tmpMovieDetails.Get("owner")

tmpMovieDetails is a List not a Map. List.Get expect an numeric index value.

i tried changing the ResponseSuccess sub to this

Sub DetailsDownloader_ResponseSuccess (Response As HttpResponse, TaskId As Int)
  Dim res As String
  res = Response.GetString("UTF8")
  
  Log("Response from server for movie details: " & res)
  Dim parser, tmpDetailsParser, tmpCopiesParser As JSONParser
  parser.Initialize(res)
  Select TaskId
    Case MovieDetailsTaskID
     Dim tmpTopLoader As Map
     tmpTopLoader.Initialize     
     tmpTopLoader = parser.NextObject  
        
     If tmpTopLoader.ContainsKey("Header") Then
      Log("header: "&tmpTopLoader.Get("Header"))
      Dim tmpMovieDetails As List
       tmpMovieDetails.Initialize
      tmpMovieDetails = tmpTopLoader.Get("Header")
      
       If tmpMovieDetails.IsInitialized Then
        Dim tmpJSONCHunk As String
             tmpJSONCHunk = tmpMovieDetails.Get(0)
        tmpDetailsParser.Initialize(tmpJSONCHunk)
        
'        Dim tmpMap As Map
'        tmpMap = tmpDetailsParser.NextObject
'        lblOwner.Text = tmpMap.Get("owner")

                  Dim tmpList As List
        tmpList = tmpDetailsParser.NextArray
        lblOwner.Text = tmpList.Get(0)
       End If      
     End If   
  End Select
  Response.Release
  ProgressDialogHide
End Sub

Still no luck - regardless of whether i try and use a list or map it fails to parse the JSON chunk because the double quotes are being stripped out on this line:

tmpMovieDetails = tmpTopLoader.Get("Header")

this is what is shown as the value of the call to tmpTopLoader.Get("Header"):

header: [{owner=Me, id=366, description=None, title=10,000 Bc}]


but this is what is receive from the PHP/MYSQL


Response from server for movie details: {"Header":[{"id":"366","title":"10,000 Bc","description":"None","owner":"Me"}],"Copies":[{"format":"Blu-Ray","onloan":"No","cost":"0.00","parentmovieid":"366"},{"format":"DVD","onloan":"No","cost":"0.00","parentmovieid":"366"}]}


if i try and feed the value of tmpTopLoader.Get("Header") into another JSON parser, i get the error shown in the attached jpeg


i don't understand what is happening or what i need to do to get this working in the way i want it. this is the first time ive ever tried to work with JSON

 

[Erel]

Here:

Dim p As JSONParser
p.Initialize(File.ReadString(File.DirAssets, "1.txt"))
Dim m As Map
m = p.NextObject
Dim headerW As List
headerW = m.Get("Header")
Dim header As Map
header = headerW.Get(0)
Log(header)
Dim copies As List
copies = m.Get("Copies")
For i = 0 To copies.Size - 1
   Dim copy As Map
   copy = copies.Get(i)
   Log(copy)
Next

(MyMap) {owner=Me, id=366, description=None, title=10,000 Bc}
(MyMap) {cost=0.00, parentmovieid= 366, onloan=No, format=Blu-Ray}
(MyMap) {cost=0.00, parentmovieid=366, onloan=No, format=DVD}