[Chargeable] MSMariaDB - Another connector to MySQL

DonManfred

Expert
Licensed User
MSMariaDB
Version:
1.05

This is a Library which uses the MariaDB-Connector to connect to a MySQL-Database.
Advantage here is that you can use the Connector without need for a Oracle licence.
AND the MariaDB-Connector is a real light-weight connector too. The connector is just approx 240kb

MSMariaDB
Version:
1.06
  • MSMaria
    Events:
    • BatchResult (batch As Map)
    • CallableResult (data as List As , meta As Map)
    • ExecResult (meta As Map)
    • ListTables (tables As List, ms as Long As )
    • QueryResult (data as List As , meta As Map)
    • QueryResult2 (data as List As , meta As Map)
    • Status (Connected As Boolean, ReConnecting As Boolean, RetriesLeft As Int)
  • Methods:
    • CloseDatabase
      Closes the database
      Example:<code>
      db.closedatabase
      </code>
    • DisableReconnect
      Disable automatic auto_reconnect if the MySQL Database Connection is lost
      By default this is enabled
    • EnableReconnect
      Enable automatic auto_reconnect if the MySQL Database Connection is lost
      By default this is enabled.
    • ExecuteASync (query As String, Task As String)
      executes ONE SQL-Commands (insert, update, delete)
      Example:<code>
      db.executeasync("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
      </code>
    • ExecuteBatchASync (batch As List, Task As String)
      executes a batch of SQL-Commands (insert, update, delete)
      Example:<code>
      Dim batch As List
      For i=1 To 100
      batch.Add("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
      Next
      db.executebatchasync(batch)
      </code>
    • Initialize (event As String, host As String, user As String, password As String, Database As String)
      Initialize the Library
      the url to your database. You dont need to prefix it with
      jdbc:mysql:// as this will be done automatically


      Example:<code>
      db.Initialize("eventname","mydbdomain.com","dbusername","dbpassword","dbname")</code>
    • ListTablesAsync
      Get a list of all tables inside this catalog (database)
      The event listtables will be raised
      Example:<code>
      db.ListTablesAsync

      Sub sql_listtables(tables As List)
      Log("sql_listtables()")
      For i=0 To tables.Size-1
      Log("Table "&tables.Get(i))
      Next
      End Sub
      </code>
    • QueryASync (query As String, Task As String)
      Query the Database. When the Method finishes the event QueryResult
      will be raised
      QueryResult gets two values. A "List of Maps" for the results. Each
      Item in the List contains a Map holding the Values from on Resultrow
      The Second value is a Map containing some informations:
      ColumnCount, RecordCount and time elapsed in ms for the query

      Example:<code>
      db.queryasync("select * from members LIMIT 0,1 ;")</code>
    • QueryASync2 (query As String, Task As String)
      Query the Database. When the Method finishes the event QueryResult2
      will be raised
      QueryResult gets two values. A "List of Strings" for the results. Each
      Item in the List contains a String holding the Values from on Resultrow
      in the format "["+field1+","+field2+"]"

      The Second value is a Map containing some informations:
      ColumnCount, RecordCount and time elapsed in ms for the query
      Example:<code>
      db.queryasync2("select * from members LIMIT 0,1 ;")</code>
    • SelectDB (database As String) As Boolean
      Manually select the database to Query.
    • check_connection
    • check_connection2 As Boolean
    • isReconnectEnabled As Boolean
      Test whether or not automatic reconnect is currently enabled.
      By default automatic auto_reconnect is enabled
      Return type: @return:true if automatic auto_reconnect is enabled, false if it is disabled
  • Permissions:
    • android.permission.ACCESS_NETWORK_STATE
    • android.permission.INTERNET
  • Properties:
    • ReconnectNumRetry As Int
      Returns the maximum number of automatic reconnection attempts before giving
      up and throwing an exception.

      If this value was not changed with {@link #setReconnectNumRetry(int)} the default
      number of attempts is 15
    • ReconnectTime As Int
      Returns the waiting time before attempting to auto_reconnect to the MySQL
      Database server.

      If this value was not changed with {@link #setReconnectTime(int)} the default
      waiting time is 5 seconds

The Library is no longer available.
 
Last edited:

DonManfred

Expert
Licensed User
check_connection is more likely meant to be a "ping".
When using the method the lib will do a select BOOL. If the connection is available then this command works. If the connection is not avaiable an exception will occur and force the lib to reconnect to the db.
There is no "real" state of such a connection i can read out of the java-connector (or maybe i just did not found this in the maria-db-connector :))... That´s the reason i added the status event to inform the app about the status as good as i can.
 

keirS

Well-Known Member
Licensed User
check_connection is more likely meant to be a "ping".
When using the method the lib will do a select BOOL. If the connection is available then this command works. If the connection is not avaiable an exception will occur and force the lib to reconnect to the db.
There is no "real" state of such a connection i can read out of the java-connector (or maybe i just did not found this in the maria-db-connector :))... That´s the reason i added the status event to inform the app about the status as good as i can.

B4X:
conn = DriverManager.getConnection(dbURL, dbUserName, dbPassword);
if(conn.isValid(10) == true) {
                      //do query
                  } else {
                     //try to reconnect
                  }
 

BarryW

Active Member
Licensed User
check_connection is more likely meant to be a "ping".
When using the method the lib will do a select BOOL. If the connection is available then this command works. If the connection is not avaiable an exception will occur and force the lib to reconnect to the db.
There is no "real" state of such a connection i can read out of the java-connector (or maybe i just did not found this in the maria-db-connector :))... That´s the reason i added the status event to inform the app about the status as good as i can.
Meaning... if we connect to an existing ip without mysql db on it, it will also log as connected...
 

DonManfred

Expert
Licensed User
Meaning... if we connect to an existing ip without mysql db on it, it will log as connected...
No. Yo usually do a connect before. And this will not work
Additionally the command will reconnect and at least then it will raise an exception.
Due to the method does not return a resultcode you are forced to watch the status-event.
It will not raise an connected true in your case i believ
 

RandomCoder

Well-Known Member
Licensed User
Sorry if this is the wrong place to be posting this question but it is directly related to MSMariaDB and the MSMySQL-Library. I'm reasonably familiar with relational databases and database design having spent several years administrating an MS Access Maintenance & Asset Care System which had some pretty horrendous SQL queries. The database was split into a front-end (containing all the views) and a back-end (containing the data) stored locally on a company network, MS Access took care of all the connection stuff.

Now this leads to my question... under what circumstances would someone choose to use one of these or the other libraries allowing direct connection to a database as opposed to using HTTP and PHP scripts? I've read @KMatle excellent tutorials but now I'm confused as to what I should base my design decisions on o_O

My basic understanding (please correct me if I'm wrong), MSMySQL-Library and any other library utilising a MSMySQL database connector allows direct connection to the database but will need to be licensed with Oracle if making a commercial App. MSMariaDB also connects directly to the database and is completely free to use. But do both of these solutions allow connecting to the database across the WEB or are they only suited to LAN's?
HTTP and PHP is another free solution but the app is not connecting directly to the database, it is instead calling PHP scripts to access the database which in turn updates or returns data. This method works across the WEB and also on LAN's, but what is the benefit of using PHP as it would appear to be a more technically challenging approach (especially to someone like me who knows no PHP!), are there security benefits?

I'd value your opinions on the subject.

Thanks,
RandomCoder
 

DonManfred

Expert
Licensed User
MSMySQL-Library and any other library utilising a MSMySQL database connector allows direct connection to the database but will need to be licensed with Oracle if making a commercial App. MSMariaDB also connects directly to the database and is completely free to use. But do both of these solutions allow connecting to the database across the WEB or are they only suited to LAN's?
No, they are work over web too (if the database is accessible from "outside").
are there security benefits?
Sure.
PLUS on php solution:
- NO SQL-Query is compiled in your app. All the logic is inside the php-script
- Databasepassword does not be compiled in your app.
MINUS on php:
- Attackable over internet if not good secured
- more complicated if not familar with php

PLUS on MySQL-libs
- Faster
- You dont have to deal with httputils and json-results to get the data. From MY libs you will get a LIST of MAPs with the resultdata directly. No need to face working with json
MINUS on MySQL Libs
- You can not use a DBCursor like in the RDC solution
- You need to store the mysql-credentials in your app. Most probably in plain ascii if not using any encryption. So someone who is familar with decompiling an apk may find the credentials

PS: Thank you again for registering my libs, Alan :)
 

RandomCoder

Well-Known Member
Licensed User
Thank you for taking the time to explain this to me Manfred. I wanted to donate more for you're library but there was no option (that I could see) to change the amount. Instead I will purchase you're other library too. I appreciate the time and effort you and many of the other B4A users put into making B4A and its forum even better.

Cheers,
RandomCoder :)

