Question Re: Problem with Synonyms in Technology Q&A

Problem with Synonyms

Former Member — Fri, 30 Oct 2009 10:16:17 GMT

Hello,

we are developing designing our reports on an Oracle database in a certain schema. To be able to run those reports at our customers we have written software which replaces our database schema by the one the customer uses besides other functionality. Often the tables used in the reports are at our customersu2019 databases synonyms on tables in yet another schema.

Until we switch to the .NET SDK this was no problem. But if I now switch the table location to a synonym the report looses all internal information about the fields of the table. Thus all information about links to parameters and joins of tables are also gone.

If I save the report after replacing the table locations and open the saved copy in the Crystal Designer, I can only see tables without any fields in the Database Expert.

Iu2019m modifying the table location with code like in the following example:

<code>

ReportDocument rdReport = new ReportDocument();

rdReport.Load(sReportName, OpenReportMethod.OpenReportByTempCopy);

CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rcDocument = rdReport.ReportClientDocument;

for (int i = 0; i < rdReport.Database.Tables.Count; ++i)

{

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable rctTable = rcDocument.DataDefController.Database.Tables;

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable rctNew = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

rctNew.Name = rctTable.Name;

rctNew.Alias = rctTable.Alias;

rctNew.QualifiedName = rctTable.QualifiedName.Replace("OLD_SCHEMA","NEW_SCHEMA");

rctNew.ConnectionInfo = rctTable.ConnectionInfo.Clone(true);

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag pbAttr = rctNew.ConnectionInfo.Attributes;

pbAttr[sAttrDBDll] = "crdb_ado.dll";

pbAttr[sAttrDBName] = sDBName;

pbAttr[sAttrDBType] = "OLE DB (ADO)";

pbAttr[sAttrServerDesc] = sServerName;

pbAttr[sAttrSQLDB] = "True";

pbAttr[sAttrSSO] = "False";

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag pbLogOnProp = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag)pbAttr[sAttrLogOnProp];

pbLogOnProp.RemoveAll();

// set new connection string

pbLogOnProp.FromString(sConnectStr);

// set user/pwd

rctNew.ConnectionInfo.UserName = sUserId;

rctNew.ConnectionInfo.Password = sPwd;

// apply changes

rcDocument.DatabaseController.SetTableLocation(rctTable, rctNew);

}

</code>

Does anybody know if there is a workaround so that I can modify the table location to a synonym without loosing the field information?

Thanks in advance.

Re: Problem with Synonyms

Former Member — Fri, 30 Oct 2009 14:49:20 GMT

Before you do this using any APIs, can you do it in the CR designer? 99% of the time, if it can not be done, or if it is an issue in the designer, the same will hold at runtime.

The symptom of the fields disappearing is indicative of the report not understanding the schema, or the schema not matching what the report is expecting. You may also want to try enabling the option Verify on First refresh and Verify Stored Procedure on First Refresh. You can do this in the designer, or code.

Ludek

Re: Problem with Synonyms

Former Member — Mon, 02 Nov 2009 08:09:30 GMT

Hi Ludek,

If I use the Set Datasource Location in the designer and replace the original tables by synonyms (which point to the original tables), the designer can also find no field information. It is also not possible to build a report based on synonyms.

I don't know how it is with other databases, but it seems that Oracle does not provide any meta information about the original object in synonyms.

But the old COM-Api didn't seem to care about the missing field information, and I could switch from a table to a synonym.

Greetings

Re: Problem with Synonyms

Former Member — Mon, 02 Nov 2009 14:38:58 GMT

Hello Ron;

Since this is also happening in the CR designer, I really think it will be best to get an answer from the CR database guys in the Data Connectivity - Crystal Reports forum;

Once it is working in the designer, see how it goes at runtime.

Ludek

Re: Problem with Synonyms

ted_ueda — Mon, 02 Nov 2009 14:43:03 GMT

The issue sounded familiar, so searching internally, there was an issue investigated using Oracle OLE DB to synonyms, and the problem (ADAPT00503823) was narrowed down to problems with how the Oracle OLE DB driver exposes the metadata.

Does this issue happen if you use a different driver?

Sincerely,

Ted Ueda

Re: Problem with Synonyms

