Search Results for

    Show / Hide Table of Contents

    Method ExecuteCustomStoredProcedure

    ExecuteCustomStoredProcedure(String, String, List<DBParameterContract>, String, Boolean)

    Executes a custom stored procedure

    Declaration
    CustomStoredProcedureResultContract ExecuteCustomStoredProcedure(string connectionId, string storedProcedureName, List<DBParameterContract> parameters, string commandType, bool addColumnNamesAsFirstRow)
    Parameters
    Type Name Description
    System.String connectionId

    The connection id to identify your connection. See also ConnectionId

    System.String storedProcedureName

    The stored procedure name. Must start with UserProc_

    System.Collections.Generic.List<DBParameterContract> parameters

    A list of database parameters. See also DBParameterContract

    System.String commandType

    The data retrieve command type DataRetrieveCommandTypeEnum

    System.Boolean addColumnNamesAsFirstRow

    If true, adds the column names as first row to the DataTableResult of the returned result

    Returns
    Type Description
    CustomStoredProcedureResultContract

    The custom stored procedure result.

    • DataTableResult is returned, if the argument ExecuteReader is passed to the parameter commandType
    • ScalarResult is returned, if the argument ExecuteScalar is passed to the parameter commandType
    • RowsAffected is returned, if the argument ExecuteNonQuery is passed to the parameter commandType
    • ReturnValue is returned, if a parameters parameter is defined as follows: the argument ReturnValue is passed to the parameter Direction
    • OutputParameterResult is returned, if at least one output or input/output parameter is defined

    Remarks

    SQL Server, Oracle:

    • Procedure name must start with UserProc_
    • Named notation is used to pass parameters
    Oracle:
    • Put custom stored PL/SQL procedures into package dcispackage
    • Before updating the InfoShare database, make a backup of the custom stored procedures. Otherwise you will loose them

    Examples

    This example calls a SQL Server custom stored procedure. The stored procedure has an input parameter and it returns all data from a user table (including the column headers). The result is returned in DataTableResult.

    POST https://YourServerName/InfoShare/Json/UserTable/ExecuteCustomStoredProcedure

    {
     "connectionId": "ed52ab64-40a8-4c7b-b234-209bb5af9263",
     "storedProcedureName": "UserProc_Test1",
     "parameters": [ 
       { "Name":"pivUserTable", "Value":"UserTable_Test1", "Direction":"Input", "DataType":"NVarchar" }
      ],
     "commandType": "ExecuteReader",
     "addColumnNamesAsFirstRow": false
    }

    Response:

    {
     "ExecuteCustomStoredProcedureResult": {
      "DataTableResult": [
        ["column1", "column2", "column3", "column4"],
        ["row1_col1", "row1_col2", "row1_col3", ""],
        ["row2_col1", "row2_col2", "", "row2_col4"], 
        ["row5_1", "row5_col2", "row5_col3", "row5_col4"],
        ["row6__1", "row6_col2", "row6_col3", "row6_col4"]
       ],
      "OutputParameterResult": [],
      "ReturnValue": null,
      "RowsAffected": "-1",
      "ScalarResult": null
     }
    }
    {
     "connectionId": "5de008d5-7683-4ff4-9555-4bd74bb1e105",
     "storedProcedureName": "UserProc_Test1",
     "parameters": [
       { "Name":"pin1", "Value":"10", "Direction":"Input", "DataType":"Varchar2" },
       { "Name":"pin2", "Value":null, "Direction":"Output", "DataType":"Varchar2", "Size":"2" }
      ],
     "commandType": "ExecuteNonQuery",
     "addColumnNamesAsFirstRow": false
    }

    Response:

    {
     "ExecuteCustomStoredProcedureResult": {
      "DataTableResult": [],
      "OutputParameterResult": [
        {"Name":"@pin2","Value":"20"}
       ],
      "ReturnValue": null,
      "RowsAffected": "-1",
      "ScalarResult": null
     }
    }
    Exceptions
    Type Condition
    ServiceException

    The connection id {connectionId} isn't valid or has expired (10008)

    ServiceException

    Verification code is needed: Verification code not yet verified (10152)

    ServiceException

    Stored procedure name {storedProcedureName} is invalid. It must start with UserProc_ (10159)

    ServiceException

    Could not parse string {dataRetrieveCommandType} to a valid command type. Valid types are: {allowedDataRetrieveCommandTypes} (10160)

    ServiceException

    Could not parse string {parameterDirectionType} to a valid parameter direction type. Valid types are: {allowedParameterDirectionTypes} (10161)

    ServiceException

    Could not parse string {parameterDataType} to a valid parameter data type. Valid types are: {allowedParameterDataTypes} (10162)

    Back to top Copyright © Kendox