Integration Platform Technologies: Siebel Enterprise Application Integration > Siebel Virtual Business Components > Custom Business Service Examples >

Siebel eScript Business Service Example for a VBC

The following is an example of Siebel eScript implementation of a business service for a VBC. The fields configured for this simple VBC are AccountId, Name, Phone, Location, and AccessId. AccessId is the primary key in the external data source. AccessId is included in the VBC fields to make updating and deleting the fields simple and is configured as a hidden field.

CAUTION:  Do not use Siebel CRM system fields, such as Id, as output properties. Undesired application behavior might result.

  function Service_PreInvokeMethod (MethodName, Inputs, Outputs) {

   if (MethodName == "Init") {

      return(Init(Inputs, Outputs));

   }

   else if (MethodName == "Query") {

      return(Query(Inputs, Outputs));

   }

   else if (MethodName == "PreInsert") {

      return(PreInsert(Inputs, Outputs));

   }

   else if (MethodName == "Insert") {

      return(Insert(Inputs, Outputs));

   }

   else if (MethodName == "Update") {

      return(Update(Inputs, Outputs));

   }

   else if (MethodName == "Delete") {

      return(Delete(Inputs, Outputs));

   }

   else {

      return (ContinueOperation);

   }

  }

  function Init (Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "InitInputs.xml");

   Outputs.SetProperty("AccountId", "");

   Outputs.SetProperty("Name", "");

   Outputs.SetProperty("Phone", "");

   Outputs.SetProperty("AccessId", "");

   Outputs.SetProperty("Location", "");

   // For debugging purposes...

   logPropSet(Outputs, "InitOutputs.xml");

   return (CancelOperation);

  }

  function Query(Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "QueryInputs.xml");

   var selectStmt = "select * from Contacts ";

   var whereClause = "";

    var orderbyClause = "";

   // You have the following properties if you want to use them

   // Inputs.GetProperty("Business Component Name")

   // Inputs.GetProperty("Business Component Id")

   // Inputs.GetProperty("Remote Source")

   // If you configured Maximum Cursor Size at the buscomp,

   // get max-rows property

   var maxRows = Inputs.GetProperty("max-rows");

   // get search-string

   var searchString = Inputs.GetProperty("search-string");

    if (searchString != "" )

    {

   // convert the search-string into a where clause

   searchString = stringReplace(searchString, '*', '%');

   searchString = stringReplace(searchString, '[', ' ');

   searchString = stringReplace(searchString, ']', ' ');

   searchString = stringReplace(searchString, '~', ' ');

   searchString = stringReplace(searchString, '"', "'");

    whereClause = " where ";

     whereClause = whereClause + searchString;

   }

   // match, search-spec, sort-spec

   var childCount = Inputs.GetChildCount();

   var child, sortProp;

   for (var i = 0; i < childCount; i++)

   {

      child = Inputs.GetChild(i);

      if (child.GetType() == "")

      {

      // Use this child property set if you want to use the old match field list.

      // We are not using this in this example. We'll use search-string instead.

      }

      else if (child.GetType() == "search-spec")

      {

      // Use this child property set if you want to use the hierarchical

      // representation of the search-string.

      // We are not using this in this example. We'll use search-string instead.

      }

      else if (child.GetType() == "sort-spec")

      {

      // This child property set has the sort spec. We'll use this in this example

      orderbyClause = " order by ";

      var sortFieldCount = child.GetChildCount();

      for (var j = 0; j < sortFieldCount; j++)

      {

         // Compose the order by clause.

         sortProp = child.GetChild(j);

         orderbyClause += sortProp.GetProperty("field");

         var sortOrder = sortProp.GetValue();

         if (sortOrder == "DESCENDING")

            orderbyClause += " desc";

         if (j < sortFieldCount-1)

            orderbyClause += ", ";

         }

      }

   }

   // Now, our complete select statement is...

   selectStmt += whereClause + orderbyClause;

   // Now, query the data source.

   var conn = getConnection();

   var rs = getRecordset();

   rs.Open(selectStmt, conn);

   // We will return no more than maxRows of records.

   var count = rs.RecordCount;

   if (maxRows != "")

      if (count > maxRows)

         count = maxRows

   // Iterate through the record set and add them to the Outputs PropertySet.

   var fcount, fields, row;

   for (i = 0; i < count; i++)

   {

      row = TheApplication().NewPropertySet();

      fields = rs.Fields();

      fcount = fields.Count;

      for (j = 0; j < fcount; j++)

      {

      var fieldValue = fields.Item(j).Value;

      if (fieldValue == null)

         row.SetProperty(fields.Item(j).Name, "");

      else

         row.SetProperty(fields.Item(j).Name, fieldValue);

      }

      Outputs.AddChild(row);

      rs.MoveNext();

   }

   // For debugging purposes...

   logPropSet(Outputs, "QueryOutputs.xml" );

   // clean up

   child = null;

   sortProp = null;

   row = null;

   rs.Close();

   rs = null;

   conn.Close();

   conn = null;

   return (CancelOperation);

  }

  function PreInsert (Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "PreInsertInputs.xml");

   var defaults = TheApplication().NewPropertySet();

   defaults.SetProperty("Location", "KO");

   Outputs.AddChild(defaults);

   // For debugging purposes...

   logPropSet(Outputs, "PreInsertOutputs.xml");

   // Cleanup

   defaults = null;

   return (CancelOperation);

  }

  function Insert (Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "InsertInputs.xml");

   var fieldList = "";

   var valueList = "";

   // Inputs must have only 1 child property set.

   var child = Inputs.GetChild(0);

   var fieldName = child.GetFirstProperty();

   var fieldValue;

   while (fieldName != "")

   {

      fieldValue = child.GetProperty(fieldName);

      if (fieldValue != "")

      {

      if (fieldList != "")

      {

         fieldList += ", ";

         valueList += ", ";

      }

      fieldList += fieldName;

      valueList += "'" + fieldValue + "'";

      }

      fieldName = child.GetNextProperty();

   }

   // The insert statement is...

   var insertStmt = "insert into Contacts (" + fieldList + ") values (" + valueList + ")";

   // Now, inserting into the data source...

   var conn = getConnection();

   conn.Execute (insertStmt);

   // In this example, we must query back the record just inserted to get

   // the value of its primary key. We made this primary key part of the buscomp

   // to make update and delete easy. The primary key is "AccessId".

   var selectStmt = "select * from Contacts where ";

   var whereClause = "";

   child = Inputs.GetChild(0)

   fieldName = child.GetFirstProperty();

   while (fieldName != "")

   {

      fieldValue = child.GetProperty(fieldName);

      if (fieldName != "AccessId")

      {

      if (whereClause != "")

         whereClause += " and ";

      if (fieldValue == "")

         whereClause += fieldName + " is null";

      else

         whereClause += fieldName + "='" + fieldValue + "'";

      }

      fieldName = child.GetNextProperty();

   }

   // The select statement is...

   selectStmt += whereClause;

   // Now, let's select the new record back

   var rs = getRecordset();

   rs.Open(selectStmt, conn);

   // We're expecting only one row back in this example.

   var fcount, fields, row, fieldValue;

   row = TheApplication().NewPropertySet();

   fields = rs.Fields();

   fcount = fields.Count();

   for (var j = 0; j < fcount; j++)

   {

      fieldValue = fields.Item(j).Value();

      if (fieldValue == null)

         row.SetProperty(fields.Item(j).Name(), "");

      else

         row.SetProperty(fields.Item(j).Name(), fieldValue);

   }

   Outputs.AddChild(row);

   // For debugging purpose...

   logPropSet(Outputs, "InsertOutputs.xml");

   // Cleanup

   child = null;

   row = null;

   rs.Close();

   rs = null;

   conn.Close();

   conn = null;

   return (CancelOperation);

  }

  function Update (Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "UpdateInputs.xml");

   var child;

   var childCount = Inputs.GetChildCount();

   var fieldName, fieldValue;

   var updateStmt = "update Contacts set ";

   var setClause = "";

   var whereClause;

   // Go through each child in Inputs and construct the

   // necessary sql statements for update and query

   for (var i = 0; i < childCount; i++)

   {

      child = Inputs.GetChild(i);

      fieldName = child.GetProperty("Field Name");

      fieldValue = child.GetProperty("Field Value");

      // We only have to update changed fields.

      if (child.GetProperty("Changed") == "true")

      {

         if (setClause != "")

            setClause += ", ";

         if (fieldValue == "")

            setClause += fieldName + "=null";

         else

            setClause += fieldName + "='" + fieldValue + "'";

      }

      if (fieldName == "AccessId")

         whereClause = " where AccessId = " + fieldValue;

   }

   // The update statement is...

   updateStmt += setClause + whereClause;

   // Now, updating the data source...

   var conn = getConnection();

   conn.Execute (updateStmt);

   // How to construct the Outputs PropertySet can vary, but in this example

   // We'll query back the updated record from the data source.

   var selectStmt = "select * from Contacts" + whereClause;

   // Now, let's select the updated record back

   var rs = getRecordset();

   rs.Open(selectStmt, conn);

   // We expect only one row back in this example.

   // In this example, we're returning all the fields and not just

   // the updated fields. You can only return those updated

   // fields with the new value in the Outputs property set.

   var fcount, fields, row, fieldValue;

   row = TheApplication().NewPropertySet();

   fields = rs.Fields();

   fcount = fields.Count();

   for (var j = 0; j < fcount; j++)

   {

      fieldValue = fields.Item(j).Value();

      if (fieldValue == null)

         row.SetProperty(fields.Item(j).Name(), "");

      else

         row.SetProperty(fields.Item(j).Name(), fieldValue);

   }

   Outputs.AddChild(row);

   // For debugging purposes...

   logPropSet(Outputs, "UpdateOutputs.xml");

   // Cleanup

   child = null;

   row = null;

   rs.Close();

   rs = null;

   conn.Close();

   conn = null;

   return (CancelOperation);

  }

  function Delete (Inputs, Outputs) {

   // For debugging purposes...

   logPropSet(Inputs, "DeleteInputs.xml");

   // Inputs must have only 1 child property set.

   var child = Inputs.GetChild(0);

   // In this example, we're only using the AccessId

   // (it is the primary key in the Contacts db)

   // for the delete statement for simplicity.

   var deleteStmt = "delete from Contacts where AccessId = " + child.GetProperty("AccessId");

   // Now, delete the record from the data source.

   var conn = getConnection();

   conn.Execute(deleteStmt);

   // For debugging purposes...

   logPropSet(Outputs, "DeleteOutputs.xml");

   // Returning empty Outputs property set.

   // clean up

   conn.Close();

   conn = null;

   return (CancelOperation);

  }

The following functions are helper functions:

  function getConnection () {

   // VBCContact is the ODBC data source name

   var connectionString = "DSN=VBCContact";

   var uid = "";

   var passwd = "";

   var conn = COMCreateObject("ADODB.Connection");

   conn.Mode = 3;

   conn.CursorLocation = 3;

   conn.Open(connectionString , uid, passwd);

   return conn;

  }

  function getRecordset() {

   var rs = COMCreateObject("ADODB.Recordset");

   return rs;

  }

  function logPropSet(inputPS, fileName) {

   // Use EAI XML Write to File business service to write

   // inputPS property set to fileName file in c:\temp directory.

   var fileSvc = TheApplication().GetService("EAI XML Write to File");

   var outPS = TheApplication().NewPropertySet();

   var fileLoc = "c:\\temp\\" + fileName;

   var tmpProp = inputPS.Copy();

   tmpProp.SetProperty("FileName", fileLoc);

   fileSvc.InvokeMethod("WritePropSet", tmpProp, outPS);

   // clean up

   outPS = null;

   fileSvc = null;

   tmpProp = null;

  }

  function stringReplace (string, from, to) {

   // Replaces from with to in string

   var stringLength = string.length;

   var fromLength = from.length;

   if ((stringLength == 0) || (fromLength == 0))

   return string;

   var fromIndex = string.indexOf(from);

   if (fromIndex < 0)

      return string;

   var newString = string.substring(0, fromIndex) + to;

   if ((fromIndex + fromLength) < stringLength)

      newString += stringReplace(string.substring(fromIndex+fromLength, stringLength), from, to);

   return newString;

  }