How to use different variants of queryDef in Adobe Campaign Classic?

The queryDef method is one of the most commonly used methods in Adobe Campaign. It allows us to execute SQL queries against the database.

Lets look at some of its variants in Adobe Campaign

This process works in two steps:

1.Define the query

You go into the xtk namespace and then go into the queryDef which is one of the methods that are defined and that translates into a WSDL. Then we call the create method against the query definition.

xtk.queryDef.create(<xml>)

2.Execute the query

In this step, you just execute the query object that you got from step 1 above.

query.ExecuteQuery()

Let us look at some of the variations of queryDef now

Variation 1: Normal method

//Define the query
var q = xtk.queryDef.create(
  <queryDef schema="nms:delivery" operation="select" lineCount="3">
    <select>
      <node expr="@id"/>
      <node expr="@label"/>
    </select>
    <where>
      <condition expr={"@created <= '2018-12-08'"} bool-operator="AND"/>
    </where>
    <orderBy>
      <node expr="@lastModified" sortDesc="false"/> 
    </orderBy>    
  </queryDef>
);

//Execute the query
var deliveries = q.ExecuteQuery()

//Print the delivery label
for each(var delivery in deliveries.delivery){
  logInfo(delivery.@label)
}

Variation 2: Using appendChild()

// Create "Select" from loop
var select = <select/>;
for each( var targetKey in space.targetKey ){
  select.appendChild(<node expr={xpath} alias={"@tmp" + j}/>);
}

// Create "Where" from loop
var where = <where/>
for( var i=0; i < aPKXPaths.length; i++ ) {
  where.appendChild(<condition expr={aPKXPaths[i] + "=" + keyValue}/>)
}

// Create the final query
var xmlQryKeys = <queryDef operation="get" schema={strTargetSchema}/>;
xmlQryKeys.appendChild(select);
xmlQryKeys.appendChild(where);

Variation 3: Applying multiple where clause

var xmlQuery = <queryDef schema="nms:trackingLogRcp" operation="select" lineCount="1000000">
  <select>
    <node expr="DateOnly(@logDate)" groupBy="1"/>
    <node expr="@userAgent" groupBy="1"/>
    <node expr="count(@id)"/>
    <node expr="countDistinct([@broadLog-id])"/>
  </select>
  <where>
    <condition expr={"@logDate IS NOT NULL and [@url-id] <> 1"}/>
  </where>
</queryDef>

Variation 4: Using queryDef with Write Method:

The queryDef is a non-static method

We can combine static and non-static methods in our implementation.

“Write” is one such static method that can be used to insert, update or delete records from the database.This method can be used on any schema in campaign as it operated on the database.

As “write” is a static method, there is no need to create objects before using it.

It uses the “_operation” argument which may be “insert”, “insertOrUpdate” or “delete”

In “insertOrUpdate”, you need to provide “_key” value to provide reconcilliation.

Syntax:

xtk.session.Write(<xml>) ;

Let us look at an example where both are in action.

var query=xtk.queryDef.create(

  <queryDef schema="nms:recipient" operation="select">
    <select>
      <node expr = "@id" />
    </select>
    
    <where>
      <condition expr= "[@email]='prajwalshetty202@gmail.com'" />
    </where>
   </queryDef>
  );
  
  var res = query.ExecuteQuery()
  
  for each (var w in res.recipient) {
    xtk.session.Write(<recipient xtkschema="nms:recipient" _operation="delete" id={w.@id} /> ) ;
    }

Hope this helps.