[chargeable] MSMySQL - Yet another MySQL-Library (but a FAST one :-))

DonManfred

Expert
Licensed User
Longtime User
This Library can be used to connect your Device to a MySQL database. The Library use a direct connection to the MySQL databaseserver.

Requisites: The Database must be accessible from "outside"
DisAdvantages: Due to the app needs the Databasecredentials (including username and password). There Credentials must be included in your App. I suggest using this Library only for private or company intern use.


MSMySQL
Version:
1.06
  • MySQL
    Events:
    • BatchResult (batch 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>
    • ExecutePeparedStatement
    • 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>
    • PeparedStatement (sql As String)
    • 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.
    • SetPeparedBlob (parameterIndex As Int, imagepath As String)
    • SetPeparedInt (parameterIndex As Int, x As Int)
    • SetPeparedLong (parameterIndex As Int, x As Long)
    • SetPeparedString (parameterIndex As Int, x As String)
    • check_connection
    • 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
    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
 
Last edited:

DonManfred

Expert
Licensed User
Longtime User
Example
B4X:
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    '             Event  Database Host          Username   Password           Databasename
    'db.Initialize("sql","host.com","dbuser","dbpassword","database")
End Sub
Sub Activity_Resume
    Log("Activity_Resume()")
    db.ListTablesAsync
    db.QueryASync("select * from b4alog ORDER BY log_id ASC;")

    Dim batch As List  
    batch.Initialize
    For i=1 To 100
        batch.Add("INSERT INTO b4alog SET log_value='Test"&i&"', log_time="&DateTime.Now&";")
    Next  
    db.ExecuteBatchASync(batch)

    db.ExecuteASync("INSERT INTO b4alog SET log_value='only one query', log_time="&DateTime.Now&";")  
End Sub
Sub MySQL_ExecResult(meta As Map)
    Log(meta)  
End Sub
Sub MySQL_BatchResult(batch As Map)
    Log(batch)  
End Sub
Sub MySQL_QueryResult(data As List, meta As Map)
    'Dim m As Map = meta
    Log("MySQL_QueryResult("&meta&")")
    For i=0 To data.Size-1
        'Log("r:"&data.Get(i))
    Next  
End Sub
Sub MySQL_ListTables(tables As List, ms As Long)
    Log("MySQL_ListTables("&ms&"ms)")
    For i=0 To tables.Size-1
        Log("Table "&tables.Get(i))
    Next
End Sub
 
Last edited:

DonManfred

Expert
Licensed User
Longtime User
Example 2 - Showing two tables from the Northwinddatabase including images in a Listview

B4X:
Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
  Dim db As MySQL
    Private lv1 As ListView
    Private lv2 As ListView
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("Main")
    db.Initialize("sql","host.com","dbuser","dbpassword","database")
End sub
Sub Activity_Resume
    Log("Activity_Resume()")
    lv1.Clear
    lv1.AddSingleLine2("Categories","Categories")
    lv1.AddSingleLine2("Employees","Employees")
End Sub
Sub lv1_ItemClick (Position As Int, Value As Object)
    Dim t As String = Value
    db.QueryASync("select * from "&t&";",t)
End Sub
Sub MySQL_QueryResult(data As List, meta As Map)
    Dim m As Map = meta
    'MySQL_QueryResult2((MyMap) {ColumnCount=3, RecordCount=5, ms=32})
    Log("MySQL_QueryResult(Columns "&m.get("ColumnCount")&", Records " _
    &m.Get("RecordCount")&","&m.Get("ms")&"ms,"&m.Get("TaskID"))
    ToastMessageShow("MySQL retrieved "&m.Get("RecordCount")&" records in "&m.Get("ms")&"ms",True)
    lv2.Clear
    For i=0 To data.Size-1
        If m.Get("TaskID") = "Categories" Then
            Dim cur As Map = data.Get(i)
            Dim bmp As Bitmap
            Dim In1 As InputStream
          Dim buffer() As Byte           
            buffer = cur.Get("Picture")
            In1.InitializeFromBytesArray(buffer,0,buffer.Length)
            bmp.Initialize2(In1)           
            lv2.AddTwoLinesAndBitmap(cur.Get("Description"),cur.Get("CategoryName"),bmp)
            'Log("r:"&data.Get(i))
        Else If m.Get("TaskID") = "Employees" Then
            Dim cur As Map = data.Get(i)
            Dim bmp As Bitmap
            Dim In1 As InputStream
          Dim buffer() As Byte           
            buffer = cur.Get("Photo")
            In1.InitializeFromBytesArray(buffer,0,buffer.Length)
            bmp.Initialize2(In1)           
            lv2.AddTwoLinesAndBitmap(cur.Get("FirstName")&" "&cur.Get("LastName"),cur.Get("Title"),bmp)
            'Log("r:"&data.Get(i))
        Else
            'Log("r:"&data.Get(i))
        End If
    Next   
End Sub

MySQL_NorthwindEx1.png
 
Last edited:

DonManfred

Expert
Licensed User
Longtime User
94 milliseconds to return 100 lines beats my current solution
Was was the time from your current solution? I would like to see a comparision

Honestly: I´m getting a little bit a red face... I just do a wrapper like i think it should be (not to much overhead). I did not do anything special...

Glad to hear that it is fast :)
 

