Avatar

Correct answer by
Community Advisor

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