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,049

Swissmade

Well-Known Member
Licensed User
Longtime User
Helle swissmade,

I've tried this, but it always returns TRUE:
B4X:
Sub InitDatabases(Naam As String, Wachtwoord As String) As Boolean
    Try
        myDB.Initialize("192.168.1.103", "dbtest", Naam, Wachtwoord, "3306")
        If myDB.IsInitalized Then
            Try
                myDB.ExecQuery("Versie", "SELECT * FROM `sysversies` ORDER BY versienummer DESC LIMIT 1")
                Return True
            Catch
                Return False
            End Try
        End If
    Catch
        Return False
    End Try
End Sub

My log shows the error for the unauthorized user.

Regard, Marcel

Hi Marcel

Try something like this
B4X:
If SQLFORFASTACCESS.IsInitialized = False Then
     SQLFORFASTACCESS.Initialize2(SQLDriver, SQLJDBC & Main.DATABASEIP & ":" & Main.DBServerPort & "/dbtest?characterEncoding=utf8", Naam, Wachtwoord)
End If

Also I think you have no right Username in the database.
If you have no Manager for the Database use HeidiSQL.
 

keirS

Well-Known Member
Licensed User
Longtime User
Helle swissmade,

I've tried this, but it always returns TRUE:
B4X:
Sub InitDatabases(Naam As String, Wachtwoord As String) As Boolean
    Try
        myDB.Initialize("192.168.1.103", "dbtest", Naam, Wachtwoord, "3306")
        If myDB.IsInitalized Then
            Try
                myDB.ExecQuery("Versie", "SELECT * FROM `sysversies` ORDER BY versienummer DESC LIMIT 1")
                Return True
            Catch
                Return False
            End Try
        End If
    Catch
        Return False
    End Try
End Sub

My log shows the error for the unauthorized user.

Regard, Marcel


IsInitalized just indicates that you have initialized the library with the your DB connection settings. To trap errors you need to create an error sub

B4X:
Sub Versie_error (trace As String)
      


End Sub

The trace string contains the whole stack trace for the error. So you can trap various errors in your sub. A point to note is Initialize doesn't actually create a connection to the DB. Connections are created and closed for each library call.
 

wineos

Member
Licensed User
Longtime User
Sorry, but I really don't know how to solve my problem: if my MySQL database can only accept localhost executions, how can I use any one of libraries?

Can anyone help me? Thanks a lot!
 

vbmundo

Well-Known Member
Licensed User

vbmundo

Well-Known Member
Licensed User
Hi,

I have a Table with over 5.000 records.

I see that EXECQUERY don't read all records.. with "SELECT * FROM TABLE"

I have running many times the same query and always get different amounts of records, which I think this script has a Bug

Can you check ?

Regards
 

DonManfred

Expert
Licensed User
Longtime User
Sorry, but I really don't know how to solve my problem: if my MySQL database can only accept localhost executions, how can I use any one of libraries?

If your provider only accept localhost connections then you are lost. You need to use another solution. Like RDC, RDC2 or a php-script as man-in-the-midle
 

Swissmade

Well-Known Member
Licensed User
Longtime User
Hi,

I have a Table with over 5.000 records.

I see that EXECQUERY don't read all records.. with "SELECT * FROM TABLE"

I have running many times the same query and always get different amounts of records, which I think this script has a Bug

Can you check ?

Regards

Hi VBmundo,
Please show some code how you connect and execute.
 

keirS

Well-Known Member
Licensed User
Longtime User
Hi,

I have a Table with over 5.000 records.

I see that EXECQUERY don't read all records.. with "SELECT * FROM TABLE"

I have running many times the same query and always get different amounts of records, which I think this script has a Bug

Can you check ?

Regards

Can you run in debug mode and post the unfiltered log file?
Are you calling the library from an activity or a service?
Try my other library: https://www.b4x.com/android/forum/t...ed-procedure-support-and-cursors.51599/page-1 does it give the right result?
 

vbmundo

Well-Known Member
Licensed User

keirS

Well-Known Member
Licensed User
Longtime User
I initialize the MariaDBConnection into the Activity..... what you recommend ?nerate

Regards

For 5000 records I would use a service. For very large query results I use stored procedures which use the INTO OUTFILE clause of the SELECT statement to generate a text file which is then downloaded and processed.
 

vbmundo

Well-Known Member
Licensed User
For 5000 records I would use a service. For very large query results I use stored procedures which use the INTO OUTFILE clause of the SELECT statement to generate a text file which is then downloaded and processed.

Keirs, My APP will allow to you write your own SQL Stattement... this APP is a MySQL Manager for Android. you wil give with it, 100 % Control of your Remote Database, Create Tables, Run Queries, Export Data, and Modify Structures and data.

Do you understand ?
 

vbmundo

Well-Known Member
Licensed User
Look

I add this controls and variables in Start Services

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public Db As MariaDBConnector
    Public DbT As MariaDBConnector
    Public MySQL_Server As String=""
    Public MySQL_Port As Int
    Public MySQL_User As String=""
    Public MySQL_Password As String=""

End Sub

And I have deleted from Activity but now in Activity I have UNDECLARED errors.
 

vbmundo

Well-Known Member
Licensed User
For 5000 records I would use a service. For very large query results I use stored procedures which use the INTO OUTFILE clause of the SELECT statement to generate a text file which is then downloaded and processed.

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
 
Top