You are here:

GetQuery

Initial Build	2006.02
xWEB	Web Method
Response Type	XML Node

Caution! This documentation applies to netFORUM Enterprise xWeb. This documentation does not apply to the netFORUM Pro xWeb solution which also has a GetQuery web method. If you are developing an integration for Pro, then you must refer to the xWeb documentation for Pro.

The GetQuery web method is a versatile tool for retrieving information from a netFORUM Enterprise database. Virtually any xWeb integration will use GetQuery to pull information about customers, events, or any other area of netFORUM Enterprise edition. Essentially a web service version of the Query Tool in iWeb, GetQuery enables you to pull data from any table in netFORUM Enterprise your account is authorized to select from.

This method should not be used with the netFORUM Pro edition. The method is in the process of being deprecated for that solution.

GetQuery allows you to specify the Object name (via the szObjectName parameter), which selects the "main" Table you are querying. Next, you select the columns to be returned, the conditions that must be applied, and the order of the records being returned while hiding the exact table names and foreign keys necessary to create the SQL query. This is accomplished by using the netFORUM object model and List Table setup in iweb to control how tables should be joined to retrieve specific information.

List Tables are used throughout the netFORUM to control what information is displayed in List Results. A table list consists of the main table and then an optional number of additional table joins to related tables. The tables joins are controlled by specifying the overall sequence used to add the tables, whether a tables uses an inner join or a left join, any join conditions, and a table alias if one is necessary.

Although GetQuery simplifies data extraction by "flattening" relational tables and linking the normalized tables together, the integrator will still need to have some idea of which tables and columns to query.

Beginning in 2007.01, the GetQueryDefinition web method can be used as a documentation/reference to get more information and a "data dictionary" about the list table setup.

Parameters

szObjectName (string). The name of the netFORUM Object. See xWeb:GetFacadeObjectList web method for a list of all netFORUM objects. Based on the Object chosen, different tables are enabled for selecting. See xWeb:GetFacadeObjectList#Object_Name for more direction on this parameter.
szColumnList (string). The list of Columns to return.
szWhereClause (string). The WHERE clause, if any. Do not include the actual word where.

You may need to include the From Table alias for columns if you get the following error: Object Reference Not Set to a Reference of an Object.

Example:

Wrong:

<m:szColumnList>cmc_cmt_key, cmc_start_date, cmc_end_date, cmc_status, eml_address </m:szColumnList>

Corrected by adding table alias:

<m:szColumnList>mb_committee_x_customer.cmc_cmt_key, 
mb_committee_x_customer.cmc_start_date, mb_committee_x_customer.cmc_end_date,
 mb_committee_x_customer.cmc_status, eml_address </m:szColumnList>

The szWhere clause can contain complex conditions such as calling a UDF:

<ns:GetQuery>
<ns:szObjectName>Individual</ns:szObjectName>
<ns:szColumnList>a,b,c,d</ns:szColumnList>
<ns:szWhereClause>dbo.fn_client_xyz_is_individual_qualified(ind_cst_key)=0</ns:szWhereClause>
<ns:szOrderBy></ns:szOrderBy>
 </ns:GetQuery>

Another example of a complex condition using GETDATE() in the szWhere:

<ns:GetQuery>
<ns:szObjectName>MailingList</ns:szObjectName>
<ns:szColumnList>mls_key,mls_name,mls_desc,mls_type_code,mls_show_online, </ns:szColumnList>
<ns:szWhereClause>mls_delete_flag=0 AND getdate() between mls_start_date and mls_end_date</ns:szWhereClause>
<ns:szOrderBy>mls_type_code, mls_name</ns:szOrderBy>
</ns:GetQuery>

szOrderBy (string). Sort Order

The manner in which the from clause is created is based on the table's list for the table that the object is based on. If additional tables are necessary they should be added by a netFORUM administrator to the List Tables setup.

Response

XML Node.

Performance

Main Article: XWeb_Performance_Tuning#GetQuery_Performance

The GetQuery web method is the most used--and misused--web method in xWeb. Please see main article for performance tips to ensure your application is fast and uses GetQuery effectively and efficiently.

Record Count Limit

If you are wondering why your xWeb GetQuery calls seems to cap the number of records returned, then read this.

The number of records returned by the GetQuery webmethod is constrained by the value of the global System Option called DataGridRowLimit. This setting is set by the site's system administrator.

