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

 

[iCAB]

Hi KeirS

I was asking if the above will work for MsSQL, but I guess I got my answer after changing the syntax to

"exec StoredProcedureToCall ?, ?, ?, ?"

However I am unable to get the Result set using

MyResultSet = MYSQLProcedure.FetchResultSet

Any ideas?

Thanks
iCAB

 

[keirS]

Hi KeirS

I was asking if the above will work for MsSQL, but I guess I got my answer after changing the syntax to

"exec StoredProcedureToCall ?, ?, ?, ?"

However I am unable to get the Result set using

MyResultSet = MYSQLProcedure.FetchResultSet

Any ideas?

Thanks
iCAB

It should work with any JDBC driver where the DBMS supports stored procedures. I will do an example using SQL server and post it up. Won't be for a day or so though.

 

[iCAB]

Hi KeirS

That would be great, as I am unable to get Fetch operation to work.

Thank you
iCAB

 

[keirS]

See first post for MSSQL sample and new version of the library. There was actually an issue with using the original library with MSSQL. The problem is that I was returning the output parameters before the resultsets:

 MyResults = MYSQLProcedure.ExecProcedure(MySQLConnection)

According to Microsoft the JDBC driver specification says that the result sets should be closed when the output parameters are retrieved. MySQL and MariaDB do not follow this spec which is a bit strange since Java and MySQL are both owned by Oracle!

 

[keirS]

Hi KeirS

That would be great, as I am unable to get Fetch operation to work.

Thank you
iCAB

Library and example have been updated. See first post.

 

[iCAB]

Library and example have been updated. See first post.

Hi Keir

Thanks for the update, greatly appreciated!

But I am still having problems with MsSQL:
1. Issue #1, this syntax is not working for me

"{call dbo.GetOders(?, ?)}"

2. if I change the syntax as in the code below, no error is generated:

"exec dbo.GetOders(?, ?)"

Fetching the output parameters is working.

Fetching the results

            'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )
  
            'This returns the result
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

Please note that there is a single Select statement at the end of the stored procedure

Thanks
ICAB

 

[keirS]

Hi Keir

Thanks for the update, greatly appreciated!

But I am still having problems with MsSQL:
1. Issue #1, this syntax is not working for me

"{call dbo.GetOders(?, ?)}"

2. if I change the syntax as in the code below, no error is generated:

"exec dbo.GetOders(?, ?)"

Fetching the output parameters is working.

Fetching the results

            'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )
 
            'This returns the result
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

Please note that there is a single Select statement at the end of the stored procedure

Thanks
ICAB

What JDBC driver are you using? The sample uses mssql-jdbc-7.0.0.jre8.jar if you are not using this JDBC driver then download it and try it. The syntax:

"exec dbo.GetOders(?, ?)"

you are using crashes when using this driver.

 

[iCAB]

Here is another thing that I just noticed

This Sequence, returns some results as described above. Meaning the first Fetch doesn't return results, the second one does

    'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )

        'This returns the result
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

        Dim myResults As Map =  MSSQLProcedure.FetchOutputParameters
        Log(myResults)

        If myResults <> Null Then
            If myResults.IsInitialized = True Then
                For Each Key In myResults.Keys
                    Log( myResults.Get(Key) )
                Next
            End If
        End If

While Fetching the output parameters first cause FetchMoreResults to fail as well

    Dim myResults As Map =  MSSQLProcedure.FetchOutputParameters
        Log(myResults)

        If myResults <> Null Then
            If myResults.IsInitialized = True Then
                For Each Key In myResults.Keys
                    Log( myResults.Get(Key) )
                Next
            End If
        End If



        'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )

        'No results in here either
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

Thanks
iCAB

 

[iCAB]

What JDBC driver are you using? The sample uses mssql-jdbc-7.0.0.jre8.jar if you are not using this JDBC driver then download it and try it. The syntax:

"exec dbo.GetOders(?, ?)"

you are using crashes when using this driver.

I am using the same one

#AdditionalJar: mssql-jdbc-7.0.0.jre8.jar

 

[iCAB]

Here is one more thing I just noticed with regards to this block

           'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )

        'This returns the result
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

The stored procedure I am using looks like this:

INSERT INTO ...
Select * from .....

