Android Question How send the BLOB field of remote MySQL Table to Android device?

Walter Scafati

Member
Licensed User
Hi, I have a question about the connection to remote DB mySQL: I realized a App able to read and write a table on remote mySQL DB located on my server.
In the table there is also a BLOB field for the image. I can send the Data fields completed of blob field to DB on the server converting the field to string data

B4X:
    Buffer2 = cursoreTmp.GetBlob("image")
    If Buffer2 <> Null Then encoded = su.EncodeBase64(Buffer2) 'data is a bytes array
    ExecuteRemoteQuery2(ADati, encoded)
B4X:
   Sub ExecuteRemoteQuery2(par() As String, Buffer2 As String)
    Dim job As HttpJob
    Dim myInd As Int

    job.Initialize("q000", Me)
    job.PostString("http://" & ip & "/" & page & ".php?par1=" & par(1) & "&par2=4&par3=" & par(2) & "&par4=" & par(2).length, Buffer2)
    End Sub
The problem is when I try to request the data from the server, only when the BLOB field in not empty.

In the JobDone sub I have the error in the follow instruction (parser.NextArray)

B4X:
   Dim MYDATA As List
    MYDATA = parser.NextArray 'returns a list with maps
I think I'm not correct conversion of the BLOB field in the string in the php files on the server.

What do you think? Where can be the problem?

Thanks.
 

OliverA

Expert
Licensed User
In the JobDone sub I have the error in the follow instruction (parser.NextArray)
What is the error? If your PHP script returns an empty JSON structure you may be running into this: https://www.b4x.com/android/forum/threads/json-nextarray.61648/. If so, then you need to figure out why your PHP is creating an empty JSON structure and you need to modify your B4A code to account for an empty JSON structure and not throw an exception. Please note that I'm guessing here since you have not provided the error message(s) that you are receiving from your application.
 

Walter Scafati

Member
Licensed User
Hi, thanks for your reply.
The error message is:


Error occurred on line: 1468 (Main)
java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
at SPI.GasMeterReader.main._jobdone(main.java:5574)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:342)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
at anywheresoftware.b4a.BA$2.run(BA.java:360)
at android.os.Handler.handleCallback(Handler.java:615)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4921)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1038)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:805)
at dalvik.system.NativeStart.main(Native Method)


these the rows 1467 and 1468:

B4X:
Dim MYDATA As List
MYDATA = parser.NextArray 'returns a list with maps
 

OliverA

Expert
Licensed User
Somewhere in your code you are getting the result of the PHP page and you are using that result to initialize your parser (via parser.Initialize method). You need to log what your PHP page returns (show us the result) and you need to show us what you are using to initialize the parser variable and any other parsing you do before you get to this point where your program throws an error. Right now, it looks like the PHP page is not returning what you expect (either it is not returning a JSON structure or you are not getting a JSON structure in the format that you are expecting), or you are initializing your parser incorrectly, or you are parsing your JSON input incorrectly.
 

Walter Scafati

Member
Licensed User
If I have a record without image (field BLOB Null) on my App I can receive the JSON data, but if I have a record with image no JSON data is received from the App.
Seem that the JSON encode in the php files no works: [$res = json_encode($rows);]
At this point I think the problem is that the image is not encoded to text for to be sent in the JSON message.

In my php file I have these rows:


PHP:
$query = "SELECT * FROM myTable";
       $sth = mysqli_query($con, $query);
       $rows = array();
       while($r = mysqli_fetch_assoc($sth)) {
            $rows[] = $r;
       }
       $res = json_encode($rows);
       echo $res;
       mysqli_free_result($sth);
       mysqli_close($con);
 

OliverA

Expert
Licensed User

DonManfred

Expert
Licensed User
In my php file I have these rows
it will not work with blobs (binary data). You´ll need to convert the blobs to Base64-encode to transfer it in this way. On android you then need to Base64 decode them back into bytes which you then can store on disc.

Your code should work if the blobs does contain only TEXT.
 

Walter Scafati

Member
Licensed User
it will not work with blobs (binary data). You´ll need to convert the blobs to Base64-encode to transfer it in this way. On android you then need to Base64 decode them back into bytes which you then can store on disc.
Ok, I agree with you but I don't understand how to apply the base64_encode(..) instruction in the php files. I already used this instruction but if I replace this row:

