[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:
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
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:
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, "")------------------------------------------------------------------------------
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_iContactIDSQLRefresh
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
Attachments
Last edited:
