Blank report due to large text field

[sdan]

Hi,

I get a blank report (grey page) with SRB. I experimented with different queries/tables, adding and removing fields. It seems that the problem appears when I include in the report one field that contains large amount of text. I even tried a report with this field alone, linked directly to the table where it is stored and I get the same result. The report generating function works fine with the other fields. As you can imagine, this is the field that I need most. Is there any workaround for this?

I am using NeoOffice 3.1.2 Patch 1, Sun Report Builder 1.1.0 and my system is Mac OS X 10.6.4.

Thanks!

p.s. Ok, after posting the above, I did find a way to extract the data by dragging the query into an empty text file. The result is not pretty, but it is better than nothing. It would be still nice to know if there is a way to use the reporting feature for large text fields.

[eremmel]

It might be helpful to post a example base document that shows the issue. Remove sensitive information first.

[sdan]

Eremmel, thanks for replying!

I tried to make an example base document with the same (very simple) structure of the original document.

It had a couple of large text records (about 44 000 chars was the largest). The report took time to generate, but eventually it was generated ok.

I tried to see at what char limit I get the blank grey page. The more I added text to the record, the slower the report generation became, but I never got to the blank grey page state.

Eventually, as I was adding text to the record, on form close I was prompted twice whether I want to save changes. I pressed yes at both prompts. Then I found out that the record content was erased. I tried with a different record and eventually it got erased too. Another thing I found out since I last posted was that Base slows down a lot when I browse long text records through a form. It can take up to 3 minutes just for a single record to appear.

Anyway, I guess the grey page problem has to do not only with the size of individual records, but also with their number. I seem to have a lot of large text records and this crashes the report generation.

I guess the conclusion here is that the users should keep their text records slim (say, 5 000 chars max). And for any of those who have a database, migrated from MS Access, with large text records, then dragging the query into an empty writer document could be a (somewhat messy and for personal use only) solution.

[r4zoli]

The example document not usable without data stored in external file database.

[eremmel]

The internal database HSQLDB has it's limitations as you discovered. I notice that you are on a mac, so you can not keep your msaccess database. You might consider an alternative, use an external database like MySQL (available for Mac?). It will give you more stability: the OOo Base program has only to contain the form and document, but the database will be in an other program.

[sdan]

The example document not usable without data stored in external file database.

Yes, I tried to upload the .data file too, but the upload was timed out. Oddly, even though in the example database I only kept a few records (2 in one of the tables and a dozen in the other), the .data file has exactly the same size as the .data file in the original database. How come?

By the way, r4zoli, let me use this opportunity to thank you for various posts of yours that have helped me with base. Respect!

The internal database HSQLDB has it's limitations as you discovered. I notice that you are on a mac, so you can not keep your msaccess database. You might consider an alternative, use an external database like MySQL (available for Mac?). It will give you more stability: the OOo Base program has only to contain the form and document, but the database will be in an other program.

Thanks for the suggestion. I started with the embedded database mode and it crashed within a couple of weeks, which was rather dramatic. Now, my data is stored externally and I haven't lost any data yet (well, apart from those records when I was experimenting with in the example database). I might look for an alternative of HSQLDB in the future, but for the time being I have to finish the project for which I am using the database. By dragging the query into an empty writer file I have the basic functionality that I need, so I am OK for now.

But if any of you guys that are developing base are interested in solving this issue (report generator crashing due to many and large text fields), then I am willing to help. Just tell me how - I am a simple user with hardly any programming skills.

[r4zoli]

Yes, I tried to upload the .data file too, but the upload was timed out. Oddly, even though in the example database I only kept a few records (2 in one of the tables and a dozen in the other), the .data file has exactly the same size as the .data file in the original database. How come?

When you delete content, the backup data is remaining in the data file.
Use SHUTDOWN COMPACT in Tools>SQL... or CHECKPOINT.

[sdan]

Cool, this worked.

