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;
}