Executing Parameteric SQL Object: the SetDbCommand Operation

Below is the example of using the SetDbCommand operation that is used to set parameter values and execute parametric SQL object. The request contains the collection of object parameters. Values are set for input parameters. The response contains execution result with refreshed information about object parameters.

SOAP request:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

</tDbCommand>

</pattern>

<meta>

<type>Unknown</type>

<args>

<it>

<id>POPULATION_MIN</id>

<n>POPULATION_MIN</n>

</it>

<it>

<id>POPULATION_MAX</id>

<n>POPULATION_MAX</n>

</it>

<it>

<id>COUNTRY_COUNT</id>

<n>COUNTRY_COUNT</n>

</it>

</args>

</openArgs>

</meta>

</metaGet>

</tArg>

</SetDbCommand>

</s:Body>

</s:Envelope>

SOAP response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

<soapenv:Body>

</id>

<n>Function</n>

</obDesc>

</obInst>

<type>StoredProc</type>

<args>

<it>

<id>POPULATION_MIN</id>

<n>POPULATION_MIN</n>

<binding>UI="IntegerEdit" DIRECTION="Input"</binding>

</it>

<it>

<id>POPULATION_MAX</id>

<n>POPULATION_MAX</n>

<binding>UI="IntegerEdit" DIRECTION="Input"</binding>

</it>

<it>

<id>COUNTRY_COUNT</id>

<n>COUNTRY_COUNT</n>

<binding>UI="IntegerEdit" DIRECTION="ReturnValue"</binding>

</it>

</args>

</execResult>

</meta>

</SetDbCommandResult>

</soapenv:Body>

</soapenv:Envelope>

JSON request:

{
 "SetDbCommand" : 
  {
   "tDbCommand" : 
    {
     "id" : "S2!M!S!M3"
    },
   "tArg" : 
    {
     "pattern" : 
      {
       "obInst" : "true",
       "openArgs" : "true"
      },
     "meta" : 
      {
       "type" : "Unknown",
       "openArgs" : 
        {
         "args" : 
          {
           "it" : 
            [
              {
               "k" : "4294967295",
               "id" : "POPULATION_MIN",
               "n" : "POPULATION_MIN",
               "vis" : "true",
               "value" : "140000000",
               "dt" : "2"
              },
              {
               "k" : "4294967295",
               "id" : "POPULATION_MAX",
               "n" : "POPULATION_MAX",
               "vis" : "true",
               "value" : "150000000",
               "dt" : "2"
              },
              {
               "k" : "4294967295",
               "id" : "COUNTRY_COUNT",
               "n" : "COUNTRY_COUNT",
               "vis" : "true",
               "dt" : "2"
              }
            ]
          }
        }
      },
     "metaGet" : 
      {
       "obInst" : "true",
       "exec" : "true"
      }
    }
  }
}

JSON response:

{
 "SetDbCommandResult" : 
  {
   "id" : 
    {
     "id" : "S2!M!S!M3"
    },
   "meta" : 
    {
     "obInst" : 
      {
       "obDesc" : 
        {
         "@fullUrl" : "\/",
         "@isShortcut" : "0",
         "@isLink" : "0",
         "i" : "OBJ9056",
         "n" : "Function",
         "k" : "9056",
         "c" : "3842",
         "p" : "9050",
         "h" : "0"
        }
      },
     "dirty" : "1",
     "type" : "StoredProc",
     "execResult" : 
      {
       "rowsAffected" : "-1",
       "args" : 
        {
         "args" : 
          {
           "it" : 
            [
              {
               "k" : "1",
               "id" : "POPULATION_MIN",
               "n" : "POPULATION_MIN",
               "vis" : "1",
               "value" : "140000000",
               "dt" : "2",
               "binding" : "UI="IntegerEdit" DIRECTION="Input""
              },
              {
               "k" : "2",
               "id" : "POPULATION_MAX",
               "n" : "POPULATION_MAX",
               "vis" : "1",
               "value" : "150000000",
               "dt" : "2",
               "binding" : "UI="IntegerEdit" DIRECTION="Input""
              },
              {
               "k" : "3",
               "id" : "COUNTRY_COUNT",
               "n" : "COUNTRY_COUNT",
               "vis" : "1",
               "value" : "1",
               "dt" : "2",
               "binding" : "UI="IntegerEdit" DIRECTION="ReturnValue""
              }
            ]
          }
        }
      }
    }
  }
}

    public static SetDbCommandResult ExecuteSQLObject(DbCommandId moniker)
    {
        var somClient = new SomPortTypeClient(); //Proxy object for operation execution
        //Operation execution parameters
        var tSet = new SetDbCommand()
        {
            tArg = new SetDbCommandArg()
            {
                pattern = new DbCommandMdPattern()
                {
                    openArgs = true
                },
                meta = new DbCommandMd()
                {
                    openArgs = new OdOpenArgs
                    {
                        args = new OdArg[]
                        {
                            new OdArg //Input parameter
                            {
                                k = uint.MaxValue,
                                id = "POPULATION_MIN",
                                n = "POPULATION_MIN",
                                dt = 2, //DbDataType.Integer
                                vis = true,
                                value = "140000000"
                            },
                            new OdArg //Input parameter
                            {
                                k = uint.MaxValue,
                                id = "POPULATION_MAX",
                                n = "POPULATION_MAX",
                                dt = 2, //DbDataType.Integer
                                vis = true,
                                value = "150000000"
                            },
                            new OdArg //Parameter that contains calculation result
                            {
                                k = uint.MaxValue,
                                id = "COUNTRY_COUNT",
                                n = "COUNTRY_COUNT",
                                dt = 2, //DbDataType.Integer
                                vis = true,
                            }
                        }
                    }
                },
                metaGet = new DbCommandMdPattern()
                {
                    exec = true
                }
            },
            tDbCommand = moniker
        };
        //Execution
        var tResult = somClient.SetDbCommand(tSet);
        return tResult;
    }