Sqlexec

Parameters

`query`	string	SQL statement with placeholders.
`...values`	any	Values of the placeholders, in order

Returns

number

Number of rows affected (INSERT/UPDATE/DELETE). 0 for DDL.

Examples

Executes a raw SQL statement (INSERT, UPDATE, DELETE, DDL operations) in the Adobe Campaign database. To read records (SELECT), use 📕 sqlSelect.

As a general rule, prefer workflow activities (Update Data, List Update). Use sqlExec for advanced data manipulation.

As a security rule, bind every value with a typed placeholder ($(l), $(sz)): never truste user input.

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

Format

The sqlExec call is called with:

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

Insert an option

To insert data in a table:

sqlExec(
  "INSERT INTO XtkOption (iOptionId, iDataType, sName, sStringValue) VALUES ($(l), 6, $(sz), $(sz))",
  123,
  "cusOptionViaSqlExec",
  "string value here"
);

Shown for illustration. In practice, use setOption, which handles data types.

Update a delivery

Update one delivery with its id, using an XTK function.

sqlExec("UPDATE NmsDelivery SET tsLastModified=$(curdate) WHERE iId=$(l)", 1234);

Updating tsLastModified forces Adobe Campaign to recompute the delivery’s statistics on its next run

Update many deliveries

The usual IN list can’t be a placeholder, so a string must be used. Sanitize strIds by casting each id to an integer:

var ids = [123, 456, 789];
var strIds = ids
  .map(function (id) {
    return parseInt(id); // cast to int to avoid SQL injection
  })
  .join(", ");
sqlExec("UPDATE NmsDelivery SET iExtractionStatus = 1 WHERE iDeliveryId IN (" + strIds + ")");

Advanced database operations

DDL statements (Data Definition Language: CREATE, DROP, ALTER, TRUNCATE) can’t be parameterized and are not reversible. They also bypass Campaign’s logical layer, so the schema definition and the physical database can drift apart.

Use carefully.

sqlExec("DROP INDEX xtkRights_xxx ON xtkRights");
sqlExec("ALTER SEQUENCE NmsTrackingUrl_xxx RESTART START WITH 123");
sqlExec("DROP TABLE table_xxx");

Raw DDL is also engine-specific: Campaign’s own upgrade scripts branch between:

SQL Server (EXEC DropColumn, sp_rename)
PostgreSQL/Oracle (ALTER TABLE, ||)
DB2 (CALL SYSPROC.ADMIN_CMD)

Anything you write this way is tied to one engine: the main reason to prefer activities or queryDef.

Get insights from native source code

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

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

sqlExec - AC JS Reference