| Bookshelf Home | Contents | Index | Search | PDF | |
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>
<!-- Display the time the request was processed -->
Your request has been processed at
<%Response.Write time%>.<br><br>
<!-- Get the form input data -->
<%
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>
<!-- Build a table to display the data -->
<TABLE CELLPADDING=4>
<!-- BEGIN column header row -->
<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.
The output is shown in Figure 3.
| Bookshelf Home | Contents | Index | Search | PDF | |
Transports and Interfaces: Siebel eBusiness Application Integration Volume III Published: 23 June 2003 |
