Expand my Community achievements bar.

SOLVED

XTK session write

Avatar

Level 2

Hi Team ,

could you please advise ,if there is something wrong with the below script ..

I have tried to use it and its not working ...

please suggest ..

xtk.session.Write(<enrich4
xtkschema="temp:enrich4" _key="@id" id={responder.@id} > <Product2
description={secondArray[1]}/> <Product3
description={secondArray[2]}/></enrich4>);

regards

shiva

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hi ShivaDT,

First, don't use xtk.session.Write for temporary schemas. Even if you manage it to work, it will be super slow.

If you need to modify flow data, export the file via data export comonent to the server HDD. By default the file will be stored in var/<Instance Name>/export folder. Use Javascript code to open the file, modify its lines and save it into another file. Use data load component to load the file back into the flow. Use another Javascript component to delete the file after it no longer required.

If you use CSV format, you will be able read the original file line by line, modify each line and store it back, so this solution will not consume large amount of memory.

My tests shows that this approach is about 100 times faster comparing with Write or WriteCollection functions. Adobe lacking library to parse or crease csv files, so you will have to build your own or adopt some existing one (Node.js module?)

Next, in your case, you don't have to do it at all. You can do your logic inside delivery template.

Lets asume, you targetData has element [Product/@description]

You can use following JSSP code to build up the lists

<%

     var allProducts =["FOOD","BINS & LINERS","BEVERAGES AND SWEETNERS","CATERING SUPPLIES","BATHROOM & KITCHEN PAPER","CLEANING CHEMICALS & EQUIPMENT","APPLIANCES"];

     var existingProducts = []

     for (var i in targetData.Product) {

          existingProducts.push(targetData.Product[i].description)

     }

     var newProducts = allProducts.filter(function(product) {

          for each (var existing in existingProducts) {

               if (existing == product) {

                    return false

               }

          }

          return true

     })

%>

Then just enumerate through the lists to produce required content

HI ShivaDT,

View solution in original post

13 Replies

Avatar

Employee

Shiva, can you share the error that you are getting? If you are running this in a workflow (good idea for testing) you can activate the workflow property to log SQL queries in the journal.

Linda

Avatar

Employee

Also, it appears you are attempting to query the temporary worktable in a workflow. Is that the case, and do you mind sharing the reason for doing so?

Avatar

Level 2

Hi Linda,

Thanks for your response ..

Below is the full context behind this XML script ..

I have a workflow which identifies the products purchased by each customer   and ranks the spend on each product.

Then there is a delivery ,which presents the products as per their spend (Rank) and the products they haven’t purchased from a list (below are the products).

The products I am looking for are : ("FOOD","BINS & LINERS","BEVERAGES AND SWEETNERS","CATERING SUPPLIES","BATHROOM & KITCHEN PAPER","CLEANING CHEMICALS & EQUIPMENT","APPLIANCES")

Example: a customer might only purchase three products FOOD, CATERING SUPPLIES & CLEANING CHEMICALS & EQUIPMENT ..  the delivery needs to have these three products as well as the remaining products he has not purchased from the list .

This customer has purchased three products and I have the order , what I trying to do with the JS is : fill in the blanks with the remaining products ..

So customers remaining products should be BINS & LINERS, CATERING SUPPLIES, CLEANING CHEMICALS & EQUIPMENT, APPLIANCES 

I am using an array in the JS to do this , the array works as per the requirements .. however I am unable to write the values which are stored in the array back in to the variables..

Avatar

Level 2

Below is the JS i have written ..

var query = xtk.queryDef.create( 

    <queryDef schema="temp:enrich4" operation="select">   

    <select>     

   <node alias="@d1"  expr="[Product1/@Description]"/>

   <node alias="@d2" expr="[Product2/@Description]"/>

   <node alias="@d3" expr="[Product3/@Description]"/>

   <node expr="@id"/>

    </select>    

  </queryDef>)

var responders  = query.ExecuteQuery();

for each (var responder in responders.enrich4){ 

    //logInfo(responder);

  var firstArray=["FOOD","BINS & LINERS","BEVERAGES AND SWEETNERS","CATERING SUPPLIES","BATHROOM & KITCHEN PAPER","CLEANING CHEMICALS & EQUIPMENT","APPLIANCES"];

    var secondArray = [];

    var numOne = responder.@d1;   

    var numTwo = responder.@d2;

    var numThree = responder.@d3;

    secondArray.push(numOne);  

    if (numTwo!=""){

    secondArray.push(numTwo);

  }

    if (numThree!=""){

    secondArray.push(numThree);

  }

  for (var i = 0; i < firstArray.length; i++) {

    var isPresent=false;

    for(var j=0;j< secondArray.length;j++)

    {

      if(secondArray[j] == firstArray[i] )

        isPresent= true;

    }

  

    if(isPresent==false)

    secondArray.push(firstArray[i]);

  }

  for(var j=0;j< secondArray.length;j++)

  { 

     document.writeln(secondArray[j]);

  }

   

   var des1 = secondArray[1];

   var des2 = secondArray[2];

  

   xtk.session.Write(<enrich4 xtkschema="temp:enrich4" _key="@id" id={responder.@id} > <Product2 description={secondArray[1]}/> <Product3 description={secondArray[2]}/></enrich4>);

   

    logInfo("id:"+ responder.@id);

  logInfo("secondArray[1]:"+ secondArray[1]);

  logInfo("responder.Product2.Description:" +des1);

  logInfo("secondArray[2]:" +secondArray[2]);

    logInfo("responder.Product3.Description:" +des2);

    

}

