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