How to Use SqlExec in Adobe Campaign Classic for Custom Queries
SqlExec is a JSSP function in Adobe Campaign Classic that allows you to run raw SQL queries directly on the database. It supports INSERT, UPDATE, DELETE, and even SELECT statements—though it only returns the number of records affected, not the actual data.
To use SqlExec in Adobe Campaign Classic, your operator account must have the SQL SCRIPT EXECUTION right enabled. This permission—labeled sql in the rights configuration—grants the ability to execute raw SQL scripts directly on the database.
Without this right, attempting to use SqlExec() will result in a permissions error.
But hey… 🤫 let’s keep this between us.
There’s been a longstanding, quietly ignored security loophole in some deployments that technically allows a savvy operator to escalate privileges to admin. Yes, it’s a security issue. Yes, it’s been known for years. No, it still hasn’t been patched in some on-prem or poorly configured instances.
Now that you’re admin let’s do some work..
Syntax
var count = SqlExec(query [, value1, value2, …]);
Parameters
- query (string): your SQL statement, with
$(…)placeholders for any parameters. - value1, …: JavaScript values that will be bound to each placeholder, in order.
Return value
Returns the number of records updated or inserted in case of an UPDATE or INSERT SQL command. In case of select it returns number of records.
Substitution Variable Types
When constructing parameterized SQL queries using SqlExec, Adobe Campaign provides a set of variable placeholders. These ensure that the correct SQL data type is used when binding JavaScript variables to the SQL statement.
| Placeholder | Description |
|---|---|
$(sz) | string |
$(m) | memo (long text) |
$(l) | long (64-bit integer) |
$(s) | short (16-bit integer) |
$(b) | byte (8-bit integer) |
$(d) | double |
$(f) | float |
$(ts) | timestamp |
$(dt) | date |
$(tm) | time |
$(dur) | timespan in milliseconds |
Auto-substitution placeholders
These placeholders do not require a value from JavaScript—they are automatically populated by the system. They can be used only with INSERT or UPDATE:
| Placeholder | Description | Operation |
|---|---|---|
$(id) | Auto-generates a unique ID from the default sequence | INSERT |
$(id:MySeqName) | Generates ID using a custom named sequence | INSERT |
$(curdate) | Inserts current timestamp (date and time) | INSERT, UPDATE |
Examples
var sql = "INSERT INTO events (eventName, startTime, duration) VALUES ($(sz), $(ts), $(dur))"; SqlExec(sql, "Webinar", new Date(), 5400000);
Using auto primary key and current date as placeholder variable
var sql = "INSERT INTO logs (logId, created, userId) VALUES ($(id), $(curdate), $(l))"; SqlExec(sql, 2023);
For more records to process you can use loops.
for each (var client in clientList) {
var sql = "INSERT INTO Reporting (sClientdesc, sClient) " +
"VALUES ('" + client.description + "', '" + client.clientCode + "')";
sqlExec(sql);
}Here are some examples of using SqlExec to update or remove data from lists in Adobe Campaign—a task that can be quite tedious when done through standard workflow activities.
In the example below, we update the list directly using SQL.
UPDATE
Unassign vouchers by voucher
đź”’ This content is for Premium Subsribers only.
Please log in to preview content. Log in or RegisterYou must log in and have a Premium Subscriber account to preview the content. When upgrading, please provide the same email address as for your WordPress account; otherwise, we will not be able to link your Premium membership. Please also provide your Discord username or contact me directly to get access to the Discord community once your subscription is purchased. You can subscribe even before your account is created; the subscription will be linked to your WordPress email address.
Premium Subscriber
9.99 € / Month
- Access to exclusive blog content
- In-depth articles not available online
- Insights from industry experts
- Free Discord community invite
- Unlimited questions in Solution Station
- Limited seats at this price
