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
I have added two stored procedures to the sample DB. The first is getorder.
This procedure is passed two parameters and returns one parameter and two result sets. Taking the sample code step by step:
Declare a connection object and a CallProc object used to configure a stored procedure.
Tell the Callproc object what stored procedure to call.
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.
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.
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.
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.
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
B4X:
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 SubI have added two stored procedures to the sample DB. The first is getorder.
B4X:
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;
ENDThis procedure is passed two parameters and returns one parameter and two result sets. Taking the sample code step by step:
B4X:
Dim MySQLConnection As MySQLConnector
Dim MYSQLProcedure As CallProcDeclare a connection object and a CallProc object used to configure a stored procedure.
B4X:
MYSQLProcedure.ProcedureCall = "call getorder(?, ?)"Tell the Callproc object what stored procedure to call.
B4X:
'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.
B4X:
'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.
B4X:
'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.
B4X:
'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.
Attachments
Last edited:
Landroid/database/MatrixCursor$RowBuilder;
