I understand that ViP/DX can facilitate the generation of report by pulling data from Notes database and an ODBC compliant database. I like to know if anyone has done it, particularly for Notes and DB2 databases.

Hello Jackson,

How do I combine data from two different data sources into one Data Object for Reporting ?

Connect each data object to the appropriate data source (Notes and ODBC. Now create an unconnected data object (Data3) and add local columns with columns (and data types) to match the columns in your two connected data objects (primary key from data1 needs to map to foreign key in data2). Please review the example…

In the following example, the AppWindow Init script executes queries for Data1 and Data2, and then combines the data in Data3. You can then use Data3 as the source for your report.

Dim row&, nrow&, status%, error&, col1$, col2$, col3$

' Run queries with the connected data objects.

Call Data1.ExecuteQuery()

Call Data2.ExecuteQuery()

' Put data from Data1 into Data3 (you can change the number of columns).

For row&=1 To Data1.ResultRowCount

status%=Data1.RowGet(row&, error&, col1$, col2$, col3$)

nrow&=Data3.RowNew()

status%=Data3.CellSetData(nrow&, 1, col1$)

status%=Data3.CellSetData(nrow&, 2, col2$)

status%=Data3.CellSetData(nrow&, 3, col3$)

status%=Data3.ExecuteUpdate()

Next row&

' Put data from Data2 into Data3 (you can change the number of columns).

For row&=1 To Data2.ResultRowCount

status%=Data2.RowGet(row&, error&, col1$, col2$, col3$)

nrow&=Data3.RowNew()

status%=Data3.CellSetData(nrow&, 1, col1$)

status%=Data3.CellSetData(nrow&, 2, col2$)

status%=Data3.CellSetData(nrow&, 3, col3$)

status%=Data3.ExecuteUpdate()

Next row&

In other cases, the connected data objects contain related information that you want to combine by using key fields to perform joins. For information about joining data from two data sources in an unconnected data object, see Chapter 13, "Data Integration," in the Revelation ViP 2.0 Application Developer's Guide.

Hope this helps,

VIPDX Tech Support

Thanks Stephen. But will it work with DB2/400 (on the IBM AS/400 platform)? I heard that the support for ODBC compliant Databases with ViP/DX is officially limited to databases on PC. Do you know if anyone has managed to pull data out of AS/400's DB2/400?

Jackson,

Your information is incorrect in the form you stated it. ViP will run on any PC machine with Windows/NT OS (but not on any other platform). Access to databases (except Lotus Notes)is by the standard Windows ODBC route . If the PC has an ODBC driver for IBM DB2 installed on it, then ViP will also be able to access the same DataSource (presumably through a LAN or WAN connection.)

So the real question is, can you access the DB2 database through ODBC from your PC? If not, then ViP cannot.

I have not personally used ODBC to DB2, but some other customers have.  My memory is that the PC client must have additional IBM library files installed in order to use the ODBC driver (as does Oracle, for instance.)

Hope this answers your question.

John

View this thread on the forum...