However, if the optional xWeb configuration setting for xwebRecordReturn is entered in /xweb/web.config, then that value overrides DataGridRowLimit. This configuration setting first appeared in the 2006.02 build; it can be higher or lower than the value of DataGridRowLimit, or the setting does not have to exist at all if DataRowGridLimit is an acceptable limit. This setting is set by the site's system administrator.

If you always want to return all records (regardless of the two settings described above), then issue the TOP -1 command with the szObjectName parameter. For example, in the szObjectName parameter used for the Individual object, then you could pass "Individual @TOP -1" where the "-1" parameter returns all records for the szColumnList - note: specific, named fields must be passed as parameter in the szColumnList variable in order to process using the "-1" parameter. For example:

To get all records:

            
                <ns:szObjectName>
            Individual @TOP -1</ns:szObjectName>

To get only the first 5,000 records:

<ns:szObjectName>Individual @TOP 5000</ns:szObjectName>

The @TOP feature was added in the 2006.01 build. Summary:

@TOP -1 OVERRIDES xwebRecordReturn WHICH OVERRIDES DataGridRowLimit

Case Study GetQuery Web Method Calls

Given that GetQuery can be used in so many ways, we are putting examples on their own case study pages.

Updates

Column Aliases

In 2007.01, GetQuery will allow for column name aliases.

In earlier versions, you could not alias a column in szColumnList. It is desirable to do so when you have aliased Tables, in order to distinguish between elements of the same name.

In earlier builds, this is not permitted and returns a fault:

<ns:szColumnList>[LastName] = ind_last_name, 
[Membership_mbr_expire_date] = Membership.mbr_expire_date, 
[OrgMembership_mbr_expire_date] = OrgMembership.mbr_expire_date</ns:szColumnList>

Starting in 2007.01, this will result in this response:

<IndividualObject>
<ind_cst_key>96769941-0f6f-4466-8a19-c759da752a8e</ind_cst_key>
<lastname>Baghdatis</lastname>
<membership_mbr_expire_date>8/31/2008 12:00:00 AM</membership_mbr_expire_date>
<orgmembership_mbr_expire_date xsi:nil="true"/>
</IndividualObject>

Note that column names are converted to all lowercase.

Any sort of column manipulate is not allowed and will result in a fault, for example:

[FirstLetter] = LEFT(ind_last_name, 1)

Doing something like this will be ignored in the result:

[Name] = ind_first_name + ' ' + ind_last_name

2006.02

For 2006.02:

GetQuery() method returns the default column listing for the object's primary table if the empty string is passed in the szColumnList parameter - the primary key for the object will still be returned in the node as the first child, regardless if the default list of elements included the primary key or not.
the GetQuery() method can be passed an added value to the szObjectName parameter - if the requesting party requests the "Individual" object, then the szObjectName can be "Individual @TOP 5" and xWeb returns the top 5 records for the object.
the GetQuery() method can be passed an added value to the szObjectName parameter - for example, if the requesting party requests the "Individual" object, then the szObjectName can contain "Individual @META" where xWeb returns a portion of the Xml Schema for the elements requested in the szColumnList.

Troubleshooting

GetQuery Does Not Recognize Columns from GetFacadeXMLSchema

Issue: If you are running a GetQuery web method on a particular Object, and you are trying to include columns in the szColumnList that appear in the Object definition based on GetFacadeXMLSchema, and those columns are from related tables (not the primary table) in some cases you might run into a soap exception. This can happen if the column belongs to a table that is a Data Object of the main table's Object, but is not included as a From Table of the Object's primary table (by way of the obj_prefix property of the Object).

The GetQuery web method operates based on the List Table/From Table setup, not the Object/Data Object setup. Typically, any data objects will also wind up as List From Tables as well, but there might be a mismatch in some cases.

By contrast, the GetFacadeXMLSchema web method DOES look at the Data Object(s) and not the List Table setup. For example, if you see org_acronym in the GetFacadeXMLSchema response, then this is because the [co_organization] table is a Data Object for your Object. If you were to call the GetFacadeObject web method for a single instance of this Object, then you WOULD get back a value in org_acronym.

This may seem like an inconsistency, but there are reasons that List Table setup and Data Object setup are not 100% the same. See List Table vs Data Object for more.

