B4J Library SQL: Simplyfying Calling Stored Procedures

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

#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.

    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 MYSQLProcedure As CallProc

Declare a CallProc object used to configure an execute a stored procedure.

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

Tell the Callproc object the stored procedure to call.

'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.

'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.

MYSQLProcedure.ExecProcedure(MySQLConnection)

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.

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.

'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

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:

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.

#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]

Please note that the sequence of calls is important. You must retrieve and process the resultsets before calling FetchOutputParameters (see post #5).

 

[victormedranop]

try this

Sub CursorToLog2(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.GetColumnName(iColumnCount) & ": " & oCursorToLog.GetString2(iColumnCount))
   Log(oCursorToLog.GetString("CodIdis"))
        Next
        Log("  ")
    Loop
End Sub

but the result wan

10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364

all colums with the real data. that 10203364 have to be the return but not an all result just CodIdis.

victor

 

[victormedranop]

For me its working I just have to take the first value and use it.
but this is an amazing library.

Victor

 

[keirS]

try this

Sub CursorToLog2(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.GetColumnName(iColumnCount) & ": " & oCursorToLog.GetString2(iColumnCount))
   Log(oCursorToLog.GetString("CodIdis"))
        Next
        Log("  ")
    Loop
End Sub

but the result wan

10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364
10203364

all colums with the real data. that 10203364 have to be the return but not an all result just CodIdis.

victor

You have the code in the wrong place. You should have:

Sub CursorToLog2(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.GetColumnName(iColumnCount) & ": " & oCursorToLog.GetString2(iColumnCount))
        'Next
          Log(oCursorToLog.GetString("CodIdis"))
          Log(oCursorToLog.GetString("Nombre"))
          'lots more logs
        Log("  ")
    Loop
End Sub
[/code

 

[victormedranop]

You were absolutely right.
always was working, was a log that confuse me. I remove all log() and
as expected.

thanks.

great job, great lib!!!!!

Victor

 

[XbNnX_507]

Hi, i know this thread is quiet old but i think this question should be of help for those using OracleDatbase...
is it possible to return a resulset Type ?

MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER) ' <- MYSQLProcedure.SQLRESULSET ? REF CURSORS

 

[keirS]

Hi, i know this thread is quiet old but i think this question should be of help for those using OracleDatbase...
is it possible to return a resulset Type ?

MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQLINTEGER) ' <- MYSQLProcedure.SQLRESULSET ? REF CURSORS

Yes. Both the samples return result sets.

 

[XbNnX_507]

Yes. Both the samples return result sets.

They indeed return a resulset set. but i don't find the MYSQLProcedure.SQLRESULSET type in your library.

MYSQLProcedure.AddOutputParameter(2,MYSQLProcedure.SQL_CURSOR) <- here

See i'm not returning a integer, nor float, nor string... i'm returning a cursor...

 

[keirS]

A result set is retrieved by calling FetchResultSet. It is not defined as an output parameter. Parameters are used for returning primitive types.

 

[XbNnX_507]

A result set is retrieved by calling FetchResultSet. It is not defined as an output parameter. Parameters are used for returning primitive types.

That's the case with Mariadb, Mysql...
With Oracle ( which i'm using ) i can return a cursor... Actually i need to return a cursor if need to call FetchResulset.

 

[keirS]

That's the case with Mariadb, Mysql...
With Oracle ( which i'm using ) i can return a cursor... Actually i need to return a cursor if need to call FetchResulset.

That's not possible with this library. It's not generic JDBC. It appears it is an extension to JDBC that is specific to the Oracle driver..

 

[aregan]

Hi Kiers

Do you know is it possible to pass a binary array / blob as a parameter to a stored procedure using this library?

I see the parameter type listed as a sql type but I dont seem to be able to pass the binary array as a parameter.

Thanks
Alan

 

[keirS]

No it’s not possible. You can pass a string to be converted to a blob. I can’t off hand remember how this works. I will post how it’s done after Christmas as I don’t have the source for this on GitLab.

 

[keirS]

Sorry for the delay in getting back on this. Having looked at the source Blobs are only supported as output parameters.