B4A Library Asynchronous MariaDB/MySQL Library

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.

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 Sub

To use download the zip file; extract the files and copy them in to your additional libraries folder.

 

[Erel]

Note that you can use JdbcSQL with this driver.

 

[Jose Briceño]

Thanks for answering .. but I do not know how to start with JdbcSQL. I would be very grateful if there is an example

 

[DonManfred]

but I do not know how to start with JdbcSQL. I would be very grateful if there is an example

What is wrong with the example available?
https://www.b4x.com/android/forum/t...ly-connect-to-remote-databases.84016/#content
Tip: First check the existence. Ask for help if not.