Based on the example above, if you want to be able to pull org_acronym in a GetQuery call, then you will need to go to that Object's primary list table (by way of obj_prefix aligning with md_table.mdt_prefix) and add co_organization as a From Table. It is not necessary to add From Columns as well, all you need is the From Table.

To verify if this condition is affecting your GetQuery call, run this SQL in a SQL Query Analyzer, replacing the two lines referencing co_organization with the table name you are working with:

SELECT obj_name, *
FROM md_table_list_table
JOIN md_table_list_from ON lsf_lst_key = lst_key
JOIN md_table ON mdt_name = lst_mdt_name
JOIN md_object ON obj_prefix = mdt_prefix
WHERE lst_key NOT IN (SELECT lsf_lst_key FROM md_table_list_from WHERE lsf_from_table = 'co_organization' )
 
AND obj_name IN 
(SELECT obj_name FROM md_object WHERE obj_key  IN 
(SELECT obd_obj_key FROM md_object_data 
WHERE obd_table_name = 'co_organization'))
ORDER BY obj_name

If you see your Object in the results, then the solution described above will solve the problem.

GetQuery Does Not Recognize Columns from Data Objects

Q. I’m running GetQuery on the EventsRegistrant object. Fields such as evt_title, reg_ixo_key, ind_full_name_cp, and cst_name_cp work in GetQuery’s 2nd parameter, which is the column list to select. However, fields such as org_name, ixo_org_cst_key, org_cst_key, org_acronym, ixo_key give errors, even though they are listed on the fields list in the Form Designer of a form based on object EventsRegistrant. Do you know why these fields don’t work?

A. This question is essentially the same as GetQuery Does Not Recognize Columns from GetFacadeXMLSchema above.

The columns available in GetQuery are based on List Tables and not Data Objects (see List Table vs Data Object for more on the differences between the two). To return these additional columns, you'll need to run different GetQuery calls from other Objects, or in the Toolkit add more List Tables to the Object (but do so with caution), or try to accomplish what you need with xWeb:ExecuteMethod, which enables you to write a more direct SQL command to get the data you need.

How Can I Find out What the "From Tables" Are?

Q. Since the GetFacadeXMLSchema web method does not document the available From Tables that are available to met in GetQuery, then how can I know what tables and columns are available?

A. If xWeb is in build 2007.01 or later, run the GetQueryDefinition web method to get this information. Xweb Laboratory provides a GUI to get this information easily.

If xWeb is earlier than this build, then run this GetQuery web method to get a list of the tables that are available in GetQuery. You will of course need to change the Token value and enter the name of the table in the szWhereClause, which currently shows the example of ev_event_speaker:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>5665bfa6-721a-4b90-9996-60e2b3176032</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:GetQuery>
<ns:szObjectName>md_table_list_from</ns:szObjectName>
<ns:szColumnList>*</ns:szColumnList>
<ns:szWhereClause>lst_mdt_name = 'ev_event_speaker'</ns:szWhereClause>
<ns:szOrderBy>lsf_from_order</ns:szOrderBy>
</ns:GetQuery>
</soapenv:Body>
</soapenv:Envelope>

