pfx-sql:select

Summary: Execute a SQL query against an external database and return all results at once.

URI Format

pfx-sql:select?sql=SELECT * FROM myTable&dataSource=myDb

Parameters

Parameter	Type	Default	Required	Description
`dataSource`	String	`dataSource`	No	Name of the Spring DataSource bean
`sql`	String		Yes	SQL query to execute
`dialect`	SQLDialect	`MYSQL`	No	SQL dialect
`outputTarget`	String	`body`	No	Where to store results: `body`, `header`, or `property`
`outputTargetName`	String		No	Name of the header/property when `outputTarget` is `header` or `property`

Examples

Basic Select

XML

<route id="selectFromDb">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:select?sql=SELECT * FROM products WHERE active = 1&amp;dataSource=myDb&amp;dialect=MYSQL"/>
    <to uri="pfx-api:loaddata?objectType=P&amp;mapper=productMapper"/>
</route>

Select to Header

XML

<route id="selectToHeader">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:select?sql=SELECT code, name FROM regions&amp;dataSource=myDb&amp;outputTarget=header&amp;outputTargetName=regionLookup"/>
    <!-- regionLookup is now available as a message header -->
    <to uri="pfx-api:fetch?objectType=P&amp;filter=allProducts"/>
    <to uri="pfx-api:loaddata?objectType=P&amp;mapper=productWithRegion"/>
</route>

Select with PostgreSQL

XML

<route id="selectPostgres">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:select?sql=SELECT * FROM inventory&amp;dataSource=pgDb&amp;dialect=POSTGRESQL"/>
    <to uri="pfx-api:loaddata?objectType=P&amp;mapper=inventoryMapper"/>
</route>

Common Pitfalls

Problem	Cause	Fix
Out of memory on large tables	`select` loads all rows into memory at once	Use `selectIterator` with `batchSize` for large datasets
Empty results	SQL syntax error or wrong dialect	Verify the SQL runs correctly in a database client; check `dialect`
Results overwrite body	Default `outputTarget` is `body`	Use `outputTarget=header` to preserve the body for downstream steps
Special characters in SQL	XML escaping needed	Use `&` for `&`, `<` for `<`, `>` for `>` in XML route definitions

See Also

pfx-sql Component — Parent reference
pfx-sql:selectIterator — Batched select for large datasets