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

Mashiane said:

Is there a way to determine if your last insert / delete / update statement was successful? eg returning a Boolean value somewhere by the lib or return Affected Rows for example.

Click to expand...

DonManfred said:

You can use the batch. For each command in the given batch you´ll get an entry in a result-list.

Click to expand...

Mashiane said:

Hi,

How can I trap duplicate keys on inserts? I'm getting an error

java.lang.Exception: Sub mysql_batchresult signature does not match expected signature.

Can I solve this by using "REPLACE " instead of "INSERT " in my sql commands?

Click to expand...

DonManfred said:

Method with a parameter TASK can be used. Set the task to a string you can remember when getting the result. The TASK will be submitted to the event sub inside the metadata
you can see TASK as kind of "Tag" you know from objects.

Click to expand...

Mashiane said:

Is this the correct approach for this?

Click to expand...

DonManfred said:

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.

Click to expand...

DonManfred said:

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.

Click to expand...

johnB said:

Is your library the way to go

Click to expand...

johnB said:

I need to Insert and Update records to keep the DB up to date and obviously SELECT as per what you can do in RDC

Click to expand...

johnB said:

but is there a B4J version

Click to expand...

johnB said:

Don, if I can use it I have no problem with a donation

Click to expand...