Android Question JDBC run a MSSQL stored procedure with in & out params

[mokrokuce]

Hello, I am stuck with JDBC and MSSQL. I have successfully connected using JDBC like so:

Sub Process_Globals
    Public msSQL As JdbcSQL
    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.100.212/OPENXMLTesting"
    Private Username As String = "test"
    Private Password As String = "test"
End Sub


Sub Connect As ResumableSub
    Log("Connecting")
    msSQL.InitializeAsync("msSQL", driver, jdbcUrl, Username, Password)
    Wait For msSQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

So now I want to run this stored procedure on MSSQL server:

ALTER procedure [dbo].[DoubleNumber]
(@number_in    int,
@number_out int output)
as
begin
    set @number_out=@number_in*2
end

...but I don't know how. I tried this:

Sub ExecSPInOut As ResumableSub
    Log("ExecSPInOut")
  
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim stmt As Object = msSQL.CreateCallStatement("Exec dbo.DoubleNumber ?,?",Array(3,0))
            Dim crsr As JdbcResultSet
            crsr=msSQL.ExecCall(stmt)
            Log("Success, cursor column count:" & crsr.ColumnCount)
          
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

...but in log I get errors:
(SQLException) java.sql.SQLException: The executeQuery method must return a result set.

Can anyone point me in any direction?

(I have attached my small project)

 

[XbNnX_507]

Hi, https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/#content

 

[mokrokuce]

Thanks, this worked like a charm!
Had to include "SQL" and "SQLProc" libraries in project and modified my procedure like so:

Sub ExecSPInOut As ResumableSub
    Log("ExecSPInOut")
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim MyResults As Map
            Dim MyResultSet As ResultSet
            Dim MSSQLProcedure As CallProc
            MSSQLProcedure.ProcedureCall = "{call dbo.DoubleNumber(?, ?)}"
            MSSQLProcedure.AddInputParameter(1,MSSQLProcedure.SQLINTEGER,"3")
            MSSQLProcedure.AddOutputParameter(2,MSSQLProcedure.SQLINTEGER)
            MSSQLProcedure.ExecProcedure(msSQL)
           
            MyResultSet = MSSQLProcedure.FetchResultSet
            'CursorToLog(MyResultSet)
'            If MSSQLProcedure.FetchMoreResults Then
'                MyResultSet = MSSQLProcedure.FetchResultSet
'                CursorToLog(MyResultSet)
'            End If 
            MyResults =  MSSQLProcedure.FetchOutputParameters
            Log(MyResults)
            MSSQLProcedure.close
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

I have omited the "CursorToLog" call because my SP doesn't select any records, it just returns params. Also, had to put in "SET NOCOUNT ON" at the very top of SP.

 

[netsistemas]

FetchOutputParameters no methot in proyect?

download the last 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...

www.b4x.com

perfect!!