Here is a response:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>03698914-f752-4eef-9d32-805c8aae88be</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<GetQueryResponse xmlns="http://www.avectra.com/2005/">
<GetQueryResult>
<md_table_list_fromObjects recordReturn="5" xsi:schemaLocation="http://www.avectra.com/2005/ md_table_list_from.xsd">
<md_table_list_fromObject>
<lsf_key>befc1e42-c2e4-45f1-ab09-b9cd958ab2f9</lsf_key>
<lsf_lst_key>936e6c9d-3002-4158-bef9-534ed10a5e22</lsf_lst_key>
<lsf_from_table>ev_event_speaker</lsf_from_table>
<lsf_from_order>0</lsf_from_order>
<lsf_from_join_type xsi:nil="true"/>
<lsf_from_join xsi:nil="true"/>
<lsf_from_alias xsi:nil="true"/>
<lsf_add_user>dmanning</lsf_add_user>
<lsf_add_date>8/14/2002 11:42:47 AM</lsf_add_date>
<lsf_change_user>dmanning</lsf_change_user>
<lsf_change_date>8/14/2002 12:49:19 PM</lsf_change_date>
<lsf_delete_flag>0</lsf_delete_flag>
<lsf_entity_key xsi:nil="true"/>
</md_table_list_fromObject>
<md_table_list_fromObject>
<lsf_key>65c802cb-f01b-4e41-b080-0b0b47758be7</lsf_key>
<lsf_lst_key>936e6c9d-3002-4158-bef9-534ed10a5e22</lsf_lst_key>
<lsf_from_table>ev_event_faculty</lsf_from_table>
<lsf_from_order>1</lsf_from_order>
<lsf_from_join_type>Join</lsf_from_join_type>
<lsf_from_join>spk_fac_key=fac_key</lsf_from_join>
<lsf_from_alias xsi:nil="true"/>
<lsf_add_user>dmanning</lsf_add_user>
<lsf_add_date>8/14/2002 12:49:10 PM</lsf_add_date>
<lsf_change_user>dmanning</lsf_change_user>
<lsf_change_date>8/14/2002 12:57:54 PM</lsf_change_date>
<lsf_delete_flag>0</lsf_delete_flag>
<lsf_entity_key xsi:nil="true"/>
</md_table_list_fromObject>
<md_table_list_fromObject>
<lsf_key>b76c29b9-f565-40f1-828f-dd6637406e96</lsf_key>
<lsf_lst_key>936e6c9d-3002-4158-bef9-534ed10a5e22</lsf_lst_key>
<lsf_from_table>co_customer</lsf_from_table>
<lsf_from_order>2</lsf_from_order>
<lsf_from_join_type>Join</lsf_from_join_type>
<lsf_from_join>fac_cst_key=cst_key</lsf_from_join>
<lsf_from_alias xsi:nil="true"/>
<lsf_add_user>dmanning</lsf_add_user>
<lsf_add_date>8/14/2002 12:50:14 PM</lsf_add_date>
<lsf_change_user xsi:nil="true"/>
<lsf_change_date xsi:nil="true"/>
<lsf_delete_flag>0</lsf_delete_flag>
<lsf_entity_key xsi:nil="true"/>
</md_table_list_fromObject>
<md_table_list_fromObject>
<lsf_key>acc9a5dd-e79d-4cdf-b064-2fda35485c0a</lsf_key>
<lsf_lst_key>936e6c9d-3002-4158-bef9-534ed10a5e22</lsf_lst_key>
<lsf_from_table>ev_event</lsf_from_table>
<lsf_from_order>30</lsf_from_order>
<lsf_from_join_type>Join</lsf_from_join_type>
<lsf_from_join>fac_evt_key=evt_key</lsf_from_join>
<lsf_from_alias xsi:nil="true"/>
<lsf_add_user>tj</lsf_add_user>
<lsf_add_date>11/10/2002 2:01:56 PM</lsf_add_date>
<lsf_change_user xsi:nil="true"/>
<lsf_change_date xsi:nil="true"/>
<lsf_delete_flag>0</lsf_delete_flag>
<lsf_entity_key xsi:nil="true"/>
</md_table_list_fromObject>
<md_table_list_fromObject>
<lsf_key>e92301fa-86f0-4c54-a81b-d53b945c99d7</lsf_key>
<lsf_lst_key>936e6c9d-3002-4158-bef9-534ed10a5e22</lsf_lst_key>
<lsf_from_table>ev_session</lsf_from_table>
<lsf_from_order>40</lsf_from_order>
<lsf_from_join_type>Left Join</lsf_from_join_type>
<lsf_from_join>fac_ses_key=ses_key</lsf_from_join>
<lsf_from_alias xsi:nil="true"/>
<lsf_add_user>tj</lsf_add_user>
<lsf_add_date>11/11/2002 10:16:31 AM</lsf_add_date>
<lsf_change_user>tj</lsf_change_user>
<lsf_change_date>11/11/2002 10:25:03 AM</lsf_change_date>
<lsf_delete_flag>0</lsf_delete_flag>
<lsf_entity_key xsi:nil="true"/>
</md_table_list_fromObject>
</md_table_list_fromObjects>
</GetQueryResult>
</GetQueryResponse>
</soap:Body>
</soap:Envelope>

To get the columns for a particular table, run this request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>69e62b34-99f1-49bf-9223-2a122b5aff2f</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:GetQuery>
<ns:szObjectName>Column</ns:szObjectName>
<ns:szColumnList>*</ns:szColumnList>
<ns:szWhereClause>mdc_mdt_name = 'ev_event_speaker'</ns:szWhereClause>
<ns:szOrderBy>mdc_sort_order</ns:szOrderBy>
</ns:GetQuery>
</soapenv:Body>
</soapenv:Envelope>

