B4J Question MS SQL Execute Stored Procedure And Get Results

[Nokia]

Trying to execute a stored procedure and get the results back

          sSQL = "EXECUTE [dbo].[add_person] " & _
                  "NULL" & _
                  ",NULL" & _
                  ",'" & sEntID & "'" & _
                  ",'" & sPracID & "'" & _
                  ",NULL" & _
                  ",'" & sLastN & "'" & _
                  ",'" & sFirstN & "'" & _
                  ",0"
Dim iR as Int = 0

 iR = sqlMS.ExecQuerySingleResult(sSQL)

In vb.net I would just execute..

[/COLOR][/FONT]
[COLOR=rgb(20, 20, 20)]         sSQL = "EXECUTE [dbo].[add_person] " & _
                  "NULL" & _
                  ",NULL" & _
                  ",'" & sEntID & "'" & _
                  ",'" & sPracID & "'" & _
                  ",NULL" & _
                  ",'" & sLastN & "'" & _
                  ",'" & sFirstN & "'" & _
                  ",0"
[/COLOR][/LEFT]
       command = New SqlCommand(sSQL, conn)
[LEFT][FONT=verdana][COLOR=rgb(20, 20, 20)]       iSQLStatus = command.ExecuteScalar()

​

The above just returns NULL. Do I have to execute it as if I was on SQL SMS.. to get a results?

sSQL = "Declare @i int, @s varchar(max) ” &  _
             "EXECUTE [dbo].[add_person] " & _
                  "@i output" & _
                  ",@s output" & _
                  ",'" & sEntID & "'" & _
                  ",'" & sPracID & "'" & _
                  ",NULL" & _
                  ",'" & sLastN & "'" & _
                  ",'" & sFirstN & "'" & _
                  ",0" & _
"Select @i as [cd], @s as [des]"

Dim iR as Int = 0

iR = sqlMS.ExecQuery(sSQL)

  Do While rs.NextRow
        log(rs.GetString("cd"))
        log(rs.GetString("des"))
  Loop
  rs.Close

 

[Erel]

Why aren't you using the smart strings literal???

Try it with SQL.CreateCallStatement and then call it with SQL.ExecCall.

 

[Nokia]

Why aren't you using the smart strings literal???

Try it with SQL.CreateCallStatement and then call it with SQL.ExecCall.

Erel,
do you have an example on how to use?

 

[Erel]

I searched for CreateCallStatement and quickly found this: https://www.b4x.com/android/forum/t...rocedure-in-mysql-database.86746/#post-549159

 

[Nokia]

I searched for CreateCallStatement and quickly found this: https://www.b4x.com/android/forum/t...rocedure-in-mysql-database.86746/#post-549159

Thanks Erel,

I have tried the example and I get error that no results are returned. the stored proc does not return any results it does retrun varables @i and @s above.. the stored proc just returns a status that it was successful or not as a 0,1,2 or 3 depending on the error and description and no record set. I'm not sure how to code this, but this is what I have ..

sSQL = "[ng_add_person] <@i output>,<@s output>,'00001','0001',NULL,'Test','Himport',0,NULL,NULL,'H.',NULL,'6027 W. Parker Rd','suite 30H','Plano','Tx','75093',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4693265176',NULL,'4633265177',NULL,NULL,'19650315','M','223564714','4633265177','N',NULL,'N','N','N','N','N','N','N','N','N','11111111-1234-5678-3333-111122223333','2F1F13FF-EA73-4DE9-8BAE-61EBC8478BC4',NULL,NULL,'Declined to Specify','English',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'[email protected]',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,'AE027C2F-31E9-49F0-88CE-BA0279CCEF75',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,NULL,'N','N','N','N','N','N','N',NULL,NULL,NULL,NULL"

    oResults = sqlMS.CreateCallStatement(sSQL, Null)
    Dim rsStatus As ResultSet = sqlMS.ExecCall(oResults)
    
    Do While rsStatus.NextRow
     Log(rsStatus.GetString2(0))
     Log(rsStatus.GetString2(1))
    Loop

 

[keirS]

Parameters are not returned as a result set. Either use my library:

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

or use JavaObject

Dim JO As JavaObject = oResults
Dim i As Int = JO.RunMethod("getInt",Array ("i"))
Dim s As String = JO.RunMethod("getString",Array ("s"))

 

[Nokia]

Parameters are not returned as a result set. Either use my library:

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

or use JavaObject

Dim JO As JavaObject = oResults
Dim i As Int = JO.RunMethod("getInt",Array ("i"))
Dim s As String = JO.RunMethod("getString",Array ("s"))

when I try the above... it get the following error
Event:com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter i was not defined for stored procedure .

 

[keirS]

Ok I forgot. Before you do the ExecCall. You need to do.

Dim JO As JavaObject = oResults
JO.RunMethod("registerOutParameter",Array ("i",4))
JO.RunMethod("registerOutParameter",Array ("s",12))

It really is easier to use the library though.