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

Discussion in 'Old Libraries Archive' started by DonManfred, Jan 22, 2015.

  1. DonManfred

    DonManfred Expert Licensed 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: Sep 28, 2017
    frank__, mephjl, mkkm and 23 others like this.
  2. DonManfred

    DonManfred Expert Licensed User

    Example
    Code:
    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: Jan 23, 2015
    Jesus Lopez Flores likes this.
  3. DonManfred

    DonManfred Expert Licensed User

    [​IMG]
     
    Last edited: Jan 22, 2015
    shrs likes this.
  4. DonManfred

    DonManfred Expert Licensed User

    Example 2 - Showing two tables from the Northwinddatabase including images in a Listview

    Code:
    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
    [​IMG]
     
    Last edited: Jan 24, 2015
    Peter Simpson likes this.
  5. DonManfred

    DonManfred Expert Licensed User

    Space for future informations #4
     
  6. DonManfred

    DonManfred Expert Licensed User

    Space for future informations #5
     
  7. barx

    barx Well-Known Member Licensed User

    Expecting a lot of future information?
     
  8. DonManfred

    DonManfred Expert Licensed User

    maybe we want to create more Examples.
    maybe some Speedcomparision with other MySQL-Librarys you find here in forum
     
    ibs06t likes this.
  9. Peter Simpson

    Peter Simpson Expert Licensed User

    Okay @DonManfred, you win. This is a seriously seriously seriously fast MySQL library. 94 milliseconds to return 100 lines beats my current solution :)

    I'll have to play with this and post some code on here.

    Untitled-2.png

    Cheers...
     
    DonManfred likes this.
  10. DonManfred

    DonManfred Expert Licensed User

    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 :)
     
  11. Peter Simpson

    Peter Simpson Expert Licensed User

    My current solution averages between 230 and 350 milliseconds to bring back the exact same lines.
    You know what, a picture say 1000 words...

    Here you go...
    Screenshot_2015-01-22-21-36-54.png
     
    DonManfred likes this.
  12. Peter Simpson

    Peter Simpson Expert Licensed User

    Okay, I've now integrated your MySQL library into a new test solution, it works well.
    It's now time for me to sign off, as I have a movie to watch...

    Test project screen shot
    Screenshot_2015-01-22-22-58-01.png
    Cheers...
     
    Last edited: Jan 22, 2015
    thedesolatesoul and DonManfred like this.
  13. DonManfred

    DonManfred Expert Licensed User

    @Peter Simpson
    You told me the library will return only 99 row when using limit 0,100

    [​IMG]
    Could it be that your sql-query only find 99 rows?

    [​IMG]
     
  14. Peter Simpson

    Peter Simpson Expert Licensed User

    Hiya, no way .
    The blue screen shot has number 4719 on it. The red screen shot is missing 4719, look at the screen shots above, the red one only has up to 4718 on it.
     
  15. DonManfred

    DonManfred Expert Licensed User

    ok, hold on... Will update to 0.20 shortly. As you can se in my Screenshots its working here ok it seems... Maybe that was one of the bugs i fixed yesterday late evening :)
     
    Peter Simpson likes this.
  16. DonManfred

    DonManfred Expert Licensed User

    V0.21

    Bugfixes:
    - QueryResult2 and QueryResult updated

    It should now better fit the results...
     
    Peter Simpson likes this.
  17. DonManfred

    DonManfred Expert Licensed User

    Update
    V0.30 released
    - It now allows you to use auto-reconnect if the connection is closed due to timeout
     
    erasmusackon and MarcoRome like this.
  18. Jaames

    Jaames Active Member Licensed 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)
     
    DonManfred and Peter Simpson like this.
  19. keirS

    keirS Well-Known Member Licensed 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.

    Code:
    Employees: 9 Records In 65ms
    Categories: 
    8 Records In 73ms
    Test server with DB in memory

    Code:
    Employees: 9 Records In 9ms
    Categories: 
    8 Records In 10ms
    Test server with DB on Dell EqualLogic SAN with 7200 rpm discs

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


    Code:
    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
     
    MohammadNew likes this.
  20. DonManfred

    DonManfred Expert Licensed User

    and connecting to a database in your own lan.

    All Speedconparisions we posted here were made from b4a to a database(s) not in the own w-lan

    But your results are looking nice ;-)
     
    Peter Simpson likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice