Integration Platform Technologies: Siebel eBusiness Application Integration Volume II > Siebel Virtual Business Components >

Custom Business Service Example

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

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 purpose...

   logPropSet(Inputs, "InitInputs.xml");

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

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

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

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

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

   // For debugging purpose...

   logPropSet(Outputs, "InitOutputs.xml");

   return (CancelOperation);

}

function Query(Inputs, Outputs)

{

   // For debugging purpose...

   logPropSet(Inputs, "QueryInputs.xml");

   var selectStmt = "select * from Contacts ";

   var whereClause = " where ";

   var orderbyClause = " order by ";

   // 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");

   // convert the search-string into a where clause

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

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

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

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

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

   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

      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're only going to return no more than maxRows of records.

   var count = rs.RecordCount();

   if (maxRows != "")

      if (count > maxRows)

         count = maxRows

   // We'll go through the recordset 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 purpose...

   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 purpose...

   logPropSet(Inputs, "PreInsertInputs.xml");

   var defaults = TheApplication().NewPropertySet();

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

   Outputs.AddChild(defaults);

   // For debugging purpose...

   logPropSet(Outputs, "PreInsertOutputs.xml");

   // clean up

   defaults = null;

   return (CancelOperation);

}

function Insert (Inputs, Outputs)

{

   // For debugging purpose...

   logPropSet(Inputs, "InsertInputs.xml");

   var fieldList = "";

   var valueList = "";

   // Inputs should 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 need to 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");

   // clean up

   child = null;

   row = null;

   rs.Close();

   rs = null;

   conn.Close();

   conn = null;

   return (CancelOperation);

}

function Update (Inputs, Outputs)

{

   // For debugging purpose...

   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

   // 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 need 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're expecting 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 purpose...

   logPropSet(Outputs, "UpdateOutputs.xml");

   // clean up

   child = null;

   row = null;

   rs.Close();

   rs = null;

   conn.Close();

   conn = null;

   return (CancelOperation);

}

function Delete (Inputs, Outputs)

{

   // For debugging purpose...

   logPropSet(Inputs, "DeleteInputs.xml");

   // Inputs should have only 1 child property set.

   var child = Inputs.GetChild(0);

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

   // (it's the primary key in the Contacts db)

   // for delete statement for simplicity.

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

   // Now, let's delete the record from the data source.

   var conn = getConnection();

   conn.Execute(deleteStmt);

   // For debugging purpose...

   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;

}

NOTE:  For more examples of VBCs, see Developing and Deploying Siebel eBusiness Applications.


 Integration Platform Technologies: Siebel eBusiness Application Integration Volume II 
 Published: 18 July 2003