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

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.

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

(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