The code in this tutorial is based on code example by @Bladimir Silva Toro, thanks for sharing.
MS SQL server provides special stored procedure 'sp_executesql' to execute SQL statements, this stored procedure accepts one string-type parameter which is the SQL query we want to execute. By using this procedure with jRDC2 we will be able to execute any SQL query/nonquery statement. All we need is to add this line to our SQL commands in config properties file on B4J jRDC.
sql.execcall= EXECUTE sp_executesql ?
it's just one parameter call, this parameter is our SQL query/nonquery statement we want to execute, you may rename 'execcall' as you like
in this example we will use two subs one to execute query and the other to execute nonquery SQL statements.
to pass SQL query/nonquery statement to jRDC2 we use code like:
MS SQL server provides special stored procedure 'sp_executesql' to execute SQL statements, this stored procedure accepts one string-type parameter which is the SQL query we want to execute. By using this procedure with jRDC2 we will be able to execute any SQL query/nonquery statement. All we need is to add this line to our SQL commands in config properties file on B4J jRDC.
sql.execcall= EXECUTE sp_executesql ?
it's just one parameter call, this parameter is our SQL query/nonquery statement we want to execute, you may rename 'execcall' as you like
in this example we will use two subs one to execute query and the other to execute nonquery SQL statements.
B4X:
Sub ExecNonQuery(SQLstring As String)As ResumableSub
Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("execcall", Array As String(SQLstring))
Wait For (req.ExecuteCommand(cmd,SQLstring)) JobDone(j As HttpJob)
If j.Success Then
'handle OK
Else
Log("ERROR: " & j.ErrorMessage)
End If
j.Release
Return j.Success
End Sub
B4X:
Sub ExecQuery (SQLquery As String)As ResumableSub
Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("execcall", Array As String(SQLquery))
Wait For (req.ExecuteQuery(cmd, 0, SQLquery)) JobDone(j As HttpJob)
If j.Success Then
req.HandleJobAsync(j, "req")
Wait For (req) req_Result(res As DBResult)
'work with result
req.PrintTable(res)
Else
Log("ERROR: " & j.ErrorMessage)
End If
j.Release
Return j.Success
End Subto pass SQL query/nonquery statement to jRDC2 we use code like:
B4X:
Private Sub Button1_Click
Dim cid As String = "TEST001"
Dim Qr As String
Dim cnm As String = "New Customer"
Dim cmod As String = "Modified Name"
'comment/uncomment blocks To run desired tests
' Qr = "delete from [test] where CustomerID like " & "'" & cid & "%'"
' Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
' Log(OK)
Qr = "insert into [test] ([CustomerID], [CustomerName]) VALUES (" & "'" & cid & "'" & "," & "'" & cnm & "'" & ")"
Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
Log(OK)
' Qr = "update [test] set [CustomerName] = " & "'" & cmod & "'" & " where [CustomerID] = " & "'" & cid & "'"
' Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
' Log(OK)
'
' Qr = "SELECT * from test where CustomerID = " & "'" & cid & "'"
' Wait For (CallSub2(Me, "ExecQuery",Qr)) Complete (OK As Boolean)
' Log(OK)
End Sub