sqlExec issue

viktorstarn

04-06-2018

Hello,

My goal is to extract the entire xml-code of a workflow (in a javascript-action), make changes to it (in this case I want to remove any example data in file import-actions that might violate GDPR) and store the new xml in the database.

I extract the xml using

     var wf = NLWS.xtkWorkflow.get(id);

     var str = wf.toDocument().toXMLString()

and try to write it back again using

     result = sqlExec("update xtkWorkflow set mData = '" + tempChunk + "' || mData where sInternalName = '" + internalName + "'");

Problem is, I guess, if the string "tempChunk" contains the combination "$(" as in "$(vars/@tableName)" the sqlExec-function believes I'm trying to use one of these function specific substition parameters:

  • $(sz) string
  • $(m) memo
  • $(l) long
  • $(s) short
  • $(b) byte
  • $(d) double
  • $(f) float
  • $(ts) timestamp
  • $(dt) date
  • $(tm) time
  • $(dur) timespan en millisecondes (ms)

and fails with an error message like this one:

2018-05-30 14:14:54 js2 Type 'vars/@tableName' is not a valid type of parameter.

So, my question is, is there any way around this?

Accepted Solutions (1)

Accepted Solutions (1)

MarcelSzimonisz

MVP

05-06-2018

Hello,

correct me if wrong but the memo is just xml representation of that object so when you change the object itself with methods mentioned above the memo is changed automatically.

So basically in your case you can do:

wkf.activities.fileImport[0].source.dataSourceConfig.sampleLines = "";//if you know you have one fileImport

//if you have more

for (var i = 0;i<Object.keys(wkf.activities.fileImport).length;i++)

     wkf.activities.fileImport[i].source.dataSourceConfig.sampleLines = "";

Or its more of generic that you need to find something withing any place of wkf and remove it?

PS: I think what you did will work but instead of sqlExec do xtk.session.write(your changed workflow xml  where you have added _key = wkf id and _operation = update) See the documentation how shold the xml look [Data oriented APIs]

Marcel

Answers (3)

Answers (3)

MarcelSzimonisz

MVP

05-06-2018

Hello viktorstarn​,

This can be done by more simple way or two:

1) With xtk.workflow.get() or xtk.workflow.load (difference is that in load has to be called save() method when finished and in get the changes are saved automatically)

You can directly change anything in the workflow by simple accessing the object:

//get

var wkf = xtk.workflow.get(id);

wkf.label = "new label";

..

..

//load

var wkf = xtk.workflow.load(id);

wkf.label = "new label";

..

..

..

wkf.save();

2) By using xtk.session.write where your workflow id will be _key and _operation set as update

See the documentation on this topic:

Data oriented APIs

SOAP methods in JavaScript

3) Also you can combine xtk.queryDef()  with xtk.workflow.get() or load()

var query = xtk.queryDef.create(

  <queryDef schema="xtk:workflow" operation="select">

  <select>

  <node expr="@id"/>

  </select>

  </queryDef>

)

var res = query.ExecuteQuery()

for each (var w in res.workflow){

     var wkf = xtk.workflow.get(w.@id);

     //var = xtk.workflow.load(id);

    

     wkf.label = "new label";

     ...

     ...

    //wkf.save()

}

Hope this helps,

Marcel

viktorstarn

07-06-2018

Great answer! Exactly what I needed! Thanks

Slimmed my script down from 240 lines to 40 ^^

Couldn't get it to work initially, "Object.keys(wkf.activities.fileImport).length" kept returning 0. But it started working when I added...

for (var key in wkf.activities.fileImport) {

    if (wkf.activities.fileImport.hasOwnProperty(key)) {

        logInfo("Key: " + key);

        logInfo("True");

    }

}

...before your for-loop while troubleshooting. Not sure why...

Also had to add the actual start- and end-tags and save function:

  for (var i = 0;i<Object.keys(wkf.activities.fileImport).length;i++) {

    wkf.activities.fileImport[i].source.dataSourceConfig.sampleLines = "<sampleLines></sampleLines>"; 

  }

  wkf.save();

Thanks again! Very helpful!!

viktorstarn

05-06-2018

Hello Marcel,

Thank you for your reply!

This way of changing properties of an entity (recipient, workflow etc.) seems straight forward and easy to use, but does it apply even if I'm aiming to change a certain detail in the xml-code of the wf (in the XML Memo) and then want to save it? What I want to do specifically is remove the <line>-tags in all of the <samleLines>-tags (if the wf contains any)...

1502182_pastedImage_0.png

...and then save the XML-code of the workflow (to get rid of example data). I've already encounter a few difficulties doing this, having had to split the XML-code-string into smaller substrings and append them to the database field in a loop to be able to save them using sqlExec() for example. But I think I've got it working now except for this last "$("-obstacle...

Can I save the XML Memo (even a very large one) using the methods you suggested? E.g.

  1. //get 
  2. var wkf = xtk.workflow.get(id); 
  3. wkf.label = "new label"
  4. wkf.data = variableContainingXMLmemo;

or

               By using xtk.session.write where your workflow id will be _key and _operation set as update (and adding something like "data = variableContainingXMLmemo")?

Thanks again,

Viktor