Change Crystal Report Connection Handle

[Former Member]

Hi,

we use Crystal Reports "Crystal ActiveX Report Viewer Library 11.5" and Reports, that are linked with an oracle database. Everything works fine, but unfortunatelly we have to use oracle temporary tables, that contains data per session. So the report can only access data from this table that has been written within the same connection.

I know how to change the connection of a report at runtime, but i have no idea, how to set an already opened connection. We have a COM/.NET-mixed application. The database connection is a standard .NET connection (IDbConnection). For using the report now we use the report connection and just assign the password.

Private Sub ChangeReportTableConnection(ByRef rptTable As CRAXDRT.DatabaseTable)

rptTable.ConnectionProperties("Password") = "Password"

End Sub

So the report will open its own connection, with that it cannot access the data of the temporary table. So we need to set the current connection to the report. Any ideas?

Maik

[former_member183750 · Accepted solution]

Hi Maik

Essentially, you are trying to piggy back on an open connection. There are no APIs, be it in the RDC or .NET SDKs. (This used to be possible with the Print Engine SDK, but that was retired in CR 8.5).

At this time, the only way out is to create a dataset off of the temp table and pass that to the report.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Hey Ludek,

thanks a lot for this answer. This saves a lot of time. Can you plese give me a code snippet how to pass a dataset to the report?

Maik

[former_member183750 · Accepted solution]

I suppose in COM speak it's a recordset.

See the sample app "Passing an ADO Recordset to a Report" here:

Report Designer Component SDK COM Samples - Business Intelligence (BusinessObjects) - SCN Wiki

Also, the RDC Dev Help is installed on your development computer:

C:\Program Files\Business Objects\Crystal Reports 11.5\Help\en\rdcsdk_com_dg_doc\doc\rdcsdk_com_dg.chm

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

thanks again. Well, i downloaded the example and now i know how to pass a recordset to a report. Unfortunately, i dont have the data in my scenario. The report connects by themself to the database. So is there a way to get the sql-statement from the report?

Next is, do i have to change my report, when i want to pass the recordset? Right now it connects directly to the database.

Thanks a lot.

[former_member183750 · Accepted solution]

No changes to the report are needed. And using the RDC, I am pretty sure you could get the SQL string. I think this would do the trick: myReport.SQLQueryString

But I am not sure and as CR XI R2 is out of support I don not have the dev help file anymore.

Of course you can always see the sql in the designer under the database menu.

- Ludek

[former_member11696 · Accepted solution]

Hi Maik,

What you are attempting to do is going to cause you all sorts of memory problems. Datasets have a limitation of about 10K rows but that drastically lowers with the number of rows in your report. And Oracle is notorious for large amounts of data.

What I suggest you do is with your app and Oracle connect to your DB using the Oracle Client under the same user info, same as you are doing for your dataset, but then export that data to an XML file:

ds.WriteXml(@"D:\Atest\YourData.xml");

ds.WriteXmlSchema(@"D:\Atest\YourData.xsd");

Now in the report it is simply setting the data source to the XML.

ds.ReadXml(@"D:\Atest\Dev Element\YourData.xml", XmlReadMode.ReadSchema);

ISCRDataSet DS1 = (ISCRDataSet) CrystalDecisions.ReportAppServer.DataSetConversion.DataSetConverter.Convert(ds);

// uses this for OLE DB DS record set

rptClientDoc.DatabaseController.SetDataSource(DS1, "FunctionSales", "FunctionTotalPrice");

Last 2 values are the table names.

You will/may have to use Tables so search forums and you should find samples on using Tables when setting to XML files. I have not tested this with Oracle so not sure exactly how to.

As Ludek said, CR XI R2 is end of life so to be supported you need to completely drop the RDC and move to .NET. And switching between COM and .NET Interopts has never and will never be supported either. Others have tried and sometime is works and sometimes not an no reason for it, it's just to many layers to go back and forth...

Don