Afternoon,
i was having some troubles with the indusoft std dbxxx functions calling and passing stored procedure inputs,outputs,return values so after some digging and some alternate help from collegues here you go... vbs+ADO connection's
if you have a Sql stored procedure "My SP" and it has 2 inputs and 3 outputs the following example will show the use of mapping and passing variables:
Somewhere Call
funcDatabase_uspMySp $Database_Connection_String,$MyValue,vSqlDate
In Globals
funcDatabase_uspMySp $Database_Connection_String,$MyValue,vSqlDate
Function funcDatabase_uspMySp(vstrSQL_Connection, MyValue, vSqlDate)
'Description: Read 01 information from the database and put the result into a TAG
'Function Number: 11
'Parameters:
'External Variables:
'Comments:
' 1 - Used in
' 2 - In the end of the Function, the TAG will have the last value of the SQL Statament line
'History:
' 20 Dec 2012 - Creation of the Function by Alan Cannon
'Variable Declaration
Dim vobjADOConnection 'ADO Connection object
Dim vobjADOCmd 'ADO Command object
Dim oResultCode ' oReturn Code from the Stored procedure
Dim oResultText 'oReturn text from the Stored Procedure
Const adVarChar = 200
Const adInteger = 3
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdStoredProc = 4
' Setup local Variables defaults
oResultCode = 0
oResultText = ""
'Inform the Begining of the Function Execution
funcLog 1,"funcDatabase_uspSetOrderData","Execution Start"
'Initialise the ADO Connection
Set vobjADOConnection = CreateObject("ADODB.Connection")
Set vobjADOCmd = CreateObject("ADODB.Command")
'Inform the action
funcLog 1,"funcDatabase_uspSetOrderData","Connecting with the Database"
'Enable Execution Error Tratement
On Error Resume Next
'Open the Local Database Server
vobjADOConnection.Open vstrSQL_Connection
'Error and Information Tratement
If (err.number <> 0) Then
'Inform Database Communication Error
funcLog err.number,"funcDatabase_uspSetOrderData", CStr(err.description)
'Disable Execution Error Tratement
On Error Goto 0
Exit Function
End If
'Enable Execution Error Tratement
On Error Resume Next
vobjADOCmd.ActiveConnection = vobjADOConnection
vobjADOCmd.CommandText = "uspMySp"
vobjADOCmd.CommandType = adCmdStoredProc
'Add Input Parameters
vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@MyValue", adInteger, adParamInput, 8, MyValue)
vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@SqlDate", adVarChar, adParamInput, 20, vSqlDate)
'Add Output Parameters
vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@intResultCode", adInteger, adParamOutput, 8, 0)
vobjADOCmd.Parameters.Append vobjADOCmd.CreateParameter("@strResultText", adVarChar, adParamOutput, 256, 0)
'Execute the function
vobjADOCmd.Execute
'Error and Information Tratement
If (err.number <> 0) Then
'Inform Database Communication Error
funcLog err.number,"funcDatabase_uspSetOrderData", CStr(err.description)
'Disable Execution Error Tratement
On Error Goto 0
Exit Function
End If
'Enable Execution Error Treatment and Parameter Closing Statements
On Error Resume Next
oResultCode = vobjADOCmd.Parameters("@intResultCode")
oResultText = vobjADOCmd.Parameters("@strResultText")
$ManualOffload.oResultText = CStr(oResultCode) & CStr(" ") & CStr(oResultText)
vobjADOConnection.Close
Set vobjADOConnection = Nothing
'Inform the End of the Funciton Execution
funcLog 1,"funcDatabase_uspSetOrderData","Execution Finish"
End Function
'*************************************************************************************************************