There are a couple of MySQL libraries already available. They all perform network functions on the main thread however which means you are limited to using target API's that are pre Honeycomb level. This is an asynchronous library so it should work with the higher API levels (it has only been tested on Jellybean and KitKat though).
This library uses the MariaDB JDBC driver. It also works with MySQL as MariaDB is a fork of the open source version of MySQL. The JDBC driver is GNU LGPL licensed and I am releasing the B4A library under the same licence.
A significant advantage over the MySQL JDBC connector is the MariaDB driver is just over 200kb in size as opposed to 800kb for the MySQL connector. It's LPGL licensing means it is royalty free whereas you will need to pay Oracle a licence fee to use the MySQL connector in a commercial environment.
One thing to note is that the library uses streaming. It fetches the results for a query from the server a single record at a time and passes them to the update sub. This minimizes memory usage but can be significantly slower than returning the whole result set in one go. For this reason I strongly recommend you only use this library from a service for anything but the most trivial of queries.
The library is really only suitable for "in house" type apps (which is what I am using it for). I will not offer any support for and do not recommend using it in commercial applications available in the Play (or any other) store.
The sample code below uses a table from the sample database from www.MySQLTutorial.org. I have tried to keep it as simple as possible so the function names mimic the B4A SQL library for SQLlite.
To use download the zip file; extract the files and copy them in to your additional libraries folder.
This library uses the MariaDB JDBC driver. It also works with MySQL as MariaDB is a fork of the open source version of MySQL. The JDBC driver is GNU LGPL licensed and I am releasing the B4A library under the same licence.
A significant advantage over the MySQL JDBC connector is the MariaDB driver is just over 200kb in size as opposed to 800kb for the MySQL connector. It's LPGL licensing means it is royalty free whereas you will need to pay Oracle a licence fee to use the MySQL connector in a commercial environment.
One thing to note is that the library uses streaming. It fetches the results for a query from the server a single record at a time and passes them to the update sub. This minimizes memory usage but can be significantly slower than returning the whole result set in one go. For this reason I strongly recommend you only use this library from a service for anything but the most trivial of queries.
The library is really only suitable for "in house" type apps (which is what I am using it for). I will not offer any support for and do not recommend using it in commercial applications available in the Play (or any other) store.
The sample code below uses a table from the sample database from www.MySQLTutorial.org. I have tried to keep it as simple as possible so the function names mimic the B4A SQL library for SQLlite.
B4X:
Sub Process_Globals
Dim MYSQLIP = "172.25.0.42" As String
Dim MYSQLDBNAME = "classicmodels" As String
Dim MYSQLPORT = "3306" As String
Dim MYSQLUSER = "user1" As String
Dim MySQLPASS = "pass1" As String
Dim MySQLConnection As MariaDBConnector
Dim StockValue As Double : StockValue = 0
End Sub
Sub Globals
End Sub
Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
MySQLConnection.Initialize(MYSQLIP,MYSQLDBNAME,MYSQLUSER, MySQLPASS,MYSQLPORT)
End If
'ExecQuery all result values returned as strings
MySQLConnection.ExecQuery("query","select * from products")
'Batch updates into a transaction
MySQLConnection.AddNonQueryToBatch("UPDATE products SET quantityInStock = 123 where productCode = 'S12_1099'")
MySQLConnection.AddNonQueryToBatch("UPDATE products SET buyPrice = 60 where productCode = 'S700_3167'")
MySQLConnection.ExecuteNonQueryBatch("updateproducts")
'ExecQuery2 all result values returned as native types
MySQLConnection.ExecQuery2("getstockvalue","select * from products")
'Will Cause an Error
MySQLConnection.ExecQuery("query","select * from product")
'Singl update
MySQLConnection.ExecNonQuery("updateSingle","UPDATE products SET MSRP = 82 where productCode = 'S700_3167'")
End Sub
Sub Activity_Pause (UserClosed As Boolean)
End Sub
Sub query_update(product As Map)
If product.ContainsKey("productName") Then
Log(product.get("productName"))
End If
End Sub
Sub query_complete(finished As Boolean)
End Sub
Sub query_error(trace As String)
Log(trace)
End Sub
Sub updateProducts_complete(UpdateCount As Int)
Log(UpdateCount)
End Sub
Sub updateProducts_error(trace As String)
Log(trace)
End Sub
Sub getstockvalue_update(product As Map)
Dim StockQTY As Int
Dim StockPrice As Double
StockPrice = product.get("buyPrice")
StockQTY = product.get("quantityInStock")
StockValue = StockValue + (StockPrice * StockQTY)
End Sub
Sub getstockvalue_complete(finished As Boolean)
Log(NumberFormat(StockValue,0,2))
End Sub
Sub getstockvalue_error(trace As String)
Log(trace)
End Sub
Sub updatesingle_error( Trace As String )
End Sub
Sub updatesingle_complete(updatecount As Int)
Log(updatecount)
End SubTo use download the zip file; extract the files and copy them in to your additional libraries folder.
