Expand my Community achievements bar.

SOLVED

Transaction JSSP

Avatar

Level 2

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,

1 Accepted Solution

Avatar

Correct answer by
Level 6

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

View solution in original post

4 Replies

Avatar

Level 6

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

Avatar

Level 2

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.

Avatar

Correct answer by
Level 6

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

Avatar

Level 2

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