Updated 2018-08-16. Please note there are breaking changes to the new version of the library (SQLCallPRoc Version 1-10.zip) so it will work correctly with the Microsoft JDBC Driver. The sample for MYSQL has changed and there is a new sample for MS SQL Server. See post #5 for why the change was necessary.
A very small library which extends the functionality of calling stored procedures. ExecCall in the SQL library does not directly support returning parameters or returning multiple result sets. Though in theory it should be possible to do this via JavaObject.
MySQL Example:
The sample code below uses the sample database from www.MySQLTutorial.org.
Sample Code
I have added a stored procedure to the sample DB called getorder.
This procedure is passed two parameters and returns one parameter and two result sets. Taking the sample code step by step:
Declare a CallProc object used to configure an execute a stored procedure.
Tell the Callproc object the stored procedure to call.
Tell 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.
Tell 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.
The first line returns the first ResultSet which is the result of the first query.
FetchMoreResults will return true if another ResultSet is available. You must finish processing the first result set before calling this as it will be closed by this call!
FetchResultSet returns the second result set.
For multiple result sets you would probably want to use a While loop.
FetchOutputParameters returns the output parameters as a Map.
Microsoft SQL Server Example:
The sample code below uses the AdventureWorksLT sample database provided by Microsoft. The correct database for your SQL server version can be found here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
I created a stored procedure with one input parameter and one output parameter called GetOrders:
It basically returns all the sales order header and line rows for passed CustomerID. It returns a count of the sales order headers for the passed CustomerID and put the result in the output parameter @OrderCount.
Here is the sample code.
Please note that the sequence of calls is important. You must retrieve and process the resultsets before calling FetchOutputParameters (see post #5).
A very small library which extends the functionality of calling stored procedures. ExecCall in the SQL library does not directly support returning parameters or returning multiple result sets. Though in theory it should be possible to do this via JavaObject.
MySQL Example:
The sample code below uses the sample database from www.MySQLTutorial.org.
Sample Code
B4X:
#Region Project Attributes
#MainFormWidth: 600
#MainFormHeight: 600
#AdditionalJar: mysql-connector-java-5.1.39-bin.jar
#End Region
Sub Process_Globals
Private fx As JFX
Private MainForm As Form
Public MySQLConnection As SQL
End Sub
Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.SetFormStyle("UNIFIED")
'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
MainForm.Show
Dim MyResults As Map
Dim MyResultSet As ResultSet
MySQLConnection.Initialize2("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/classicmodels","********","*******")
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,"5")
'Register Output Parameters
MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER)
MYSQLProcedure.ExecProcedure(MySQLConnection)
MyResultSet = MYSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
If MYSQLProcedure.FetchMoreResults Then
MyResultSet = MYSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
End If
MyResults = MYSQLProcedure.FetchOutputParameters
Log(MyResults.Get(2))
MYSQLProcedure.close
End Sub
Sub CursorToLog(oCursorToLog As ResultSet)
Dim iRowCount As Int
Dim iColumnCount As Int
Log(" ")
Do While oCursorToLog.NextRow
For iColumnCount = 0 To oCursorToLog.ColumnCount -1
Log(oCursorToLog.GetString2(iColumnCount))
Next
Log(" ")
Loop
End Sub
I have added a stored procedure to the sample DB called 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;
END
This procedure is passed two parameters and returns one parameter and two result sets. Taking the sample code step by step:
B4X:
Dim MYSQLProcedure As CallProc
Declare a CallProc object used to configure an execute a stored procedure.
B4X:
MYSQLProcedure.ProcedureCall = "call getorder(?, ?)"
Tell the Callproc object the stored procedure to call.
B4X:
'Register Input Parameters
MYSQLProcedure.AddInputParameter(1,MYSQLProcedure.SQLINTEGER,"10100")
MYSQLProcedure.AddInputParameter(2,MYSQLProcedure.SQLINTEGER,"1")
Tell 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)
Tell 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:
MYSQLProcedure.ExecProcedure(MySQLConnection)
B4X:
MyResultSet = MYSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
If MYSQLProcedure.FetchMoreResults Then
MyResultSet = MYSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
End If
MYSQLProcedure.close
The first line returns the first ResultSet which is the result of the first query.
B4X:
select * from orders where orders.ordernumber = ordernumber;
FetchMoreResults will return true if another ResultSet is available. You must finish processing the first result set before calling this as it will be closed by this call!
FetchResultSet returns the second result set.
For multiple result sets you would probably want to use a While loop.
B4X:
'Get first result set
MyResultSet = MYSQLProcedure.FetchResultSet
'Process results
'Loop to get further result sets
Do While MYSQLProcedure.FetchMoreResults
MyResultSet = MYSQLProcedure.FetchResultSet
'Process Results
Loop
B4X:
MyResults = MYSQLProcedure.FetchOutputParameters
Log(MyResults.Get(2))
FetchOutputParameters returns the output parameters as a Map.
Microsoft SQL Server Example:
The sample code below uses the AdventureWorksLT sample database provided by Microsoft. The correct database for your SQL server version can be found here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
I created a stored procedure with one input parameter and one output parameter called GetOrders:
B4X:
CREATE PROCEDURE GetOders
@CustumerID Int,
@OrderCount Int Out AS
SET NOCOUNT ON
SELECT * FROM SalesLT.SalesOrderHeader WHERE CustomerID = @CustumerID
SELECT * FROM SalesLT.SalesOrderDetail WHERE SalesOrderID IN (SELECT SalesOrderID FROM SalesLT.SalesOrderHeader WHERE CustomerID = @CustumerID)
SELECT @OrderCount = COUNT(SalesOrderID) FROM SalesLT.SalesOrderHeader WHERE CustomerID = @CustumerID
GO
It basically returns all the sales order header and line rows for passed CustomerID. It returns a count of the sales order headers for the passed CustomerID and put the result in the output parameter @OrderCount.
Here is the sample code.
B4X:
#Region Project Attributes
#MainFormWidth: 600
#MainFormHeight: 600
#AdditionalJar: mssql-jdbc-7.0.0.jre8.jar
#End Region
Sub Process_Globals
Private fx As JFX
Private MainForm As Form
Public MSSQLConnection As SQL
End Sub
Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.SetFormStyle("UNIFIED")
'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
MainForm.Show
Dim MyResults As Map
Dim MyResultSet As ResultSet
MSSQLConnection.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://VM-B4X\SQLEXPRESS:56860;databaseName=AdventureWorksLT2014;user=****;password=****;")
Dim MSSQLProcedure As CallProc
' 'Set the procedure to call
MSSQLProcedure.ProcedureCall = "{call dbo.GetOders(?, ?)}"
' 'Register Input Parameters
MSSQLProcedure.AddInputParameter(1,MSSQLProcedure.SQLINTEGER,"29929")
' 'Register Output Parameters
MSSQLProcedure.AddOutputParameter(2,MSSQLProcedure.SQLINTEGER)
MSSQLProcedure.ExecProcedure(MSSQLConnection)
MyResultSet = MSSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
If MSSQLProcedure.FetchMoreResults Then
MyResultSet = MSSQLProcedure.FetchResultSet
CursorToLog(MyResultSet)
End If
MyResults = MSSQLProcedure.FetchOutputParameters
Log(MyResults)
MSSQLProcedure.close
End Sub
Sub CursorToLog(oCursorToLog As ResultSet)
Dim iRowCount As Int
Dim iColumnCount As Int
Log(" ")
Do While oCursorToLog.NextRow
For iColumnCount = 0 To oCursorToLog.ColumnCount -1
Log(oCursorToLog.GetString2(iColumnCount))
Next
Log(" ")
Loop
End Sub
[code]
Attachments
Last edited: