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


  1. 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";
    }
    
  2. 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);
    }
    
  3. 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	+']}');
    }
    
  4. 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

Next Steps

Updated 10/18/2016

Time to Complete

10 Min.

Intermediate

Next Steps

Next
Back to top