Bookshelf v7.7: Retrieving Siebel Data Using VB and ASP

Transports and Interfaces: Siebel eBusiness Application Integration Volume III > Using Siebel OLE DB Provider > How Scripts and Custom Applications Affect Your Data >

Retrieving Siebel Data Using VB and ASP

You can view Siebel data using Visual Basic. Programmers writing custom VB programs and scripts that need to access data in the Siebel repository find the Siebel OLE DB Provider support useful for such tasks.

To view Siebel data using Visual Basic

Start Microsoft Visual Basic.
Enter code similar to the following example (providing your actual ID, password, selection criteria, and so on):
'This program will connect to the Siebel OLE DB Provider, retrieve data and save 'the records in a file with tab separated fields. Other tools can then be used to 'further process the data.

Dim Fso, File

'Setup program parameters.

ProviderString = "SiebelOLEDB.Provider.1"

DataSourceString = "siebel://MyGateway/MyEnterprise/MyObjMgr/MyServer"

UserIdString = "MyUserId"

PasswordString = "MyPassword"

OutFileString = "output.txt"

'Build the connection string.

ConnectString = "Provider=" & ProviderString & ";User Id=" & UserIdString & ";Password=" & PasswordString & ";Data Source=" & DataSourceString & ";"

'Ask the user if they are ready to establish a connection and retrieve the data.

Message = "Ready to connect using" & Chr(13) & Chr(10) & ConnectString & Chr(13) & Chr(10) & "Do you want to continue?"

Continue = MsgBox(Message, vbYesNo, "Ready to Connect")

If Continue = vbYes Then

'Create the output file for storing the data.

Set Fso = CreateObject("Scripting.FileSystemObject")

Set File = Fso.OpenTextFile(OutFileString, 2, True)

'Establish a connection.

Set Connection = CreateObject("ADODB.Connection")

Connection.Open ConnectString

'Execute a query to create a record set.

'Retrieve all accounts involved in any electrical related business.

QueryString = "Select * from Account_Account_1 where 'Line of Business' = 'Electrical*'"

Set RecordSet = Connection.Execute(QueryString)

'If there is any data then write a header record with column names.

If Not RecordSet.EOF Then

First = True

For Each Field in RecordSet.Fields

'Write each field within double quotes and a tab separator between them.

If First Then

File.Write """"

First = False

Else

File.Write """" & Chr(9) & """"

End If

File.Write Field.Name

Next

File.WriteLine """"

End If

'Keep track of the number of records.

RecordCount = 0

Do While Not RecordSet.EOF

First = True

For Each Field in RecordSet.Fields

'Write each field within double quotes and a tab separator between them.

If First Then

  File.Write """"

  First = False

Else

  File.Write """" & Chr(9) & """"

End If

File.Write Field.Value

Next

File.WriteLine """"

RecordCount = RecordCount + 1

RecordSet.MoveNext

Loop

'Clean up local variables.

RecordSet.Close

Connection.Close

Set RecordSet = nothing

Set Connection = nothing

File.Close

Set File = nothing

Set Fso = nothing

'Notify the user of the number of records retrieved and stored.

Message = "Successfully retrieved and stored " & RecordCount & " records in " & OutFileString

MsgBox Message, vbOkOnly, "Data Retrieved"

End If

Programmers, Webmasters, and others who need to display data from the Siebel Database in a Web page or portal find Siebel Systems' OLE DB Provider support useful for such tasks.

To view Siebel data using ASP

Create an HTML page to display a form for gathering user input with the following HTML:
<html>

<head>

<TITLE>Request Account Information</TITLE>

</head>

<body>

<FONT FACE="Verdana, Arial, Helvetica">

<br><P ALIGN=left><FONT SIZE=4>Request Account Information.</FONT></P>

<HR ALIGN=center NOSHADE SIZE=4>

<form action="test04.asp" method="POST">

<P ALIGN=left>Please enter your User ID, password and account id to access your account information.</P>

<TABLE CELLPADDING=4>

<TR>

<TD>User Id:</TD>

<TD><input type="TEXT" name="UserId"></TD>

</TR>

<TR>

<TD>Password:</TD>

<TD><input type="PASSWORD" name="Password"></TD>

</TR>

<TR>

<TD>Account Id:</TD>

<TD><input type="TEXT" name="AccountId"><br></TD>

</TR>

</TABLE>

<br>

<input type="submit" value="Submit">

</form>

</FONT>

</BODY>

</html>
Create an Active Server Page (ASP) file that retrieves the input parameters, connects to the Siebel OLE DB Provider and builds the output to be sent back and displayed in the browser.
Use the following HTML code:

<HTML>

<HEAD>

<TITLE>Account Information</TITLE>

</HEAD>

<BODY>

<FONT FACE="Verdana, Arial, Helvetica" SIZE=2>



Your request has been processed at

<%Response.Write time%>.<br><br>



<%

UserId = Request.Form("UserId")

Password = Request.Form("Password")

AccountId = Request.Form("AccountId")

'Connect to Siebel and retrieve the account information as an OLE DB Rowset.

Set Connection = Server.CreateObject("ADODB.Connection")

ConnectString = "Provider=SiebelOLEDB.Provider.1;User Id=" + UserId + ";Password=" + Password + ";Data Source=siebel://MyGateway/MyEnterprise/MyObjMgr/MyServer;"

Connection.Open ConnectString

If Len(AccountId) = 0 Then

Query = "Select * from Account"

Else

Query = "Select * from Account where Id = '" + AccountId + "'"

End If

Set RecordSet = Connection.Execute(Query)

If Not RecordSet.EOF Then

%>

This is your current account information.

<br><br>



<TABLE CELLPADDING=4>



<TR>

<%For Each Field in RecordSet.Fields%>

<TH><FONT SIZE=2><%Response.Write Field.Name%></FONT></TH>

<%Next%>

</TR>

<%Do While Not RecordSet.EOF%>

<TR>

<%For Each Field in RecordSet.Fields%>

<TD><FONT SIZE=2><%Response.Write Field.Value%></FONT></TD>

<%Next%>

</TR>

<%

RecordSet.MoveNext

Loop

%>

</TABLE>

<%

End If

' Clean up variables.

RecordSet.Close

Connection.Close

Set RecordSet = nothing

Set Connection = nothing

%>

</FONT>

</BODY>

</HTML>

The form created by the HTML page prompts the user for input, as shown in Figure 2.

Figure 2. Sample HTML User Input Page

The output is shown in Figure 3.

Figure 3. Sample HTML Output

Transports and Interfaces: Siebel eBusiness Application Integration Volume III