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.

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 Sub

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

Attachments

  • mariadblibray.zip
    214.5 KB · Views: 1,050

keirS

Well-Known Member
Licensed User
Longtime User
My APP will be a MySQL Manager to Android APP

I don't know what SQL Stattement will run the users..

Their will run a 5000 rows SQL or 5.000.000, of couse this last query will fail

I don"t think the library is suitable for such an app. It doesn't support SSL connections so is not secure. If you want to write such an app I would look at using SSH and the MySQL CLI.

It will handle 5000 records with no problem though. My test data set has tables with over 100,000 records and I have an app which duplicates the whole lot on a local SQLLite database using this library.


For any more help you will need to post your full code as it's impossible to see what's happening with what you have posted. Also you will need to post the unfiltered logcat file as I have already requested.
 

vbmundo

Well-Known Member
Licensed User
I don"t think the library is suitable for such an app. It doesn't support SSL connections so is not secure. If you want to write such an app I would look at using SSH and the MySQL CLI.

It will handle 5000 records with no problem though. My test data set has tables with over 100,000 records and I have an app which duplicates the whole lot on a local SQLLite database using this library.


For any more help you will need to post your full code as it's impossible to see what's happening with what you have posted. Also you will need to post the unfiltered logcat file as I have already requested.

hi,

What MySQL control can you recommend that my ?
 

keirS

Well-Known Member
Licensed User
Longtime User
Forget the SSL, are some MySQL/MariaDB control that you recommend to me, much better than this ?

I haven't used any of the other libraries apart from my own so I am not in a position to recommend them. Perhaps you missed the bit in the original post about this library:

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.
 

keirS

Well-Known Member
Licensed User
Longtime User
I recommend you to use jRDC2 to connect to remote databases. It is safer than connecting the clients directly to the database and it also supports SSL.

Erel are you saying that JRDC2 supports SSL between the JDBC driver and a MySQL server? If I have a Java server running JRDC2 and a MySQL server then how do I tell JRDC2 which truststore/keystore file to use and the passwords for them which are required to enable an SSL between the two?
 

vbmundo

Well-Known Member
Licensed User
I recommend you to use jRDC2 to connect to remote databases. It is safer than connecting the clients directly to the database and it also supports SSL.

Hi Erel, have you any example to use this jRDC2 ?

KeirS have a great MariaDB/MySQL control, but he said that It's not safe.

Regards
 

vbmundo

Well-Known Member
Licensed User
I haven't used any of the other libraries apart from my own so I am not in a position to recommend them. Perhaps you missed the bit in the original post about this library:

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.

Thanks !!
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
Erel are you saying that JRDC2 supports SSL between the JDBC driver and a MySQL server?
It might be possible. However what I meant is that you can run jRDC on the same server that the database runs and the connection between the clients and jRDC will be secured with SSL.

Hi Erel, have you any example to use this jRDC2 ?
This is not the correct place to discuss jRDC2. See these search results: https://www.b4x.com/search?query=RDC
 

vbmundo

Well-Known Member
Licensed User
Hi Erel,

You said
I recommend you to use jRDC2 to connect to remote databases. It is safer than connecting the clients directly

My application will allow connected to their user databases, where they charged to hand his credentials, which is why we need the necessary arrangements.

The user can't install any JRDC in their server...
 

carlos7000

Well-Known Member
Licensed User
Longtime User
Hi.
Using the library, sends the data correctly, but always the function that collects errors runs. The variable 'trace' stores the following message:

B4X:
java.lang.NullPointerException: Attempt to read from field 'int org.mariadb.jdbc.internal.mysql.MySQLProtocol.datatypeMappingFlags' on a null object reference
    at org.mariadb.jdbc.MySQLResultSet.getMetaData(MySQLResultSet.java:402)
    at db.b4amariadb.mariadbconnector$1.run(mariadbconnector.java:138)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:423)
    at java.util.concurrent.FutureTask.run(FutureTask.java:237)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
    at java.lang.Thread.run(Thread.java:833)

I suppose that may be require updating the file MariaDB-java-client-1.1.5.jar
 

Swissmade

Well-Known Member
Licensed User
Longtime User
You are using an very old version
Here it is
 

Attachments

  • mariadb-java-client-1.4.2.jar
    363 KB · Views: 261

keirS

Well-Known Member
Licensed User
Longtime User
Hi.
Using the library, sends the data correctly, but always the function that collects errors runs. The variable 'trace' stores the following message:

B4X:
java.lang.NullPointerException: Attempt to read from field 'int org.mariadb.jdbc.internal.mysql.MySQLProtocol.datatypeMappingFlags' on a null object reference
    at org.mariadb.jdbc.MySQLResultSet.getMetaData(MySQLResultSet.java:402)
    at db.b4amariadb.mariadbconnector$1.run(mariadbconnector.java:138)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:423)
    at java.util.concurrent.FutureTask.run(FutureTask.java:237)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
    at java.lang.Thread.run(Thread.java:833)

I suppose that may be require updating the file MariaDB-java-client-1.1.5.jar

Not seen that before. You could try the later version of the client library posted above.
 

Jose Briceño

Member
Licensed User
how to make transactions:
I was holding it like that and it's wrong
Main.MySQL1.BeginTransaction
Try
Main.MySQL1.ExecNonQuery ("INS1", "INSERT INTO
Catch
Log (LastException.Message)
End Try
Main.MySQL1.EndTransaction
 
Top