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

mokrokuce

Member
Licensed User
Hello, I am stuck with JDBC and MSSQL. I have successfully connected using JDBC like so:
B4X:
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:
B4X:
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:
B4X:
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)
 

Attachments

mokrokuce

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

B4X:
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.
 
Top