Android Question display image from mysql db using remote RDC connection

eng.khalidvb · Mar 7, 2018

Hello all
I have question as titled to display image from mysql db. I have seen many posts which are showing the way to to display picture from mysql db using php or by using local db sqlite in device itself. I need to have a simple code using buffer and byets to convert and read image into image view from myapp b4a directly without using any php file. Any example can help ? Your support appreciated.

BillMeyer · Mar 8, 2018

Read through this https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/page-5

It has example code that could assist you.

Alexander Stolte · Mar 8, 2018

You can use jRDC2 for the database connection, with the "DBRequestManager" on your device you can convert pictures to bytes and bytes to pictures.

eng.khalidvb · Mar 9, 2018

Hello

I have checked this example before, but I am using MYSQL db and this example works with SQLITE db.

here is my code. any one have an idea how to solve this, since I tried from my side it's not working.

B4X:

#Region  Activity Attributes
    #FullScreen: true
    #IncludeTitle: false
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Private Meal_Query = "Meal_Query" As String
    Private Meal_Pic = "Meal_Pic" As String
 Dim mealprice As Int
 
 
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private Meal_Pic As ImageView
    Private lbl_mealname As Label
    Private lbl_Pro_Date As Label
    Private lbl_Ex_Date As Label
    Private lbl_Price As Label
   
    Private sp_qnty As Spinner
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("Order_Layout")

    sp_qnty.add("1")
    sp_qnty.Add("2")
    sp_qnty.Add("3")
    sp_qnty.Add("4")
    sp_qnty.Add("5")
    sp_qnty.Add("6")
    sp_qnty.Add("7")
    sp_qnty.Add("8")
    sp_qnty.Add("9")
    sp_qnty.Add("10")
   

    ' Query of Menu:
    ExecuteRemoteQuery("SELECT mealname,prod_date,ex_date,price,picture FROM menu_table where mealname='" & Talabat_Menu.mealname_value & "'", Meal_Query)
    ProgressDialogShow("Please wait ...")
End Sub

Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim myconn As HttpJob
    myconn.Initialize(JobName, Me)
    myconn.PostString("http://talabat.co.uk/talabat_str.php",Query)
   
End Sub



Sub JobDone(Job As HttpJob)
   
    ProgressDialogHide
   
    If Job.Success  Then
        Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
       
        Select Job.JobName
                   
           
            Case Meal_Query
               
                Dim UserDetails As List
                UserDetails = parser.NextArray 'returns a list with maps
                For i = 0 To UserDetails.Size - 1
                    Dim X As Map
                    X = UserDetails.Get(i)
                    lbl_mealname.Text = X.Get("mealname")
                    lbl_Pro_Date.Text = X.Get("prod_date")
                    lbl_Ex_Date.Text = X.Get("ex_date")
                    lbl_Price.Text = X.Get("price")
                    mealprice = X.Get("price")
                   
                    Dim Buffer() As Byte
                    Dim IpSt As InputStream
                    Dim Bitmap1 As Bitmap
                   
                    Buffer = X.Get("picture")
                    IpSt.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
                    Bitmap1.Initialize2(IpSt)
                    IpSt.Close
                    Meal_Pic.SetBackgroundImage(Bitmap1)
                   
                Next
               
        End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
       
   
    End If
   
    Job.Release
   
   
End Sub


Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub btn_order_Click
   
    lbl_Price.Text  = sp_qnty.SelectedItem * mealprice
   
    Msgbox(sp_qnty.SelectedItem,"Total Price")
   
End Sub


Sub sp_qnty_ItemClick (Position As Int, Value As Object)
   
   
   
   
End Sub

Alexander Stolte · Mar 9, 2018

try this for converting the pics.

B4X:

'Converts an image to a bytes array (for BLOB fields).
Public Sub ImageToBytes(Image As Bitmap) As Byte()
    Dim out As OutputStream
    out.InitializeToBytesArray(0)
    Image.WriteToStream(out, 100, "JPEG")
    out.Close
    Return out.ToBytesArray
