Passing a Blob field to MySQL

[Tom Law]

I am developing a B4A SQLite database project which is being used to process service records in the field. At the end of the job a customer signature is collected, canvas data is passed to a buffer array and inserted into the SQLite database as a blob field along with the text fields which are passed as strings. This all works fine and the data and signature can be read using SQLite viewer.

The next stage of the program needs to pass the data to an online MySQL database. I am using the 'ExecuteRemoteQuery' routine provided in Erels MySQL database example i.e.:

ExecuteRemoteQuery("UPDATE NetService SET SiteNameAddress='" & m.GetValueAt(7) & "',RecordMode='" & m.GetValueAt(15)& "',Signature='" & Array As Object(Buffer) & "',DateIn='" & m.GetValueAt(18) & "',DateOut='" & m.GetValueAt(19) & "' WHERE ServiceID = '" & SID & "'", 7 )

(The signature is being passed using the Array As Object(Buffer)). The code passes text fields over perfectly but I am not convinced that the blob field is being properly transferred. The field appears in MySQL as something like [BLOB - 28B]. If this is downloaded it is a 4kb file which seems to consist of about 11 letters and the rest is blank space. Clearly either the blob has not transferred correctly or I have misunderstood something. Does MySQL accept a blob field transferred from SQLite in this way or should I be encoding it somehow (in which case could you show me how?).

:sign0104:

 

[Erel]

ExecuteRemoteQuery expects a string query. Add a Log message and test the query string. You will see that the buffer is not converted to a string as you expect it. Instead the Java type name appears.

The simplest solution is to use StringUtils to convert the data to Base64 string. Also change the MySQL column to TEXT.
Then when you read the data you will need to again convert the Base64 string to bytes.

 

[Tom Law]

Erel - Thank you so much, that worked beautifully. :sign0142:

The program can now send a perfect copy of a recorded signature. My only concern is whether the strings formed by this method (about 3500 characters per signature) will cause a problem with string space either on B4A or Visual Basic (on the desktop receiver) .

i.e am I likely to have a problem when sending a hundred or so signatures this way?

 

[Erel]

I don't expect any performance issue with 3500 characters.

hundred or so signatures

per second? minute? day? ...