Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » IRunTask run output to a database
IRunTask run output to a database [message #761538] Tue, 06 December 2011 16:07 Go to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Folks,

I'm trying to work out the best way to store the output of an IRunTask (the reportdocument) to a database and I'm a little confused...

IRunTask supports a String parameter which saves to a file named in that String, or it Supports an IDocArchiveWriter - the documentation for which is not clear - it states, for example, that this "Writer" is for "reading"...

Is there somewhere I can go to find information on IDocArchiveWriter (I've tried the Integrating and Extending book, this forum and the BIRT documentation to no avail) or does anyone have any other pointers that would, for example, enable me to send the output of the RunTask to a stream? (I'm considering changing the BIRT code at the moment!)

I could use a temporary file, but it seems clunky to save the file, only to read it again...

Any ideas would be gratefully received!

Pete

Re: IRunTask run output to a database [message #761587 is a reply to message #761538] Tue, 06 December 2011 17:35 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Pete,

I do not have an example of this, but there are two interfaces for
writting/reading a rptdocument. IDocArchiveWriter, and
IDocArchiveReader. If you write a specialized writer most of the time
you will need to specify a specialized reader. In your case I assume
you will use the input stream to open the report document once it is
generated? BIRT supplies FileArchiveWriter(/Reader) and a
FolderArchiveWriter(Reader) classes that you could use as examples to
add your code to write to the db. I think this would be very interesting
to do. If you need some help with it let me know.

Jason

On 12/6/2011 11:07 AM, Peter Cliff wrote:
> Folks,
>
> I'm trying to work out the best way to store the output of an IRunTask
> (the reportdocument) to a database and I'm a little confused...
>
> IRunTask supports a String parameter which saves to a file named in that
> String, or it Supports an IDocArchiveWriter - the documentation for
> which is not clear - it states, for example, that this "Writer" is for
> "reading"...
>
> Is there somewhere I can go to find information on IDocArchiveWriter
> (I've tried the Integrating and Extending book, this forum and the BIRT
> documentation to no avail) or does anyone have any other pointers that
> would, for example, enable me to send the output of the RunTask to a
> stream? (I'm considering changing the BIRT code at the moment!)
>
> I could use a temporary file, but it seems clunky to save the file, only
> to read it again...
>
> Any ideas would be gratefully received!
>
> Pete
>
>
Re: IRunTask run output to a database [message #761897 is a reply to message #761587] Wed, 07 December 2011 08:54 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Jason,

Thanks for the pointers - very useful! I've started looking into writing a custom Writer/Reader and will no doubt have questions so watch this space!

Thanks again!

Pete
Re: IRunTask run output to a database [message #765207 is a reply to message #761897] Tue, 13 December 2011 16:11 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
I think I've made some progress... I've got a IDocArchiveWriter that outputs the individual streams to the database (via JDBC & Blob).

Now I'm trying to verify if the data written is correct and have started putting together an IDocArchiveReader. I can get so far with this, but it keeps falling over with an uncaught EOFException when reading the /core stream. Looking in the database, /core is indeed empty.

So my question is this - should I be seeing anything in the /core stream (hopefully that makes some sense!) and if not why does everything stall when it cannot find anything there?

Pete
Re: IRunTask run output to a database [message #765246 is a reply to message #765207] Tue, 13 December 2011 17:30 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Peter

Is anything being written? Do you get the /design stream, etc


Jason

On 12/13/2011 11:11 AM, Peter Cliff wrote:
> I think I've made some progress... I've got a IDocArchiveWriter that
> outputs the individual streams to the database (via JDBC & Blob).
>
> Now I'm trying to verify if the data written is correct and have started
> putting together an IDocArchiveReader. I can get so far with this, but
> it keeps falling over with an uncaught EOFException when reading the
> /core stream. Looking in the database, /core is indeed empty.
>
> So my question is this - should I be seeing anything in the /core stream
> (hopefully that makes some sense!) and if not why does everything stall
> when it cannot find anything there?
>
> Pete
Re: IRunTask run output to a database [message #765521 is a reply to message #765246] Wed, 14 December 2011 08:42 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Hello!

Yes, I see the design, but looks like none of the others. I suspect I've got something wrong somewhere with the database writes. There are a few transaction/locking issues that I suspect will be the problem here.

Pete
Re: IRunTask run output to a database [message #765785 is a reply to message #765521] Wed, 14 December 2011 17:19 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
I think I've hit a wall. I'm going to describe that wall here in the hope it either clarifies things for me or strikes a chord with someone and they say "actually, you can do it like this!"...

BIRT (using IDocArchiveWriter) seems to want (if my interpretation of the relevant interfaces/abstract classes is correct) to be able to open and non-sequencially write to multiple output streams at once. (In practice I'm not sure it does use the random access of the API, but it is there all the same). When writing blobs to Oracle you have to get the stream, write the data and then commit. I do not think it is possible to (safely) open another stream with the same connection when one is open.

I have implemented an IDocArchiveWriter, along with an associated RAOutputStream, where the Oracle connection belongs to the OutputStream and maintains the stream, etc. and the commit is called when IDocArchiveWriter is closed using a similar mechanism to that found in FileArchiveWriter. This works, but it requires a number of open connections to the database which is fairly expensive. Further, BIRT appears to always be calling write(int b), and I'm keeping it simple in this experiment I've not yet implemented buffering. As you'd expect this gets pretty slow writing a byte at a time to the database... (Though the data there now looks correct at least!)

So that leads to buffering and here I looked at the RAStreamBuffer classes which use buffer to memory and then to a RandomAccessFile and here I get confused trying work out how I might implement RandomAccessFile-like functionality to Oracle BLOBs.

Which brings us full circle. Perhaps the most straightforward way of doing this is to simply use either FileArchiveWriter or FolderArchiveWriter, "buffer" the file to disk, and when complete write the BIRT streams (as BLOBs) to Oracle sequencially. This feels wrong - I'd prefer to be able to stream direct to the database - but I'm not sure BIRT's expectations are compatible with Oracle's and being the man in the middle is proving tricky!

I could, possibly, create a IDocArchiveReader to make direct reads from the database possible rather than reverse the process on reading (extract BLOBs to disk and use FolderArchiveReader).

There you have it! Thoughts, consolation and ideas most welcome! Smile

[Updated on: Wed, 14 December 2011 17:26]

Report message to a moderator

Re: IRunTask run output to a database [message #765816 is a reply to message #765785] Wed, 14 December 2011 18:05 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Peter,

I know creating the file and then writing to Oracle feels wrong, but it
may end up being faster. If you write the file to a tmp, then commit it
to Oracle and then delete it. Any chance you can open a bugzilla entry
for this, because this has been asked for many times. I want to have a
look at it using MySQL and see if I get the same results. Any chance
you can share what you have so far?

Jason

On 12/14/2011 12:20 PM, Peter Cliff wrote:
> I think I've hit a wall. I'm going to describe that wall here in the
> hope it either clarifies things for me or strikes a chord with someone
> and they say "actually, you can do it like this!"... :)
>
> BIRT (using IDocArchiveWriter) seems to want to be able to open and
> non-sequencially write to multiple output streams at once. (In practice
> I'm not sure it does use the random access of the API, but it is there
> all the same). When writing blobs to Oracle you have to get the stream,
> write the data and then commit. I do not think it is possible to open
> another stream with the same connection when one is open.
>
> I have implemented an IDocArchiveWriter, along with an associated
> RAOutputStream, where the Oracle connection belongs to the OutputStream
> and maintains the stream, etc. and the commit is called when
> IDocArchiveWriter is closed using a similar mechanism to that found in
> FileArchiveWriter. This works, but it requires a number of open
> connections to the database which is fairly expensive. Further, BIRT
> appears to always be calling write(int b), and I'm keeping it simple in
> this experiment I've not yet implemented buffering. As you'd expect this
> gets pretty slow as writing a byte at a time to the database... :)
>
> So that leads to buffering and here I looked at the RAStreamBuffer
> classes which use buffer to memory and then to a RandomAccessFile and
> here I get confused trying work out how I might implement
> RandomAccessFile-like functionality to Oracle BLOBs.
> Which brings us full circle. Perhaps the most straightforward way of
> doing this is to simply use either FileArchiveWriter or
> FolderArchiveWriter, "buffer" the file to disk, and when complete write
> the BIRT streams (as BLOBs) to Oracle sequencially. This feels wrong -
> I'd prefer to be able to stream direct to the database - but I'm not
> sure BIRT's expectations are compatible with Oracle's and being the man
> in the middle is proving tricky! :)
> I could, possibly, create a IDocArchiveReader to make direct reads from
> the database possible rather than reverse the process on reading
> (extract BLOBs to disk and use FolderArchiveReader).
>
> There you have it! Thoughts, consolation and ideas most welcome! :)
>
Re: IRunTask run output to a database [message #766219 is a reply to message #765816] Thu, 15 December 2011 11:48 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Jason,

Thanks for sticking with this!

I'm veering towards creating the file and then commiting to the database... Happy to open a bugzilla about this. I guess ideally it'd be possbile to pass an OutputStream to IRunTask and the complexity of the multiple streams, etc. is handled internally... Still, that perhaps runs counter to the Docuement specification desire to maintain flexibility.

That said, I'm persevering and found that wrapping the Blob's OutputStream in a BufferedOutputStream makes speeds seem pretty reasonable and the database is populated with what looks to be good data.

Now I'm very confused however, because when I use FolderArchiveWriter I get some 69 files across 9 folders for this (simple) test report:

$ tree
.
|-- Data
| `-- hierarchy
|-- DataEngine
| |-- NamingRelation
| |-- VesionInfo
| `-- queryStartingID
|-- QuRs0
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- QuRs1
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- QuRs2
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- QuRs3
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- QuRs4
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- QuRs5
| |-- DataSetData
| |-- DataSetLens
| |-- ExprMetaInfo
| |-- ExprRowLen
| |-- ExprValue
| |-- GroupInfo
| |-- OriginalQueryDefn
| |-- QueryDefn
| `-- ResultClass
|-- bookmark
|-- content
| |-- content.dat
| |-- page.dat
| `-- page.idx
|-- core
|-- design
|-- design.ir
|-- pages
|-- pages_index
|-- reportletBookmarks
`-- toc

9 directories, 69 files

In the database, using my own DatabaseArchiveWriter I only get 14 streams:

/content/content.dat
/content/page.dat
/content/page.idx
/DataEngine/VesionInfo
/pages
/pages_index
/core
/design
/Data/hierarchy
/design.ir
/dataEngine/queryStartingID
/reportletBookmarks
/bookmark
/toc

(Though I'm sure I've seen all of those QuR ones previously).

Checking what gets called it looks like only these 14 are being requested for creation during the Run task.

I suspect I'm doing something stupid but feel like I'm starting to not see the wood for the trees! Smile

I'll ask about sharing the code.

[Updated on: Thu, 15 December 2011 13:34]

Report message to a moderator

Re: IRunTask run output to a database [message #766278 is a reply to message #766219] Thu, 15 December 2011 13:48 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
As requested, filed Enhancement Request here:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=366806
Re: IRunTask run output to a database [message #766348 is a reply to message #766278] Thu, 15 December 2011 15:33 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Peter,

I would like to see this work. Any chance you can share your code so
far through bugzilla or email? I would like to try it with MySQL.

Jason

On 12/15/2011 8:48 AM, Peter Cliff wrote:
> As requested, filed Enhancement Request here:
>
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=366806
Re: IRunTask run output to a database [message #766744 is a reply to message #766348] Fri, 16 December 2011 10:49 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Hi Jason,

I've asked if it is OK to share the code. I'm in a new job and I'm unsure of the policies here.

I'll let you know!

Pete
Re: IRunTask run output to a database [message #766903 is a reply to message #766744] Fri, 16 December 2011 16:10 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Sounds good. If you can maybe you and I can do a skype session to look
it over.

On 12/16/2011 5:49 AM, Peter Cliff wrote:
> Hi Jason,
>
> I've asked if it is OK to share the code. I'm in a new job and I'm
> unsure of the policies here.
>
> I'll let you know!
>
> Pete
Re: IRunTask run output to a database [message #767539 is a reply to message #766903] Sun, 18 December 2011 08:58 Go to previous messageGo to next message
przemyslaw.bech is currently offline przemyslaw.bechFriend
Messages: 3
Registered: December 2011
Junior Member
Hi All,

I was thinking aboute moving from jasper to birt but after seeing this topic I dont think I'll use birt. Such a simple thing, as beeing able to provide rptdocument as a stream, is not available here...

In every application we ware building we always store xml reports in db and load them when needed to run the report and print paginated results. With BIRT this basic scenerio seems unavailable.

I dont see any possibility to take rptdesign, xml file, as a stream, than create rptdocument object without saving it to file and than use it to generate paginated report. So I dont wont to store rptdocument neither in file nor in db, just as an intermediate object in seesion. Even if I generate rptdocument with designer I would like to be able to upload it to the application server and run it but I suppose its impossible without saving this file to a directory on server.

If I'm wrong please show short code sample.

[Updated on: Sun, 18 December 2011 09:28]

Report message to a moderator

Re: IRunTask run output to a database [message #768108 is a reply to message #767539] Mon, 19 December 2011 15:36 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
przemyslaw.bech, I'm not sure I'm clear what it is you are trying to do, but it sounds like it would be possible and there is some confusion over "rptdocument".

In this thread, when I talk about a "rptdocument" I'm refering to the intermediary data file that is the result of compilation of a report design and the data. This "rptdocument" is then rendered to whatever format you want and the resultant file streamed or saved or thrown away as you please. I think you are confusing the output document with "rptdocument" (and who could blame you? The terminology is confusing!)

For what you are talking about, I see no reason why you could not use BIRT.

You could wrap your XML (sourced from the database) with Java objects that form the data source for the BIRT report and then when you run & render the report you can stream it directly to where ever you want.

This is the crux of my issue - a rendered report can be streamed, a "rptdocument" (pre-render) cannot.

So, assuming you have a fancy report design with XML hooked in somewhere a servlet that did something like this would do:


EngineConfig config = new EngineConfig();
// ... set some config options ...

ReportEngineFactory ref = new ReportEngineFactory();
IReportEngine engine = ref.createReportEngine( config );

// read the report design - could be from a db or where ever...
IReportRunnable design = engine.openReportDesign( designStream );

// set up render options - here a HTML output
IRenderOption opt = new HTMLRenderOption();
opt.setOutputStream( outputStream ); // any output stream - to the browser for ex.

IRunAndRenderTask task = engine.createRunAndRenderTask( design );
task.setRenderOption( opt );

task.run();


So don't give up on BIRT because I'm pretty sure it can do what you're after!

[Updated on: Mon, 19 December 2011 15:55]

Report message to a moderator

Re: IRunTask run output to a database [message #778171 is a reply to message #766348] Wed, 11 January 2012 14:23 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Hi Jason,

Happy New Year!

As requested, I'm attaching the IDocumentArchiveWriter to an Oracle database I've been working on. Hopefully this'll be helpful for you!

I've been using the following table for the tests:

CREATE TABLE "STREAMS" (
  "ID" NUMBER NOT NULL,
  "DOCID" NUMBER,
  "PATH" VARCHAR2(1024),
  "DATA" BLOB
)

CREATE SEQUENCE "STREAM_ID_SEQ" START WITH 1 INCREMENT BY 1 


I've also attached the test design I've been using.

This version of the code runs slowly (I think because it doesn't buffer writes) and there are a couple of exceptions being thrown.

Some of the data going into the database looks fine - the design is there for example, but as mentioned in a previous post, a large number of the document streams appear to be missing...

Let me know how your own experiments with it go! Should be simple enough to migrate to MySQL as it uses JDBC but you'll probably need to change the sequence bit - MySQL has auto-increment I think?

[Updated on: Wed, 11 January 2012 14:24]

Report message to a moderator

Re: IRunTask run output to a database [message #778322 is a reply to message #778171] Thu, 12 January 2012 11:39 Go to previous messageGo to next message
sam va is currently offline sam vaFriend
Messages: 121
Registered: July 2011
Senior Member
Hi Peter,

We had a similar requirement earlier about 4 months ago.
--Generate an rptdocument
--Store in the DB (Oracle)
--Download when the user tried to view the report
Somehow we decided at that time not to tread the path you have been. Right now we create the rptdocument in a temporary file system, upload to oracle DB and then delete the temporary file. It does work like a charm, but definitely love to look at your code working.

Thanks
Re: IRunTask run output to a database [message #778334 is a reply to message #778322] Thu, 12 January 2012 12:30 Go to previous messageGo to next message
Peter Cliff is currently offline Peter CliffFriend
Messages: 14
Registered: December 2011
Location: South West England
Junior Member
Hi Sam,

That is pretty much what we're doing now - create a temp file then persist it to the databse. When needed, we retrieve it from the database into a temp file and render as required.

I'm not surprised you took that route. It definitely gets the job done and seems most straight forward. This works so I suspect we'll keep working that way, but I'll keep an eye on this thread to see if anything changes - I'd like to see my code working too and may well revisit it! Smile

Pete

[Updated on: Thu, 12 January 2012 12:31]

Report message to a moderator

Re: IRunTask run output to a database [message #778406 is a reply to message #778171] Wed, 11 January 2012 18:35 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Thanks Peter

On 1/11/2012 9:23 AM, Peter Cliff wrote:
> Hi Jason,
>
> Happy New Year!
>
> As requested, I'm attaching the IDocumentArchiveWriter to an Oracle database I've been working on. Hopefully this'll be helpful for you!
>
> I've been using the following table for the tests:
>
> CREATE TABLE "STREAMS" (
> "ID" NUMBER NOT NULL,
> "DOCID" NUMBER,
> "PATH" VARCHAR(1024),
> "DATA" BLOB
> )
>
> CREATE SEQUENCE "STREAM_ID_SEQ" START WITH 1 INCREMENT BY 1
>
> I've also attached the test design I've been using.
>
> This version of the code runs slowly (I think because it doesn't buffer writes) and there are a couple of exceptions being thrown.
>
> Some of the data going into the database looks fine - the design is there for example, but as mentioned in a previous post, a large number of the document streams appear to be missing...
>
> Let me know how your own experiments with it go! Should be simple enough to migrate to MySQL as it uses JDBC but you'll probably need to change the sequence bit - MySQL has auto-increment I think?
>
>
Re: IRunTask run output to a database [message #884995 is a reply to message #778334] Tue, 12 June 2012 08:39 Go to previous messageGo to next message
Jan Kohnert is currently offline Jan KohnertFriend
Messages: 192
Registered: July 2009
Senior Member
Hello,
I've written a MemoryFolder that runs without files. It could probably easily be adjusted to run against a DB. But this step however would need a DB design that respects the stream concept the folder archive architecture.
http://www.eclipse.org/forums/index.php/mv/msg/351212/884990/#msg_884990
Re: IRunTask run output to a database [message #909195 is a reply to message #761538] Thu, 06 September 2012 18:05 Go to previous message
Madhav Suram is currently offline Madhav SuramFriend
Messages: 10
Registered: August 2011
Junior Member
Hi Peter, Sam,

We have a requirement to save the generated reports into the DB and it was a simple task for PDF, PPT and XLS formats. For HTML it has the complexities of the images not present in the same file as the content. Hence only for HTML, we plan to save the .rptdocument created using IRunTask and then at the time of "download" plan to run this through IRenderTask just like how you mentioned in your earlier post.

Following your experiences, I have written the output to a temporary physical file and then saving that into the DB. And in the return too, fetch the blob data from DB and write into a temporary physical file which gets passed onto IRenderTask. When I try to save the .rptdocument into the DB, the way I'm doing it, doesn't seem to be saving it right into the DB. Is it possible for you to share the code snippet on how you read the file and save into DB and fetch and write into a temporary file?
(http://www.eclipse.org/forums/index.php/t/371194/)

Thanks & Regards,
Madhav
Previous Topic:Data cubes and performance issues
Next Topic:BIRT 4.2 - New & Notable missing?
Goto Forum:
  


Current Time: Wed Dec 17 21:35:52 GMT 2014

Powered by FUDForum. Page generated in 0.02283 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software