End Sub
'Converts a bytes array to an image (for BLOB fields).
Public Sub BytesToImage(bytes() As Byte) As Bitmap
    Dim In As InputStream
    In.InitializeFromBytesArray(bytes, 0, bytes.Length)
    Dim bmp As Bitmap
    bmp.Initialize2(In)
    Return bmp
End Sub

what is not working?

eng.khalidvb · Mar 10, 2018

Hello Alexander Stolte,

I am not able to read image from bytes.

this is my select query from MYSQL DB:

B4X:

ExecuteRemoteQuery("SELECT mealname,prod_date,ex_date,price,picture FROM menu_table where mealname='" & Talabat_Menu.mealname_value & "'", Meal_Query)
    ProgressDialogShow("Please wait ...")

I am able to retrieved the data except image not able to read it and here is my JOBDONE Code:

B4X:

Sub JobDone(Job As HttpJob)
  
    ProgressDialogHide
  
    If Job.Success  Then
        Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
      
        Select Job.JobName
                  
          
            Case Meal_Query
              
                Dim UserDetails As List
                UserDetails = parser.NextArray 'returns a list with maps
                For i = 0 To UserDetails.Size - 1
                    Dim X As Map
                    X = UserDetails.Get(i)
                    lbl_mealname.Text = X.Get("mealname")
                    lbl_Pro_Date.Text = X.Get("prod_date")
                    lbl_Ex_Date.Text = X.Get("ex_date")
                    lbl_Price.Text = X.Get("price")
                    mealprice = X.Get("price")
                  
                    Dim Buffer() As Byte
                    Dim IpSt As InputStream
                    Dim Bitmap1 As Bitmap
                  
                    Buffer = X.Get("picture")
                    IpSt.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
                    Bitmap1.Initialize2(IpSt)
                    IpSt.Close
                    Meal_Pic.SetBackgroundImage(Bitmap1)
                  
                Next
              
        End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
      
  
    End If
  
    Job.Release
  
  
End Sub

My issue is with this part of code:

B4X:

Dim Buffer() As Byte
                    Dim IpSt As InputStream
                    Dim Bitmap1 As Bitmap
                  
                    Buffer = X.Get("picture")
                    IpSt.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
                    Bitmap1.Initialize2(IpSt)
                    IpSt.Close
                    Meal_Pic.SetBackgroundImage(Bitmap1)

App Source Code files attached. Yo can check my full code.

OliverA · Mar 10, 2018

I'm just going to guess here. You are receiving JSON. JSON encodes binary as Base64. Therefore, X.Get does not return bytes, but an Base64 encoded string. Of course it would be a lot easier to help if we had some server code available that creates the JSON structure you are receiving.
You could try this

B4X:

Dim su as StringUtils
Buffer = su.DecodeBase64(X.Get("picture"))

KMatle · Mar 10, 2018

You never posted how the image was stored. However. Best way is the following. Why? It just stores the image as a simple Base64 string which is compatible to all platforms:

Image2Base64String = store image (coming from a bitmap, could be a file also if you change the code)

B4X:

Dim FileBuffer(0) As Byte
           Dim Out As OutputStream
           Out.InitializeToBytesArray(0)
           bm.WriteToStream(Out,100,"JPEG")
           FileBuffer=Out.ToBytesArray         
           Out.Close
           Dim B64String As String
           B64String=su.EncodeBase64(FileBuffer)
           Parms.Put(ImageNr,B64String)

Retrieve image (coming from a php job but as I've written, it's just a string)

B4X:

Dim B64String As String
                        B64String=ResponseMap.Get("image")
                        Dim FileBytes() As Byte
                        Dim su As StringUtils
                        FileBytes=su.DecodeBase64(B64String)

To and from my php scripts I send encrypted Base64 strings. The benefit is: It's all about strings. If you need enryption just encrypt the string which is done by bytes which can then encoded to a Base64 string.

