8Siebel Virtual Business Components
Siebel Virtual Business Components
This chapter describes the virtual business component (VBC), its uses, and restrictions. This chapter also describes how you can create a new VBC in Siebel Tools. The following topics are included:
About Virtual Business Components
A virtual business component (VBC) provides a way to access data that resides in an external data source using a Siebel business component. The VBC does not map to an underlying table in the Siebel Database. You create a new VBC in Siebel Tools workspace and deliver the workspace. The VBC calls a Siebel business service to provide a transport mechanism.
You can take two approaches to use VBCs, as illustrated in the following figure:
Use the XML Gateway business service to pass data between the VBC and one of the Siebel transports, such as the EAI HTTP Transport or the EAI MSMQ Transport.
Write your own business service in Siebel eScript or in Siebel VB to implement the methods described in this chapter.
Using VBCs for Your Business Requirements
The following features enhance the functionality of VBCs to better assist you in meeting your business requirements:
VBCs support drilldown behavior:
You can drill down on a VBC to a standard business component, another VBC, or the same VBC.
You can drill down onto a VBC from a standard business component, another VBC, or the same VBC.
A parent applet can be based on a VBC.
You can define VBCs that can participate as a parent in a business object. The VBC you define can be a parent to a standard BC or a VBC.
You still can use an older version of the XML format or property set by setting the VBC Compatibility Mode parameter to the appropriate version. For information, see the table in the topic Setting User Properties for the Virtual Business Component .
You can pass search and sort specifications to the business service used by a VBC.
You can use the Validation, Pre Default Value, Post Default Value, Link Specification, and No Copy attributes of the VBC fields.
You can use predefined queries with VBCs.
You can have picklists based on VBCs, and use picklist properties such as No Insert, No Delete, No Update, No Merge, Search Specification, and Sort Specification.
You can use the Cascade Delete, Search Spec, Sort Spec, No Insert, No Update, and No Delete link properties when a VBC is the child business component on the link.
You can use the No Insert, No Update, No Delete, Search Spec, Sort Spec, and Maximum Cursor Size business component properties.
Usage and Restrictions for Virtual Business Components
The following are the uses and restrictions of VBCs:
You can define a business object as containing both standard business components and VBCs.
When configuring applets based on VBCs, use CSSFrame (Form) and CSSFrameList (List) instead of specialized applet classes.
(Optional) Using the same name for the VBC field names and the remote data source field names can reduce the amount of required programming.
VBCs cannot be docked, so they do not apply to remote users.
VBCs cannot contain a multivalue group (MVG).
VBCs do not support many-to-many relationships.
A pick applet based on a VBC instantiates the VBC without any parent reference (no link is used). As result, the VBC business service does not receive the source field value from the parent component If the VBC business service must access the current parent business component context, then you can use the ActiveBusObject method of the TheApplication object in a business service server script to do the following:
Obtain the current business object instance (assuming this is the instance with this VBC).
Instantiate the parent business component (assuming the name of the parent BC is known).
Obtain the parent business component field for referencing it as a source (the field must be active in the current parent business component).
VBCs cannot be loaded using Enterprise Integration Manager.
Standard business components cannot contain multivalue groups based on VBCs.
VBCs cannot be implemented using any business component class other than CSSBCVExtern. This means specialized business components such as Quotes and Forecasts cannot be implemented as VBCs.
You cannot use Workflow Monitor to monitor VBCs.
You cannot execute queries against VBCs when the search specification uses a function that is normally supported for Query mode against regular business components, such as ParentFieldValue().
Using Virtual Business Components
To use VBCs to share data with an external application, perform the following high-level tasks:
Configuring your VBC business service:
Configure your XML Gateway Service or write your own business service.
For information, see XML Gateway Service and Custom Business Service Methods.
Configure your external application.
For information, see External Application Setup.
Creating a New Virtual Business Component
You create a new VBC in Siebel Tools.
To create a new virtual business component
In Siebel Tools, lock the appropriate project.
In the Object Explorer, select the Business Component object.
Right-click, and then choose New Record.
Name the business component.
Select the project you locked in Step 1.
Set the class to CSSBCVExtern. This class provides the VBC functionality.
Setting User Properties for the Virtual Business Component
When defining the VBC, you must provide the user properties shown in the following table.
Table Setting Virtual Business Component User Properties
| User Property | Description |
|---|---|
Service Name |
The name of the business service. |
Service Parameters |
(Optional) Any parameters required by the business service. The Siebel application passes this user property, as an input argument, to the business service. |
Remote Source |
(Optional) External data source that the business service is to use. This property allows the VBC to pass a root property argument to the underlying business service, but it does not allow a connection directly to the external datasource. The Siebel application passes only this user property as an input argument. |
VBC Compatibility Mode |
(Optional) Determines the format of the property set passed from a VBC to a business service, or the format in which the outgoing XML from the XML Gateway will be. A valid value is Siebel xxx, where xxx can be any Siebel release number. Some examples would be Siebel 6 or Siebel 7.0.4. If xxx is less than 7.5, the format will be in a release that is earlier than release 7.5. Otherwise, a new property set, and the XML format will be passed. If you are creating a VBC in version 7.5 or higher, then it is not necessary to define this new user property, because the default is to use the new PropertySet from a VBC and the new outgoing XML from the XML Gateway. For your existing VBC implementation, update your VBC definition by adding this new user property, and setting it to Siebel xxx, where xxx is the version number that you want. |
To define user properties for a virtual business component
In the Object List Editor in Siebel Tools, select the virtual business component for which you want to define user properties.
In the Object Explorer, expand the Business Component tree, and then select Business Component User Prop.
In the Object List Editor, click in the Business Component User Props list, right-click, and then choose New Record.
Type the name of the user property, such as Service Name, in the Name field.
Type the value of the user property, such as a business service name, in the Value field.
Repeat the process for every user property you want to define for this VBC.
XML Gateway Service
The XML Gateway business service communicates between Siebel Business Applications and external data sources using XML as the data format. For information on XML format, see Examples of the Outgoing XML Format and Examples of the Incoming XML Format. The XML Gateway business service can be configured to use one of the following transports:
EAI MQSeries Server Transport
EAI HTTP Transport
EAI MSMQ Transport
You can configure the XML Gateway by specifying the transport protocol and the transport parameters you use in the Service Parameters User Property of the VBC, as shown in the following table. When using the XML Gateway, specify the following user properties for your VBC.
Table User Properties
| Name | Value |
|---|---|
Service Name |
XML Gateway |
Service Parameters |
variable1 name=variable1 value; variable2 name=variable2 value>;... |
Remote Source |
External Data Source |
VBC Compatibility Mode |
Siebel xxx, where xxx can be any Siebel release number. |
For example, if you want to specify the EAI HTTP Transport, then you can use something like the following:
"Transport=EAI HTTP Transport;HTTPRequestURLTemplate=<your URL>;HTTPRequestMethod=POST"
You can also implement a VBC with MQSeries. The following procedure lists the steps you take to implement this.
To implement a VBC with MQSeries
Call the EAI Business Integration Manager (Server Request) business service.
Define another service parameter for the name of a workflow to run, with the following user properties on the VBC:
Service Name. XML Gateway.
Service Parameters. Transport=EAI Business Integration Manager (Server Request);ProcessName=EAITEST.
Define a workflow, EAITEST, to call the EAI MQSeries Server Transport with the SendReceive method.
Define a new process property, <Value>, on the workflow, and use it as an output argument on the EAI MQSeries Server Transport step in the workflow.
XML Gateway Methods
The XML Gateway provides the methods presented in the following table.
Table XML Gateway Methods
| Method | Description |
|---|---|
Init |
Initializes the XML Gateway business service for every business component. |
Delete |
Deletes a given record in the remote data source. |
Insert |
Inserts a record into a remote data source. |
PreInsert |
Performs an operation that tests for the existence of the given business component. Only default values are returned from the external application. |
Query |
Queries the given business component from the given data source. |
Update |
Updates a record in the remote data source. |
XML Gateway Method Arguments
The XML Gateway init, delete, insert, preInsert, query, and update methods take the arguments presented in the following table.
Table XML Gateway Arguments
| Argument | Description |
|---|---|
Remote Source |
The VBC Remote Source user property. The remote source from which the service is to retrieve data for the business component. This must be a valid connect string. When configuring the repository business component on top of the specialized business component class CSSBCVExten, you can define a user property Remote Source to allow the Transport Services to determine the remote destination and any connect information. If this user property is defined, then it is passed to every request as the remote-source tag. |
Business Component Id |
Unique key for the given business component. |
Business Component Name |
Name of the business component or its equivalent, such as a table name. |
Parameters |
The VBC Service Parameters user property. A set of string parameters required for initializing the XML Gateway. |
About Handling White Space
White space is handled by the XML parser while processing the request from the XML Gateway business service. When the white space is part of the XML syntax, it must be discarded by the XML parser and not preserved (passed on to the processing application). If the white space is in any other location (such as in element content within a document), then it must be preserved according to the XML specification, because it might have some meaning.
For example:
<mydata> <mytag>stuff</mytag> </mydata>
and
<mydata><mytag>stuff</mytag></mydata>
are different to an XML parser.
To preserve white space, use the xml:space attribute with the value preserve, for example:
<mydata xml:space="preserve"> <mytag>stuff and more stuff</mytag> </mydata>
The value of xml:space applies to all children of the element containing the attribute unless overridden by one of the children.
For more information on white space handling and the xml:space attribute, see Microsoft Developer Network (http://msdn.microsoft.com).
Examples of the Outgoing XML Format
Examples of the XML documents generated and sent by the XML Gateway to the external system are presented in the following table. These examples are based on the Siebel eScript example in Custom Business Service Examples. See DTDs for XML Gateway Business Service for examples of the DTDs that correspond to each of these methods.
Table Outgoing XML Tags and Descriptions
| Method | Format of the XML Stream | Description |
|---|---|---|
Delete Request |
|
siebel-xmlext-delete-req This tag requests removal of a single record in the remote system. |
Init Request |
|
This tag fetches the list of fields supported by this instance.
The business component ID.
The remote source from which the service is to retrieve data for the business component. |
Insert Request |
|
siebel-xmlext-insert-req This tag requests the commit of a new record in the remote system. The insert-req XML stream contains values for fields entered through the business component. |
PreInsert Request |
|
siebel-xmlext-preinsert-req This tag allows the connector to provide default values. This operation is called when a new row is created, but before any values are entered through the business component interface. |
Query Request |
|
This tag queries by example. The query-req XML stream contains parameters necessary to set up the query. In this example, the query requests that record information be returned from the remote system.
Maximum number of rows to be returned. The value is the Maximum Cursor Size defined at the VBC plus one. If the Maximum Cursor Size property is not defined at the VBC, then the max-rows property is not passed.
The search specification used to query and filter the information.
Hierarchical representation of the search-string. For information, see Search-Spec Node-Type Values.
List of sort fields and sort order.
Note: In some cases you might retrieve external data for display in a child list applet, using a link to a parent business component. If the parent business component field on which the link is based is empty, then the query request is sent without a search-spec tag, but instead with the following tag:
match field="
Child BC Fieldname
"/>
|
Update Request |
<value changed="false" field="Name">Max Adams</value>
|
siebel-xmlext-update-req This tag requests changes to the field values for an existing row. All values for the record are passed with the value tags, and with the changed attribute identifying the ones that have been changed through the Siebel application. |
Search-Spec Node-Type Values
The search-string is in the Siebel query language format. The search-string is parsed by the Siebel query object and then turned into the hierarchical search-spec. The following table shows the different search-spec node-types and their values.
Table Search-Spec Node-Types
| Node-Type | PropertySet and XML Representation |
|---|---|
Constant |
Example:
The valid value-types are TEXT, NUMBER, DATETIME, UTCDATETIME, DATE, and TIME. |
Identifier |
Example: The value Name is a valid business component field name. |
Unary Operator |
Example: The valid values are NOT, EXISTS, IS NULL, IS NOT NULL. |
Binary Operator |
Example: The valid values are LIKE, NOT LIKE, SOUNDSLIKE, =, <>, <=, <, >=, >, AND, OR, +, -, *, /, ^. |
Examples of the Incoming XML Format
The following table contains examples of XML documents that are sent from an external system to the XML Gateway in response to a request. These examples are based on the Siebel eScript example in Custom Business Service Examples. See DTDs for XML Gateway Business Service for examples of the DTDs that correspond to each of these methods.
Table Incoming XML Tags and Descriptions
| Method | Format of the XML Stream | Description |
|---|---|---|
Delete Return |
|
siebel-xmlext-delete-ret. Only the XML stream tag is returned. |
Error |
|
Format of the XML stream expected by the Siebel application in case of an error in the external application. If the error is specific to a field, then the field name must be specified. The tags for this XML stream, and the entire XML stream, are optional:
|
Init Return |
|
siebel-xmlext-fields-ret The fields-ret XML stream return contains the list of VBC fields supported by the external application for this instance. The following field names are reserved by the Siebel application, and must not appear in this list:
|
Insert Return |
|
siebel-xmlext-insert-ret If the remote system has inserted records, then they can be returned to be reflected in the business component in an insert-ret XML stream in the row tag format as the insert-ret stream. |
PreInsert Return |
|
siebel-xmlext-preinsert-ret Returns default values for each field, if there is any default value. |
Query Return |
|
The query-ret XML stream contains the result set that matches the criteria of the query.
|
Update Return |
|
siebel-xmlext-update-ret If the remote system updated fields, then the fields can be returned to be reflected in the business component in an update-ret XML stream in the row tag format as the update-ret stream. |
External Application Setup
When you have your XML Gateway Service configured, set up your external application accordingly to receive and respond to the requests. At a minimum, the external application must support the Init() and Query() methods, and depending upon the functionality provided by the VBC, the remaining methods might or might not be necessary.
Custom Business Service Methods
Your business service must implement the Init and Query methods as described in this topic. The Delete, PreInsert, Insert, and Update methods are optional, and depend on the functionality required by the VBC.
These methods pass property sets between the VBC and the business service. VBC methods take property sets as arguments. Each method takes two property sets: an Inputs property set and an Outputs property set. The methods are called by the CSSBCVExtern class in response to requests from other objects that refer to, or are based on the VBC.
If VBCs are used, then custom business services are written to access external relational databases. However, it is recommended that you use external business components (EBCs) to access these databases instead of writing custom business services. For more information on EBCs, see External Business Components.
Common Method Parameters
The following table shows the input parameters common to every method. Note that all these parameters are at the root property set.
Table Common Input Parameters
| Parameter | Description |
|---|---|
Remote Source |
(Optional) Specifies the name of an external data source. This is the VBC’s Remote Source user property, if defined. For information, see the table in topic Setting User Properties for the Virtual Business Component. |
Business Component Name |
Name of the active VBC. |
Business Component Id |
Internally generated unique value that represents the VBC. |
Parameters |
(Optional) The VBC’s Service Parameters user property, if defined. For information, see the table in topic Setting User Properties for the Virtual Business Component. A set of parameters required by the business service. |
VBC Compatibility Mode |
(Optional) This is the VBC’s Compatibility Mode user property, if defined. For information, see the table in topic Setting User Properties for the Virtual Business Component. |
When a response has been received, the method packages the response from the external data source into the output’s property set.
Business Services Methods and Their Property Sets
The following examples display each method's input and output property sets for a VBC Contact that displays simple contact information for a given account. These examples are based on the example in the Custom Business Service Examples.
The output property set of the Insert and Update methods for VBC does not affect the data in the business component, unlike the Query method, which uses the output property set to populate the business component. The output property set for Insert and Update is used to indicate that what fields or record has been changed.
Delete Method
The Delete method is called when a record is deleted. The following figure illustrates the property set for the Delete input.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?>
<?Siebel-Property-Set EscapeNames="true"?> <PropertySet Business_spcComponent_spcId="1" Business_spcComponent_spcName="Contact"> <PropertySet AccountId="1-6" Name="Max Adams" Phone="(408)234-1029" Location="San Jose" AccessId="146" /> </PropertySet>
The following figure illustrates the property set for the Delete output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet />
Error Return Method
The following figure illustrates the property set for the Error Return, when an error is detected.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet> <Status Status="4" Error_spcField="Name" Error_spcText="Name must not be empty"/> </PropertySet>
Init Method
The Init method is called when the VBC is first instantiated. It initializes the VBC. It expects to receive the list of fields supported by the external system.
The following figure illustrates the property set for the Init input.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8"?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet Business_spcComponent_spcId="1" Business_spcComponent_spcName="Contact"/>
The following figure illustrates the property set for the Init output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet AccountId="" Name="" Phone="" Location="" AccessId="" />
Insert Method
The Insert method is called when a New Record is committed. The following figure illustrates the property set for the Insert input.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet Business_spcComponent_spcId="1" Business_spcComponent_spcName="Contact"> <PropertySet AccountId="1-6" Name="Max Adams" Phone="(398)765-1290" Location="Troy" AccessId="" /> </PropertySet>
The following figure illustrates the property set for the Insert output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet <PropertySet AccountId="1-6" Name="Max Adams" Phone="(398)765-1290" Location="Troy" AccessId="146" /> </PropertySet>
PreInsert Method
The PreInsert method is called when a New Record operation is performed. It supplies default values. The following figure illustrates the property set for the PreInsert input.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8"?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet Business_spcComponent_spcId="1" Business_spcComponent_spcName="Contact"/>
The following figure illustrates the property set for the PreInsert output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet> <PropertySet Location="San Jose" /> </PropertySet>
Query Method
The Query method is called when a search is performed. The Query method must be supported by every VBC. Each record that matches the query is represented as a property set. For example, if 5 records match the query, then there will be 5 child property sets. Each property set contains a list of field names, that is field value pairs representing the values of each field for that particular record. The following figure illustrates the property set for the Query input and is followed by its XML representation.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?>
<?Siebel-Property-Set EscapeNames="true"?>
<PropertySet
max-rows="6"
search-string="([Phone] IS NOT NULL) AND ([AccountId] = "1-6")"
Business_spcComponent_spcId="1"
Business_spcComponent_spcName="Contact">
<PropertySet AccountId="1-6" />
<search-spec>
<node node-type="Binary Operator">AND
<node node-type="Unary Operator">IS NOT NULL
<node node-type="Identifier">Phone</node>
</node>
<node node-type="Binary Operator">=
<node node-type="Identifier">AccountId</node>
<node value-type="TEXT" node-type="Constant">1-6</node>
</node>
</node>
</search-spec>
<sort-spec>
<sort field="Location">ASCENDING</sort>
<sort field="Name">DESCENDING</sort>
</sort-spec>
</PropertySet>
The following figure illustrates the property set for the Query output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet> <PropertySet AccountId="1-6" Name="Sara Chen" Phone="(415)298-7890" Location="San Francisco" AccessId="128" /> <PropertySet AccountId="1-6" Name="Eric Brown" Phone="(650)123-1000" Location="Palo Alto" AccessId="129" /> </PropertySet>
Update Method
The Update method is called when a record is modified. The following figure illustrates the property set for the Update input.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet Business_spcComponent_spcId="1" Business_spcComponent_spcName="Contact"> <PropertySet Field_spcName="AccountId" Changed="false" Field_spcValue="1-6"/> <PropertySet Field_spcName="Name" Changed="false" Field_spcValue="MaxAdams"/> <PropertySet Field_spcName="Phone" Changed="true" Field_spcValue="(408)234-1029"/> <PropertySet Field_spcName="Location" Changed="true" Field_spcValue="SanJose"/> <PropertySet Field_spcName="AccessId" Changed="false" Field_spcValue="146" /> </PropertySet>
The following figure illustrates the property set for the Update output.
The following is the XML representation of the property set shown in the previous figure:
<?xml version="1.0" encoding="UTF-8" ?> <?Siebel-Property-Set EscapeNames="true"?> <PropertySet <PropertySet Phone=="(408)234-1029" Location="San Jose" /> </PropertySet>
Custom Business Service Examples
These examples show the implementation of a business service for a VBC in both Siebel eScript and Siebel VB:
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.
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;
}
Siebel VB Business Service Example for a VBC
The following is an example of Siebel VB implementation of a business service for a VBC. The fields configured for this simple VBC are AccountId, Name, Phone, and Location.
(declarations)
Option Explicit
Declare Function stringReplace(mystr As String, fromchar As String, tochar As
String) As String
Declare Function getData(execSQL As String, Results As PropertySet) As Integer
Function getData(execSQL As String, Results As PropertySet) As Integer
Dim sSrv As String, sDbn As String
Dim sUsr As String, sPsw As String
Dim oCon As Object, oRec As Object
Dim Row As PropertySet
Dim FileName, TextToSave
' *** SQL Server connectivity parameters
sSrv = "v817.siebel.com" '*** Oracle tns
sUsr = "system" '*** SQL Server: a user's login Id
sPsw = "manager" '*** SQL Server: a user's password
' *** Create SQL Server ADODB connection dynamically
Set oCon = CreateObject("ADODB.Connection")
oCon.Open "Provider=MSDAORA;" & _
"Data Source=" & sSrv & ";" & _
"User ID=" & sUsr & ";" & "Password=" & sPsw & ";"
' *** Perform SQL query
Set oRec = oCon.Execute(execSQL)
' *** Process SQL query result and save into file
While Not oRec.Eof
Set Row=TheApplication.NewPropertySet()
Row.SetProperty "AccountId", oRec.Fields.Item("AccountId").Value
Row.SetProperty "Name", oRec.Fields.Item("Name").Value
Row.SetProperty "Location", oRec.Fields.Item("Location").Value
Row.SetProperty "Phone", oRec.Fields.Item("Phone").Value
Results.AddChild Row
Set Row = Nothing
oRec.MoveNext
Wend
' *** Object cleanup
Set oRec = Nothing
Set oCon = Nothing
getData = 0
End Function
Sub Init(Inputs As PropertySet, Outputs As PropertySet)
Outputs.SetProperty "AccountId", ""
Outputs.SetProperty "Name", ""
Outputs.SetProperty "Phone", ""
Outputs.SetProperty "Location", ""
End Sub
Sub Query(Inputs As PropertySet, Outputs As PropertySet)
Dim sselectStmt As String
Dim swhereClause As String
Dim sorderbyClause As String
Dim ssearchstring As String
Dim child As PropertySet
Dim sortProp As PropertySet
Dim childCount As Integer
Dim i As Integer
Dim ret As Integer
Dim FileName, TextToSave
sselectStmt = "select * from siebel.Contact2 "
swhereClause = "where "
sorderbyClause= "order by "
ssearchstring = Inputs.GetProperty("search-string")
If Len(ssearchstring) > 0 Then
ssearchstring = stringReplace(ssearchString, "*", "%")
ssearchstring = stringReplace(ssearchString, "[", " ")
ssearchstring = stringReplace(ssearchString, "]", " ")
ssearchstring = stringReplace(ssearchString, "~", " ")
ssearchstring = stringReplace(ssearchString, chr$(34), "'")
sselectStmt = sselectStmt & swhereClause & ssearchstring
End If
' Write select statement to this file
FileName = "C:\Test.txt"
TextToSave = "select is " & sselectStmt
Open FileName For Append As #1
Print #1, TextToSave
Close #1
ret = getData(sselectStmt, Outputs)
End Sub
Function stringReplace(mystr As String, fromchar As String, tochar As String) As
String
'Replace all occurrences of fromchar in mystr with tochar
Dim i As Long
If Len(mystr) = 0 Or Len(fromchar) = 0 Then
stringReplace = mystr
Else
i = InStr(1, mystr, fromchar)
Do While i > 0
mystr = Left(mystr, i - 1) & tochar & Mid(mystr, i + Len(fromchar))
i = i + Len(fromchar)
i = InStr(i, mystr, fromchar)
Loop
stringReplace = mystr
End If
End Function
Function Service_PreInvokeMethod (MethodName As String, Inputs As PropertySet,
Outputs As PropertySet) As Integer
Service_PreInvokeMethod = ContinueOperation
If MethodName = "Init" Then
Service_PreInvokeMethod = CancelOperation
Init Inputs, Outputs
Exit Function
End If
If MethodName = "Query" Then
Service_PreInvokeMethod = CancelOperation
Query Inputs, Outputs
Exit Function
End If
End Function