You'll get this response (shortened for brevity):

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>547a6a58-0d95-4edc-bab8-7a9c936accc5</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<GetQueryResponse xmlns="http://www.avectra.com/2005/">
<GetQueryResult>
<ColumnObjects recordReturn="13" xsi:schemaLocation="http://www.avectra.com/2005/ Column.xsd">
<ColumnObject>
<mdc_key>b2f8edc9-9eb9-44f9-a5b9-468f1a0bb7ec</mdc_key>
<mdc_name>spk_fac_key</mdc_name>
<mdc_mdt_name>ev_event_speaker</mdc_mdt_name>
<mdc_table_name>ev_event_speaker</mdc_table_name>
<mdc_description>Unique Key</mdc_description>
<mdc_list_header xsi:nil="true"/>
<mdc_sort_order>100</mdc_sort_order>
<mdc_control_class xsi:nil="true"/>
<mdc_input_mask xsi:nil="true"/>
<mdc_data_type>av_key</mdc_data_type>
<mdc_width>16</mdc_width>
<mdc_width_max>16</mdc_width_max>
<mdc_nullable>0</mdc_nullable>
<mdc_ext>0</mdc_ext>
<mdc_readonly>0</mdc_readonly>
<mdc_readonlyedit>0</mdc_readonlyedit>
<mdc_hidden>0</mdc_hidden>
<mdc_required>1</mdc_required>
<mdc_autopostback>0</mdc_autopostback>
<mdc_has_lookup>0</mdc_has_lookup>
<mdc_not_editable>0</mdc_not_editable>
<mdc_change_log_flag>0</mdc_change_log_flag>
<mdc_value_from xsi:nil="true"/>
<mdc_value_column xsi:nil="true"/>
<mdc_value_text_column xsi:nil="true"/>
<mdc_value_where xsi:nil="true"/>
<mdc_value_where_add xsi:nil="true"/>
<mdc_value_orderby xsi:nil="true"/>
<mdc_default_value xsi:nil="true"/>
<mdc_validation_class xsi:nil="true"/>
<mdc_report_header1 xsi:nil="true"/>
<mdc_report_header2 xsi:nil="true"/>
<mdc_add_user>XMEN\dmanning</mdc_add_user>
<mdc_add_date>8/9/2002 5:11:03 PM</mdc_add_date>
<mdc_change_user xsi:nil="true"/>
<mdc_change_date xsi:nil="true"/>
<mdc_delete_flag>0</mdc_delete_flag>
<mdc_entity_key xsi:nil="true"/>
<mdc_query_select_flag>1</mdc_query_select_flag>
</ColumnObject>
<ColumnObject>
<mdc_key>af87919f-76f8-4bb2-9588-b15bf2735690</mdc_key>
<mdc_name>spk_spt_key</mdc_name>
<mdc_mdt_name>ev_event_speaker</mdc_mdt_name>
<mdc_table_name>ev_event_speaker</mdc_table_name>
<mdc_description>speaker type</mdc_description>
<mdc_list_header xsi:nil="true"/>
<mdc_sort_order>200</mdc_sort_order>
<mdc_control_class>DropDownList</mdc_control_class>
<mdc_input_mask xsi:nil="true"/>
<mdc_data_type>av_key</mdc_data_type>
<mdc_width>16</mdc_width>
<mdc_width_max>16</mdc_width_max>
<mdc_nullable>1</mdc_nullable>
<mdc_ext>0</mdc_ext>
<mdc_readonly>0</mdc_readonly>
<mdc_readonlyedit>0</mdc_readonlyedit>
<mdc_hidden>0</mdc_hidden>
<mdc_required>0</mdc_required>
<mdc_autopostback>0</mdc_autopostback>
<mdc_has_lookup>0</mdc_has_lookup>
<mdc_not_editable>0</mdc_not_editable>
<mdc_change_log_flag>0</mdc_change_log_flag>
<mdc_value_from>ev_event_speaker_type</mdc_value_from>
<mdc_value_column>spt_key</mdc_value_column>
<mdc_value_text_column>spt_code</mdc_value_text_column>
<mdc_value_where>spt_delete_flag = 0</mdc_value_where>
<mdc_value_where_add xsi:nil="true"/>
<mdc_value_orderby>spt_code</mdc_value_orderby>
<mdc_default_value xsi:nil="true"/>
<mdc_validation_class xsi:nil="true"/>
<mdc_report_header1 xsi:nil="true"/>
<mdc_report_header2 xsi:nil="true"/>
<mdc_add_user>XMEN\dmanning</mdc_add_user>
<mdc_add_date>8/9/2002 5:11:03 PM</mdc_add_date>
<mdc_change_user>dmanning</mdc_change_user>
<mdc_change_date>8/14/2002 1:17:22 PM</mdc_change_date>
<mdc_delete_flag>0</mdc_delete_flag>
<mdc_entity_key xsi:nil="true"/>
<mdc_query_select_flag>1</mdc_query_select_flag>
</ColumnObject>
</GetQueryResult>
</GetQueryResponse>
</soap:Body>
</soap:Envelope>

