[Chargeable] MSMariaDB - Another connector to MySQL

DonManfred · Feb 16, 2015

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.

Peter Simpson · Feb 20, 2015

Excellent library, it works absolutely perfect and it's fast too. But I wouldn't expect anything else from you

Next the Microsoft SQL library

I'm only kidding, or am I...

BarryW · Feb 21, 2015

Why you dont change your check_connection to boolean? So that easily monitor the connection.

DonManfred · Feb 21, 2015

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 · Feb 21, 2015

DonManfred said:
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
                  }

DonManfred · Feb 21, 2015

Thank you, @keirS i will give it a try.

BarryW · Feb 21, 2015

DonManfred said:
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 · Feb 21, 2015

BarryW said:
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

Peter Simpson · Feb 22, 2015

Thank you for all your feedback and support @DonManfred. At least you support your libraries and help developers with questions unlike ~~some library developers on here~~ one particular library developer on here...

Keep up the good work, cheers...

thedesolatesoul · Feb 22, 2015

Peter Simpson said:
At least you support your libraries and help developers with questions unlike some library developers on here.

Thats a bit harsh.

Peter Simpson · Feb 22, 2015

No not really, I'm actually only talking about one particular cheeky person @thedesolatesoul, I refuse the mention his or her name ever again on this great community. Don't you just love the ignore list

RandomCoder · Feb 24, 2015

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

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 · Feb 25, 2015

RandomCoder said:
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").

RandomCoder said:
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 · Feb 25, 2015

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 · Feb 25, 2015

RandomCoder said:
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

Thank you again

Peter Simpson · Feb 26, 2015

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 · Feb 26, 2015

Peter Simpson said:
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 · Mar 1, 2015

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.

DonManfred · Mar 1, 2015

it should support it.

If not then give me an database-snapshot including a stored procedure and i´ll add it. I dont have any stored procedures

JakeBullet70 · Mar 1, 2015

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!

[Chargeable] MSMariaDB - Another connector to MySQL

Expert

Expert

Active Member

Expert

Well-Known Member

Expert

Active Member

Expert

Expert

Expert

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member