Skip to Content

Creating an OData Service with Create Operation and XSJS Exit

Previous

Creating an OData Service with Create Operation and XSJS Exit

By Craig Cmehil

Creating an OData Service with Create Operation and XSJS Exit

You will learn

Now to expand your code to include an XSJS exit.

Details

Please note - This tutorial is based on SPS11


Step 1: Create new OData service

Create another OData service named user2.xsodata for dev602.data::User.Details. This time, also link the create operation to the Server Side JavaScript Library (XSJSLIB) xsjs::usersCreateMethod.xsjslib and the function usersCreate. This will be the exit code that performs validation before the insert of the new record. Note: if you don’t want to type this code, we recommend that you cut and paste it from this web address http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex3_12

service namespace "dev602.services"{
	"dev602.data::User.Details" as "Users"
	  create using "xsjs:usersCreateMethod.xsjslib::usersCreate";
}
Please log in to access this content.
Step 2: Create first XSJS library

In the xsjs folder create the file usersCreateMethod.xsjslib. Here is the code for this file. Note: if you don’t want to type this code, we recommend that you cut and paste it from this web address http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex3_13

$.import("xsjs", "session");
var SESSIONINFO = $.xsjs.session;

/**
@param {connection} Connection - The SQL connection used in the OData request
@param {beforeTableName} String - The name of a temporary table with the single entry before the operation (UPDATE and DELETE events only)
@param {afterTableName} String -The name of a temporary table with the single entry after the operation (CREATE and UPDATE events only)
*/
function usersCreate(param){
	var after = param.afterTableName;    

	//Get Input New Record Values
	var	pStmt = param.connection.prepareStatement('select * from "' + after + '"');	 
	var User = SESSIONINFO.recordSetToJSON(pStmt.executeQuery(), 'Details');
	pStmt.close();


	//Validate Email
	if(!validateEmail(User.Details[0].E_MAIL)){
		throw 'Invalid email for '  + User.Details[0].FIRSTNAME +  
        ' No Way! E-Mail must be valid and ' + User.Details[0].E_MAIL + ' has problems';
	}

	//Get Next Personnel Number
	pStmt = param.connection.prepareStatement('select "dev602.data::purchaseOrderSeqId".NEXTVAL from dummy');
	var rs = pStmt.executeQuery();
	var PersNo = '';
	while (rs.next()) {
		PersNo = rs.getString(1);
	}
	pStmt.close();
	//Insert Record into DB Table and Temp Output Table
	for( var i = 0; i<2; i++){
		var pStmt;
		if(i<1){
			pStmt = param.connection.prepareStatement('insert into "dev602.data::User.Details" values(?,?,?,?)' );			
		}else{
			pStmt = param.connection.prepareStatement('TRUNCATE TABLE "' + after + '" ' );
			pStmt.executeUpdate();
			pStmt.close();
			pStmt = param.connection.prepareStatement('insert into "' + after + '" values(?,?,?,?)' );		
		}
		pStmt.setString(1, PersNo);
		pStmt.setString(2, User.Details[0].FIRSTNAME);		pStmt.setString(3, User.Details[0].LASTNAME);
		pStmt.setString(4, User.Details[0].E_MAIL);
		pStmt.executeUpdate();
		pStmt.close();
	}
}

function validateEmail(email) {
    var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
    return re.test(email);
}
Please log in to access this content.
Step 3: Create second XSJS library

Create another file in the xsjs folder named session.xsjslib. Here is the code for this file. Note: if you don’t want to type this code, we recommend that you cut and paste it from this web address http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex3_14

/**  
@function Outputs the Session user and Language as JSON in the Response body
*/
function fillSessionInfo(){
	var body = '';
	body = JSON.stringify({
		"session" : [{"UserName": $.session.getUsername(), "Language": $.session.language}]
	});
	$.response.contentType = 'application/json';
	$.response.setBody(body);
	$.response.status = $.net.http.OK;
}