Former Member — Mon, 02 Nov 2009 14:55:16 GMT

I get the same problem whether I use the Microsoft OLE DB Provider for Oracle or the Oracle Provider for OLE DB.

Using an ODBC connection to the Oracle Database on the other hand works fine.

Re: Problem with Synonyms

ted_ueda — Mon, 02 Nov 2009 15:01:00 GMT

I don't know about the Microsoft OLE DB driver, but I do know that the issue in what I referred to was something to do with the Oracle OLE DB driver.

Sincerely,

Ted Ueda

Re: Problem with Synonyms

Former Member — Mon, 02 Nov 2009 15:09:46 GMT

Hello Ted,

can you tell me where I can find the issue ADAPT00503823?

With best regards,

Ron

Re: Problem with Synonyms

Former Member — Mon, 02 Nov 2009 16:02:14 GMT

I've found a note regarding the issue.

If I understand the status correctly, the patch was released in November 2008.

But this note only mentions the JDBC driver, so I presume that this issue hasn't been fixed to work with the Oracle OLE DB driver or the MSDAORA OLE DB driver.

Re: Problem with Synonyms

ted_ueda — Mon, 02 Nov 2009 16:34:56 GMT

The JDBC issue I'm pretty familiar with, since I'd encountered it directly.

It's a different issue - the CR JDBC driver didn't support synonyms at all (i.e., it wasn't a issue with the Oracle JDBC thin driver).

The ADAPT's are internal tracking numbers - it's used as reference to issues, but not available externally.

Sincerely,

Ted Ueda

Re: Problem with Synonyms

Former Member — Tue, 03 Nov 2009 08:50:26 GMT

I also tried to use the Oracle Server connection using the crdb_oracle.dll. With this connection the meta information of the synonyms are read correctly and my problem described above does not appear.

The question now is, is that a bug of Crystal Reports or a bug of Oracle/Microsoft?

The similar bug in the JDBC driver was solved by Crystal, wasn't it? This would be a reason to file a bug report here.

Sincerely,

Ron

Re: Problem with Synonyms

ted_ueda — Tue, 03 Nov 2009 14:26:33 GMT

The underlying issue was different for the OLE driver in comparison with the JDBC. Both require special handling for synonyms, and that's what the track for JDBC resolved, since the Oracle thin driver did provide adequate metadata. The OLE didn't.

Sincerely,

Ted Ueda

Re: Problem with Synonyms

Former Member — Tue, 19 Jan 2010 16:16:44 GMT

I am having a similar problem with synonyms in crystal 2008 designer connecting to a sql server 2005 database using OLE DB provider. I can connect and the synonym shows in the field explorer with a plus sign but it doesn't allow you to expand the plus sign to show the fields and use them in a report!

Additionally if i design the report against the original table then run it in java crystal for eclipse subbing out the the database for a database where the synonym is present instead of the table it runs but finds no data.

Any ideas with these two problems?

Re: Problem with Synonyms

Former Member — Tue, 19 Jan 2010 16:32:56 GMT

I have reported this behavior to SAP as a bug in November.

I finally got today the message:

"The issue is planned to be fixed in CR 2008 SP3 and Release of SP3 would be(tentative) in May or June."

So we have no choice than to wait until SP3 is released.

Re: Problem with Synonyms

ted_ueda — Tue, 19 Jan 2010 17:29:07 GMT

Hello Jeremy,

As Ron points out, the OLE DB issue is scheduled for SP3 for both Oracle and SQL Server - the developer worked around the limitation of the OLE DB driver.

The Java issue - are you using JDBC connectivity?

Sincerely,

Ted Ueda

Re: Problem with Synonyms

Former Member — Tue, 19 Jan 2010 17:37:52 GMT

Yeah Im running jdbc using the jtds sql driver. I design the report against the original table, then run the report in java using setTableLocation code to point to a database where this table is just a synonym. The synonym works for command based reports but not when i design against the original table like this.

Jeremy

Re: Problem with Synonyms

ted_ueda — Tue, 19 Jan 2010 18:17:42 GMT

You may want to post a new forum question on the Crystal Reports Java forum - don't want to lengthen this one.

Sincerely,

Ted Ueda