Android Question Write/edit to a BLOB field at MySQL server

[Bob Spielen]

I'm trying to send the Type var to a MySQL server in the cloud......
Can you indicate where to find a solution to send a Type var to a Blob field in the MySQL? In advance, I thank you very much for that.

PS.: Do you think that a PHP file could do the job of downloading a BLOB field more efficiently? Base64 encode/decode image perhaps?

 

[KMatle]

PS.: Do you think that a PHP file could do the job of downloading a BLOB field more efficiently? Base64 encode/decode image perhaps?

I do it that way. There are examples here to get an image from a file, convert it to bytes and Base64 encode it. Then it's "just a text" which can be inserted in a (LONG)TEXT column. Just try.

 

[Bob Spielen]

I do it that way. There are examples here to get an image from a file, convert it to bytes and Base64 encode it. Then it's "just a text" which can be inserted in a (LONG)TEXT column. Just try.

A question ... do I have to save my bytes ( Buffer() As Byte = ser.ConvertObjectToBytes(Starter.TpPed) )

to a file , in order to encode it with Base64.EncodeFromImage(FolderPath as string, FileName as string) or does it exists a way to encode it directly?

Thank you very very much!

 

[OliverA]

https://www.b4x.com/android/help/stringutils.html#stringutils_encodebase64

 

[KMatle]

This should be the fastest way to encode it:

Dim su as StringUtils 'needed
Dim B64String As String
B64String=su.EncodeBase64(Bit.InputStreamToBytes(File.OpenInput(Dir, FileName)))

 

[Bob Spielen]

Thank you, "Sirs"!!!

 

[Bob Spielen]

A last issue..... getting an error message.... (spite of string length uploaded is the same as downloaded and string the same).....
Attached the project for your evaluation, if necessary. THANKS IN ADVANCE!


Log file:


Logger connected to: asus ASUS_X008D
--------- beginning of crash
--------- beginning of system
--------- beginning of main
*** Service (starter) Create ***

