B4A Library [Class] DBMySQL connect via MySQL Tunnel

aalekizoglou · Mar 26, 2013

[Class] DBMySQL connect via MySQL Tunnel

I have developed this set of classes to implement a pair of connection / dataset objects to connect to remote MySQL database. The main idea is to implement the same functionality found in Delphi's dataset class.
Beware that the entire dataset structure (rows / fields) is stored in memory and we are dealing with devices with lower memory than desktops. So a simple query like SELECT * FROM large_table-with_blobs might be a dissaster in terms of memory and bandwidth. You would be use SELECT field_a, field_b and LIMIT 10 to efficiently utilize the device's memory.

------------------------------------------------------------------------------
ClsDBMySQLconnection class implements a connection to MySQLTunnel.php located in a remote machine. The MySQLTunnel.php is open sourced, developed by me as well, and can be downloaded from https://sourceforge.net/projects/mysqltunnel/
ClsDBMySQLconnection class depends on
HTTP and HttpUtils2 (http://www.b4x.com/forum/basic4andr...httputils2-web-services-now-even-simpler.html) wich require stringutils and threading
ByteConverter (http://www.b4x.com/forum/additional...icial-updates/6787-byteconverter-library.html)
Encryption (http://www.b4x.com/forum/additional...l-updates/6839-base64-encryption-library.html)
RandomAccessFile (http://www.b4x.com/forum/additional...tes/25445-randomaccessfile-v1-32-bug-fix.html)

Functions:
Initialize(HTTPTunnel, Host, Port, DataBase, UserName, Password, Charset As String, Compress As Boolean, HashKey As String)

Use it to initialize the MySQL Connection with the following parameters:
HTTPTunnel = http://path_to_webservice/tunnel.php
Host = MySQL Host. If blank localhost will be used
Port = Port to connect. If blank uses the server's default
DataBase = Database to be used. If blank database will be provided in Query
UserName = Username to authenticate to MySQL
Password = Password to authenticate to MySQL
Charset = Charset to be used for MySQL
Compress = Ask the Tunnel to Compress the Result to save Network bandwidth
HasKey = HaskKey used for the encryption. Only ASCII characters are acceptable. Should be the same as in mysqltunnel.php. Leave blank for no encryption

Note that the MySQL connection is not persistent. Whenever the app needs to send or retrieve information it sends over the request to the tunnel and receives back the result. In order to minimize the bandwidth required you may use compression, and if you want encryption for some kind of data security. Of course you rather use https for advanced security.
For example:

B4X:

MySQLConnection.Initialize("http://webservices.mymanager.gr/mysqltunnel.php", "127.0.0.1", "", "qualisys_demodb", "qualisys_demodb", "12345!@#$%", "utf8", True, "")

Please Note: This is a test database in our datacenter that might be dropped at some point.

------------------------------------------------------------------------------
ClsDBSQLScript class implements a simple SQL script placeholder to store the various SQL scripts (Insert, Update, Refresh, etc) required for the dataset.

Functions:

Clear
Use it to clear the Scrip

Insert(Offset As Int, Text As String)
Inserts the specified Text at the offset

Append(Text As String)
Appends the specified Text at the end

Remove(StartOffset, EndOffset As Int)
Removes the specified characters

ToString
Converts the object to a string

Length
Returns the number of characters

------------------------------------------------------------------------------
ClsDBField class implements a field object to be used in the dataset. Fields are automatically created for the dataset after the first execution and based on the SELECT query. Generated fields will have appropriate field types and length based on the Table's structure.

Functions:

GetName
Get the field name

GetOriginalName
Get the field OriginalName

GetTable
Get the field Table

GetOriginalTable
Get the field OriginalTable

GetMaxLength
Get the field MaxLength

GetLength
Get the field Length

GetFieldType
Get the field FieldType

GetAutoIncrement
Get the field AutoIncrement

GetUniqueKey
Get the field UniqueKey

GetIndex
Get the field Index

SetValue(Value As Object)
Set the field Value. Dataset should be set in insert or edit mode before settings the field's value.

SetValueAsString(Value As String)
Set the field Value as String

SetValueAsLong(Value As Long)
Set the field Value as Long

SetValueAsDouble(Value As Double)
Set the field Value as double

SetValueAsDateTime(Value As Long)
Set the field Value as DateTime

SetValueAsBoolean(Value As Boolean)
Set the field Value As an Boolean

SetValueAsBytes(Value() As Byte)
Set the field Value As Byte() Base64 Encoded. Used for BLOBS

SetValueAsBitmap(Value As Bitmap, Quality As Long)
Set the field Value As Bitmap Base64 Encoded used for BLOBS

IsNull
Check if the field Value is null

GetValue
Get the field Value

GetValueAsString
Get the field Value As a String

GetStringValueAsBase64
Get the string field Value, base64 encoded. Return "" if not string

GetValueAsLong
Get the field Value As an Long

GetValueAsDouble
Get the field Value As an Double

GetValueAsDateTime
Get the field Value As an DateTime

GetValueAsBoolean
Get the field Value As an Boolean

GetValueAsBytes
Get the field Value As Byte() used for BLOBS

GetOriginalValue
Get the Original field Value

GetOriginalStringValueAsBase64
Get the Original string field Value, base64 encoded. Return "" if not string

GetReadOnly
Get the field ReadOnly

GetRequired
Get the field Required

NumberFormat
Get or set a string to be used for NumberFormat

------------------------------------------------------------------------------
ClsDBParameter class implements a parameter object to be used in the dataset. Parameters are automatically created for the dataset after the first execution and based on the query and statements. Generated parameters will have appropriate types based on the Table's structure.

Functions:

GetName
Get the parameter name

GetParamType
Get the parameter name

SetValue(Value As Object)
Set the parameter Value.

GetValue(Value As Object)
Get the parameter Value.

------------------------------------------------------------------------------
ClsDBMySQLDataset class implements the main dataset object. The dataset encapsuates all the functionality to handle rows and fields, and retrieve information and communicate changes to MySQLTunnel.php via the ClsDBMySQLconnection.
ClsDBMySQLDataset class depends on
JSON (http://www.b4x.com/forum/additional...al-updates/6924-new-json-library-v1-00-a.html)
AHLocale (http://www.b4x.com/forum/additional-libraries-classes-official-updates/7561-ahlocale-library.html)
Reflection

Properties:

SQL
The Query script to execute such as SELECT iContactID, bActive, strMaleFemale, strFirstName, strLastName, strEmail, dBirthdate, objPhoto, mNotes FROM contact LIMIT 100

SQLDelete
The Delete script to execute such as DELETE FROM contact WHERE iContactID = :Old_iContactID

SQLInsert
The Insert script to execute such as INSERT INTO contact (iContactID, bActive, strMaleFemale, strFirstName, strLastName, strEmail, dBirthdate, objPhoto, mNotes) VALUES iContactID, :bActive, :strMaleFemale, :strFirstName, :strLastName, :strEmail, :dBirthdate, bjPhoto, :mNotes)

SQLUpdate
The Update script to execute such as UPDATE contact SET bActive = :bActive, strMaleFemale = :strMaleFemale, strFirstName = :strFirstName, strLastName = :strLastName, strEmail = :strEmail, dBirthdate = :dBirthdate, objPhoto = bjPhoto, mNotes = :mNotes WHERE iContactID = :Old_iContactID

SQLRefresh
The Refresh script to execute such as SELECT iContactID, bActive, strMaleFemale, strFirstName, strLastName, strEmail, dBirthdate, objPhoto, mNotes FROM contact WHERE iContactID = :Old_iContactID

Note: Parameters in the scripts should start with a collon :
You are allowed not to use the same name for parameters as the field names, but in any case this is recommended. There is a special parameter starting with :Old_[FIELDNAME] which holds the original value of the [FIELDNAME]

Functions:

Initialize(MySQLConnection As ClsDBMySQLConnection, Module As Object)
Initializes ClsDBMySQLDataset. Parameters are MySQLConnection and a calling Activity

ChangeEventsToActivity(Module As Object)
Change the activity that holds the events to be called

Note: All events will be called, if exist, in the registered calling activity, via the Initialize or the ChangeEventsToActivity call.

Execute
Execute the SQL query. Return True on success, False otherwise. You might want to check the dataset's state before executing.
Beware that Executing the SQL query, as well as running every other statement on the MySQLTunnel.php, is asynchronous as it relies on HTTP requests

RecordRefresh
Execute the SQLRefresh query. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Insert
Inserts a new record to the dataset before the record pointer and places the dataset in dsInsert mode. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Append
Appends a new record at the end of the dataset and places the dataset in dsInsert mode. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Edit
Puts the current record available for editing and places the dataset in dsEdit mode. Call Edit before changing any value on the current row. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Cancel
Cancel modification to the active record if those changes are not yet posted. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Post
Writes modified record to the database by executing the SQLInsert or SQLUpdate query based on the datasets state. Return True on success, False otherwise. You might want to check the dataset's state before executing.

Delete
Execute the SQLDelete query. Return True on success, False otherwise. You might want to check the dataset's state before executing.

RecordCount
Indicates the total number of records associated with the dataset.

RecNo
Indicates the active record in the dataset.

BOF
Indicates whether a dataset is positioned at the first record.

EOF
Indicates whether a dataset is positioned at the last record.

FirstRecord
Moves To the first record In the Dataset. Return True on success, False otherwise. Check dataset state before executing

NextRecord
Moves to the next record in the dataset. Return True on success, False otherwise. Check dataset state before executing

PreviousRecord
Moves to the previous record in the dataset. Return True on success, False otherwise. Check dataset state before executing

LastRecord
Moves To the first record In the Dataset. Return True on success, False otherwise. Check dataset state before executing

GetBookmark
Return a Bookmark of the current record

GotoBookmark(Bookmark As typBookmark)
Goto bookmark and sets the current record. Return True if success, False if record has been deleted. Check dataset state before executing

Locate(KeyFields() As Object, KeyValues() As Object, loCaseInsensitive, loPartialKey As Boolean)
Search the dataset for a specified record AND make it the active record. Locate does a serial search.
Returns false, indicating that a matching record was not found and the active record was not changed. Check dataset state before executing

GotoRecord(NewRecNo As Long)
Moves the Record Pointer to RecNo AND make it the active record. Check dataset state before executing

FieldCount
Indicates the number of field components associated with the Dataset.

Fields(iIndex As Long)
Finds a field based on its index.

FieldByName(strFieldName As String)
Finds a field based on its name.

Params(iParam As Long)
Finds a parameter based on its index.

ParamByName(strParameterName As String)
Finds a parameter based on its name.

ErrorNumber
Return the latest error number

ErrorDescr
Return the latest error description

State
Return the Dataset State

Events:

OnErrorDecoding(JSONResult As String, DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called if a JSON Decode rrror occurs

OnBeforeExecute(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the Execute command run.

OnAfterExecute(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the Execute command run and the HTTP job has returned.

OnErrorExecute(ErrorMessage As String, DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called if an error occurs on the Execute command.

OnBeforeInsert(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the Insert command run.
OnAfterInsert(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the Insert command run.

OnBeforeEdit(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the Edit command run.

OnAfterEdit(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the Edit command run.

OnBeforePost(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the Post command run.

OnAfterPost(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the Post command run.

OnErrorPost(ErrorMessage As String, DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called if an error occurs on the Post command.

OnBeforeDelete(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the Delete command run.

OnAfterDelete(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the Delete command run.

OnErrorDelete(ErrorMessage As String, DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called if an error occurs on the Delete command.

OnBeforeRefresh(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called before the RecordRefresh command run.

OnAfterRefresh(DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called after the RecordRefresh command run.

OnErrorRefresh(ErrorMessage As String, DBMySQLDataset As ClsDBMySQLDataset)
Event that will be called if an error occures on the RecordRefresh command.

------------------------------------------------------------------------------

Attached you may find ClassDBMySQL.zip which holds the classes and MySQLTunnel.zip which is a revised sample from Erel's MySQL.zip based on the new classes. I have not done any development on the UI of the sample since this was out of the scope of the class. For example there is not state saving on the two activities. I didn't bother with those issues. What I wanted to show in the sample is the way to insert a record with VARCHAR, DATE, and BLOB for a picture.
Beware that the sample uses and connects to a sample DB in our datacenter where I have build a table with various field types.
If you want to connect to your database server what you need is:

1) DDL to create that table in your database

B4X:

CREATE TABLE `contact` (
  `iContactID` int(11) NOT NULL AUTO_INCREMENT,
  `bActive` tinyint(1) NOT NULL DEFAULT '1',
  `strMaleFemale` enum('MALE','FEMALE') DEFAULT NULL,
  `strFirstName` varchar(50) DEFAULT NULL,
  `strLastName` varchar(75) NOT NULL,
  `strEmail` varchar(255) DEFAULT NULL,
  `dBirthdate` date DEFAULT NULL,
  `objPhoto` blob,
  `mNotes` text,
  PRIMARY KEY (`iContactID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;

2)MySQLTunnel.php found in https://sourceforge.net/projects/mysqltunnel/
You need a Apache/PHP or IIS/PHP in your server to have the script running.

3)Change the connection in the sample to your server

Note:The datatypes for various fields from MySQL are reported from the tunnel. If you check function GetCorrectDataType in mysqltunnel.php you'll see how it transcript various MySQL fields. For example it explicitly returns the field type as boolean if it is int(1). In fact MySQL's boolean is an alias for int(1), since there is no real boolean type in MySQL. If the tunnel connects through mysql protocol then this function is called. On the other hand if you use mysqli then function GetCorrectDataTypeMySQLI is used to get the correct types.

Hope you find the class usefull. You can use it however you want and of course if you find errors, bugs, or missing functionality please reply to this thread.

------------------------------------------------------------------------------
Versions
28/03/2013, Version 1.0, initial version
16/04/2013, Version 1.1, changes for obfuscated compilation
25/04/2013, Version 1.2, changes for Dataset.SQL.Clear not raising out of index after the dataset has been initialized, Fixed demo database connection
31/05/2013, Version 1.4, Updated to version B4A 2.7, changed getters and setters. Added ClsDBMySQLScript to allow running scripts on server. Added DataChange Event to notify customviews* of data changes and scrolling. Added DBField.NumberFormat to be used for formating number fields. Bug fixing. You should also download the latest MySQLTunnel from Sourceforge. There are bug fixes there too.

------------------------------------------------------------------------------
Based on B4A 2.7 we are implementing a set of DBAware customviews that will connect to ClsDBMySQLDataset via their DataSource/DataSet properties. We've already developed:

ClsDBEditText for generic Text
ClsDBEditNumber for numeric values
ClsDBSpinner for spinner
ClsDBCheckbox for checkbox
ClsCompLookupEditText for LookupListBox with foreign table lookup

aalekizoglou · Jun 2, 2013

I have uploaded a new version in the first post

TomDuncan · Nov 19, 2013

Hi.
With my xamp server this are ok but if I do a call to my website I get
Parse error: syntax error, unexpected '?' in /home/xxxx/public_html/mysqltunnel.php on line 527
I have a database setup and using on my web site. which I use with a Delphi 7 project.
Any thoughts.

Tom

aalekizoglou · Nov 19, 2013

Nice to know there are other people working with Delphi here.

It seems there might be some libs missing in your server. Do you have access to Apache's errors.log and access.log? Can you debug to see what's wrong?

Definitely the problem would be on PHP's side with some libraries missing. I suppose you've got the latest mysqltunnel.php version from sourceforge.

TomDuncan · Nov 19, 2013

Yep, through and through Delphi man.
Latest project used zeros. Good fun, now working ways to interface with android.
The server is from and company called siteground. They use cpanel as the interface.
I had to allow my tcpip address at home to get the zeos part to work.
They only error log I could find said eexactly what was in my last post.
Might have to go to a sqlite version which downloads the database on entry.
Not good but a the moment is all i can tthink of.

Tom

TomDuncan · Nov 19, 2013

And yes latest everything.
Got them down this mmorning.
Pity though, loved the idea.

Tom

aalekizoglou · Nov 19, 2013

Yes, it's a pity since I've written mysqltunnel to make things quick and simple. And on the other part B4A's dataset is used in a lot of project we have and is working fine.

For Delphi we've been working with UniDAC since 2007. Before that MyDAC was used.
Lately I've played a lot with CopyCAT for MySQL synchronization. Will try to incorporate that in our applications for off-line databases. For Android I've used sqlite for off-line but I lack Greek character functionality such as Searching.

I've planned to play with Delphi XE5 for android development as well.
Regards,

TomDuncan · Nov 19, 2013

Might try again in the morning.
Then if that does not work, will use it for local work and then for doing a sqlite update.
This is a little app for my wife and i to log houses which we like on the australian realestate.com
we are selling our farm so b4a is an ideal platform to develop the software.

Tom

TomDuncan · Nov 19, 2013

This morning put the tunnel php script in it's own folder.
At least now it is generating a log file.
Here are the last results.
MySQL Error = Access denied for user 'xxx'@'xxxxxxxx' (using password: YES)
I have tried with and without port.
either localhost or website address.
All with same results.
Somewhere within cPanel their must be a switch to allow access to the database.
This does however, work with my Delphi app using the same credentials.

Tom

TomDuncan · Nov 19, 2013

Just changed the host part in the init to LocalHost and all works.
Great, now and can get started.

Tom

TomDuncan · Nov 20, 2013

So far all is going well.
Have added

B4X:

    ExecutionType = 1
    MySQLDataset.Execute
    ProgressDialogShow("Please Wait")
  btnTake.Visible=False
End Sub

Sub OnAfter_Execute(DBMySQLDataset As ClsDBMySQLDataset)
    Select ExecutionType
        Case 1
            Dim i As Long
            AddressView.Clear

This will Update the correct Execution Type, say display a list of houses or select all for a selected house.

TomDuncan · Nov 20, 2013

Thanks for your great addition.
I have finished the basic app. Looks good.
Will only be used for my wife and I at the moment.
I will test it outside my domain when I go to town next. (we do not have mobile reception at home)
and the Internet is via Satellite. (yuk and slow)

Tom

andre.astafieff · Jan 21, 2014

Could anyone help me with this error?

B4X:

(Intent) Intent { act=android.intent.action.MAIN flg=0x20000000 cmp=anywheresoftware.b4a.samples.camera/.main }
no extras
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
host=192.168.1.105&charset=utf8&dbname=cidades_br&username=bebi&password=bebi&query=%7B%22SQL%22%3A%22SELECT+*+from+cidades_br%22%2C%22params%22%3A%7B%7D%7D&compress=0
startService: class anywheresoftware.b4a.samples.httputils2.httputils2service
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Error occurred on line: 608 (clsdbmysqldataset)
java.lang.RuntimeException: JSON Object expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextObject(JSONParser.java:47)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:485)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:174)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:858)
    at anywheresoftware.b4a.keywords.Common.CallSubNew3(Common.java:823)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:485)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:232)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:174)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.BA$3.run(BA.java:315)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5222)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
    at dalvik.system.NativeStart.main(Native Method)

andre.astafieff · Jan 21, 2014

I discovered the error.
I was trying to load a table with 21,886 records and 11 columns.
Was monitoring the variable mapResult sub JSONDecodeHeaders I realized the problem ...

B4X:

<b>Fatal error</b>:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 36 bytes) in <b>G:\xampp\htdocs\tunel\mysqltunnel.php</b> on line <b>371</b><br />

It would be a limitation of the database or application?

aalekizoglou · Feb 5, 2014

andre.astafieff said:
I discovered the error.
I was trying to load a table with 21,886 records and 11 columns.
Was monitoring the variable mapResult sub JSONDecodeHeaders I realized the problem ...

B4X:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 36 bytes) in G:\xampp\htdocs\tunel\mysqltunnel.php on line 371 

It would be a limitation of the database or application?

Andres,

I would say this is an error coming from your PHP. You need to set php.ini to allocate more memory for scripts, if you wish to handle large dataset. You see the entire dataset must be loaded in memory, to generate the JSON resultset.

Thus I would be carefull with the phone's memory as well, case the exact resultset must be loaded in memory of the phone.

Regards,

KSC · Apr 29, 2014

Installed project and get mysql syntax error on update of row with a photo included. 1st add of row with phot works fine, made photo column medium blob per previous remarks. MySql error pops up when updating the notes field after a photo is already in row. Appears encode/decode with encrption appears amiss. Can you confirm? response time to remote db is good without photo included. when included save message does not appear until up to 10 seconds. Is saving photo in db the best approach?

aalekizoglou · Apr 29, 2014

KSC said:
Installed project and get mysql syntax error on update of row with a photo included. 1st add of row with phot works fine, made photo column medium blob per previous remarks. MySql error pops up when updating the notes field after a photo is already in row. Appears encode/decode with encrption appears amiss. Can you confirm? response time to remote db is good without photo included. when included save message does not appear until up to 10 seconds. Is saving photo in db the best approach?

KSC, With regards to the syntax error, I have to check what's wrong and let you know. To tell you the truth, it's been a long time since I last made changes to the library as I haven't found any problems working with it.

Saving a photo into the DB means having to transfer over the amount of Bytes (KB or maybe MB) to the remote server. Restoring back the field with the photo means getting that amount of Bytes back from the remote. Even though the library supports compression (you can try that as well), there will be a CPU overhead to the android device when preparing the blob data for sending over, which means some delay.

On the other hand some seconds delay when storing a blob field might not be as bad as it sounds considering you are saving a picture You might want to try resizing the picture to lower dimensions before saving it. If you are saving an 5MPixels or 8Mpixel image then the amount of data would be some megabytes in JPG format?

KSC · Apr 29, 2014

Further testing shows that the notes field below the photo is becoming corrupted as well. I see funny characters in it when the myusql syntax error appears. periods single quotes in the text seems to be breaking the encyption/compression. The field is defined as text in the db. Does it require some type of escape function on special characters when storing? The photo is hit or miss on storage and retrieval. How do I select a photo? It always takes a picture when I click on the camera icon and do not see a control to select a smaller picture as you suggest.

KSC · May 1, 2014

Is it possible to get support on this sample project? I have found 2 things wrong. The Notes field (which is defined as 'Text' in the database) is not base64 encoding and decoding correctly. I see that db field type of 'text' was not defined in the global definitions and case logic was not there either. If I switched the field to varchar(1000) then it works correctly with special characters (like apostrophes, backslashes etc) are escaped correctly. The second problem is the base64 encode and decode of the photos is not working on a retrieval from the database. Snapshots are uploaded to the database, supposedly base64 encoded. Yet on the retrieval from the database debug shows it is exceptioning within the 'initalize2' method and never retrieves the photo. When you change orientation from portrait to landscape you will see the photo is lost and cannot be retrieved. From threads I have seen on images stored as blobs, assumption is the photo has not be encoded/decoded correctly. I am running the latest Android Kitkat 4.4.2 using adt/sdk api 19 under dalvik. (Nexus 5). A reply would be appreciated.

aalekizoglou · May 2, 2014

KSC said:
Is it possible to get support on this sample project? I have found 2 things wrong. The Notes field (which is defined as 'Text' in the database) is not base64 encoding and decoding correctly. I see that db field type of 'text' was not defined in the global definitions and case logic was not there either. If I switched the field to varchar(1000) then it works correctly with special characters (like apostrophes, backslashes etc) are escaped correctly. The second problem is the base64 encode and decode of the photos is not working on a retrieval from the database. Snapshots are uploaded to the database, supposedly base64 encoded. Yet on the retrieval from the database debug shows it is exceptioning within the 'initalize2' method and never retrieves the photo. When you change orientation from portrait to landscape you will see the photo is lost and cannot be retrieved. From threads I have seen on images stored as blobs, assumption is the photo has not be encoded/decoded correctly. I am running the latest Android Kitkat 4.4.2 using adt/sdk api 19 under dalvik. (Nexus 5). A reply would be appreciated.

KSC, I'll have to check on those two problems and give you feedback. Is it a sample project you have or are you building a production app? What is the timeframe we have to check and find the problem?

Do you have a schema and B4A sample project with the libraries that you can share with me?

aalekizoglou · May 2, 2014

Well, it seems I found the problem for TEXT field. Further investigation will help me find the problem with BLOBs as well. I'll try to fix that.

B4A Library [Class] DBMySQL connect via MySQL Tunnel

Attachments

Member

Active Member

Member

Active Member

Active Member

Member

Active Member

Active Member

Active Member

Active Member

Active Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Privacy & Transparency

Privacy & Transparency