Working with Massive Data

xWeb is generally not the best vehicle to exchange massive amounts of data. xWeb is built toward working with smaller sets of data quickly and efficiently. Moving massive amounts of data over http will slow down servers and impact users and should be avoided. If you are doing a one-time import, that may be acceptable when planned in advance, but if this is necessary on a routine basis, a different solution may be called for.

That being said, if this is necessary for a one-time import, you might need to run a series of smaller queries to avoid possible timeouts when moving large numbers (10,000+, but YMMV depending on environmental variables).

Here is one possible approach:

Add the column cst_recno to the query columns (this is what you will loop on).
Change query to maybe SELECT TOP 1000 ... WHERE cst_recno > "startrecno"Create a looping mechanism to call the xweb query.
START LOOP (while rows returned > 0
  Set looping param "startrecno" = 0
  Run GetQuery
  Place the max(cst_recno) into "startrecno"  Process through GetQuery results and do what you need to do
LOOP

Here is a simplified pseudo-code version that can help you process through a large set of customers. You will see that you keeep calling GetQuery until you no longer get any records:

            string szColList = "cst_key, cst_recno, cst_sort_name_dn";string szObject = "Individual";string szWhere = "";string szOrderBy = "cst_recno"; int iHighestCstRecno = 0;array a; /// array to contain results from web method while(true){  // where clause - get any records with cst_recno > than the  // highest one that you could process in the previous loop iteration  szWhere = "cst_recno > " iHighestCstRecno.ToString();   a = this.CallGetQuery(szObject, szColList, szWhere, szOrderBy);  if a.count == 0  {    // no more records left, therefore break out    break;  }  // process through records in array...  for(int i = 0; i < a.Count; i++)  {    // process each record...    this.DoSomething( a[i, 0] , a[i, 1] , a[i, 2] );    // set variable's value with "cst_recno" which is in the [i, 1] array element    iHighestCstRecno = a[i, 1];  }} public array CallGetQuery(string szObject, string szColList, string szWhere, string szOrderBy){  // call xWeb's GetQuery web method with these values...}

This is just a way to write a looping procedure to keep calling GetQuery, in smaller chunks, until you get to the end of the data. This "work around" is actually a better way since you run less risk of timing out with trying to get a massive number records over the web service. Instead, churn through a smaller set, one set at a time.

If you want to ensure that you get all 5,000 records before you process any data, then store the results in memory (array or XML or however is appropriate) until you get through all your web method calls records, and then process your records.

Duplicate Records

Q. GetQuery seems to return duplicate records. I cannot figure out why. Help!

A. Remember that GetQuery is generated based on the List Table setup. It is possible that the combination of from tables and their join expressions could be causing inadvertent duplicates. Until 2007.01, it is not possible to add a distinct statement to GetQuery, so you will need to ensure that the List Table setup is not causing this. Take care in "fixing" this condition, because the "fix" might have a side effect with Queries on those tables and other related areas. Take great care in making any changes.

Invalid query error

Q. When I call GetQuery, I get this error:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Body>
<soap:Fault>
<faultcode>detail</faultcode>
<faultstring>An error occured ---> Invalid query.</faultstring>
<detail/>
</soap:Fault>
</soap:Body>
</soap:Envelope>

A. If the szWhereClause contains any keywords such as:

select
insert
update
delete
exec
execute
_entity_key

then you will get this error. This is to prevent any dangerous SQL commands from firing. This means you will NOT be able to do a subselect, such as:

<ns:szWhereClause>cst_key in (select cmc_cst_key 
from mb_committee_x_customer where cmc_cmt_key = 'aalkjdsflajdfadfads'
</ns:szWhereClause>

Problems with Table Alias

netFORUM List Tables can use a table alias if the same table is used more than once.

Before the 2007.01 build, GetQuery requires that the xWeb User (which is the user account that authenticates a program in the Authenticate web method) be a "super-user" in order to work with aliased tables. If the xWeb user is not a "super-user", then aliases will not work.

Here is an example of a GetQuery request that uses List From Table aliases with Membership, ChapterMembership and OrgMembership. These aliases are defined by the List Table From Table Alias, pictured on the right:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>bb3dae56-cad8-4d11-b813-44114d0ec839</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:GetQuery>
<ns:szObjectName>Individual</ns:szObjectName>
<ns:szColumnList>ind_first_name, Membership.mbr_expire_date, ChapterMembership.mbr_expire_date, OrgMembership.mbr_expire_date, OrgMembership.mbr_mbt_key</ns:szColumnList>
<ns:szWhereClause>cst_member_flag=1 and OrgMembership.mbr_mbt_key= '32C54456-7E1E-4C07-AB59-A7E66D11D9C9'</ns:szWhereClause>
<ns:szOrderBy>cst_sort_name_dn</ns:szOrderBy>
</ns:GetQuery>
</soapenv:Body>
</soapenv:Envelope>

Note that the result returns ambiguous element names. You will need to carefully parse the response to get back the correct columns:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/03/addressing" 
xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>bb3dae56-cad8-4d11-b813-44114d0ec839</Token>
</AuthorizationToken>
<wsa:Action>http://www.avectra.com/2005/GetQueryResponse</wsa:Action>
<wsa:MessageID>uuid:448f27ce-d5a3-42e1-bb53-68fa463114a0</wsa:MessageID>
<wsa:RelatesTo>uuid:59fa0476-0888-488f-b6b9-ab906fa57df3</wsa:RelatesTo>
<wsa:To>http://schemas.xmlsoap.org/ws/2004/03/addressing/role/anonymous</wsa:To>
<wsse:Security>
<wsu:Timestamp wsu:Id="Timestamp-56e8bbf8-74c0-4db1-ab96-7b3549ee7b05">
<wsu:Created>2007-09-13T14:22:00Z</wsu:Created>
<wsu:Expires>2007-09-13T14:27:00Z</wsu:Expires>
</wsu:Timestamp>
</wsse:Security>
</soap:Header>
<soap:Body>
<GetQueryResponse xmlns="http://www.avectra.com/2005/">
<GetQueryResult>
<IndividualObjects recordReturn="223" xsi:schemaLocation="http://www.avectra.com/2005/ Individual.xsd">
<IndividualObject>
<ind_cst_key>26222006-9005-46e6-b285-80189a96e927</ind_cst_key>
<ind_first_name>Sabry</ind_first_name>
<mbr_expire_date xsi:nil="true"/>
<mbr_expire_date xsi:nil="true"/>
<mbr_expire_date>12/31/2007 12:00:00 AM</mbr_expire_date>
<mbr_mbt_key>32c54456-7e1e-4c07-ab59-a7e66d11d9c9</mbr_mbt_key>
</IndividualObject>
<IndividualObject>
<ind_cst_key>2e4b25cb-8690-4f73-9c39-7bcf6ccf62ef</ind_cst_key>
<ind_first_name>Kirsten</ind_first_name>
<mbr_expire_date>10/31/2008 12:00:00 AM</mbr_expire_date>
<mbr_expire_date>12/31/2008 12:00:00 AM</mbr_expire_date>
<mbr_expire_date>12/31/2007 12:00:00 AM</mbr_expire_date>
<mbr_mbt_key>32c54456-7e1e-4c07-ab59-a7e66d11d9c9</mbr_mbt_key>
</IndividualObject>
<IndividualObject>
<ind_cst_key>2e4b25cb-8690-4f73-9c39-7bcf6ccf62ef</ind_cst_key>
<ind_first_name>Kirsten</ind_first_name>
<mbr_expire_date>10/31/2008 12:00:00 AM</mbr_expire_date>
<mbr_expire_date>1/31/2008 12:00:00 AM</mbr_expire_date>
<mbr_expire_date>12/31/2007 12:00:00 AM</mbr_expire_date>
<mbr_mbt_key>32c54456-7e1e-4c07-ab59-a7e66d11d9c9</mbr_mbt_key>
</IndividualObject>
</IndividualObjects>
</GetQueryResult>
</GetQueryResponse>
</soap:Body>
</soap:Envelope>

If the user is not a "super user" then GetQuery will not correctly resolve the columns with table aliases and method call will not work. See user for instructions on how to make the xWeb user be a "super user".

Beginning in 2007.01, the user will not need to be a "super user" to work with GetQuery table aliases.

Account is not authorized to perform Select

Main Article: XWeb User Object Security

If you get this error after running GetQuery:

Account is not authorized to perform Select on XXXXX object

where XXXXX is the object name that you passed in the szObjectName parameter, it is because your xWeb User does not have the necessary security settings to select data on this object. In order to get that permission, the netFORUM system administrator must grant select permissions on that Object in the xWeb User Object Security section of netFORUM iWeb in the Admin module. See main article for more.

Also, make sure that the name of the object you are passing in the szObjectName is in fact a valid object name. See XWeb:GetFacadeObjectList#Object_Name for more direction on this.

Asterisk (*) is not a valid value for szColumnList

If you attempt to run a GetQuery and pass the asterisk as the parameter value for szColumnList then you will get this fault.

For example, this request will result in that fault message:

<ns1:GetQuery>
<ns1:szObjectName>Individual @TOP -1</ns1:szObjectName>
<ns1:szColumnList>*</ns1:szColumnList>
<ns1:szWhereClause>ind_cst_key='c4b8add9-1673-4d3c-bae4-7861329b4b10'</ns1:szWhereClause>
</ns1:GetQuery>

To resolve this issue, select only the columns you need in szColumnList. Note: the ability to pass * was removed in the 2010.01 release of netFORUM.

The WebService URL does not exist

When trying to connect on the login page, you might see this error, where the 999 number is the IP address of the xWeb URL:

[+] The WebService URL does not exist. System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 99.999.999.99:999

If you get this error when trying to connect, first make sure that the URL you enter is the netFORUM xWeb URL address followed by ?WSDL.

If you are entering the URL in this format and still getting the error, make sure that this URL is correct by entering it into a browser and making sure you see the WSDL.

If the WSDL does appear, and the netFORUM site is hosted by Avectra, and the URL is an IP address and not a DNS name, then make sure the IP address is the internal version of an IP address. Ask your Avectra contact to ensure you're working with the right IP.

GetQueryLab needs the internal verison of the IP address because of internal Avectra firewall issues.

For your actual integration, you should use the external address for the web service location which ultimately should be a DNS and not an IP address.

FAQ

Q. I'm trying to use the Get Query method to return some results. The query that I want to run involves multiple joins among tables. Is there any way I can do this using Get Query? I was unable to find an example joining tables.

A. The available tables in GetQuery (which you can view in xWeb Lab or GetQueryDefinition) cannot be altered by an integrator. A netFORUM site administrator can add additional table joins in the netFORUM Toolkit by adding more List Tables. Work with your netFORUM client to have this done.

If that's not an option, or if the joins are too complex, then consider developing a specialized web method using ExecuteMethod. This will require some custom development but there are some scenarios where this is the only practical solution. Again, check with your netFORUM client to have this done.

Q. Are all the columns available in GetQuery able to be used in xWeb:UpdateFacadeObject and xWeb:InsertFacadeObject? Do the GetQuery columns overlap with the controls in xWeb:GetFacadeXMLSchema?

A. Yes and no. There are differences. See List Table vs Data Object for an explanation.

Q. Let's say I want to get information about an Object through GetQuery, then update that Object with xWeb:UpdateFacadeObject. How can I know which GetQuery columns correspond to what controls in the object schema that I will pass back to UpdateFacadeObject?

A. Usually this will be straightforward, but not always. In development, run xWeb:GetFacadeObject for a particular Object to see all the data coming back. Then run GetQuery for the same specific Object and record. Observe which controls in GetFacadeObject align with which data elements in the GetQuery response. Then, in production, run GetQuery to get the data you want, but be sure to map this data back to the corresponding schema controls. Once again, there is usually 99% percent correlation between the two. The exceptions are certain schema elements in the more complicated Objects such as Individual that might have two data objects pointing to the same database table. The schema for the columns in these two tables will have different names to avoid ambiguity.