B4A Library Yet Another MySQL Library But With Stored Procedure Support and Cursors

There has been a bit discussion lately about stored procedures both on the B4A and B4J bits of the forum so I thought I would post a library which supports stored procedures with IN,OUT and INOUT parameters and also supports procedures which return multiple result sets.

The sample code below uses the sample database from www.MySQLTutorial.org. You need to download the MySQL Connector/J JDBC driver mysql-connector-java-5.1.34-bin.jar from the MySQL Website and put it in your additional libraries folder and download the zip file at the bottom of the page and place the .jar and .xml files and put them in your additional libraries folder

Sample Code

Sub Process_Globals

    Dim MYSQLIP = "172.25.0.44" As String
    Dim MYSQLDBNAME = "classicmodels"  As String
    Dim MYSQLPORT = "3306"  As String
    Dim MYSQLUSER = "******"  As String
    Dim MySQLPASS = "*****"  As String
    Dim MySQLConnection As MySQLConnector
    Dim MYSQLProcedure As CallProc

End Sub

Sub Globals

End Sub


Sub Activity_Create(FirstTime As Boolean)
     If FirstTime Then
            MySQLConnection.Initialize(MYSQLIP,MYSQLDBNAME,MYSQLUSER, MySQLPASS,MYSQLPORT)
    End If

    'return query as cursor
    MySQLConnection.ExecQuery("query","select * from products")
    'Batch updates into a transaction
    MySQLConnection.AddNonQueryToBatch("UPDATE products SET quantityInStock = 123 where productCode = 'S12_1099'")
    MySQLConnection.AddNonQueryToBatch("UPDATE products SET buyPrice = 60 where productCode = 'S700_3167'")
    MySQLConnection.ExecuteNonQueryBatch("updateproducts")
    'Will Cause an Error
    MySQLConnection.ExecQuery("query","select * from products")
    'Singl update
    MySQLConnection.ExecNonQuery("updateSingle","UPDATE products SET MSRP = 82 where productCode = 'S700_3167'")

    'Delcare Called Procedure
    Dim MYSQLProcedure As CallProc

    'Set the procedure to call
    MYSQLProcedure.ProcedureCall = "call getorder(?, ?)"
    'Register Input Parameters
    MYSQLProcedure.AddInputParameter(1,MYSQLProcedure.SQLINTEGER,"10100")
    MYSQLProcedure.AddInputParameter(2,MYSQLProcedure.SQLINTEGER,"1")
    'Register Output Parameters
    MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER)
    'Add Call back subs
    '0 is always for the parameter sub
    MYSQLProcedure.AddCallSub(0,"params")
    '1 for the first result set returned
    MYSQLProcedure.AddCallSub(1,"orderheader")
    '2 for the second result set returned
    MYSQLProcedure.AddCallSub(2,"orderlines")
    'Run the procedure
    'proctest is the sub for general error capture reporting
    MySQLConnection.ExecProcedure("proctest",MYSQLProcedure)



End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub query_complete(c As MySQLCursor)
    'CursorToLog(c)

End Sub


Sub query_error(trace  As String )
     Log(trace)
End Sub


Sub updateProducts_complete(UpdateCount As Int)
    Log(UpdateCount)

End Sub

Sub updateProducts_error(trace As String)
    Log(trace)

End Sub

Sub params_parameters(m As Map)
   If m.ContainsKey(2) Then
      Log("Out Parameter 2 =" & m.Get(2))
   End If


End Sub
Sub orderheader_complete(c As MySQLCursor )
  CursorToLog(c)

End Sub

Sub orderlines_complete(c As MySQLCursor )
     CursorToLog(c)
End Sub
Sub proctest_error(trace As String)
  Log(trace)
End Sub

Sub CursorToLog(c As MySQLCursor)

For RowCounter = 0 To c.GetRowCount -1
    Log("")
    Log("Row: " & RowCounter)
    c.Position = RowCounter
    For ColumnCounter = 0 To c.GetColumnCount -1
   
        Log(c.GetColumnName(ColumnCounter) & ":" & c.GetString2(ColumnCounter))
   

    Next
Next



End Sub


Sub query_update(product As Map)
  If product.ContainsKey("productName") Then
          Log(product.get("productName"))
   End If


End Sub
Sub updatesingle_complete(updatecount As Int)
  Log(updatecount)