DonManfred

Expert
Licensed User
Longtime User
V0.21

Bugfixes:
- QueryResult2 and QueryResult updated

String csv = "[";
int numColumns = rsmd.getColumnCount();
for (int i=1; i<numColumns+1; i++) {
if (i==1){
} else {
csv = csv+",";
}

String column_name = rsmd.getColumnName(i);
if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
csv = csv+rs.getArray(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
csv = csv+rs.getInt(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
csv = csv+rs.getBoolean(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
csv = csv+rs.getBlob(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
csv = csv+rs.getDouble(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
csv = csv+rs.getFloat(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
csv = csv+rs.getInt(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
csv = csv+rs.getNString(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
csv = csv+rs.getString(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
csv = csv+rs.getInt(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
csv = csv+rs.getInt(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
csv = csv+rs.getDate(column_name);
} else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
csv = csv+rs.getTimestamp(column_name);
}else{
csv = csv+rs.getObject(column_name);
}
}
csv = csv+"]";

int varchar float
[3,1816-001,1428.57]

It should now better fit the results...
 

Jaames

Active Member
Licensed User
Longtime User
This is the best and simplest to use MySQL library I came across, Keep up the great work!
Thanks for all of your support. (I even got the lib without paying, until my card is ready to pay. Another thanks for that)
 

keirS

Well-Known Member
Licensed User
Longtime User
Difficult to compare without your library but timings for my library using Northwind are as follows. All using a 54mb wireless connection.


Database on my laptop.

B4X:
Employees: 9 Records In 65ms
Categories: 8 Records In 73ms

Test server with DB in memory

B4X:
Employees: 9 Records In 9ms
Categories: 8 Records In 10ms

Test server with DB on Dell EqualLogic SAN with 7200 rpm discs

B4X:
Employees: 9 Records In 22ms
Categories: 8 Records In 22ms



B4X:
Sub Process_Globals
   
    Dim MYSQLIP = "172.0.0.100" As String
    Dim MYSQLDBNAME = "northwind"  As String
    Dim MYSQLPORT = "3306"  As String
    Dim MYSQLUSER = "*****"  As String
    Dim MySQLPASS = "*****"  As String
    Dim MySQLConnection As MariaDBConnector
   
    Dim catgTmStart As Long
    Dim empsTmStart As Long
    Dim empsCount As Long
    Dim catgCount As Long
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
   
    catgCount = 0
    empsCount = 0
    catgTmStart  =DateTime.Now
    MySQLConnection.ExecQuery("catg","select * from categories")
    empsTmStart =  DateTime.Now
    MySQLConnection.ExecQuery("emps","select * from employees")
   

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub emps_update(emp As Map)
    empsCount = empsCount  + 1
End Sub


Sub emps_complete(finished As Boolean)
  Log("Employees: " & empsCount & " Records In " & (DateTime.Now - empsTmStart) & "ms")

End Sub


Sub catg_update(catg As Map)
     catgCount = catgCount + 1
End Sub


Sub catg_complete(finished As Boolean)
   Log("Categories: " & catgCount & " Records In "  &  (DateTime.Now - catgTmStart) & "ms")
End Sub 
Sub query_error(trace As String)
  Log(trace)
End Sub
 
Top