Recently a development team met some
difficulties in data source computation when developing iReport reports. After
the use of esProc for cross database computation, the problem is resolved.
This
is a project payment progress report, as part of a project management system.
It’s based on an Oracle database. The reports needs to present the project
name, payment amount, contract value, payment progress (in percentage), and
name of the project manager, etc., for all ongoing projects. Amount these the
first 3 items contributes to the difficulties met by the development team.
Note
that the project payment record is stored in accounting system, rather than the
Oracle database used by the development team. The accounting system is built on
MSSQL database. The contract value information is also stored in a separate
MSSQL database, used by contract management system. After analysis to the
database, the project information record has an one-to-many relationship with
payment record, and an one-to-one relationship with contract information.
By
simplifying the table and fields other than the hardest part of the problem,
the issue can then be described as: we need to join table project，payProcess and contract from three heterogeneous
databases.
It’s
fairly easy to write the SQL statements if the three tables are in the same
database. However they now belong to three heterogeneous databases, the join is
then difficult. Meanwhile, the two MSSQL databases are used by two independent commercial
software, which the development team can load data from, but not able to modify
or administrate. This adds to the difficulty of the development. Although iReport
support simple cross database computation with two tables, it’s very difficult
to do this with three tables. The development team can not leverage iReport to
meet this requirement.
The
use of new reporting tool that supports multiple data sources might help.
However as iReport is the only one used in the development process so far, a
sudden change is not realistic. iReport supports user-defined
JavaBean as data source, this can also be used to handle cross database
computation. But JAVA is not a language for computation. The “join” computation
between multiple sets requires a lot of coding. The workload makes it a
non-sense. ETL tool is also a possible way to consolidate tables from multiple
databases into one. The ETL approach normally requires incremental update to
databases, which means timestamp and triggers need to be used in payProcess and contract
table. As we know, commercial software does not allow databases to be modified
in this way. ETL cannot provide the real time updates required for payment
progress monitoring, which means it’s not suitable for this report.
Under such
condition, the use of esProc for cross database computation
is a handy approach. Here are the codes：
A1,
A2, A3 are for loading data from different databases, which are native syntax
for each database. Note the difference in SQL for each heterogeneous database.
A4
is an association statement. From here on the syntax has nothing to do with certain
database. The “join” function associate A1, A2, A3 according to projectID. Note
that this is inner join. If it’s left join, the syntax of “join@1” should be
used, whereas for full join, the syntax is “join@f”.
A5
is to output the required fields from the join, which could be part or all
fields from A1, A2 and A3.
The
statement of result A5 means to output the computation result by JDBC, so that iReport
could rerieve them directly through JDBC. It’s also possible for esProc to be
used in JAVA codes directly by means of JDBC call.
The
above codes are just a prototype, which can actually run successfully. In
reality more table association and data fields need to be added, plus some and
parameter filters.
As we could see, the solving of cross database computing issue with esProc is fairly easy. esProc supports JAVA well, and is handy to be used with iReport.
没有评论:
发表评论