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

[gapi]

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

The library is not free. You need to donate as low as 5$ to get the library.

I have 'donate' you, and now?
Tks

 

[DonManfred]

I have 'donate' you, and now?

I just send out the registration mail a minute ago.

Thank you for donating! ;-)

 

[Paolo SD]

Hi, I need to insert a record into a table and obtain the ID of the newly inserted record. The first command I can enter with ExecuteASync ... for example

INSERT INTO S1 (User) VALUES ("Foo");

To obtain the user ID:

SELECT LAST_INSERT_ID () AS Lindex;

but to read the result should I use another QueryASync in the MySQL_ExecResult event

I tried to put both commands in QueryASync, but does not work

Is there an easier way?

thank you.

 

[DonManfred]

Is there an easier way?

Yes. You could use a php-script as man in the middle which inserts the record and do a query for the last record and return this id.
With the mySql lib you need to call a second query; that´s how MySQL works.

 

[Paolo SD]

I have a problem with ExecuteBatchASync
I have a code with INSERT, DELETE and UPDATE. does not work!
If you only use INSERT or UPDATE, or DELETE then it works, but if you use both not working.

I used this trick to perform various SQL lines

Dim Query AS List

'disable button to reenable after end of SQL lines

Query.Add("Insert...")
Query.Add("Delete...")
db.ExecuteASync("Update...", "Task1")

Sub MySQL_ExecResult(meta As Map)
    Select (meta.Get("TaskID"))     
        Case "Task1"
            If (Query.Size>0) Then
                db.ExecuteASync(Query.Get(0), "Task1")
                Query.RemoveAt(0)
            Else
                 'for example enable button or other action
            End If
    End Select
End Sub

 

[azzam223]

Can I Connect To MsSql Server with this lib

 

[DonManfred]

Can I Connect To MsSql Server with this lib

no

 

[Croïd]

Don, how i call "Cursor1.Rowcount" ? I must add sql lib ? ( for cursor)

 

[jahswant]

NO cursors here.

 

[Croïd]

NO cursors here.

Thanks Jashswani

 

[Croïd]

Can you tell me why I can enter numbers but not letters in my base !

 

[DonManfred]

No. You need to provide MORE information.
For example the db-schema you are using. if for example iD is an autoincrementdield then you can not set your own id.
Additional the field iD is most probably not known as in the select you do use ID and not iD. So, what is the right name?

 

[Croïd]

No. You need to provide MORE information.
For example the db-schema you are using. if for example iD is an autoincrementdield then you can not set your own id.
Additional the field iD is most probably not known as in the select you do use ID and not iD. So, what is the right name?

Thanks for your answer Manfred.

Yes I 'have ID (int) with autoincrementdield and 2 varchar (username & password)

 

[DonManfred]

Yes I 'have ID (int) with autoincrement

then you should most probably use a insert statement like

db.ExecuteASync($"INSERT INTO tbluser1 SET username="${txtusername.Text}", password="${txtpassword.Text}";"$,"")

 

[jahswant]

Code:
db.ExecuteASync($"INSERT INTO tbluser1 SET username="${txtusername.Text}", password="${txtpassword.Text}";"$,"")

Full Support.

 

[Croïd]

Full Support.

 

[prodisoft]

Hello DonManfred,
I want to use "ExecuteASync" for UPDATE in a MySQL file but I don't know if my update is correct because in ExecResult(meta as Map) only give me the time and and Task but not say me if the operation was correct. In batch process meta "results" give 0 or 1 by operation line but how i know it in a ExecResult?.
Thanks

 

[avrtech]

Hello all

I'm starting to play with b4a and started tests with MySQL library.
I started the test program host and configured MySQL data base
I created tables in the database

I compile a program and I install on my phone right after starting the program I see any, mistake as in the screenshot below:




Can someone steer me where I made a mistake, or where to find the error?

best regards Tomasz

 

[avrtech]

Ok I changed the access rights to host the database and currently do not have any errors.

Example soft add new data to base

Now I have to control the display of data from the database ... does anyone have a simple idea that in Designer were visible data retrieved from the database?

 

[DonManfred]

that in Designer were visible data retrieved from the database?

No. The data will come from the database when te app is running. No chance to see it at design-time.

You can see an example code at this post.