Ps. unfortunately I won't get chance to play with your library until tonight as work now beckons :(
 

DonManfred

Expert
Licensed User
Instead I will purchase you're other library too.
i just was wondering. Thank you very much. I really appreciate it. This reminds me to better build a Button where the buyer can put whatever he want :)
I did such button for one of my other libs.

You can donate as much you want here :D


Thank you again :)
 
Last edited:

Peter Simpson

Expert
Licensed User
Hiya @RandomCoder. I have developed a number of MSSQL and MySQL solution on Android, but the one thing that they all have in common is that I always use libraries to connect to database. The PHP and RDC solutions are good solutions but they are just not for me. I prefer one less connection to worry about and a direct connection to either an MSSQL or MySQL server without anything before that connection is perfect for me, thus using a library is my preferred solution. MSMariaDB is extremely fast and simple to use and I've not found any issues with it whatsoever.
 

RandomCoder

Well-Known Member
Licensed User
Hiya @RandomCoder. I have developed a number of MSSQL and MySQL solution on Android, but the one thing that they all have in common is that I always use libraries to connect to database. The PHP and RDC solutions are good solutions but they are just not for me. I prefer one less connection to worry about and a direct connection to either an MSSQL or MySQL server without anything before that connection is perfect for me, thus using a library is my preferred solution. MSMariaDB is extremely fast and simple to use and I've not found any issues with it whatsoever.
Thank you Peter for the reply. Manfred's library is what has finally spurred me to make more progress with my Asset Care app. At the moment it's a concept that I'm working on. But it may have a real use in the company I work for and who know's, I may open it to a wider audiance if I achieve something useful?
 

JakeBullet70

Well-Known Member
Licensed User
Hi all.

I am getting my MySQL database ready for connecting to my Android app and while I was writing the 1st stored procedure I though, Can the MSMariaDB lib support this? I hate to say it but I love sprocs... ;)

Thanks.
 

JakeBullet70

Well-Known Member
Licensed User
Thanks @DonManfred. I am will give it a shot and let you know. If not I can just get you a connection to my test DB and you can play with it there.

Thanks again!
 
Top