Data handling

Order By clause

If the order of the data does not matter, the Order By clause can be left blank. Otherwise, you must specify a sort order using the Field Display Format Editor. If that’s not available, use the ApplySortOrderAsOrderByClause() API in the scripting to generate the Order By clause for the multiplexed data source. The fetches for the underlying data sources are then issued using the appropriate syntax for each data source, and the results are multiplexed (interleaved) together correctly in light of the sort order.

Distinct

There is special processing that occurs within the multiplexed data source if the collection is distinct, meaning that duplicate records are being thrown out during the load processing. In order for duplicate records to be detected, all of the underlying data sources must perform an Order By that includes every property being loaded.

If you do not specify an Order By for any particular property, the multiplexed data source assumes the property is ordered in ascending order by the underlying data source. This is often the case. However, if you find that duplicates are showing up, make sure that all properties are explicitly ordered by the Order By clause.

Where clause

Similarly, when a Where clause is passed to the multiplexed data source that is in an unknown syntax, the multiplexed data source assumes it to be in the syntax of the underlying data sources and simply passes it though unchanged. However, if any of the data sources do not understand the syntax, they will fail. Therefore, it is safest to create a Where clause using the Field Display Format Editor. If that's not available, you must use the ApplyFilterAsWhereClause() API in the scripting to generate the Where clause. If you do this, then the Where clause is translated correctly for each underlying data source, even if they use different syntaxes from each other.

Understanding how the Where clause is generated (all data source types)

Normally, when a new filter arrives, the system follows a procedure to generate a new Where clause as follows:

• Create a mapped filter.
  Note: When using the AlertSelfFilterChanged API, this step is skipped and the incoming filter becomes the CurrentMappedFilter immediately.
  • Process each filter item in the incoming filter by matching it with the filtering specification in the FDF. For each match, a condition is added to the mapped filter against the appropriate property with the value from the filter item.
  • Add conditions to the mapped filter as specified by the FDF filtering section for filters against session variables, layout variables, parent record properties and literals.
• Combine any explicit Where clause specified in the FDF definition with the mapped filter from the previous step, and generate a Where clause in the query language appropriate to the data source.

At the time that the FilterChanged event handler is invoked in the scripting, the incoming filter (found in parms.Filter) has already been processed as specified above, and the result is in the WhereClause property on the collection.

The mapped filter mentioned the first step is available to the scripting at:

context.CurrentMappedFilter

The portion of the Where clause that was explicitly set in the FDF in the second step is available to the scripting at:

context.WhereClauseHeader

If you were to combine them manually in scripting, you could use:

context.Items.WhereClause = context.WhereClauseHeader

context.Items.AddFilterToWhereClause(context.CurrentMappedFilter)

Creating a custom Where clause in scripting

To alter the Where clause at this point, you have these options:

