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.

 

[Douglas Farias]

@keirS its possible , close the connection?
and how to use for in the maps?

for example
select * from users

it make a map to all user, how to get this info ?

 

[BarryW]

Can this library insert a blob?

 

[DonManfred]

Can this library insert a blob?

Actually no i think. Have not tested it. But as the lib does not have any Prepared Statement-methods i think it is actually not possible. Or maybe i just dont know how

Edit: Sorry, i did not look correctly... It is not my thread.
So please ignore my previous answer I was meant to answer to my msMariaDB Library

 

[keirS]

You can use ExecNonQuery to insert a blob which effectively runs a statement. Sort of dependent on the size of the blob though. The version of the Maria DB driver used does not support streaming data to a blob which is a more reliable way of writing blobs to MySQL.

 

[BarryW]

So it cannot read blob? i am right?

 

[keirS]

It can definitely read a BLOB. It reads it in as a Byte stream..

 

[BarryW]

Can you post an example on how to insert a blob sir . . . Tnx . . .

 

[keirS]

Can you post an example on how to insert a blob sir . . . Tnx . . .

What do you want to store to the BLOB? An image or a file?

 

[BarryW]

An image... maybe an image that is converted into bytes. Or if it is not possible then just insert an image on ur way. Tnx.

 

[keirS]

Here you go.

Using the database:
testimg

Using a table imglist :
create table imglist (imgno int, image blob)

What I am doing is:

1. Drawing a red circle on a canvas
2. Using MFLib to Base64 encode the canvas's bitmap to a string.
3. Inserting the a row into the imglist table with the encoded bitmap
4. Querying the imglist table
5. Decoding the encoded bitmap and displaying it in an Imageview


One thing you may have to do is insert mysql escape characters into the encoded string. Not sure if this is necessary or not. It wasn't in the example.

Sub Process_Globals
   
    Dim MYSQLIP = "172.25.0.201" As String
    Dim MYSQLDBNAME = "testimg"  As String
    Dim MYSQLPORT = "3306"  As String
    Dim MYSQLUSER = "****"  As String
    Dim MySQLPASS = "****"  As String
    Dim MySQLConnection As MariaDBConnector
    Dim img64 As MF_Image
End Sub

Sub Globals

 Dim bBitmap As Bitmap
 Dim cPic As Canvas
 Dim iPic As ImageView
Dim sImgStr As String 
End Sub


Sub Activity_Create(FirstTime As Boolean)
    'Connect of database testimg
    MySQLConnection.Initialize(MYSQLIP,MYSQLDBNAME,MYSQLUSER, MySQLPASS,MYSQLPORT)
     
    Dim sInsertStatement As String
   
    'Initalize image view
    iPic.Initialize("")
    'add imagview to activity
    Activity.AddView(iPic,1,1,100%x,100%y)
   
    'Create mutable bitmap
    bBitmap.InitializeMutable(500,500)
   
    'Initalize canvas to use the bitmap
    cPic.Initialize2(bBitmap)
   
    'draw a red circle
    cPic.DrawCircle(100,100,50,Colors.Red,True,0)
   
    'encode the canvas bitmap to a stirng
    sImgStr = img64.BitmapToString(cPic.Bitmap,100)
   
    'Insert the string into the IMGLIST table in image column
    sInsertStatement = "INSERT INTO IMGLIST (imgno, image) VALUES(1," & "'" & sImgStr & "'" & ")"
    MySQLConnection.ExecNonQuery("insert",sInsertStatement)
   
     
    End Sub
   
   
   
    Sub insert_error(error As String)
      Log (error)
    End Sub
   
    Sub insert_complete(updatecount As Int)
       'After the insert is completed query the imglist table
        MySQLConnection.ExecQuery("query","SELECT * FROM IMGLIST")
    End Sub
   
   
    '"SELECT * FROM IMGLIST" query update
    Sub query_update(record As Map)
       
        If record.ContainsKey("image") Then
          'Decode the image from a Base64 sring and display it in theIimageview
          iPic.Bitmap = img64.StringToBitmap(record.Get("image"))
          iPic.Visible = True
        End If 
   
    End Sub
   
    Sub query_error(error As String)
        Log(error)
    End Sub
   
    Sub query_complete(finished As Boolean)
   
    End Sub
   


Sub Activity_Pause (UserClosed As Boolean)

End Sub

 

[BarryW]

How about if it is a file?

 

[keirS]

Read the file into a byte array and then use Agraham's Base64 library to encode the byte array to a string.

 

[Swissmade]

Hi all,
Just one question,
Can this libraries be used with B4J???

 

[Erel]

B4J jSQL library can connect to all types of databases.

 

[Swissmade]

B4J jSQL library can connect to all types of databases.

Ok thanks

 

[krissam]

A new question for keirS.

I'm trying your mariadb library with this piece of code :

Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.
Dim MYSQLIP = "192.168.x.x" As String
Dim MYSQLDBNAME = "Dwh_Unimag" As String
Dim MYSQLPORT = "3306" As String
Dim MYSQLUSER = "xxx" As String
Dim MySQLPASS = "xxxxxxxxxxxx" As String
Dim MySQLConnection As MariaDBConnector
End Sub

Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.

End Sub

Sub Activity_Create(FirstTime As Boolean)
'Do not forget to load the layout file created with the visual designer. For example:
'Activity.LoadLayout("Layout1")
If FirstTime Then
MySQLConnection.Initialize(MYSQLIP,MYSQLDBNAME,MYSQLUSER, MySQLPASS,MYSQLPORT)
End If
If MySQLConnection.IsInitalized Then
'ExecQuery all result values returned as strings
MySQLConnection.ExecQuery("structure","select id_structure, id_SAP, nom, nom_responsable from Structure")
End If
End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub structure_update(structure As Map)
Log( structure.Size)
For i = 0 To structure.Size-1
Log("| " & structure.GetKeyAt(i) &" | " & structure.GetValueAt(i))
Next
End Sub

Sub structure_complete(finished As Boolean)

End Sub

Sub structure_error(trace As String)
Log(trace)
End Sub

My questions are :

1. If a make a mistake in the password, I get a java error. How can I check this, to send a message to the user ?
2. What if the best way to fill a scrollview or a listview ?
3. Is there a notion fo cursor ?

Thnaks for your help.
Regards

 

[Swissmade]

1. If a make a mistake in the password, I get a java error. How can I check this, to send a message to the user ?

First of all please use Code Insert this is making read code much better.
You Can use

Try
***** 'Your Code Here'
catch
log(Lastexeption.message)
end try

 

[keirS]

A new question for keirS.




My questions are :

1. If a make a mistake in the password, I get a java error. How can I check this, to send a message to the user ?
2. What if the best way to fill a scrollview or a listview ?
3. Is there a notion fo cursor ?

Thnaks for your help.
Regards

1. As per what swissmade says above.
2. Depends on the size of the listview. You could add the each item to the listview in the update sub.
3. Sorry don't understand what you mean by this?

 

[MarFraWare]

First of all please use Code Insert this is making read code much better.
You Can use

Try
***** 'Your Code Here'
catch
log(Lastexeption.message)
end try

Helle swissmade,

I've tried this, but it always returns TRUE:

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