The SQL Execute Statement integration takes a returnType parameter (the business object name) and maps columns to fields by name automatically - by exact name, case-insensitive, without any transformation (CUSTOMER_NAME does not become customerName); so name the fields like the columns or alias the columns in the SELECT:
-- service flow: SQL Execute Statement with returnType = "Order" (BO fields: number, customer, total, created)
select ORDER_NO as "number", CUSTOMER_NAME as "customer", TOTAL_AMOUNT as "total", CREATED_TS as "created"
from ORDERS where STATUS = ?
-- parameters: tw.local.params (SQLParameter list: value = tw.local.status, type = VARCHAR)
-- output: tw.local.orders (list of Order) - every row becomes an Order with the aliased columns
Rules: alias every column to the field name (quoted aliases keep the case on Db2 / Oracle; SQL Server is case-insensitive); columns with no matching field are ignored, fields with no column stay null; types must be compatible (numeric to Decimal / Integer, TIMESTAMP to Date, VARCHAR to String); for SELECT * either name the table columns like the fields (a view is the elegant way: create a database view with the aliases once, then select * from V_ORDERS) or leave returnType empty - the service then returns generic result rows (one Record-like object per row with the column names as properties) that you convert in a script:
// generic rows -> typed BOs when aliasing is not possible
tw.local.orders = new tw.object.listOf.Order();
for (var i = 0; i < tw.local.rows.listLength; i++) {
var r = tw.local.rows[i], o = new tw.object.Order();
o.number = r.ORDER_NO; o.customer = r.CUSTOMER_NAME; o.total = r.TOTAL_AMOUNT; o.created = r.CREATED_TS;
tw.local.orders.insertIntoList(i, o);
}Nested objects (customer.name) cannot be auto-mapped from flat rows - map them in a script or use two queries; large result sets should be paged in SQL (FETCH FIRST n ROWS ONLY / OFFSET) because every row becomes a BAW object in memory.
References