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 · Apr 12, 2013

peacemaker said:
B4A gets error:

If:
1) Try to add a new record
2) not tap "Save", just go BACK
3) try to edit an existing record (long tap)

That is fixed if add "Main.MySQLDataset.Cancel" into EditForm activity:

------------------------------------------------------
Question: is it possible to edit the db locally when no Internet ?
And synchronize with MySQL later when Internet is OK...

You are right on this. As I said in the my first post, I have not dealt with the UI correctness. In a real application if you tap on add and then tap on Back without saving the dataset, then you should call cancel after you've created a hock on back key. Maybe present a MsgBox as well with a question.

aalekizoglou · Apr 12, 2013

Thanks peacemaker,
Fixed that on https://sourceforge.net/projects/mysqltunnel/

There is another bug fix in mysqltunnel.php, so please download it from there.

aalekizoglou · Apr 12, 2013

peacemaker said:
Now when DB field for the photo is BLOB, it's saved truncated (and cannot be shown without the error).
I changed the field to MEDIUMBLOB (up to 16 MB file) - it's saved OK now, but ... anyway not shown if to edit...

UPD: Out of memory error, if to make photo by the back camera...
Used

But anyway - not stable working. Sometimes "Error loading bitmap"

Images into BLOB fields were way too difficult to develop since I needed to incorporate base64 encoding/decoding. I am not sure what will happen with various devices and different camera settings. But anyway I am here to help on this, since I want to develop a more stable version.

aalekizoglou · Apr 12, 2013

jiangws said:
My log is like:

10.04.2013 11:55:28 - Host = 192.168.0.6
10.04.2013 11:55:28 - Port =
10.04.2013 11:55:28 - Charset = utf8
10.04.2013 11:55:28 - DBName = test
10.04.2013 11:55:28 - Username = xxxx
10.04.2013 11:55:28 - Password provided= xxxxxx
10.04.2013 11:55:28 - Query is JSON = {"SQL":"SELECT iContactID, bActive, strMaleFemale, strFirstName, strLastName, strEmail, dBirthdate, objPhoto, mNotes FROM contact LIMIT 100","params":{}}
10.04.2013 11:55:28 - Query = SELECT iContactID, bActive, strMaleFemale, strFirstName, strLastName, strEmail, dBirthdate, objPhoto, mNotes FROM contact LIMIT 100
10.04.2013 11:55:28 -
10.04.2013 11:55:28 - Trying to connect
10.04.2013 11:55:28 - Compressing Result. Size in bytes: 152
10.04.2013 11:55:28 - MySQL Error =

jiangsaw,

I suppose there is an error in you web server apache/php to connect to mysql without providing the MySQL port number. Before running the request from B4A, try to run from your browser something like

B4X:

http://[YOUR_URL]/mysqltunnel.php?host=192.168.0.6&port=3306&charset=utf8&dbname=test&username=xxxx&password=xxxxxx&query=SELECT * FROM contact

If this works, you might change

B4X:

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

to

B4X:

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

or edit the default mysql port in you server's php.ini

peacemaker · Apr 12, 2013

Thanks, aalekizoglou.
But found that only Debug-version of app is working. If to compile the release (obfuscated) - just progress-bar (release without obfuscation is OK !).
It's due to CallSub in your classes that use the sub name as variable...ohh, let's try to maybe fix...

peacemaker · Apr 13, 2013

DBMySQLConnection.ExecuteScript(sSQLParsed, "JobExecute_Done", "JobExecute_Failure", Me)

changes like this is needed, to be non-obfuscated

jiangws · Apr 13, 2013

Hi aalekizoglou,

Thank for your reply.

"host=192.168.0.6&port=3306"->wrong
"host=127.0.0.1&port=3306"->Ok
"host=127.0.0.1->Ok
"host=localhost&port=3306"->OK
"host=localhost->OK

but it will be wrong if I install webserver and mysql server in differcent machine.

aalekizoglou said:
jiangsaw,

I suppose there is an error in you web server apache/php to connect to mysql without providing the MySQL port number. Before running the request from B4A, try to run from your browser something like

B4X:

http://[YOUR_URL]/mysqltunnel.php?host=192.168.0.6&port=3306&charset=utf8&dbname=test&username=xxxx&password=xxxxxx&query=SELECT * FROM contact

If this works, you might change

B4X:

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

to

B4X:

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

or edit the default mysql port in you server's php.ini

jiangws · Apr 13, 2013

Hi peacemaker,

I changed to MEDIUMBLOB , It's OK.

Thank a lot of.

peacemaker said:
Now when DB field for the photo is BLOB, it's saved truncated (and cannot be shown without the error).
I changed the field to MEDIUMBLOB (up to 16 MB file) - it's saved OK now, but ... anyway not shown if to edit...

UPD: Out of memory error, if to make photo by the back camera...
Used

But anyway - not stable working. Sometimes "Error loading bitmap"

jiangws · Apr 13, 2013

About null after save new record

Hi aalekizoglou,

The name and surname are "NULL" after I save new record.

I revised the function of OnAfterPost:

B4X:

Sub OnAfterPost(DBMySQLDataset As ClsDBMySQLDataset)
   lstView.Clear
   Dim Bookmars As typBookmark = MySQLDataset.GetBookmark

   ProgressDialogHide   
   MySQLDataset.Execute
   
'   MySQLDataset.FirstRecord
'   Do While Not(MySQLDataset.EOF) 
'      Dim tl As TwoLines      
'      tl.First = MySQLDataset.FieldByName("strLastName").GetValue
'      tl.Second = MySQLDataset.FieldByName("strFirstName").GetValue
'      lstView.AddTwoLines2(tl.First, tl.Second, tl)
'      MySQLDataset.NextRecord
'   Loop
'   
'   If Bookmars <> Null Then MySQLDataset.GotoBookmark(Bookmars)
   ToastMessageShow("Record saved succesfully." , False)
   edtContacts.Text = MySQLDataset.RecordCount
   
