B4A Library [Class] DBMySQL connect via MySQL Tunnel

[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:

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

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

 

[KSC]

Athanasios,

The project is your sample project, with no modifications, listed in your 1st post. Was hoping you could fix these issues and re-post the project for all others to benefit.

 

[KSC]

Have you had a chance to post an update on these issues?

 

[aalekizoglou]

Have you had a chance to post an update on these issues?

No, not yet. Sorry, I've been very busy past week.

 

[KSC]

Can you post the patch for the Notes "text" problem that you found?
Any luck with the photo blob retrieval issue? Can you provide code to take a smaller sized (less pixel) image to store in the db?

 

[TomDuncan]

Just got back to some work with MySQL and tunneling.
Have some success but some funny ones.
I am using sql updates and inserts for all actions.

This Query works fine if I use phpMyAdmin

INSERT INTO plan ( idkey, starttime, endtime, patient, lastupdate, deleted ) VALUES (  'key', '2015-10-15 10:40:00' , ADDTIME( '2015-10-15 10:40:00', '00:20:00'), 'added at ten forty' , '2016-02-05' , 0 );

However if I run this with the b4a app it changes the starttime and endtime values.
Rounds then to 10am.

Did another test at 1:40 pm and had some result, rouned to 1pm. even though the Query done directly is fine (in phpMyAdmin)

Tom

 

[scsjc]

Congratulations with this great work !!!