PHP:
while($r = mysqli_fetch_assoc($sth)) {
with

B4X:
while($r = base64_encode(mysqli_fetch_assoc($sth))) {
I receive the JSON data empy like this : "[ ]"

Perhaps I will must to apply this command only to binary field, but how I can do this if I using "SELECT * FROM .... ", all fieds on my query.
 

OliverA

Expert
Licensed User
echo $res;
For the records that you receive "[]" on the client side, what does the PHP code above print? If "echo $res" prints "[]", the the issue lies squarely with the PHP side of the issue. You may (may, since I have no clue about PHP) also run afoul on how json_encode encodes your associative array (see https://stackoverflow.com/a/4770325).
 

OliverA

Expert
Licensed User
Perhaps I will must to apply this command only to binary field, but how I can do this if I using "SELECT * FROM .... ", all fieds on my query.
B4X:
$query = "SELECT * FROM myTable";
$sth = mysqli_query($con, $query);
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
     $r["whatevermyblobfieldisnamed"] = base64_encode($r["whatevermyblobfieldisnamed"]);
     $rows[] = $r;
}
$res = json_encode($rows);
echo $res;
mysqli_free_result($sth);
mysqli_close($con);
 

Walter Scafati

Member
Licensed User
Thanks OliverA, I tried this solution, the error when the BLOB field in not NULL is disappeared but the BLOB filed is received empty from client.
 

OliverA

Expert
Licensed User
BLOB filed is received empty from client
You probably need to be a little bit more specific. On the server side you have
B4X:
echo $res;
. On your client side you need to
B4X:
log(job.getstring) 'Change job to whatever your HTTPJob variable name is, if it is different
as per @KMatle's suggestion. Do these differ? If yes, something funky is going on, otherwise it's just an issue of parsing the received data. That's the other issue, we've not really been given the code you use to receive the data and parse it (the code should show how you are receiving the data and how you are parsing it). We also need the output of the above mentioned job.getstring to see what the data looks like that you are trying to parse on the client side. We also need the output of "echo $res" to see what you are sending to your client. By showing us what you are sending, what you are receiving, how you process the received information, what you expect from the processing, and what the actual results are, then we may be able to help you some more.
 

Walter Scafati

Member
Licensed User
Ok, perhaps I made some error, now I can receive the JSON Data and the BLOB there is:


[{"Cod_Cliente":"01230910","Denominazione":"CLIENTE1","Matricola_misuratore":"10433","Lett_Prec_Mis":"497","Lett_Rilevata_Mis":"1536","Codice_letturista":"L5","Foto_misuratore":"\/9j\/4SfVRXhpZgAASUkqAAgAAAARAA4BAgAgAAAA2gAAAA8BAgAgAAAA+gAAABABAgAgAAAAGgEAABIBAwABAAAAAQAAABoBBQABAAAAOgEAABsBBQABAAAAQgEAACgBAwABAAAAAgAAADEBAgAgAAAASgEAADIBAgAUAAAAagEAABMCAwABAAAAAgAAACACBAABAAAAAAAAACECBAABAAAAAAAAACICBAABAAAAAAAAACMCBAABAAAAAAAAACQCBAABAAAAAAAAACUCAgAgAAAAfgEAAGmHBAABAAAAngEAACADAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAERPT0dFRQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAVGl0YW5zMl9ERzcwMAAAAAAAAAAAAAAAAAAAAAAAAABIAAAAAQAAAEgAAAABAAAATWVkaWFUZWsgQ2FtZXJhIEFwcGxpY2F0aW9uCgAAAAAyMDE4OjAxOjI2IDEwOjE5OjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGQCaggUAAQAAANACAACdggUAAQAAANgCAAAiiAMAAQAAAAAAAAAniAMAAQAAAF4AAAAAkAcABAAAADAyMjADkAIAFAAAAOACAAAEkAIAFAAAAPQCAAABkQcABAAAAAECAwAEkgoAAQAAAAgDAAAHkgMAAQAAAAIAAAAIkgMAAQAAAP8AAAAJkgMAAQAAAAAAAAAKkgUAAQAAABADAACQkgIAAgAAADM5AACRkgIAAgAAADM5AACSkgIAAgAAADM5AAAAoAcABAAAADAxMDABoAMAAQAAAAEAAAACoAQAAQAAAIACAAADoAQAAQAAAOABAAAFoAQAAQAAAJYDAAACpAMAAQAAAAAAAAADpAMAAQAAAAAAAAAEpAUAAQAAABgDAAAGpAMAAQAAAAAAAAAAAAAAEU4AAEBCDwAWAAAACgAAADIwMTg6MDE6MjYgMTA6MTk6MzUAMjAxODowMToyNiAxMDoxOTozNQAAAAAACgAAAF4BAABkAAAAZAAAAGQAAAAIAAMBAwABAAAABgAAABIBAwABAAAAAQAAABoBBQABAAAAhgMAABsBBQABAAAAjgMAACgBAwABAAAAAgAAAAECBAABAAAAzQQAAAICBAABAAAAACMAABMCAwABAAAAAgAAAAAAAABIAAAAAQAAAEgAAAABAAAAAgABAAIABAAAAFI5OAACAAcABAAAADAxMDAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD\/2P\/gABBKRklGAAEBAAABAAEAAP\/bAEMAAgEBAQEBAgEBAQICAgICBAMCAgICBQQEAwQGBQYGBgUGBgYHCQgGBwkHBgYICwgJCgoKCgoGCAsMCwoMCQoKCv\/bAEMBAgICAgICBQMDBQoHBgcKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCv\/AABEIAIAAoAMBIQACEQEDEQH\/xAAfAAABBQEBAQEBAQAAAAAAAAAAAQIDBAUGBwgJCgv\/xAC1EAACAQMDAgQDBQUEBAAAAX0BAgMABBEFEiExQQYTUWEHInEUMoGRoQgjQrHBFVLR8CQzYnKCCQoWFxgZGiUmJygpKjQ1Njc4OTpDREVGR0hJSlNUVVZXWFlaY2RlZmdoaWpzdHV2d3h5eoOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4eLj5OXm5+jp6vHy8\/T19vf4+fr\/xAAfAQADAQEBAQEBAQEBAAAAAAAAAQIDBAUGBwgJCgv\/xAC1EQACAQIEBAMEBwUEBAABAncAAQIDEQQFITEGEkFRB2FxEyIygQgUQpGhscEJIzNS8BVictEKFiQ04SXxFxgZGiYnKCkqNTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqCg4SFhoeIiYqSk5SVlpeYmZqio6Slpqeoqaqys7S1tre4ubrCw8TFxsfIycrS09TV1tfY2dri4+Tl5ufo6ery8\/T19vf4+fr\/2gAMAwEAAhEDEQA\/APRf2WreGXwXYyBOsY5r6T8P2sQt0ygPHc13rY82cpcx0+lCGJgSoIzXVaVNE0YQnr3NZVExxk7l4houUjJHqDUsMkTpgr9c9axKuyRtrDpnPcH3pNkZHIzUyvc0TlcPJj9+T1NKscefu\/rUs0uyeJIz0XPNWI0i6evWspXH7zJlEJBBXIzzSyGIDCDJ7\/8A16zlcE5FWYjd8p75PNV5piASHOfXNTZicmVZNUFjFNdyyO4iiZ2AfHCqSQD74rmdEbxJf6Naa7rPjzWjeX1ulxILS8EUUPmKHEUcYBAVQQuSSTgknni1e9xSkx9zH4kKkxfEzXF9A7Qyf+hJWNqieONpEXxJvGz3m0y3b+gquaVxc1z5F\/ZEnE3w+02TOSYhX0vozgQINx+70r1Y\/Cck7+0LmoeJ9L8M6fNq2sXsUEEERkmlnlCRxoOCzMfujOOxJJwAScV5B4i\/bX1LU7qWx+FfhO71SOJtjajdStaW2fYAhj\/wJ8\/7I6VlWny+o0U9F\/aw+NlvcedP4c0KdephtNXkWQfiZeT+Jr1z4V\/tX+HfGN9H4d8TWc+k6i5AWDUGUCQ\/7EvAJ9nGD
\/fzxXNz66mkXdnrA1fTipU6pArZ+5NII2H1VsEVJHqFq4O3U7Vsntcof61TasbJD47sNwtxEx77ZVP9akjM0hOzn6HNZtmliaH7WB\/qGJ+lSrPeAZMD8Dk4qHqMcl1OOsTfiOlK9xMy5MZJ9ah3CxVmuJME7Dz1qnPedix696RD3MjxRqKW3h\/UrgMcj



where "Foto_misuratore" is the BLOB field. Now must I to convert the BLOB (received like string) to Binary with su.DecodeBase64(...)?
 
Top