If I comment out the INSERT statement, the result is returned in the first FetchResultSet, if I put INSERT INTO back, I see the behavior described above

 

[victormedranop]

Let me tell you this is amazing job.
But why this does not work.

Log(MyResultSet.GetString("Nombre"))

but in the function CursorToLog
I can see all the data?

Thanks

Victor

 

[keirS]

Here is one more thing I just noticed with regards to this block

           'This returns empty result
        Dim MyResultSet As ResultSet = MSSQLProcedure.FetchResultSet
        CursorToLog( MyResultSet )

        'This returns the result
        Do While MSSQLProcedure.FetchMoreResults
            MyResultSet = MSSQLProcedure.FetchResultSet
            CursorToLog( MyResultSet )
        Loop

The stored procedure I am using looks like this:

INSERT INTO ...
Select * from .....

If I comment out the INSERT statement, the result is returned in the first FetchResultSet, if I put INSERT INTO back, I see the behavior described above

That is a problem with your stored procedure and is exactly what I would expect to happen if you do not have "SET NOCOUNT ON" in your stored procedure as the first thing returned is the row count from the INSERT.

 

[iCAB]

That is a problem with your stored procedure and is exactly what I would expect to happen if you do not have "SET NOCOUNT ON" in your stored procedure as the first thing returned is the row count from the INSERT.

Thanks, using NOCOUNT ON solves the issue. But reversing the order, meaning Fetching output parameters first, then FetchResultSet is causing an exception now FetchResultSet is called

Thanks again
iCAB

 

[victormedranop]

What JDBC driver are you using? The sample uses mssql-jdbc-7.0.0.jre8.jar if you are not using this JDBC driver then download it and try it. The syntax:

"exec dbo.GetOders(?, ?)"

you are using crashes when using this driver.

look at this

 Dim MSSQLProcedure As CallProc
 MSSQLProcedure.ProcedureCall = $"DECLARE @RC int
 DECLARE @CallerID nvarchar(25) = (?)
 EXECUTE @RC = [odbcRead].[__ClienteSAP]
 @CallerID"$

was simple.

 

[keirS]

Thanks, using NOCOUNT ON solves the issue. But reversing the order, meaning Fetching output parameters first, then FetchResultSet is causing an exception now FetchResultSet is called

Thanks again
iCAB

That's Microsoft for you.:

Stored procedures can return update counts and multiple result sets. The Microsoft JDBC Driver for SQL Server follows the JDBC 3.0 specification, which states that multiple result sets and update counts should be retrieved before the OUT parameters are retrieved. That is, the application should retrieve all of the ResultSet objects and update counts before retrieving the OUT parameters by using the CallableStatement.getter methods. Otherwise, the ResultSet objects and update counts that haven't already been retrieved will be lost when the OUT parameters are retrieved.

In other words this is not a bug it is working as Microsoft intended.

 

[keirS]

Let me tell you this is amazing job.
But why this does not work.

Log(MyResultSet.GetString("Nombre"))

but in the function CursorToLog
I can see all the data?

Thanks

Victor

Try this CursorToLog and it will tell you what the column names are:

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

 

[iCAB]

That's Microsoft for you.:


In other words this is not a bug it is working as Microsoft intended.

Sorry I just noticed the comment at the end of the sample code as well. Thanks a lot for your help.

This library is extremely helpful
Good Job!

 

[victormedranop]

Try this CursorToLog and it will tell you what the column names are:

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

shows the columns name.

CodSAP: CXXXX3364
CodIdis: 1DDDD64
Nombre: FrayDDDXXX a Rivera
TelOficina:
TelCasa: 8095XXXXXXX
TelCelular: 809YYYYYYYY
Fax:
RNCCedula: 0XXXXXXXXXXXXXXX1
EjecutivoVentas: RaXSSSUUUnal
AgenteCobros:

 

[keirS]

shows the columns name.

CodSAP: CXXXX3364
CodIdis: 1DDDD64
Nombre: FrayDDDXXX a Rivera
TelOficina:
TelCasa: 8095XXXXXXX
TelCelular: 809YYYYYYYY
Fax:
RNCCedula: 0XXXXXXXXXXXXXXX1
EjecutivoVentas: RaXSSSUUUnal
AgenteCobros:

OK does this work?

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