Workflow: Data -> List with maps -> Convert to JSON-String -> Base64 string and back

eng.khalidvb · Mar 12, 2018

Hello all,
I tried all codes shared by members here but still not working I don't know where I am wrong with.

I have used visual studio to store image into MySQL database and image stored as BLOB(bytes)

Here is the code:

B4X:

Dim mstream As New System.IO.MemoryStream()
                    PicMeal.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Png)
                    Dim arrImage() As Byte = mstream.GetBuffer()
                    mstream.Close()

                    Dim cm As New MySqlCommand("insert into menu_table (mealname,type,prod_date,ex_date,price,picture) values(@mealname,@type,@prod_date,@ex_date,@price,@picture)", conn)
                    cm.Parameters.Add("@mealname", MySqlDbType.VarChar).Value = TextMealName.Text
                    cm.Parameters.Add("@type", MySqlDbType.VarChar).Value = cbo_mealtype.Text
                    cm.Parameters.Add("@prod_date", MySqlDbType.Date).Value = Date_product.Value
                    cm.Parameters.Add("@ex_date", MySqlDbType.Date).Value = Date_Expiry.Value
                    cm.Parameters.Add("@price", MySqlDbType.Decimal).Value = TextPrice.Text
                    cm.Parameters.AddWithValue("@picture", arrImage)

Now I want to retrieved or read the blob from MySQL to imageview, but I am not able to read blob and show into image view.

here is my b4a code:

Here is the select image query:

B4X:

ExecuteRemoteQuery("SELECT mealname,prod_date,ex_date,price,picture FROM menu_table where mealname='" & Talabat_Menu.mealname_value & "'", Meal_Query)
    ProgressDialogShow("Please wait ...")

here is my read image code:

B4X:

Sub JobDone(Job As HttpJob)
 
    ProgressDialogHide
 
    If Job.Success  Then
        Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
     
        Select Job.JobName
                 
         
            Case Meal_Query
             
               
Dim mealDetails As List
    mealDetails = parser.NextArray 'returns a list with maps
    For i = 0 To mealDetails.Size - 1
     Dim X As Map
     X = mealDetails.Get(i)
     lbl_mealname.Text = X.Get("mealname")
     lbl_Pro_Date.Text = X.Get("prod_date")
     lbl_Ex_Date.Text = X.Get("ex_date")
     lbl_Price.Text = X.Get("price")
             
                    'Read Image
                    Dim Buffer() As Byte
                    Dim IpSt As InputStream
                    Dim Bitmap1 As Bitmap
                    Buffer = X.Get("picture")
                    IpSt.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
                    Bitmap1.Initialize2(IpSt)
                    IpSt.Close
                    Meal_Pic.SetBackgroundImage(Bitmap1)
                 
                 
                Next
             
        End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
     
 
    End If
 
    Job.Release
 
 
End Sub

any idea ?

OliverA · Mar 12, 2018

after

B4X:

res = Job.GetString

do a

B4X:

log(res)

and let us know what you are getting (I'm guessing a "null")

eng.khalidvb · Mar 12, 2018

This is the logs:

HTML:

Logger connected to:  HUAWEI HUAWEI CUN-U29
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Response from server: [{"Student_id":"22033"}]
Response from server: [{"studentname":"Ali Mohammed Al Balushi"}]
** Activity (main) Pause, UserClosed = false **
** Activity (menu) Create, isFirst = true **
** Activity (menu) Resume **
** Activity (menu) Pause, UserClosed = false **
** Activity (talabat_menu) Create, isFirst = true **
** Activity (talabat_menu) Resume **
Response from server: [{"mealname":"Burger","price":"1.600"},{"mealname":"Pitza","price":"2.200"},{"mealname":"Salad Perfecto","price":"0.800"},{"mealname":"Beef Peces","price":"2.100"}]
** Activity (talabat_menu) Pause, UserClosed = false **
** Activity (talabatorder) Create, isFirst = true **
** Activity (talabatorder) Resume **
Response from server:
Error occurred on line: 78 (TalabatOrder)
org.json.JSONException: End of input at character 0 of
    at org.json.JSONTokener.syntaxError(JSONTokener.java:450)
    at org.json.JSONTokener.nextValue(JSONTokener.java:97)
    at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:60)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:339)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA$2.run(BA.java:360)
    at android.os.Handler.handleCallback(Handler.java:815)
    at android.os.Handler.dispatchMessage(Handler.java:104)
    at android.os.Looper.loop(Looper.java:194)
    at android.app.ActivityThread.main(ActivityThread.java:5950)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:987)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:782)