Avatar

Level 2

Is it resolved? I am looking for a similar thing

var schemaName = vars.targetSchema.substring(vars.targetSchema.indexOf(":") + 1);

logInfo("Schem name = " +schemaName);

var formatString = function(str) {
   
       blah blah blh......................

    return str;
}

var query = xtk.queryDef.create(
         <queryDef schema={vars.targetSchema} operation="select">
          <select>
            <node expr="@Label"/>
            <node expr="@FullName"/>
            <node expr="@lineNum"/>
          </select>
         </queryDef>);
  
var resultSet = query.ExecuteQuery();
for each (var row in resultSet.enrich5)
{
instance.vars.Label=formatString(row.@Label);
//logInfo(formatString(resultSet.enrich5.@Label));
xtk.session.Write(<query _operation="update" _key="@lineNum" Label= {formatString(row.@Label)}  xtkschema={vars.targetSchema}/>);

}

Avatar

Level 2

@vipul

Can you please help with this

Avatar

Community Advisor

Hello all,

you might try sqlExec() function to do the job. I think write and write collecion are not to be used with temp tables. (At least i tried to do the same as below)

----

var query = xtk.queryDef.create(

         <queryDef schema={vars.targetSchema} operation="select">

          <select>

            <node expr="@id"/>

            <node expr="@email"/>

          </select>

         </queryDef>);

var res = query.ExecuteQuery();

function doStuff(item){

  return 'stuff_done';

}

for each (var item in res)

  sqlExec("UPDATE " + vars.tableName + " SET sEmail='"+ doStuff(item) +"' where iId=" + item.@id);

---

You just need to find your column names in SQL by exploring targeting dimension.

1474686_pastedImage_21.png

Result --

1474687_pastedImage_22.png

Marcel

Avatar

Level 1

Hi,

I can confirm that I have never been able to get xtk.session.Write to change the temporary workflow data, we have always had to use sqlExec as marcel.gent.86 describes.

The following two queries should do the same but only the second actually updates the temporary workflow data:

Doesn't work

1509345_pastedImage_4.png

Does work

1509344_pastedImage_3.png

It would be great if Adobe could explain why xtk.session.Write doesn't update the data. Is there a step we are missing?

Avatar

Level 3

Hi everyone, can I use

sqlExec("UPDATE " + vars.tableName + " SET sEmail='"+ doStuff(item) +"' where iId=" + item.@id);

to update groups and lists? Or to insert data?

Avatar

Community Advisor

Hello shivaDT, this is only to work with temp table within the workflow. you can load the group then do the change to its data in temp table and then load temp data back to the group. you can use save list activity. Or you can change data of group by the sqlExec but you need to find the group's internal name e.g grp13737291

queryDef on nms:group

Marcel

Avatar

Community Advisor

HI ShivaDT,

You can use below sql to update your temp data:

xtk.session.Write(<query xtkschema={vars.targetSchema} id={iId} _operation="update" />)

Thanks,

Jyoti

Avatar

Correct answer by
Level 4

Hi ShivaDT,

First, don't use xtk.session.Write for temporary schemas. Even if you manage it to work, it will be super slow.

If you need to modify flow data, export the file via data export comonent to the server HDD. By default the file will be stored in var/<Instance Name>/export folder. Use Javascript code to open the file, modify its lines and save it into another file. Use data load component to load the file back into the flow. Use another Javascript component to delete the file after it no longer required.

If you use CSV format, you will be able read the original file line by line, modify each line and store it back, so this solution will not consume large amount of memory.

My tests shows that this approach is about 100 times faster comparing with Write or WriteCollection functions. Adobe lacking library to parse or crease csv files, so you will have to build your own or adopt some existing one (Node.js module?)

Next, in your case, you don't have to do it at all. You can do your logic inside delivery template.

Lets asume, you targetData has element [Product/@description]

You can use following JSSP code to build up the lists

<%

     var allProducts =["FOOD","BINS & LINERS","BEVERAGES AND SWEETNERS","CATERING SUPPLIES","BATHROOM & KITCHEN PAPER","CLEANING CHEMICALS & EQUIPMENT","APPLIANCES"];

     var existingProducts = []

     for (var i in targetData.Product) {

          existingProducts.push(targetData.Product[i].description)

     }

     var newProducts = allProducts.filter(function(product) {

          for each (var existing in existingProducts) {

               if (existing == product) {

                    return false

               }

          }

          return true

     })

%>

Then just enumerate through the lists to produce required content

HI ShivaDT,