End Sub

I have added two stored procedures to the sample DB. The first is getorder.

    CREATE` PROCEDURE `getorder`(IN ordernumber integer,INOUT acounter integer)
    BEGIN

    select * from orders where orders.ordernumber = ordernumber;
    select * from orderdetails where orderdetails.ordernumber = ordernumber order by orderLineNumber;
    set acounter = acounter + 1;

    END

This procedure is passed two parameters and returns one parameter and two result sets. Taking the sample code step by step:

Dim MySQLConnection As MySQLConnector
Dim MYSQLProcedure As CallProc

Declare a connection object and a CallProc object used to configure a stored procedure.

MYSQLProcedure.ProcedureCall = "call getorder(?, ?)"

Tell the Callproc object what stored procedure to call.

'Register Input Parameters
MYSQLProcedure.AddInputParameter(1,MYSQLProcedure.SQLINTEGER,"10100")
MYSQLProcedure.AddInputParameter(2,MYSQLProcedure.SQLINTEGER,"1")

Tells the Callproc object the value of the input parameters.

The first parameter of AddInputParameter is the number of the parameter being passed. So 1 is ordernumber in the called procedure and 2 is acounter.

The second parameter of AddInputParameter is the SQL Type of the parameter. In this case both are integers.

The third parameter of AddInputParameter is the value of the parameter. Values are always passed as strings and conversion to the correct SQL Type is performed internally by the library.

'Register Output Parameters
MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER)

Tells the CallProc object what output parameters are expected.

In the case of the getorder procedure the second parameter is an INOUT parameter so has to be registered as both an input parameter and an output parameter.

The first Parameter for AddOutputParameter is the number of the parameter being returned.

The second parameter is the SQL Type of the stored procedure parameter.

'Add Call back subs
'0 is always for the parameter sub
MYSQLProcedure.AddCallSub(0,"params")
'1 for the first result set returned
MYSQLProcedure.AddCallSub(1,"orderheader")
  '2 for the second result set returned
MYSQLProcedure.AddCallSub(2,"orderlines")

AddCallSub is used to tell the library what sub stub to use for each result set. 0 is used to denote the sub stub for parameters.

In this example params_parameters is passed a Map of the returned parameter values.
orderheader_complete is used to pass the the cursor resulting from 'select * from orders where orders.ordernumber = ordernumber' query and orderlines_complete is used to return the cursor resulting form the 'select * from orderdetails where orderdetails.ordernumber = ordernumber order by orderLineNumber' query.

'Run the procedure
'proctest is the sub for general error capture reporting
MySQLConnection.ExecProcedure("proctest",MYSQLProcedure)

Finally run the procedure. A general sub stub is used for error reporting. In this case "proctest_error".


Result sets are returned as memory backed Android cursors (MySQLCursor) so you should be careful about how big a result set is returned. SQL types are translated to the native SQLite types so boolean values are integers for example. DATE, TIMESTAMP and TIME types are returned as strings.

For the MAP returned for the stored procedure parameters the native B4A types are generally used for numbers with the the exception of the DECIMAL and NUMERIC types which are returned as strings.

There are bound to be bugs in this library as it's a stripped out subset of a much bigger library.



This library is free for non commercial use and for use within a commercial organization. Any use for distributing monetized apps be it by direct payment, advertising or anything else is strictly prohibited.

 

[BarryW]

Can this work on online mysql?

Also how to insert blob such as bytes an how to retrive blobs?

Tnx

 

[keirS]

Can this work on online mysql?

Also how to insert blob such as bytes an how to retrive blobs?

Tnx

Yes it can; but I wouldn't recommend it as it doesn't use SSL so it's not a secure connection.

You would retrieve a blob just like you would from a cursor in SQLite. You would write a blob to a table in the same way as I have already shown you for the MariaDB library.

 

[kuntasev]

A silly question,
What is the difference between this library and "Asynchronous MariaDB/MySQL Library"?.
Which of the two is best to work with MYSQL and what is more updated ?.
Why not unify the two into one?

Thx.

 

[keirS]

A silly question,
What is the difference between this library and "Asynchronous MariaDB/MySQL Library"?.
Which of the two is best to work with MYSQL and what is more updated ?.
Why not unify the two into one?

Thx.

1. They use two different JDBC drivers. Maria DB is LPGL licensed so is free for commercial use and the MYSQL one uses the official MySQL JDBC driver.
2. The MariaDB library does not support Stored Procdures to the same extent as the MySQL one. It doesn't support returning multiple result sets from one procedure for example.
3. The MySQL one returns cursors and the MariaDB one returns a Map per record.
4. The MariaDB one is more fault tolerant. The MySQL one not so much.

There are quite a lot of code differences between the two. They were written for two different projects withe very differing requirements. The MySQL one is actually a subset of a much bigger Java library where the cursors are mutable so you can change the values stored in the rows, add new rows and delete rows and then update the MySQL DB. So going forward this library is likely to be more updated, but I would probably make a version with mutable cursors chargeable.

• "MySQLCursor
  Methods:
  • Add (addMap As Map, position As Int)
    Add a row with the given map.
    The Keys is the column name and the value is the row value
  • Add2 (addCV As ColumnValues, position As Int)
    Add a row with the given ColumnValue.
    The Keys is the column name and the value is the row value
  • Close
    Close the cursor
  • Delete (position As Int)
    Deletes the Row At The given position
  • GenerateSQLDelete As Map
    Generate SQL Delete Statements to update MYSQL DB
  • GenerateSQLInsert As Map
    Generate SQL Insert Statements to update MYSQL DB
  • GenerateSQLUpate As Map
    Generate SQL Update Statements to update MYSQL DB
  • GetAddedRows As Int[]
    Returns a list of cursor positions for Added Rows
  • GetBlob (columnName As String) As Byte[]
    Returns the Blob stored at the given column.
  • GetBlob2 (index As Int) As Byte[]
    Returns the blob stored in the column at the given ordinal.
  • GetColumnCount As Int
    Returns the number of columns in the cursor.
  • GetColumnName (Index As Int) As String
    Returns the name of the column at the specified index.
    The first column index is 0.</
  • GetColumnNames As String[]
    Returns the names of the columns in the cursor as a string array.
  • GetDeletedRows As Int[]
    Returns a list of cursor positions for Deleted Rows
  • GetDouble (columnName As String) As Double
    Returns the Double value stored in the given column.
    The value will be converted to Double if it is of a different type.
  • GetDouble2 (index As Int) As Double
    Returns the Double value stored in the column at the given ordinal.
    The value will be converted to Double if it is of a different type
  • GetEmptyMap As Map
    Convenience method to get an empty map of all the column names.
    The Keys is the column name and the values are null.
  • GetInt (columnName As String) As Int
    Returns the Int value stored in the given column.
    The value will be converted to Int if it is of a different type.
  • GetInt2 (index As Int) As Int
    Returns the Int value stored in the column at the given ordinal.
    The value will be converted to Int if it is of a different type.
  • GetLong (columnName As String) As Long
    Returns the Long value stored in the given column.
    The value will be converted to Long if it is of a different type.
  • GetLong2 (index As Int) As Long
    Returns the Long value stored in the column at the given ordinal.
    The value will be converted to Long if it is of a different type
  • GetPosition As Int
    Get the position of the cursor.
  • GetRowCount As Int
  • GetString (columnName As String) As String
    Returns the String value stored in the given column.
    The value will be converted to String if it is of a different type
  • GetString2 (index As Int) As String
    Returns the String value stored in the column at the given ordinal.
    The value will be converted to String if it is of a different type
  • GetType (index As Int) As Int
    Gets the Type For the given column index
    
    0 = NULL
    1 = Integer
    2 = Float
    3 = String
    4 = BLOB
  • GetUpdatedRows As Int[]
    Returns a list of cursor positions for Updated Rows
  • SetColumnUpdateCriteria (Index As Int, CriteriaMap As Map)
    Set Update criteria for the given column index
  • Update (updateMap As Map, Position As Int)
    Update row with the given map.
    The Keys are the names of the columns to update and values are the row values
  • Update2 (updateMap As ColumnValues, Position As Int)
  Properties:
  • Position As Int [write only]

 

[sigster]

Hi

Thanks for this Library

I am edit database from Mysql then I need to add data to 2 Combobox
how can I kill the connection, in my mysql server it open new and new connection so open progress are to too many

 

[keirS]

Are you calling stored procedures? If so there is a bug in the code which doesn't close the connection. If you are not using stored procedures you should use Erel's library: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/