End Sub

It works, but I think it's not best method.

aalekizoglou said:
You are right on this. As I said in the my first post, I have not dealt with the UI correctness. In a real application if you tap on add and then tap on Back without saving the dataset, then you should call cancel after you've created a hock on back key. Maybe present a MsgBox as well with a question.

aalekizoglou · Apr 15, 2013

jiangws said:
Hi aalekizoglou,

Thank for your reply.

"host=192.168.0.6&port=3306"->wrong
"host=127.0.0.1&port=3306"->Ok
"host=127.0.0.1->Ok
"host=localhost&port=3306"->OK
"host=localhost->OK

but it will be wrong if I install webserver and mysql server in differcent machine.

There should be no problem on this. At least this is not a problem of the mysqltunnel.php.

Just make sure you can connect to remote mysql on 192.168.0.6 from the machine running Web server using the username and password provided.
If this works there should no problem on
"host=192.168.0.6&port=3306"->wrong

aalekizoglou · Apr 15, 2013

peacemaker said:
changes like this is needed, to be non-obfuscated

peacemaker,

you are correct. Hadn't tried obfuscated before. What changes do we need to make that running in a obfuscated compilation?

peacemaker · Apr 15, 2013

Actually i had to made many changes in classes' sub names (adding "_"), but final requirement was "JobExecute_Done" and "JobExecute_Failure".

Some changes done and maybe no need:

'JSONDecoding Events
Public On_ErrorDecoding As String

'Execute Events
Public On_BeforeExecute As String
Public On_AfterExecute As String
Public On_ErrorExecute As String

'Insert Events
Public On_BeforeInsert As String
Public On_AfterInsert As String

'Edit Events
Public On_BeforeEdit As String
Public On_AfterEdit As String

'Post Events
Public On_BeforePost As String
Public On_AfterPost As String
Public On_ErrorPost As String

'Delete Events
Public On_BeforeDelete As String
Public On_AfterDelete As String
Public On_ErrorDelete As String

'Refresh Events
Public On_BeforeRefresh As String
Public On_AfterRefresh As String
Public On_ErrorRefresh As String

'Cancel Events
Public On_BeforeCancel As String
Public On_AfterCancel As String

'Scroll Events
Public On_BeforeScroll As String
Public On_AfterScroll As String

aalekizoglou · Apr 15, 2013

peacemaker,

OK. I saw that and made the changes too. I'll update the initial thread, too.

Thanks,

peacemaker · Apr 16, 2013

I'm digging a problem with

clsdbmysqldataset_updatebuffer (B4A line: 834)

Row = Dataset.Rows.Get(RecordNumber - 1)
java.lang.ArrayIndexOutOfBoundsException: length=12; index=-1

if the first record is already existing...

Totally i have 14 fields, and no Boolean used !
But RecordNumber = 0 and index=-1

I cannot understand why Boolean is found, i use DATA, TEXT, INT MySQL fields...

peacemaker · Apr 16, 2013

Seems,

'Initialize Record Pointer
RecordNumber = 0

is a mistake... i'm debugging with RecordNumber = 1...

And it's unclear - why INT field just after Posting is ftBOOLEAN in "UpdateRow".

aalekizoglou · Apr 17, 2013

peacemaker,

I cannot understand your problem. The datatypes for various fields from MySQL are reported from the tunnel. If you check function GetCorrectDataType you'll see that 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.

So 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. There is no boolean field there and that might be a problem.

Can you clarify the problem you have, so that I can understand where the problem is?

peacemaker · Apr 17, 2013

Aha! That this int(1) is those problem !
I used it for my INT fields, it's enough.

Now changed to int(2) - and problem solved
Thanks for the info, better to add some warning text to the first your post about this - i guess most of B4A users starting with MySQL do not know such detailes that is hidden limit.

peacemaker · Apr 20, 2013

Found next problem.
If to change the SQL requests, i try to clear old:

MySQL_Dataset.SQL.Clear
MySQL_Dataset.SQLRefresh.Clear
MySQL_Dataset.SQLInsert.Clear
MySQL_Dataset.SQLUpdate.Clear

but get

clsdbmysqldataset_parseparameters (B4A line: 1361)
End Sub
java.lang.IndexOutOfBoundsException: Invalid index 8, size is 7

I mean - is it possible for the initialised MySQL_Dataset to change the SQL requests ?
How correctly ?

I just full re-init is required for each new SQL request ?

rg58sma · Apr 21, 2013

I have the same

peacemaker said:
The same problem like mine. The PHP script returns not only JSON coded fields from the DB, but and some notice (or other text) about the PHP code execution.
And this text cannot be decoded.
Start your project in Debug mode and see the Job result where it's stopped in the code.

But anyway this solution looks like the most professional and universal, need just to debug well: as i see all other MySQL libs here do not work stable on any Android version.
This one, i hope, should be universal, as based on the standard for MySQL PHP code and the well known HTTP class.

I have the sameeee

aalekizoglou · Apr 22, 2013

peacemaker said:
Found next problem.
If to change the SQL requests, i try to clear old:

but get

I mean - is it possible for the initialised MySQL_Dataset to change the SQL requests ?
How correctly ?

I just full re-init is required for each new SQL request ?

I'll check on this. Think I know where the problem is. Will let you know.

B4A Library [Class] DBMySQL connect via MySQL Tunnel

Attachments

Member

Member

Member

Member

Expert

Expert

Member

Member

Member

Member

Member

Expert

Member

Expert

Expert

Member

Expert

Expert

Member

Member

Privacy & Transparency

Privacy & Transparency