Transaction JSSP

[#1]

Hi everyone,

I am trying to perform a transaction in JSSP Code (ac 6.1) but my rollback isn't working so as my commit.

What am i doing wrong ?

Does someone already used  https://docs.campaign.adobe.com/doc/AC/en/jsapi/m-DBEngine-rollback.html ?

var vCnx = application.getConnection();

try{

vCnx.begin();

callMyFunction1();// xtk.Write inside

callMyFunction2();// xtk.Write inside but throw exception

vCnx.commit();

}catch(e){

vCnx.rollback();

}

vCnx.dispose();

Thanks you,

Quentin,

[#2 · Accepted solution]

Hi Quentin,

This is because xtk.session.write doesn't use the connection vCnx. When using the DBEngine class (returned by application.getConnection()), you have to stick with the functions it offers (i.e. execute()). See all functions here: DBEngine (Class)

I tested the below code in a sandbox. If the line 16 is commented, it works well and create a recipient named "hello". If the line 16 is uncommented, then the exception is raised and the recipient is not created.

<%
logonEscalation('webapp');
var vCnx = application.getConnection();
function createRecipients(vCnx){
  vCnx.execute("INSERT INTO nmsRecipient (iRecipientId, sFirstName) VALUES (123456, 'hello');");  
}
function createFilter(vCnx){
  throw new Error('exception here');
}
try{
  vCnx.begin();
  createRecipients(vCnx);
  createFilter(vCnx); // if commented, the recipient is created, otherwise the SQL transaction is rolled back
  vCnx.commit();
} catch(e) {
  document.write('fco:test-sql-transaction.jssp ERROR: '+JSON.stringify(e));
  vCnx.rollback();
} finally {
   vCnx.dispose();
}
%>

Is it what you're looking for?

With this method, you would have to use plain SQL, beware of SQL injections. You can escape your data with Data protection: escaping functions

[#3]

Thanks you so much, you really helped me here ! And Thanks for you blog too , really good ressources

[#4]

Hi,

What is your full JSSP code? What's the exact error in the logs?

Are you using logonEscalation('webapp')?

What's the output of

<% for(var i in application.operator){
  document.write(i+': '+application.operator[i]+'\n');
}
logonEscalation('webapp');
for(var i in application.operator){
  document.write(i+': '+application.operator[i]+'\n');
} %>

Thank you

[#5]

Hi and  thanks you for your help.

My code is the following.

<%@ page import="xtk:server/jsspcontext.js,xtk:dashboard.js"%>
<%@ page import="xtk:common.js,nms:operation.js,xtk:server/jssphelper.js,xtk:shared/nl.js"%>
<% 
// Headers to disable cache 
  response.setContentType("text/html;charset=utf-8");
  logon("TOTO", "TOTO");
  var newContext =  logonEscalation('webapp');  // i had add this since your answer
  var contacts =  JSON.parse(request.getBodyAsString());
var vCnx = application.getConnection();
  try{
  vCnx.begin();// Début Transaction
// Insertion des recipients   
 createRecipients(contacts.List,dateInsertion);
 createFilter(contacts.projet,dateInsertion); // here i have created an error to test my rollback 
 ...
// End
  vCnx.commit();
}catch(e){
  vCnx.rollback();
}finally {
  vCnx.dispose(); 
}
function createRecipients(recipientArray,dateInsertion){
var doc  = new DOMDocument("recipient-collection");
var root = doc.root;
root.setAttribute("_operation","insertOrUpdate");
root.setAttribute("xtkschema","nms:recipient");
for(var i = 0 ; i < recipientArray.length;i++){
var insert = doc.createElement("recipient");
insert.setAttribute("lastName",recipientArray[i].lastName);
insert.setAttribute("firstName",recipientArray[i].firstName);
insert.setAttribute("salutation",recipientArray[i].salutation);
insert.setAttribute("email", unescape(recipientArray[i].email));
insert.setAttribute("address_1",recipientArray[i].address1);
...
root.appendChild(insert);
}
xtk.session.WriteCollection(doc.root);
}
function CreateAssocProfileFilterGroup(profileId,filterId,groupId,nbClients){
try{
var doc  = new DOMDocument("profile_filter_association");
var root = doc.root;
root.setAttribute("_operation","insert");
root.setAttribute("xtkschema","wal:XOXO");
root.setAttribute("queryFilter-id",98085980); // FORCED ERROR HERE
root.setAttribute("profile-id",97290529);
root.setAttribute("idGroup",groupId);
root.setAttribute("nbrClients",nbClients);
xtk.session.Write(doc.root);
}catch(e){
    throw e;
}
}

So i don't have log for now, but if the transaction were working  no insert will occur, but it's not the case, even with the forced error my recipients are inserted..

So neither my rollback or my commit are working.

Your snippet return the following result :

login:XXX

id: 103XXXXX

computeString: XXX XXX

groups: 82XXX,86XXXX,188XXXXX

rights: admin

timezone: Europe/Paris

locale: fr-FR

home:

login: webapp

id: 829124

computeString: Agent des applications web (webapp)

groups: 1282146

rights: admin,deliveryPrepare,deliveryValidate,folderInsert,import,recipientImport,workflow

timezone: Europe/Paris

locale: fr-FR

home:

Thanks again for your help.

Bests regards,

Quentin.