eng.khalidvb · Mar 12, 2018

This is the error once trying to fetch picture and convert from bytes to image, honestly I don't know how to fix this issue.

your help appreciated if you can little bit modify my code and guide me hot to fix the issue.

Error occurred on line: 78 (TalabatOrder)
org.json.JSONException: End of input at character 0 of
at org.json.JSONTokener.syntaxError(JSONTokener.java:450)
at org.json.JSONTokener.nextValue(JSONTokener.java:97)
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:60)

OliverA · Mar 12, 2018

1)

eng.khalidvb said:
Response from server: Error occurred on line: 78 (TalabatOrder)

Your server responded with nothing (an empty result set?) and did not return an empty array (a []). JSONParser is very picky about it's input and if the input is not in the correct format, it will raise an error. Solution: Validate the input before sending it to JSONParser. Check your server code and see why it returns an empty string instead of an empty array.
2)

eng.khalidvb said:
Response from server: [{"mealname":"Burger","price":"1.600"},{"mealname":"Pitza","price":"2.200"},{"mealname":"Salad Perfecto","price":"0.800"},{"mealname":"Beef Peces","price":"2.100"}]

Is this the result of

B4X:

ExecuteRemoteQuery("SELECT mealname,prod_date,ex_date,price,picture FROM menu_table where mealname='" & Talabat_Menu.mealname_value & "'", Meal_Query)

