Executing SQL Inside Script Hooks

Sometimes, you need to break out a do a little SQL, say to create a Person Key, before you add someone to a database (and you don't have auto keys turned on in the DB)

// Create new Person key and set it on the work and conn entries
var SQL_STRING = "select IDM.PERSON_SEQ.nextval from dual";
var con = thisConnector.getConnector().getConnection();
var pstmt = con.prepareStatement(SQL_STRING);
var rs = pstmt.executeQuery();

if ( rs.next() ) {

var number = rs.getString(1);
task.logmsg("New person, created md person key of " + number);
work.MD_PERSON_KEY =  new com.ibm.di.entry.Attribute();
work.MD_PERSON_KEY.setValue(number);
conn.MD_PERSON_KEY =  new com.ibm.di.entry.Attribute();
conn.MD_PERSON_KEY.setValue(number);
} else { 
task.logmsg("Error getting sequence number");
}

rs.close();
pstmt.close();


Comments