Kbase 17525: How to use multiple database connections in Actuate
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
How to use multiple database connections in Actuate
This an example of how to construct a report using two
connections to two different databases containing similar
data. In this case we will be connecting to the sports
database distributed with Progress and the sfdata database
distributed with Actuate.
Our report will concatenate the customer's ids and names
from the Customer table in sports with the customers table
in sfdata.
We will use an ODBC connection to connect to sfdata so you
may need to create a DSN for this example to work.
Steps:
1. Open Actuate. Select File - New. From the resulting
dialog select Blank Report Design. Name the Title, File
Name, and Report Root Name TwoConnections. ( Don't
overwrite the path or file extension on the File Name. )
2. Add a Page List component of type AcSimplePageList to
the root object. Add a Page to the Page List. Add a Flow
the Page.
3. Add a Report component to the root object. Add a Data
Filter to the Report Section. Select AcMultipleInputFilter
as the type of Data Filter. Add a Query to the Multiple
Input Filter. Add another Query to the Multiple Input
Filter. Add a Connection to SqlQuerySource. Select
AcProgressConnection for the type. Right click the
Progress Connection. Select Properties. Enter the startup
parameters for the sports database.
4. Click on SqlQuerySource. Open the Query Editor by
clicking the SQL button. The sports database has no
security so you can just click OK to go to the Query
Editor. Expand the sports database in the DB Browser by
clicking the plus ( + ) sign next to it. Drag the Customer
table over to the Query Editor. Drag the Cust-Num and Name
fields on to the Columns tab. Close the Query Editor by
selecting File - Close.
5. Add another Connection to the other Query
( SqlQuerySource1 ). Select AcODBCConnection for the type.
Right click the ODBC Connection. Select Properties. Enter
sfdata for the DataSource. Click on SqlQuerySource1. Open
the Query Editor by clicking the SQL button. The sfdata
database has no security so you can just click OK to log
on. Expand the sfdata database in the DB Browser. Drag the
customers table over to the Query Editor. Drag the custID
and customName fields down to the Columns tab. Close the
Query Editor by selecting File - Close.
6. Add a Data Row to the Multiple Input Filter. Right
click it. Select Properties. Click the Variables tab.
Click New. Enter CustNum for the Name. Change the Type to
Integer. Click OK. Click New to add another variable.
Enter CustName for the Name. Change the Type to String.
Click OK. Close the Component Editor.
At this point the Structure Pane in the Design Editor
should look something like this:
- TwoConnections
I
I - Content - ReportSection
I I
I I - DataStream - MultipleInputFilter
I I I
I I I - Input - SqlQuerySource
I I I I
I I I I---Connection - ProgressConnection
I I I I
I I I I---DataRow - Datarow
I I I
I I I - Input - SqlQuerySource1
I I I
I I I---Connection - ODBCConnection
I I I
I I I---DataRow - DataRow1
I I
I I---DataRow - DataRow2
I
I - PageList - SimplePageList
I
I - PageStyle - Page
I
I---Content - TopDownFlow
7. Right click on the Multiple Input Filter. Select
Properties. Click on the Methods Tab. Scroll down to
Function Start() As Boolean. Select it. Click Override.
Enter the following code:
Function Start( ) As Boolean
' Start the Multiple Input Filter
Start = Super::Start( )
If Not Start Then
Exit Function
End If
' Keep track of the input adapter from which to read.
Set UnionIter = InputAdapters.NewIterator()
End Function
8. Close the Method Editor. Select Sub Finish(). Click
Override. Enter the following code:
Sub Finish( )
' Delete the Iterator created earlier
Set UnionIter = Nothing
' Finish the Multiple Input Filter
Super::Finish( )
End Sub
9. Close the Method Editor. Select Function Fetch() As
AcDataRow. Click Override. Enter the following code:
Function Fetch( ) As AcDataRow
' Declare DataRow variables.
Dim aRow As TwoConnections::DataRow
Dim bRow As TwoConnections::DataRow1
Dim FRow As TwoConnections::DataRow2
' Declare Boolean flags
Dim bFlagA As Boolean
Dim bFlagB As Boolean
' Create a new DataRow to hold the result.
Set FRow = New TwoConnections::DataRow2
' Start with the first input adapter.
UnionIter.Restart()
' Assign the first input adapter to the current input.
Set CurrentInput = UnionIter.GetNext()
' Initialize the flags.
bFlagA = False
bFlagB = False
' Try to read the next row of input from the first input
' adapter.
Set aRow = CurrentInput.Fetch()
' If we have no aRow set the flag to false to mark the
' first input adapter as empty.
If aRow Is Nothing Then
bFlagA = True
' Otherwise copy the data into our temporary data row
Else
FRow.CustNum = aRow.Customer_CustX2DNum
FRow.CustName = aRow.Customer_Name
End If
' If the first input adapter is empty, try the next one.
If bFlagA = True Then
Set CurrentInput = UnionIter.GetNext()
' Try to read the next row of input from the second
' input adapter.
Set bRow = CurrentInput.Fetch()
' If we have no bRow set the flag to false to mark the
' first input adapter as empty.
If bRow Is Nothing Then
bFlagB = True
' Otherwise, copy the data into our temporary data row
Else
FRow.CustNum = bRow.customers_custID
FRow.CustName = bRow.customers_customName
End If
End If
' If both input adapters are empty then return nothing
' to signify no rows remaining.
If bFlagA And bFlagB Then
Set Fetch = Nothing
' Otherwise, return our temporary row.
Else
Set Fetch = FRow
End If
End Function
10. Close the method editor. Click on the variables tab.
Click New. For the Name enter UnionIter. For the Type
enter AcIterator. Click OK. Click New. For the Name enter
CurrentInput. For the type enter AcDataAdapter. Click OK.
Close the Component Editor.
11. Add a Frame to the Content of the Report Section. Add
an Integer control to the Frame. Set the ValueExp to
CustNum. Add a Text control to the Frame. Set the ValueExp
to CustName.
12. Run the report.
Progress Software Technical Support Note # 17525