? If so, that result makes no sense, since prod_date, ex_date and picture are missing. Are you sure you giving me the right output?
3)
PHP's json_encode will not automatically base64 stuff for you and will actually produce a null value for anything that it outside of its encoding parameter. If you're just using the PHP code provided in (https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content) then it will not suffice. The kicker is that I actually provided the answer (https://www.b4x.com/android/forum/t...ql-table-to-android-device.88602/#post-562712) to a similar question (https://www.b4x.com/android/forum/t...ql-table-to-android-device.88602/#post-563134).

eng.khalidvb · Mar 13, 2018

Hello,

I tried the same but still not able to return blob value, since I am using PHP code to connect to MySQL db.

here is my php connection code:

B4X:

<?php

$databasehost="localhost";
$databasename="talabatc_mydb";
$databaseusername="talabatc_myuser";
$databasepassword="pasword";

$con=mysqli_connect($databasehost,$databaseusername,$databasepassword,$databasename)or die(mysqli_error($con));
mysqli_set_charset($con,"utf8");
$query=file_get_contents("php://input");
$sth=mysqli_query($con,$query);

if(mysqli_errno($con)){
header("HTTP/1.1500InternalServerError");
echo$query.'\n';
echomysqli_error($con);
}
else
{
$rows=array();
while($r=mysqli_fetch_assoc($sth)){
$rows[]=$r;
}
$res=json_encode($rows);
echo$res;
mysqli_free_result($sth);
}
mysqli_close($con);
?>

Here is connection code from my application:

B4X:

Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim myconn As HttpJob
    myconn.Initialize(JobName, Me)
    myconn.PostString("http://talabat.co.uk/talabat_str.php",Query)
   
End Sub

Here is my select query to return data and picture as well:

B4X:

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("Order_Layout")

    sp_qnty.add("1")
    sp_qnty.Add("2")
    sp_qnty.Add("3")
    sp_qnty.Add("4")
    sp_qnty.Add("5")
    sp_qnty.Add("6")
    sp_qnty.Add("7")
    sp_qnty.Add("8")
    sp_qnty.Add("9")
    sp_qnty.Add("10")
   

    ' Query of Menu:
    ExecuteRemoteQuery("SELECT mealname,prod_date,ex_date,price,picture FROM menu_table where mealname='" & Talabat_Menu.mealname_value & "'", Meal_Query)
    ProgressDialogShow("Please wait ...")
End Sub

Here is my code to fetch the data and picture:

B4X:

Sub JobDone(Job As HttpJob)
   
    ProgressDialogHide
   
    If Job.Success  Then
        Dim res As String
        res = Job.GetString
        Log(res)
       
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
       
        Select Job.JobName
                   
           
            Case Meal_Query
               
                Dim mealDetails As List
                mealDetails = parser.NextArray 'returns a list with maps
                For i = 0 To mealDetails.Size - 1
                    Dim X As Map
                    X = mealDetails.Get(i)
                    lbl_mealname.Text = X.Get("mealname")
                    lbl_Pro_Date.Text = X.Get("prod_date")
                    lbl_Ex_Date.Text = X.Get("ex_date")
                    lbl_Price.Text = X.Get("price")
               
                    'Read Image
                    Dim Buffer() As Byte
                    Dim IpSt As InputStream
                    Dim Bitmap1 As Bitmap
                    Buffer = X.Get("picture")
                    IpSt.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
                    Bitmap1.Initialize2(IpSt)
                    IpSt.Close
                    Meal_Pic.SetBackgroundImage(Bitmap1)
                   
                   
                Next
               
        End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
       
   
    End If
   
    Job.Release
   
   
End Sub

Here is my application source code uploaded in below link since its large files size and no able to uploaded:

https://files.fm/u/f874n5bw

Pictures of my application running in real device uploaded also.

DonManfred · Mar 13, 2018

You are putting all result into the json. Even it is a blob. You need to convert the blobdata to base64 (if the data in the db is binary) before you add it to the resultarray which runs trough json_encode

eng.khalidvb said:
while($r=mysqli_fetch_assoc($sth)){
$rows[]=$r; # Check the content of $r first. if a field in it is binary, convert it to base64
}
$res=json_encode($rows);

OliverA · Mar 13, 2018

Did you even follow my link (https://www.b4x.com/android/forum/t...ql-table-to-android-device.88602/#post-562712)? This was posted there:

B4X:

$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);

Do you spot what needs to be done? @DonManfred is telling you the same thing. Now if you don't want to hard code $r["whatevermyblobfieldisnamed"], then you need to figure out how to determine blob fields with PHP's mysqli interface. Once you have that, you need to check out post#7 & #8 (@KMatle) to ensure you are decoding it in you client app before usage.

And, when nothing is returned (your query fails to return a result), then you need to check for that on your client code before blindly feeding Job.GetString to JSONParser.NextArray or it will bomb out (your application).

eng.khalidvb · Mar 13, 2018

I am not that much good in php, if you can help and give an example to convert the blob through php before return the blob I will be high appreaciated.

So as per my understanding I can convert blob via php code by converting into base64 in php code itself.

DonManfred · Mar 13, 2018

Check @OliverA answer!

OliverA · Mar 13, 2018

eng.khalidvb said:
So as per my understanding I can convert blob via php code by converting into base64 in php code itself.

Yes. See #16.

eng.khalidvb · Mar 13, 2018

Okay I will do as you said but my question is this php code it should be in saprete php file or in same php connection file ? And how I can get the picture shows in my image view control based on my code as mentioned ? Can be done using the base64 and get image by initilize the bitmap and returen the value into image view control ?

Android Question display image from mysql db using remote RDC connection

Member

Well-Known Member

Expert

Member

Expert

Member

Attachments

Expert

Expert

Member

Expert

Member

Member

Expert

Member

Attachments

Expert

Expert

Member

Expert

Expert

Member

Similar Threads