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

[Walter Scafati]

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

    Buffer2 = cursoreTmp.GetBlob("image")
    If Buffer2 <> Null Then encoded = su.EncodeBase64(Buffer2) 'data is a bytes array
    ExecuteRemoteQuery2(ADati, encoded)

   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)

   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.

 

[Erel]

There are three common ways to connect to a remote database:

1. With the PHP script that you are using.
2. With jRDC2.
3. Directly with JdbcSQL.

The recommended option is #2.
The PHP script doesn't support blobs. jRDC2 does support blobs and it is more powerful and more secure.

Option 3 also supports blobs.

 

[KMatle]

su.EncodeBase64(Buffer2)

As you have encoded it to Bas64 it's just a simple string. So you don't need to insert a blob. Just insert it to a row defined as MEDIUMTEXT (16 MB max. length).

 

[OliverA]

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]

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:

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

 

[OliverA]

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]

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:

$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]

This is getting out of my comfort zone (not a PHP expert), but have you tried json_last_error (http://php.net/manual/en/function.json-last-error.php) to see if PHP is giving you some sort of error message in regards to your JSON conversion?

 

[Walter Scafati]

No, I will try this.

 

[KMatle]

What is the content of

job.getstring

?

 

[DonManfred]

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]

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:

while($r = mysqli_fetch_assoc($sth)) {

with

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.

 

[Walter Scafati]

What is the content of

Code:
job.getstring
?

I can try this, but I think that the php files don't send the JSON Data

 

[OliverA]

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]

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.

$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]

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]

BLOB filed is received empty from client

You probably need to be a little bit more specific. On the server side you have

echo $res;

. On your client side you need to

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]

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(...)?

 

[OliverA]

https://www.b4x.com/android/forum/t...ase64-encoded-image-string.29738/#post-172657

 

[Walter Scafati]

Thanks OliverA now all works! Solved!