ExecuteMethod
|
Initial Build |
2007.01 |
|
xWEB |
Web Method |
|
Response Type |
XMLNode |
ExecuteMethod is a more powerful tool than GetQuery for creating complex queries that return data as XML. The queries are defined within iWeb's DynamicXmlBuilder and can include multiple nested queries. The web method will return an XmlNode with nodes and elements based on the query definitions set up in iWeb. The query will be inline SQL or database stored procedure. ExecuteMethod will execute the SQL is in the query, and that query returns a single recordset.
Multiple variables can be defined in a query, and values can be passed as parameters to the query when the web service is invoked.
This method can be secured to allow only specific xWeb Users to make calls to specific methods. See for instructions on setting security for this web method.
Important! ExecuteMethod should only be used for reading data and should never be used for writing data. xWeb contains other methods for writing data. Writing data directly in this method circumvents the business rules in NetForum Objects and is not recommended or supported.
Parameters
The following parameters are used in ExecuteMethod:
- serviceName The name of the web service. Data type: string.
- methodName The name of the web service method. Data type: string.
- Parameters This parameter is optional and is a collection of one or more <Parameter> elements, each of which contains a <Name> and <Value>. Based on the <Name>, the parameter <Value> can be parsed in the node SQL. See the following example:
<parameters> <!--Zero or more repetitions:--> <Parameter> <Name></Name> <Value></Value> </Parameter> </parameters>
Response
XmlNode
Exceptions and Errors
xWeb returns the Invalid Security Credentials error when an xWeb user is not authorized to call a specific methodName. To authorize the user to call the method, go to the Web Service definition for the method and add the xWeb user to the authorized web servers child form. See Web Service #Web Method Security for more information.
Usage and Examples
Generally, the client or Abila will develop web method definitions in the Web Service section in NetForum, and provide the name of the web method definition and usage instructions to the vendor or developer of the calling program .
NetForum will automatically escape single quotes. For example, if you have a method for a member directory that has a search on last name with a parameter called LastName, the request should look like the following:
<parameters>
<Parameter>
<Name>LastName</Name>
<Value>O'Leary</Value>
</Parameter>
</parameters>
Case Studies
- xWeb: ExecuteMethod Case Study1 - Case study using queries for EventCalendar and EventDetail.
- xWeb: ExecuteMethod C# - VS.NET C# code showing how to code against this web method.
- xWeb: ExecuteMethod Advanced C# - Develop a new VS.NET Web Services application that leverages ExecuteMethod. This Web Service returns a serialized class for a more rigid schema.
Caching Results
Results of the web service can be cached on a time interval in minutes. Each unique combination of method and parameter values will be cached for the number of minutes specified.
If you have methods that return the same data repeatedly, such as a list of Committees or an Event Calendar that doesn't get updated often, consider caching. Caching is valuable because after the SQL is run for the first time, it saves the results in a binary file on the web server, which the web server can then open up and return without having to make any SQL commands and without the server round-trip to get the response from the SQL server. The web server will keep returning this file until it has aged more than the minutes duration. After this, xWeb will run the SQL again and regenerate the cached file.
Even if you cache every 10 minutes, if you have a web site that gets thousands of hits a day, you will running this method at most 144 times a day instead of thousands of times a day. (Even better, get the integrating application to cache these results so it doesn't call the web service thousands of times a day for data that is relatively static.)
If the data must be real time, is more volatile, or may return widely varying parameter values to your web method such as a cst_key to get information about a customer, do not enable caching. If you cache a web method that gets information about a customer, caching is bad because xWeb will cache these files all day long, and they will probably not even get used again.
To do this, you may add to the /xweb/web.config the following settings:
<appSettings>
.....
<!-- xWeb caching -->
<add key="EventMethods|GetEvents|CacheMinutesDuration" value="10"/>
<add key="ChapterMethods|GetChapters|CacheMinutesDuration" value="30"/>
<!-- end web caching -->
The format is: WebService Name | WebMethod Name | CacheDurationMinutes .
The value is how many minutes to cache the response. See DynamicXmlBuilder Cache for more information about caching.
When caching is enabled for a web method and AuthenticationTokenCachingPolicy is enabled, ExecuteMethod web calls will not run SQL commands after an initial result is cached.
Throwing Exceptions
When allowing parameters to be sent to the stored procedure it is often necessary to validate the parameter values. When a parameter fails validation or an error occurs while running the stored procedure, an error message needs to be returned to explain why the method failed. The RAISERROR function can be used to produce custom SQL errors that will be caught be ExecuteMethod and returned as a SOAP Exception. See RAISERROR for more details.
RAISERROR('Invalid OrganizationKey parameter. Key does not exist.',11,1)
The second parameter (11) is the message severity. The severity must be between 11 and 18 to be caught as an exception by the ExecuteMethod function and returned as a SOAP Exception.
JSON Method
POST /xWeb/JSON/ExecuteMethod
DATA: JSON
{
"ExecuteMethod": {
"serviceName": "TestExecuteMethodWithxWebJSON",
"methodName": "GetFirstLetterRootNode",
"parameters": [
{
"Name": "firstletter",
"Value": "B"
},
{
"Name": "constantValue",
"Value": "HereItIs"
}
]
}
}
SAMPLE RESULT
HTTP STATUS: 200
DATA: JSON
{
"root": {
"SortName": [
{
"cst_sort_name_dn": "B AA",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B BB",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B Hybrid",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "b K",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B Pooja",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B Pooja",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B Suman",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "B&B Enterprises",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "b13647669a3439ad",
"ConstValue": "HereItIs"
},
{
"cst_sort_name_dn": "b7c973970e441143",
"ConstValue": "HereItIs"
}
]
}
}
FAQ
Q. How can I know the names of the parameters I need to pass in the <Parameter>'s <Name> element for a particular method call?
A. The NetForum administrator who set up the site will need to tell you.
Q. It appears that what I need to pass to the <Value> element for a given parameter is a 36-character GUID. How can I know what that value is?
A. There is no universal answer to this question. Suppose the method returns information for a given Event. In this case, you will likely need to pass the primary key of that event. How will you know that key? You should probably make an earlier web method call to get a list of events, and get the event primary keys through that method call. Next, when the user or a program needs to get more information about a specific event, then you will have the value.
Q. I am the integrator working with this web method. How can I know the structure of the result set? Is there a schema or XSD? The WSDL shows that the return type is XmlNode.
A. Due to the flexible nature of this web method, there is not a hard schema. The only way to know the result type is to execute it and see the results. Usually, when you are given documentation, this should include the URL endpoint of the xWeb web service, the userName and password to call the Authenticate web method, as well as sample SOAP Requests and Responses for the various ways you should call the ExecuteMethod web method.
Q. When I run my SQL command in SQL, I get a valid XML response. But ExecuteMethod is not returning XML, it's just returning something like this: [XML_F52E2B61-18A1-11d1-B105-00805F49916B].
A. In the Web Method Node setup, in the Toolkit metadata in NetForum iWeb, are you sure that you named the root node xml? You need to name it this to cause ExecuteMethod to return the result as XML.
Q. I am trying to pass a list of multiple categories like this in the EventCategories Parameter on line 8 below:
- <soapenv:Body>
The SQL code in my method looks like this (simplified):
SELECT x, y, z FROM TABLE
WHERE ev_event_category.etc_code IN ({#EventCategories})
The special Parsed Value character # is supposed to automatically convert A,B,C into 'A','B','C' but it's just making it NULL and causing the method to throw an exception. What do I need to do to make this work?
A. Pass the same Parameter set again, only for the second iteration put a leading # in front of the parameter Name as shown on line 11 below. For example:
This second example above will parse properly and ultimately execute the following valid T-SQL command:
SELECT x, y, z FROM TABLE WHERE ev_event_category.etc_code IN ('A','B','C')
Q. I made some changes to the definition of an ExecuteMethod method and I don't see the changes reflected when I run the method. It still returns data the old way.
A. Clear cache and try again. Parts of ExecuteMethod metadata are cached for performance reasons. If you alter the setup, you may need to clear cache afterwards.
Q. In the response, if a column's value is null, then the node does not appear at all in the response. How can I ensure the column always returns?
A. To do this, the SQL command will need to include a "ELEMENTS XSINIL" directive to return columns with a null value. See FOR_XML_PATH_SQL#NULL_columns_excluded_from_response for more.