Now the file is small, but when I tried to upload it, I got the message "The extension data is not allowed". I changed it to .txt, but again this was not allowed. So to trick the system, I changed the extension to odt. In order to use it, you have to rename it back to 'bibliography.data', I guess.

Do you need the .properties and .script files?

Let me remind you that with the few entries in the example database, there is no problem with the report. I have the problem in the original database, where there are many large text records, but the data file is too large to upload.

Let me also say that the workaround to drag the query into an empty writer file suits my needs for now. So I guess the issue is worth pursuing only if you think that this could improve base or help others.

[r4zoli]

Do you need the .properties and .script files?

Yes.

So I guess the issue is worth pursuing only if you think that this could improve base or help others.

My goal is to improve report builder.

[sdan]

Great! Here you are:

[r4zoli]

Now I can test it, and I have on win7 the text in report.

When you finished report, change to edit mode, click on edit icon left from pdf export.
Select text cell, and apply optimal row hight, then all text will be shown, for me it is the 5 page report.

[sdan]

When you finished report, change to edit mode, click on edit icon left from pdf export.
Select text cell, and apply optimal row hight, then all text will be shown, for me it is the 5 page report.

Yes, I know the edit/optimal row height trick from another thread and I use it for another report which has large (but not huge) text records, but this is not the issue here.

The problem I have in the original (large) database is not that I cannot see the whole field in the report. The problem is that the report never gets generated. My guess is that this happens because there are many large text records, which crash the report generator. Writer gets stuck at the grey page screen and no matter how long I wait, the report does not appear.

This does not happen in the example database, because it only has very few records.

Is there any way I can send you the original database? The size of the datafile is 66 MB.

[eremmel]

You might also give a receipt how to blow up the small database to a size that shows the issue that you experience.
E.g. how many times do we need to duplicate the records from the database to get a large enough database...

You also stated that the report never gets generated. Do you mean that it never finishes and you have to 'kill' it. What do you observe when you have a look at process monitor and observer CPU? I noticed in the past a few times that SRB/SRO was consuming lots of CPU because the memory was not well tuned (Tools -> Options... -> OpenOffice.org -> Java, select the default java version) and add parameter like -Xmx512m to give java more memory.

[sdan]

how many times do we need to duplicate the records from the database to get a large enough database...

The large text field is 'Notes' in the 'Notes' table (max 100,000 characters). The 'Notes' table has 13 records in the example database and 1516 in the full database. The problem with the report 'Notes by topic' appeared first when the query 'Notes by topic' returned 117 records.

You might also give a receipt how to blow up the small database to a size that shows the issue that you experience.

First, you need a really large record. In order to do that open the form 'Notes'. Click on the 'Notes' field of the first record. Select and copy all text. Then paste it below in the same field 14 times. Its size is now 61824 characters.

(When I pasted it the 15th time, closing the form I was prompted twice to save changes and then I lost the content of the Notes field in this record.)

Then you have to duplicate this record, say 100 times? I have no idea how to do it, but here is a link:

http://www.oooforum.org/forum/viewtopic.phtml?t=83607

In order to generate the report, you should also change the condition on 'Topic' in the 'Notes by topic' query to match the topic of the large field that you duplicated. If you duplicated the first record, then the topic is 'Methodology & Philosophy'.

You also stated that the report never gets generated. Do you mean that it never finishes and you have to 'kill' it.

Yes, it never finishes. But it is not in the state of not responding either - I do not have to use force quit to close it. The writer window simply remains grey forever (well, couple of hours is the most I have waited.)

What do you observe when you have a look at process monitor and observer CPU?

Not much. The CPU remains calm.

I tried to upload a couple of screen shots, but their size (compressed) is more than 128 kb, so this failed.

The processor is 2GHz Intel Core 2 Duo, so I see two CPU graphs. For a second, one of them gets almost to 100%, but then it goes back to normal. About half of the system memory remains free or inactive.