xWeb Laboratory

xWeb GetQuery Laboratory, located at http://xweblab.avectra.com, is a website developed and hosted by Abila to assist integrators with the GetQuery web method in xWeb. GetQuery Laboratory connects directly to your xWeb web service and provides a user-friendly interface to generate GetQuery method requests and view the responses. Use this tool to get familiar with GetQuery, to browse netFORUM Objects available to you, and try out various GetQuery calls and then plug those calls into your application.

xWeb Laboratory also can be used as a quick diagnostics test as described in xWeb Diagnostics.

Overview

GetQuery is the linchpin of any xWeb netFORUM integration as this web method is used to retrieve any kind of data from netFORUM.

With its flexibility, reach and power, GetQuery can be challenging to work with for integrators. netFORUM has many hundreds of database tables, and more than ten thousand columns, and it can be difficult to know where to start.

The GetQuery Laboratory provides a visual wizard to help you generate GetQuery requests that you can then incorporate into your integration. The tool allows you to enter the URL address of your netFORUM xWeb web service and the username and Password of your xWeb User (which the Laboratory will use to run the Authenticate web method). Once you have authenticated to your xWeb site, then you go to a page where you select the parameters for a GetQuery web method call.

The tool will show you the SOAP request and response based on the fields you choose. Additionally, a sample Extensible Stylesheet Language template is generated, and the results are transformed through this XSL page to show you HTML results. The actual XML results are displayed. And, finally, a sample C# .NET sample code is displayed.

Instructions

Navigate to URL

The URL address of the GetQuery Laboratory website is:

http://xweblab.avectra.com

Authenticate

On the login page, enter the URL of your xWeb site (it will end in ".asmx") as well as the userName and password for the xWeb User and click Authenticate. Do not enter a network account for an iWeb user or an eWeb Login account. This userName/password is for the xWeb user that will authenticate the program.


When you press the Authenticate button, GetQuery Lab will connect to the xWeb Web Service at the URL address you specify, and pass the userName and password entered to the Authenticate web method. If authentication succeeds, then you will be presented with the main screen. Otherwise, an error message will be displayed.

Choose Object

Choose the Object such as Individual, Organization etc., from the drop-down list, that you want to query.

What you see in this list is the Object description followed by the Object Name in parentheses. When you interact with this Object in xWeb, you will pass the value of the Object Name to other web methods in the szObjectName parameter.

For example, in the nearby image, the object with the description of Name Prefix has an object name of co_prefix. If you work with this Object, you should pass co_prefix as the value to the szObjectName parameter in other web methods. See xWeb:GetFacade ObjectList#Object_Name for more detail on this topic.

Choose Columns

Choose the specific Columns you want to output. Depending on the build of the site you are connecting to, you may select the columns from a ListBox or a TreeView, or both.

TreeView

Use the TreeView column chooser to select the columns you want to query. First, expand the main table, then expand each From Table and check any columns you want to return. Once you have chosen your columns, click Run to see the results.


When you click Run, the TreeView will collapse to make room for the results. You can expand the TreeView again to change your columns around, then click Run again to run the query again. When the Object loads initially, a handful of columns from the first From Table are selected.

ListBox

The ListBox column chooser allows you to select your columns from a multi-select ListBox:


These columns come from the List Table setup. See GetQueryDefinition for an explanation of which Tables and Columns are available.

The format of the column list is:

TableName(alias)::ColumnName (Column Description)

for example:

co_individual::ind_first_name (First Name)

or:

co_customer(OrgCustomer)::cst_sort_name_dn (Sort Name)

If the from table does not have an alias, there will be no parentheses.

Column Aliases

If you choose columns that belong to a From Table with an alias, xWeb Laboratory will alias the column like this: 

org_customer.cst_sort_name_dn AS org_customer_cst_sort_name_dn

In your own application, you may choose to alias these columns differently. See xWeb:GetQuery#Column_Aliases for more information.

Column Chooser Tips

  • If you select columns in both the TreeView and the Columns ListBox, the application will ignore what is in the ListBox and instead work with whatever you've chosen in the TreeView.
  • If you get a required field red error when you click Run with nothing selected in the ListBox, click any field in the Columns ListBox to make this message go away.
  • After checking the columns you want in the TreeView, click Run to run GetQuery again.
  • After clicking Run, wait for the page to post back and refresh, and then click on HTML Output or one of the other tabs to see the fresh response.

Top

Enter the number of records to return. The default value is 10. The maximum is 100. See GetQuery#Record_Count_Limit for more information.

Enter Where Clause

Enter your Where clause. The default is to exclude hide/deleted records. Add additional filters as needed.

Enter Order By

Enter the order by. The default is based on the Object's default order by property.

Click Run

Click Run to generate the results based on the values you chose above. Each time you change any of these values, you will need to click Run again to fetch new results.

Output

The results from the GetQuery are shown on eight separate tabs Click the tab header to view that output.


Each output type is explained in the sections below.

HTML Output

Shows a HTML grid of the results. This is actually the result of an XSL Transform, based on the contents of the XSL Template panel.

SOAP Request

Shows the SOAP request for the GetQuery.

GetQuery Lab SOAP Request

You will see that table aliases will be included if the column you chose has a from table with an alias.

SOAP Response

XML Node

XSL Template

Shows a Extensible Stylesheet Language template to transform the XML results. This is a starting point to give you an idea of how to output results in XSL.

The results on the first tab is the result of being transformed through this template.

You can also edit the XSL template and see those changes reflected on the HTML tab. If you change the columns and click Run again, however, this will not update the XSL that you changed.

.NET Template