• As mentioned, if you must set an explicit Where clause in the scripting, the best way to do this is to create a filter and apply it to the collection using the ApplyFilterAsWhereClause() API.

  Dim f As IPFFilterI = context.CreateFilter()

  f.FilterItems.Add(... 'Add custom filter items here

  context.Items.ApplyFilterAsWhereClause(f)

• If you want to include the static portion of the Where clause from the FDF, so it is not lost, you can combine the WhereClauseHeader with your custom filter using the AddFilterToWhereClause() API:

  context.Items.WhereClause = context.WhereClauseHeader

  Dim f As IPFFilterI = context.CreateFilter()

  f.FilterItems.Add(... 'Add custom filter items here

  context.Items.AddFilterToWhereClause(f)

• If you do not want to use the filter functionality, you can set the Where clause directly. When doing this, please see the section below about SQL Injection Attacks.

  Dim whereClause As String

  whereClause = <your custom where clause here>

  context.Items.WhereClause = whereClause 'override the system

In the case of a multiplexed collection, any Where clause you generate that is not in the native internal language of the multiplexed data source is assumed to be in the language of the underlying data sources and is passed to them unchanged. Thus, creating custom Where clauses is generally valid only when all of the underlying data sources use the same query language.

Avoiding vulnerability to SQL injection attacks (SQL-related data source types)

When working with data entered by a user via an outward facing website, there is a danger of malicious intent on the part of the user. One way a user may try to harm a website is by using a SQL Injection Attack. This is where the user enters data in a special way in order to trick the system into running harmful code against the database.

This example code is vulnerable to this attack:

Dim whereClause As String
whereClause = String.Format("Item = '{0}'", 
parms.Filter.FilterItems.Item(IPFFilterItemType.Property, "Item").Value)
context.Items.WhereClause = whereClause 'override the system

The reason is that you do not know where the filter item came from. Perhaps the item number in the filter was entered by a malicious user and includes foreign SQL code as part of a SQL Injection attack.

To protect against this, the system always processes incoming data when creating the Where clause or any other interaction with the data sources. However, if you are creating a Where clause manually (option three above), you must be sure to do this correctly yourself. This is done by using the AsLiteral() API.

Dim ds As IPFDatasourceI = context.Datasources("PrimarySite")
Dim whereClause As String
whereClause = String.Format("Item = {0}", 
ds.AsLiteral(parms.Filter.FilterItems.Item(IPFFilterItemType.Property, 
"Item").Value))
context.Items.WhereClause = whereClause 'override the system

In the example, we first select a data source that uses the query language of the collection. Then, we wrap the dangerous data with a call to the AsLiteral() API. This processes the data, so a SQL Injection Attack does not work.

This procedure must always be done when working with data received from outward facing web pages.

Modifying data

Data may be modified and saved to a multiplexed collection the same as any other collection. The multiplexed data source tracks where each record came from and routes updates as appropriate to the underlying data sources.

Creating new records

When creating a new record, use the CreateItem() API. When adding a new record within a multiplexed collection, you must also indicate which underlying data source the record should be added to. This is done via the SetUnderlyingDatasource() API. For example:

IPFItemI newItem = context.Items.CreateItem(1);

newItem.SetUnderlyingDatasource("OH");

It is necessary to use this API with the multiplexed data source so it knows which underlying data source to save the new record to. Otherwise, the save operation will fail.

Invoking methods and custom load methods

When using the InvokeMethod() or LoadFromMethod() APIs with a multiplexed data source, the method is actually invoked concurrently on each underlying data source. Because of this, even though there is only one set of input parameters, there will be multiple sets of output parameters and return values. Therefore, the input parameters cannot be updated with any outputs as is usual. They will remain unaffected. Instead, they will be available in the IPFMethodReturnValueI via the Values property. This property contains the collection of return values. You can check the HasMultipleValues property to know whether or not to use the Values property. When HasMultipleValues is true, the Values property is not applicable.

The LoadFromMethod() API will automatically multiplex (interleave) the various result sets according to the sort order, if one was given. You can set the order by on the collection by using the ApplySortOrderAsOrderByClause() prior to calling LoadFromMethod. Each data set is assumed to have been sorted by the method using that sort order. If the sort orders are different, the order of the combined result set is undefined. Therefore, you must ensure that the order of the data as returned from the method is the same as the Order By clause that is set on the multiplexed collection.

Failures and partial failures within the multiplexed data source

When using InvokeMethod() or LoadFromMethod(), it is possible that some of the underlying data sources will execute their methods correctly and some will fail. The MethodCompleted property on the return value will be false if at least one of the underlying data sources failed. You can then iterate through the Values collection to see which ones failed and which ones succeeded.

In the case of a LoadFromMethod() call, even if some of the data sources failed, the result set will include the partial results from those that succeeded. If you want to inform the user that only some of the data was returned, you must check the results for errors. If you want to implement an all-or-nothing experience, you must clear the collection of any partial data when an error is detected.

Similarly, an IPFCollectionI.Load() call or a load performed by the architecture may return a failure and yet yield a partial result set. If you want to implement an all-or-nothing experience, you must clear the collection of any partial data when an error is detected.

In the case of a load performed by the architecture, after a collection is loaded, the CollectionLoaded event handler is run. You can get access to the IPFLoadResultI object from the parms of that event handler.