This design allows to call stored procedure and parameterized queries.
| Name | Type | Description |
|---|---|---|
| connection | part | Connection part, usually a Parent. |
| commandText | string | Required Command Text: Stored procedure name or SQL statement. |
| commandType | name | Required Command type: Stored procedure or SQL statement. (:StoredProcedure or :Statement) |
| InputParameters | list | (List of Lists) Specification of the Input Parameters Inner List describes the Parameter in format: {parameterName, dbType, value, size} where: * (String) parameterName - the name of the parameter of the stored procedure or SQL statement (it should start with the @, for example "@parIn") * (DbType) dbtype - see documentation on System.Data.DbType enumeration for the possible values. Example: System.Data.DbType.String * value - the input value * size - should be specified for strings/BLOBs to indicate the storage size, otherwise should be set to -1 There are ado* helper methods in %%dbConnection mixin that you may call |
| OutputParameters | any | (List of Lists) Specification of the Output Parameters Inner List describes the Parameter in format: {parameterName, dbType, value, size} where: * (String) parameterName - the name of the parameter of the stored procedure or SQL statement (it should start with the @, for example "@parOut") * (DbType) dbtype - see documentation on System.Data.DbType enumeration for the possible values. Example: System.Data.DbType.String * value - the input value * size - should be specified for strings/BLOBs to indicate the storage size, otherwise should be set to -1 There are ado* helper methods in %%dbConnection mixin that you may call |
| Name | Type | Description |
|---|---|---|
| ResultSet | list | The result set returned from the Stored Procedure or Statement, if any |
| OutputValues | list | List of the results. The values correspond to the OutputParameters. |
In this case, the result is returned in a result set.
| Child Name: | spRun | |
| Child Design: | DbQueryCommand | |
| Name | Type | Supplied |
| connection | part | Root.sqlServerConnection |
| CommandText | string | "ReadResultSet" |
| commandType | name | :StroredProcedure |
Stored Procedure:
PROCEDURE dbo.ReadResultSet AS
SELECT CarID, CarType, Color FROM CarsTable
Intent > ResultSet
--> {{1, "Pontiac ", "Red "}, {2, "Lada ", "Green "}, {3, "BMW ", "Black "}, {4, "Chrysler ", "Yellow "}, {5, "FIAT ", "Brown "}}
In this case, the result is returned in output parameters. Please note, the order is determined by the order of the descriptions in the OutputParameters rule.
| Child Name: | carSpecs | |
| Child Design: | dbQuerycommand | |
| Name | Type | Supplied |
| Connection | part | Root.sqlServerConnection |
| CommandType | name | :Statement |
| CommandText | string | " SELECT @outColor = Color, @outType =CarType from CarsTable where CarID = @carID " |
| InputParameters | list | {adoIntegerParameter(" @carID ", 1)} |
| OutputParameters | list |
{adoStringParameter(" @outType ", "", 50), _ adoStringParameter(" @outColor ", "", 50)} |
Intent >OutputValues
--> {"Pontiac ", "Red "}