This tab shows a simple C# .NET code fragment suggesting how this web method could be invoked in .NET.

In xWeb Laboratory, the .NET Template is a more customized version of the following: 

netForumXML nfXml = new netForumXML();
nfXml.Url = "http://127.0.0.1/netforum/xweb/secure/netForumXML.asmx";
nfXml.Authenticate("username", "******"); // Replace with actual password.
 
string strObject = "Individual @TOP 10";
string strColumns = "ind_first_name,ind_last_name";
string strWhere = "ind_delete_flag=0";
string strOrderBy =  "ind_last_name,ind_first_name";
 
XmlNode node = nfXml.GetQuery(
    strObject,
    strColumns,
    strWhere,
    strOrderBy
);
 
// Lists the ind_first_name for each Individual node.
foreach (XmlNode xnIndividual in node.ChildNodes)
{
    Console.WriteLine(xnIndividual["ind_first_name"].InnerText);
}
 
// Writes out the entire XML node
Console.WriteLine(node.OuterXml);

XML Schema (XSD)

This pane shows a schema that could be used based on the supplied GetQuery definition. A generated class in C# .NET or VB.NET is shown in the next section. The auto-generated schema is: 

            <?xml  version="1.0"  encoding="utf-16"?>
<xs:schema  id="NewDataSet"  targetNamespace="http://www.avectra.com/2005/"  
xmlns:mstns="http://www.avectra.com/2005/"  
xmlns="http://www.avectra.com/2005/"  
xmlns:xs="http://www.w3.org/2001/XMLSchema"  
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"  
attributeFormDefault="qualified"  elementFormDefault="qualified">
  <xs:element  name="IndividualObjects">
    <xs:complexType>
      <xs:sequence>
        <xs:element  name="IndividualObject"  minOccurs="0"  maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element  name="ind_cst_key"  type="xs:string"  minOccurs="0"  />
              <xs:element  name="ind_first_name"  type="xs:string"  minOccurs="0"  />
              <xs:element  name="ind_last_name"  type="xs:string"  minOccurs="0"  />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute  name="recordReturn"  form="unqualified"  type="xs:string"  />
    </xs:complexType>
  </xs:element>
  <xs:element  name="NewDataSet"  msdata:IsDataSet="true"  msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice  minOccurs="0"  maxOccurs="unbounded">
        <xs:element  ref="IndividualObjects"  />
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

The same result can be accomplished at the MS-DOS command line: 

xsd.exe xmlnode.xml

Generated Class

This page shows an auto-generated C# .NET or VB.NET class based on the XSD above. The image is abbreviated. See xWeb Laboratory Generated Class for complete text. The class is auto-generated.


For case studies on how to use a strongly-typed class such as this, see xWeb: ExecuteMethod C# and xWeb: ExecuteMethod Advanced C#. The second link uses a class generated by this feature in a xWeb integration.

Errors

If you receive an error, you will see a red message on the upper left of the page. Click the link to see the full error.

Object Security

see: xWeb User Object Security

Object security is a method of limiting the objects an xWeb user can access. Once object security is enabled for an xWeb user, all access rights to the netFORUM objects are revoked. Permission to select, insert, update or delete records in netFORUM must be granted for each object. When object is security is enabled access to the objects required by the xWeb Laboratory to function is revoked and must be restored before the xWeb Laboratory can be used. An xWeb user must have select permission to the following objects for xWeb Laboratory to function:

  • Column
  • md_table_list_column
  • md_table_list_from
  • md_table_list_table
  • Object
  • md_table

Troubleshooting

Authentication Errors

The xWeb Laboratory authenticates from a server in the Abila data center. If you've configured IP Security on the username, you will need to add the IP of this server as an allowed IP. Also, if you are using an IP for your xWeb URL, you may need to use the Abila internal IP to ensure that the two servers can connect correctly.

Other Errors

Q. The page authenticates, but the GetQuery page does not work.

A. Make sure your xWeb User is permitted to select data from the various metadata tables.

Q. I'm trying to get a simple list of records, and it looks like I have results, but they are blank.

A. In the where clause, add a filter that omits blank records, for example for individuals: 

            AND ltrim(rtrim(ind_last_name)) != ''

The reason you're seeing blank results is because your database has some individuals with the empty string in the first and last name, and those blanked-out individuals are coming up at the top of the sort list.

Q. The application is timing out.

A. There is a 20-minute session limit. If you are idle for longer than that, you will need to log back in to restart your session. Consider using the bookmark feature to make a browser favorite (right-mouse-click the link and choose "add to favorites").

Q. I'm not seeing the Tables and Columns that I need in the column chooser. How can I get more tables and columns?

A. To get more tables, the List Table setup for the Object needs to be expanded. Talk to Abila or your site administrator if you believe you need more table joins.

Issues with TreeView and ListBox Not Working

As of 6/7/2013, we are aware of some UI glitches with the TreeView and ListBox. It appears that the UI controls that we originally used to develop this app years ago are having trouble with modern browsers. We are working on this. In the meanwhile, if you do it just right, here is how to get TreeView to work in IE10 (and probably other browsers). We understand that this is not ideal, but it will function.

  1. Select the Object you want to work with from the Object drop-down list.
  2. Click the TreeView link.
  3. Point your mouse underneath the letter "i" in ListBox and just a tiny bit underneath it. The target area is a bit tiny so you may need to move your mouse around slowly to get it.
  4. Click your mouse button and you will be able to expand the tree view and then you can start selecting tables and columns.

As for the ListBox, it is not doing a postback when you select columns. Therefore, please use the TreeView as an alternate.