ctX.TpItem = (MyMap) {0-id=2, 0-qtd=3, 0-taborig=pizza, 1-id=2, 1-qtd=3, 1-taborig=pizza, 2-id=2, 2-qtd=3, 2-taborig=pizza}
ctX.bxdo = 3
ctX.ende = Ende 0
ctX.fentr = 4
ctX.fpgto = 5
ctX.id = 0
ctX.idnuvem = 7
ctX.regdel = 8
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
~w:1004,main,57
** Activity (main) Resume **
B64String up = eJzt0L1OwzAUhmEHF0oHNoSQWDqwUjURt8CQnR0l8mlkKT8mNajK1WMbIRqkMjGhd7HOsc9nP8mNUUrdZndhrR+rjRyqzrWy2ftq9DLev3jnxPhVOM6uwlLuy956W7V2ErNU2UXYe3all+46zSzCsn2wRp/F7jx1r95oHdtVan1VD6Nt0qGz01SlUD4L5fNQfiJUzELFPFScCNUHM3xOxU56I+krnkKx3qbZnfR+1Iuve3eu8YOOVRbfCk+qWC9T3b+9S6djnW4ZpTHS6ss/+qnqW6yOxerIqeZO9ZtT/XAqnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJE+e/dH4AZ2WvPg==
String Length up = 408
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Job.GetString=<br />
<b>Warning</b>: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in <b>/var/www/html/digibob.online/web/laparma.php</b> on line <b>22</b><br />
[]
Net Ok!, Executa_Query: SELECT * FROM pedidos WHERE idnuvem = 29 JobName: BxPedidos
Job.GetString=[{"id":"0","idnuvem":"29","nome":"Roberto","cel":"996075798","codcli":"0","data":"data","entregue":"","valor":"0","pago":"0","codformapgto":"0","codformaentrega":"0","pedidostr":"pedidostr","origem":"origem","msg":"mesg","bxdo":"10","regdelivery":"2","pedbin":"eJzt0L1OwzAUhmEHF0oHNoSQWDqwUjURt8CQnR0l8mlkKT8mNajK1WMbIRqkMjGhd7HOsc9nP8mNUUrdZndhrR+rjRyqzrWy2ftq9DLev3jnxPhVOM6uwlLuy956W7V2ErNU2UXYe3all+46zSzCsn2wRp\/F7jx1r95oHdtVan1VD6Nt0qGz01SlUD4L5fNQfiJUzELFPFScCNUHM3xOxU56I+krnkKx3qbZnfR+1Iuve3eu8YOOVRbfCk+qWC9T3b+9S6djnW4ZpTHS6ss\/+qnqW6yOxerIqeZO9ZtT\/XAqnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJE+e\/dH4AZ2WvPg=="}]
*********************** Case 'BxPedidos' *****
Job.GetString=[{"id":"0","idnuvem":"29","nome":"Roberto","cel":"996075798","codcli":"0","data":"data","entregue":"","valor":"0","pago":"0","codformapgto":"0","codformaentrega":"0","pedidostr":"pedidostr","origem":"origem","msg":"mesg","bxdo":"10","regdelivery":"2","pedbin":"eJzt0L1OwzAUhmEHF0oHNoSQWDqwUjURt8CQnR0l8mlkKT8mNajK1WMbIRqkMjGhd7HOsc9nP8mNUUrdZndhrR+rjRyqzrWy2ftq9DLev3jnxPhVOM6uwlLuy956W7V2ErNU2UXYe3all+46zSzCsn2wRp\/F7jx1r95oHdtVan1VD6Nt0qGz01SlUD4L5fNQfiJUzELFPFScCNUHM3xOxU56I+krnkKx3qbZnfR+1Iuve3eu8YOOVRbfCk+qWC9T3b+9S6djnW4ZpTHS6ss\/+qnqW6yOxerIqeZO9ZtT\/XAqnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJE+e\/dH4AZ2WvPg=="}]
B64String dwn = eJzt0L1OwzAUhmEHF0oHNoSQWDqwUjURt8CQnR0l8mlkKT8mNajK1WMbIRqkMjGhd7HOsc9nP8mNUUrdZndhrR+rjRyqzrWy2ftq9DLev3jnxPhVOM6uwlLuy956W7V2ErNU2UXYe3all+46zSzCsn2wRp/F7jx1r95oHdtVan1VD6Nt0qGz01SlUD4L5fNQfiJUzELFPFScCNUHM3xOxU56I+krnkKx3qbZnfR+1Iuve3eu8YOOVRbfCk+qWC9T3b+9S6djnW4ZpTHS6ss/+qnqW6yOxerIqeZO9ZtT/XAqnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJEydOnDhx4sSJE+e/dH4AZ2WvPg==
String Length dwn = 408

main_jobdone (B4A line: 221)
Starter.TpPed=ser.ConvertBytesToObject(Buffe
java.lang.ClassCastException: java.lang.Object[] cannot be cast to b4a.example.starter$_tppedt[]
at b4a.example.main._jobdone(main.java:723)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:186)
at anywheresoftware.b4a.keywords.Common$11.run(Common.java:1151)
at android.os.Handler.handleCallback(Handler.java:836)
at android.os.Handler.dispatchMessage(Handler.java:103)
at android.os.Looper.loop(Looper.java:208)
at android.app.ActivityThread.main(ActivityThread.java:6267)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1063)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:924)

 

[Bob Spielen]

Hello, some more infos......

Dim DumObj() As Object
DumObj=ser.ConvertBytesToObject(Buffer)

DumObj brings inside:

