Sqlselect

Parameters

`format`	string	Result shape: a root name then one typed column per SQL field, as `name:type` or `@name:type` (`@` = XML attribute, otherwise element). Types such as `long`, `string`, `datetime`, `timestamp`, `memo`.
`query`	string	SQL statement with placeholders.
`dataSource`	string	Datasource name. Empty string or omitted uses the default database.
`...values`	any	Values of the placeholders, in order.

Returns

Examples

Get SQL records (via SELECT) from the Adobe Campaign database. To write data, use 📕 sqlExec.

Prefer 📕 queryDef for simple needs: it works from the schema, enforces access rights, and is portable across database engines. sqlSelect bypasses all three (raw SQL, engine-specific, no rights check), so reach for it only when a query can’t be expressed with queryDef, such as: DISTINCT, aggregates, system tables.

Not sure what to choose between Workflow, queryDef API and sqlSelect? Read the Database article.

Format

The sqlSelect call is called with:

The XML shape of the result, with a root name followed by one typed columns, i.e. "delivery, @id:long, @contact:datetime"
- The first token (delivery) names the repeating row element.
- Each name:type maps one SQL column (a leading @ makes it an XML attribute)
- The type is a full word (long, string, datetime, memo), distinct from the $() token used to bind values.
The SQL query with field names & placeholders
- Fields like iOptionId and sName. The prefix encodes the type (i integer, s string). Find them in Configure list for a schema, or via Transition > right-click > Open > Schema for a workflow temporary table.
- Typed placeholders like $(l) and $(sz) (l integer, sz string)
The arguments list, in the same order as the placeholders

Get a recipient

var res = sqlSelect(
  "recipient, @id:long, @email:string, @firstName:string, @lastName:string",
  "SELECT iRecipientId, sEmail, sFirstName, sLastName FROM NmsRecipient WHERE iRecipientId = $(l)",
  "",
  recipientId,
);

if (res.recipient.length() === 0){
  logError("No recipient with id " + recipientId);
}

var rcp = res.recipient[0];
logInfo(rcp.@firstName + " " + rcp.@lastName + " ");

The result is XML and not an array: to get the only record, use res.recipient[0]

Get a list of deliveries

var res = sqlSelect(
  "delivery, @id:long, @contact:datetime",
  "SELECT iDeliveryId, tsContactDate FROM NmsDelivery WHERE iState = $(l)",
  "",
  12,
);

for each (var d in res.delivery) {
  logInfo(d.@id + " contacted on " + d.@contact);
}

The result is XML and not an array: to get the list size, use res.delivery.length()

Identifiers and engine-specific SQL

A placeholder binds a value, not a table or column name. Upgrade scripts build the table name into the string ("SELECT ... FROM " + vars.tableName) because an identifier can’t be bound. That is safe only because vars.tableName is a workflow temporary-table name produced by AC, not external input; apply the same trust test to your own code.

Raw SQL also ties you to one SQL engine. Stock code reading SQL Server system views:

var res = sqlSelect(
  "result, name:string",
  "SELECT name FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'NmsRtEventStat')"
);

will not run on PostgreSQL. That portability cost is the main reason to prefer queryDef.

Get insights from native source code

Install Adobe Campaign on Windows, then get all sqlSelect calls in Adobe Campaign source code:

PS C:\Program Files\Adobe\Adobe Campaign Classic v7\datakit>
Get-ChildItem -Path . -Recurse | select-string -pattern 'sqlSelect\(' -CaseSensitive > ~/sqlSelect.txt

sqlSelect - AC JS Reference