/**
@function Escape Special Characters in JSON strings
@param {string} input - Input String
@returns {string} the same string as the input but now escaped
*/
function escapeSpecialChars(input) {
	if(typeof(input) != 'undefined' && input != null)
	{
	return input
    .replace(/[\\]/g, '\\\\')
    .replace(/[\"]/g, '\\\"')
    .replace(/[\/]/g, '\\/')
    .replace(/[\b]/g, '\\b')
    .replace(/[\f]/g, '\\f')
    .replace(/[\n]/g, '\\n')
    .replace(/[\r]/g, '\\r')
    .replace(/[\t]/g, '\\t'); }
	else{

		return "";
	}
}

/**
@function Escape Special Characters in Text strings (CSV and Tab Delimited)
@param {string} input - Input String
@returns {string} the same string as the input but now escaped
*/
function escapeSpecialCharsText(input) {
	if(typeof(input) != 'undefined' && input != null)
	{
	input.replace(/[\"]/g, '\"\"');
	if(input.indexOf(",") >= 0 ||
	   input.indexOf("\t") >= 0 ||
	   input.indexOf(";") >= 0 ||
	   input.indexOf("\n") >= 0 ||
	   input.indexOf('"') >= 0 )
	{input = '"'+input+'"';}

	return input;
	}
	else{

		return "";
	}
}

/**
@function Converts any XSJS RecordSet object to a Text String output
@param {object} rs - XSJS Record Set object
@param {optional Boolean} bHeaders - defines if you want column headers output as well; defaults to true
@param {optional String} delimiter - supplies the delimiter used between columns; defaults to tab (\\t)
@returns {String} The text string with the contents of the record set
*/
function recordSetToText(rs,bHeaders,delimiter){
	bHeaders = typeof bHeaders !== 'undefined' ? bHeaders : true;
	delimiter = typeof delimiter !== 'undefined' ? delimiter : '\t'; //Default to Tab Delimited

	var outputString = '';
	var value = '';
	var meta = rs.getMetaData();
	var colCount = meta.getColumnCount();

	//Process Headers
	if(bHeaders){
		for (var i=1; i<=colCount; i++) {
			outputString += escapeSpecialCharsText(meta.getColumnLabel(i)) + delimiter;			
		}
		outputString += '\n';  //Add New Line
	}
	while (rs.next()) {
		for (var i=1; i<=colCount; i++) {
		     switch(meta.getColumnType(i)) {
		     case $.db.types.VARCHAR:
		     case $.db.types.CHAR:
		          value += rs.getString(i);
		          break;
		     case $.db.types.NVARCHAR:
		     case $.db.types.NCHAR:
		     case $.db.types.SHORTTEXT:
		          value += rs.getNString(i);
		          break;
		     case $.db.types.TINYINT:
		     case $.db.types.SMALLINT:
		     case $.db.types.INT:
		     case $.db.types.BIGINT:
		          value += rs.getInteger(i);
		          break;
		     case $.db.types.DOUBLE:
		          value += rs.getDouble(i);
		          break;
		     case $.db.types.DECIMAL:
		          value += rs.getDecimal(i);
		          break;
		     case $.db.types.REAL:
		          value += rs.getReal(i);
		          break;
		     case $.db.types.NCLOB:
		     case $.db.types.TEXT:
		          value += rs.getNClob(i);
		          break;
		     case $.db.types.CLOB:
		          value += rs.getClob(i);
		          break;	          
		     case $.db.types.BLOB:
		    	  value += $.util.convert.encodeBase64(rs.getBlob(i));
		          break;	          
		     case $.db.types.DATE:
		          value += rs.getDate(i);
		          break;
		     case $.db.types.TIME:
		          value += rs.getTime(i);
		          break;
		     case $.db.types.TIMESTAMP:
		          value += rs.getTimestamp(i);
		          break;
		     case $.db.types.SECONDDATE:
		          value += rs.getSeconddate(i);
		          break;
		     default:
		          value += rs.getString(i);
		     }
			   outputString += escapeSpecialCharsText(value) + delimiter;
			   value = '';
		     }
			outputString += '\n';  //Add New Line
		}


	return outputString;
}

/**
@function Converts any XSJS RecordSet object to a JSON Object
@param {object} rs - XSJS Record Set object
@param {optional String} rsName - name of the record set object in the JSON
@returns {object} JSON representation of the record set data
*/
function recordSetToJSON(rs,rsName){
	rsName = typeof rsName !== 'undefined' ? rsName : 'entries';

	var meta = rs.getMetaData();
	var colCount = meta.getColumnCount();
	var values=[];
	var table=[];
	var value="";
	while (rs.next()) {
	for (var i=1; i<=colCount; i++) {
		value = '"'+meta.getColumnLabel(i)+'" : ';
	     switch(meta.getColumnType(i)) {
	     case $.db.types.VARCHAR:
	     case $.db.types.CHAR:
	          value += '"'+ escapeSpecialChars(rs.getString(i))+'"';
	          break;
	     case $.db.types.NVARCHAR:
	     case $.db.types.NCHAR:
	     case $.db.types.SHORTTEXT:
	          value += '"'+escapeSpecialChars(rs.getNString(i))+'"';
	          break;
	     case $.db.types.TINYINT:
	     case $.db.types.SMALLINT:
	     case $.db.types.INT:
	     case $.db.types.BIGINT:
	          value += rs.getInteger(i);
	          break;
	     case $.db.types.DOUBLE:
	          value += rs.getDouble(i);
	          break;
	     case $.db.types.DECIMAL:
	          value += rs.getDecimal(i);
	          break;
	     case $.db.types.REAL:
	          value += rs.getReal(i);
	          break;
	     case $.db.types.NCLOB:
	     case $.db.types.TEXT:
	          value += '"'+ escapeSpecialChars(rs.getNClob(i))+'"';
	          break;
	     case $.db.types.CLOB:
	          value += '"'+ escapeSpecialChars(rs.getClob(i))+'"';
	          break;	          
	     case $.db.types.BLOB:
	    	  value += '"'+ $.util.convert.encodeBase64(rs.getBlob(i))+'"';
	          break;	          
	     case $.db.types.DATE:
	    	 var dateTemp = new Date();
	    	 dateTemp.setDate(rs.getDate(i));
	    	 var dateString = dateTemp.toJSON();
	         value += '"'+dateString+'"';
	          break;
	     case $.db.types.TIME:
	    	 var dateTemp = new Date();
	    	 dateTemp.setDate(rs.getTime(i));
	    	 var dateString = dateTemp.toJSON();
	         value += '"'+dateString+'"';
	          break;
         case $.db.types.TIMESTAMP:
             var dateTemp = new Date();
             dateTemp.setDate(rs.getTimestamp(i));
             var dateString = dateTemp.toJSON();
             value += '"'+dateString+'"';
             break;
	     case $.db.types.SECONDDATE:
	    	 var dateTemp = new Date();
	    	 dateTemp.setDate(rs.getSeconddate(i));
	    	 var dateString = dateTemp.toJSON();
	         value += '"'+dateString+'"';
	          break;
	     default:
	          value += '"'+escapeSpecialChars(rs.getString(i))+'"';
	     }
	     values.push(value);
	     }
	   table.push('{'+values+'}');
	}
	return 	JSON.parse('{"'+ rsName +'" : [' + table	+']}');

}
Please log in to access this content.
Step 3: Save and run

Save and run the Node.js and then the web module. Change the URL to /xsodata/user2.xsodata Unfortunately its much more complicated to test Create/Update/Delete methods from the browser as they create other HTTP verbs. Later we will build a user interface which can call this service in order to fully test it.

Results
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

10 Min.

Intermediate

Next Steps

Next
Back to top