pfx-api:fetch

pfx-api:fetch

The primary export method. Fetch (query) data from any supported Pricefx object type with support for filters, SQL syntax, pagination, batched mode, field projection, sorting, and multiple output formats.

Overview

The fetch method retrieves data from Pricefx and places it into the exchange body. It supports:

• Filtering via named filter beans or inline SQL syntax

• Batched mode for large data sets (millions of rows)

• Pagination with startRow/endRow

• Field projection (resultFields for client-side, valueFields for server-side)

• Sorting and distinct results

• Multiple output formats: JSON, CSV, EXCEL, PDF, and detail variants

URI Format

pfx-api:fetch?objectType=P&filter=myFilter

Parameters

Filter vs SQL

You can specify the query in two ways:

Named Filter Bean

Define a filter in your Spring XML context:

<pfx:filter id="activeProductsFilter" resultFields="sku,attribute1,attribute2" sortBy="sku">
    <pfx:and>
        <pfx:criterion fieldName="attribute1" operator="notNull"/>
        <pfx:criterion fieldName="attribute2" operator="greaterThan" value="100"/>
    </pfx:and>
</pfx:filter>

Reference it in the URI:

<to uri="pfx-api:fetch?objectType=P&amp;filter=activeProductsFilter"/>

Inline SQL Syntax

Use the sql parameter with a SQL-like query (the FROM clause is omitted):

<to uri="pfx-api:fetch?objectType=P&amp;sql=SELECT sku, attribute1 WHERE attribute2 > 100 ORDER BY sku ASC"/>

Batched Mode

For large data sets, enable batched mode to fetch data in chunks:

<to uri="pfx-api:fetch?objectType=DMDS&amp;dsUniqueName=MyDS&amp;filter=myFilter&amp;batchedMode=true&amp;batchSize=10000"/>

When batchedMode=true:

1. The first call to fetch retrieves the first batch and stores iteration state in the exchange

2. Use pfx-api:fetchIterator in a loop to retrieve subsequent batches

3. The iterator returns null (or empty) when all data has been fetched

Batched Mode with Split

<route>
    <from uri="direct:fetchLargeDataset"/>
    <to uri="pfx-api:fetch?objectType=DMDS&amp;dsUniqueName=MyDS&amp;filter=myFilter&amp;batchedMode=true&amp;batchSize=10000"/>
    <split>
        <simple>${body}</simple>
        <to uri="direct:processRecord"/>
    </split>
    <loop doWhile="true">
        <simple>${body} != null &amp;&amp; ${body.size()} > 0</simple>
        <to uri="pfx-api:fetchIterator"/>
        <split>
            <simple>${body}</simple>
            <to uri="direct:processRecord"/>
        </split>
    </loop>
</route>

resultFields vs valueFields

• resultFields: Client-side projection. All fields are fetched from the database, but only the specified fields are included in the result Map. Use when you need named fields.

• valueFields: Server-side projection. Only the specified fields are fetched from the database. More efficient for large tables. The result is a list of values (not a Map), in the same order as the specified fields.

resultFields Example

<to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;resultFields=sku,attribute1,attribute2"/>

Result: [{"sku": "ABC", "attribute1": "val1", "attribute2": "val2"}, ...]

valueFields Example

<to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;valueFields=sku,attribute1,attribute2"/>

Result: [["ABC", "val1", "val2"], ...]

Sorting

Use sortBy to control the order of results:

<to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;sortBy=sku ASC"/>

The sortBy parameter overrides any sortBy defined in the filter bean.

Examples

Fetch All Products

<route>
    <from uri="direct:fetchProducts"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=allProductsFilter"/>
</route>

Fetch with SQL and Pagination

<route>
    <from uri="direct:fetchFiltered"/>
    <to uri="pfx-api:fetch?objectType=P&amp;sql=SELECT sku, attribute1 WHERE attribute2 > 100 ORDER BY sku ASC&amp;startRow=0&amp;endRow=1000"/>
</route>

Fetch with CSV Output

<route>
    <from uri="direct:fetchCsv"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;output=CSV&amp;useColumnNames=true"/>
</route>

Fetch Data Source with Batched Mode

<route>
    <from uri="direct:fetchDS"/>
    <to uri="pfx-api:fetch?objectType=DMDS&amp;dsUniqueName=MyDS&amp;filter=myFilter&amp;batchedMode=true&amp;batchSize=10000"/>
</route>

Fetch Count Only

<route>
    <from uri="direct:countProducts"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;countOnly=true"/>
    <log message="Total products: ${body}"/>
</route>

Fetch with Distinct Results

<route>
    <from uri="direct:fetchDistinct"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=myFilter&amp;valueFields=attribute1&amp;distinctResults=true"/>
</route>

Headers Produced

Common Pitfalls

1. Not using batchedMode for large data -- Fetching millions of rows without batchedMode will load everything into memory at once, causing OutOfMemoryError. Always use batchedMode=true for large datasets.

2. Confusing resultFields and valueFields -- resultFields returns Maps with named keys; valueFields returns arrays of values. If your downstream processing expects Maps, do not use valueFields.

3. Missing dsUniqueName for DMDS/DM -- When fetching from a Data Source or Datamart, dsUniqueName is required.

4. SQL syntax pitfalls -- The sql parameter does not support a FROM clause. Only SELECT, WHERE, and ORDER BY are supported. Complex joins are not available.

5. Filter bean not found -- If the named filter bean does not exist in the Spring context, the fetch will fail with a bean resolution error. Verify the filter ID matches exactly.

6. sortBy override -- When both the filter bean and the URI parameter define sortBy, the URI parameter wins. This can be surprising if you expect the filter's sort order.