Crashes when retrieving additional rows of data

[shearerg]

Hi,

I've been experimenting for a while using DAI against an Ingres database. In general everything is fine, but I have found one problem which I have not been able to overcome.

My problem is that when reports return more than 1000 data rows, attempting to retrieve the additional data crashes DAI. Following this I have been having to reboot my PC in order to get DAI working again. Despite error messages from DAI indicating a database problem, the database and ODBC connection to it continue to work fine with other applications.

I'm using a recent Ingres release (Community edition 9.3.0-151) and the latest DAI Personal. This looks like an incompatibility between DAI and the Ingres ODBC driver ... but I'm really hoping it can be overcome. If anyone else has experienced this problem, I'd appreciate your advice.

The Ingres driver is configured to use 'cursors'. If the 'select loop' option is used, no data is returned to DAI. I was led to believe there were some additional advanced driver configuration options within DAI, but I have not been able to find any.

Some images are attached to illustrate the failure process.
1 DAI - the initial successful data retrieval
2 DAI - what happens when the arrow is clicked to retrieve more data
3 DAI - what happens when you try to continue using DAI, although database is working fine ....

Any advice will be appreciated.

Regards,
Greg Shearer

[Carsten Sørensen]

Hi Greg,

Thanks for letting us know!

I think you are in the right direction with "cursors". We have had similar issues with Pervasive (as I remember it) in the past. What we were able to in that case was to set a different cursor on the Options Tab of the report (default is usually "dynamic" or "forward only") - maybe "Keyset" or "Static" would work with Ingres...

Is it always - a 1000 - records? or does this occur only when "scrolling" the data-set? I.e. Can you e.g. set the maximum records per page to 5000 and get that/the full details without problems?

cursoroptions.jpg (104.67 KiB) Viewed 9366 times

Best regards,
Carsten

[shearerg]

Carsten,

Thanks for the reply. And I must admit that it made me hopeful of a solution ... but unfortunately that doesn't seem to be the cause. I'm pretty sure the cursor setting you suggested is what I had been looking for but had been unable to find.

However, I've since tried all 4 possible options and none fix the problem, which is disappointing. I've also increased the row limit to 5000 with the same result, as it is a large potential data set. But the problem clearly isn't dependent on the number of rows initially returned.

So, I could work around the problem by restricting the range of the underlying query, although I had hoped to avoid this approach ... as it still leaves a potential problem for the future for which I don't have a solution.

I've also posted a question on the Ingres driver forum, so perhaps some other suggestions will be put forward there.

If you have any other ideas, please let me know.

Thanks again,
Greg

[Carsten Sørensen]

Hi Greg,

Well - it was at least something to try - I will double check that we don't overwrite the setting internally..

We'll try to set-up a sample Ingres DB and reproduce the issue!

It's summer-time! we should try to eliminate disappointments

We'll be back - when we know more!

Best regards,
Carsten

[Carsten Sørensen]

Hi Greg,

I have been able to re-produce the behavior - Ingress is apparently very sensitive to moving in the data-set - especially if Dynamic AI request a "move x number of records" and the data-set ends before that. I have never come across that we were not allowed to use this method: "recordset.move x" number of records - and it will "normally" move x or until the end. For Ingress it isn't allowed and the connection (that specific instance) will crash. If that happens I was able to get back again running IISRESET from the CMD prompt (didn't have to reboot) (assuming that you are on IIS).

I have found a work-around (to check for EOF each single record moved) but we will need some time to implement and fully test whether that will fix it. It looks like it will though...

Please send me an e-mail on cs@cintac.com - if you want me to inform you when we have implemented that methodology for Ingress in a new build.

Best regards,
Carsten

[shearerg]

Hi Carsten,

I have to say that I'm amazed that you've done so much work already to identify the cause of my problem! I'm very impressed!! I'll certainly send you an email, and in the mean time I'll work with more restricted data sets when I need to demonstrate DAI. Thanks for the tip on IISRESET. Can't say I'm in any way expert with IIS, and I hadn't yet gone looking for a reboot alternative.

Also, I'll post your responses on the Ingres forum in case someone recognises a problem which could be present in the driver. It would be great to have this issue resolved.

Thanks again for all your work

Greg

[shearerg]

Hi Carsten,

I have some more information on this fault, in case it indicates anything significant to you.

Today I've been trying to obtain an ODBC trace of the fault to post on the Ingres forum. With this I've been totally unsuccessful, despite even following advice from support.microsoft.com. I'm assuming this is because DAI uses ADO and OLEDB ... which doesn't work with ODBC Trace ... is that right? (I've since learnt that ODBC run through IIS doesn't work with ODBC Trace)

Anyway, as part of this work I have also tried smaller limits for 'Maximal number of records per page' and I've found that this does affect behaviour. For example, using settings of 50 and 100 appears to allow the report to behave correctly ... but only for a short time. In both instances, progressively stepping forward through the data set continues correctly until approximately 1000 records are read, at which time the report fails as already described.

Despite this, the problem is more subtle then simply a 1000 row buffer limitation or similar, as setting the limit to 5000 allows the initial data retrieval to go ahead without any problem. However, the report crashes as usual if further rows are requested.

Does this throw any more light on the problem? There is also an offer to investigate the problem on the Ingres forum, but without an ODBC trace I may not have much luck.

Regards,
Greg

[Bob Cergol]

Hello Greg,

I found this thread interesting even though I've not had any similar problem using MS-SQL Server. I had to set maximum records to greater than 1000 on one of my earliest reports to make it work the way I wanted -- but later questioned: Do users really need to have that many records "live" in one page. FWIW, I've found that defining more groupings with level breaks, combined with "open details in own report" makes the reports extremely fast, by limiting the results sets, even though all the details are still read for group totaling, and actually provides the users with an all-around better "drill-down" report experience. I'm wondering if the same wouldn't be true for your "5000+ details record" report -- at least as a convenient workaround until Carsten fine-tunes DAI for this idiosyncracy in Ingres. Your screen shot shows the plus symbol for expanding the grouping in the same page. Putting a level break there would open the expansion in a separate pane and you would not need to have all 5000 records fetched for one page.

Regards,
Bob

[shearerg]

Hi Bob, and thanks for the response.

I've also come to realise the same thing, although it was excellent to uncover the problem and have it corrected .... in order to avoid disappointment at some future time! Which reminds me to mention that the problem has been corrected, although the fix won't yet be in the standard download.

Although I've been fiddling with DAI on-and-off for a while now, I'm only just coming to understand many of the structural options for reports. And I agree that I should now be able to structure report components that generally require far less than 1000 rows. I'm looking forward to a new phase of more detailed development in order to sell DAI to some of our managers .... and just hoping that other work doesn't get in the way too much over the next few weeks.

Thanks again,
Greg