TestConnectivity or VerifyDatabase for testing staled DB connection?

[Former Member]

Hi,

We have been using the Crystal Report 2008 .NET APIs in our C++ application (with VS 2005) for a few years. It has the following workflow:

when repport request received:

create a new ReportDocument,

call ReportDocument->Load to load the report

Get user db connection information and put in new TableLogOnInfo

For each table in the report and each table of each subreport

call Table->ApplyLogOnInfo with TableLogOnInfo

call Table->TestConnectivity

setup paramaters ..

call either ReportDocument->ExportToDisk or PrintToPrinter

ReportDocument->Close

The above approach has been working fine until one of our customer created a huge report (over 2 MB) with a lot of subreport and tables. The steps of Loading the report and connecting to the DB took a few minutes. So, instead of of closing the report each time and creating a new ReportDocument object, the object is now retained and remained open to avoid the report reloading and db connection steps. The new approach also works fine, except when there is a network interruption between our application and the DB server. The report cannot be printed/exported anymore because the DB connection is staled.

I tried to check the DB connection by calling VerifyDatabase before the actaul print/export operation, no exceptions were thrown even when DB connection was broken. I also tried using TestConnectivity with a table of the ReportDocument, it also did not complain about the DB connection after it was broken. But the subsequent ExportToDisk call would fail.

Are there any methods to detect staled DB connections (I was not able to find appropriate ones from the API referernce, crsdk_net_apiRef_12_en.chm)?

Thanks

Kin H Chan

[Adam_Stone · Accepted solution]

Can't say I have ever seen anyone ask for this type of functionality before. Don't know of anything, but it sounds like an idea that should be posted to [Idea Place|http://www.sdn.sap.com/irj/scn/idea-place]

[former_member11696]

Hi Kin,

The cause is once the SQL is sent off to the DB Cr simply waits for the data to start streaming to the Report Engine. While that happens the Engine will not accept any other requests. So making any calls won't happen until the Engine gets the complete data set. Which in your case is never going to happen.

This is why we recommend closing and disposing after each report as well as freeing up all of that memory and database connections.

As for detecting if the connection has been lost there is no way for CR to do this. There is a RAS registry key but if you are not using RAS ( requires a full version of CR 2008 or above ) then the default is 10 minutes. Once this time has been reached then and exception is throw.

If you are using RAS then this key determines that timeout:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Report Application Server\InprocServer - EnterpriseRequestTimeout(min) = 600000 ( = 10 minutes )

I don't suggest altering this value though because for large datasets you won't have time to get the data before CR disconnects from the DB.

Thank you

Don

[Former Member]

Hi Don,

Thank you for the information provided.

If there is no staled DB connection function that I can use, then I guess my choice will be checking the exception from report generation functions and perform DB re-connection (even though the exception message from report generation is not very specific - the one that I saw from my test was "This field name is not known").

Thanks

Kin