[0=[IsInitialized=false, TpItem=(MyMap) {0-id=2, 0-qtd=3, 0-taborig=pizza,
1-id=2, 1-qtd=3, 1-taborig=pizza,
2-id=2, 2-qtd=3, 2-taborig=pizza},
bxdo=3
, ende=Ende 0, fentr=4, fpgto=5
, id=0, idnuvem=7, regdel=8
],

and I dont know how to get the values out......thats the structure of my Type var......

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

What is the output of this code:

Dim DumObj() As Object = ser.ConvertBytesToObject(Buffer)
For Each o As Object In DumObj
 Log(GetType(o))
 Log(o)
Next

 

[Bob Spielen]

In Main I have the Type definition:

Sub Process_Globals
    Type TpPedT(id As Int, idnuvem As Int, fpgto As Int, fentr As Int, bxdo As Int, ende As String, regdel As Int, TpItem  As Map )
    Public TpItem As Map
    Public TpPed(100) As TpPedT  '~357  
End Sub

And the output you are asking for is:

b4a.example.main$_tppedt
[IsInitialized=false, TpItem=(MyMap) {0-id=2, 0-qtd=3, 0-taborig=pizza, 1-id=2, 1-qtd=3, 1-taborig=pizza, 2-id=2, 2-qtd=3, 2-taborig=pizza}, bxdo=3
, ende=Ende 0, fentr=4, fpgto=5
, id=0, idnuvem=7, regdel=8
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.example.main$_tppedt
[IsInitialized=false, TpItem=null, bxdo=0
, ende=null, fentr=0, fpgto=0
, id=0, idnuvem=0, regdel=0
]
b4a.ex.

 

[Erel]

I recommend you to switch to a List instead of an array.

If you want to keep it as an array then you should do something like:

Dim DumObj() As Object = ser.ConvertBytesToObject(Buffer)
Dim MyArray(DumObj.Length) As TpPedT  
Dim index As Int
For Each o As Object In DumObj
 MyArray(index) = o
 index = index + 1
Next

 

[Bob Spielen]

Ok, Mr. Erel,
Thanks a lot for your, as always, fast reply and for sure I will change from array to list.

 

[nwhitfield]

You can certainly use a BLOB to store data like an image - for other types, encoding as base64 as suggested above would probably be wise.

On the specific matter of images, however, I'd probably advise against it. It's trivial to store them (once uploaded, read the file into a variable, store it in a BLOB) and to retrieve them with code like the stuff below, which I used for the previous version of one of my web sites.

However, when I rebuilt the website, I did some more tests on performance and found (these were 100x150 pixel thumbnails of members) that I got better performance simply by storing the thumbnails in the filesystem, rather than querying the database. If you did want to do that, and assuming that 'thumbnail' is a BLOB in your database, something like this PHP code will do the trick (I've omitted site security checks, and setting up the DB connection):

header('Content-type: image/jpeg') ;

$defaultThumb = 'images/notfound.jpg' ;

$profileID = $blufDB->real_escape_string($_GET['id']) ;
$image_info = $blufDB->query(sprintf("SELECT * FROM imageInfo WHERE memberid = %d",$profileID)) ;

if ( $image_info->num_rows != 1 ) {
   print file_get_contents($defaultThumb) ;
} else {
   $image = $image_info->fetch_assoc() ;
   if ( $image['thumbnail'] == '' ) {
     print file_get_contents($defaultThumb) ;
   } else {
     print $image['thumbnail'] ;
   }
}

In practice, what I do now, since member ids are numeric, is have a folder structure that's like 0/1/2/3 so each folder stores images for ten members - in this case those with numbers starting 01230-01239. And the thumbnail is thumbnail-id.jpg, eg thumbnail-3.jpg. It's then quick to work out the folder from the memberid and do this (omitting security stuff and checks for a missing file):

preg_match('/([0-9])([0-9])([0-9])([0-9])/',sprintf('%05d',$memberid),$paths) ;
$folder = $paths[1] . '/' . $paths[2] . '/' . $paths[3] . '/' . $paths[4] ;
$imageroot = '/var/site/private/thumbnails/' ;

header('Content-type: image/jpeg') ;
print file_get_contents($imageroot . $folder . '/thumbnail-' . $memberid . '.jpg') ;

Of course, it's even quicker if the images are in the bit of the filesystem under the server's document root, but in this case for privacy, I want them accessible only via a script, so you can't grab an image just by knowing its URL. I get far better performance retrieving images this way than I did storing them as BLOBs. Obviously, there may be other advantages to having them in the database (adding meta data fields, tags, etc), but I'd generally stick the metadata in there, and keep the images separate.

 

[Bob Spielen]

You can certainly use a BLOB to store data like an image - for other types, encoding as base64 as suggested above would probably be wise.

On the specific matter of images, however, I'd probably advise against it. It's trivial to store them (once uploaded, read the file into a variable, store it in a BLOB) and to retrieve them with code like the stuff below, which I used for the previous version of one of my web sites.

Very nice post! THANKS A LOT FOR IT!