CR runtime uses tables from the wrong schema (Oracle)

[costa-b]

Hello:

I am in process of re-writing an old Delphi app that ran CR reports via the VCL component to a .Net app that uses the .Net CR components.

As for versions, I am using VS 2013, .Net 4.5.1 and I installed CRforVS_13_0_14. The reports connect to an Oracle 10g database. The reports that I am testing haven't been created by me. Some of them are very old.

I am experiencing a weird behavior in the .Net version where the CR runtime uses tables from the wrong schemas while the designer (11.0.0.895) works fine.

At runtime I am using the following code to set the connection information:

	private ConnectionInfo CreateConnectionInfo()
	{
	ConnectionInfo connectionInfo = new ConnectionInfo();
	connectionInfo.UserID = _reportRequestInfo.ReportRunUser;
	connectionInfo.Password = _reportRequestInfo.ReportRunPassword;
	connectionInfo.ServerName = _reportRequestInfo.ReportRunInstance;
	return connectionInfo;
	}

	private static void SetDbLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument)
	{
	Tables tables = reportDocument.Database.Tables;
	foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
	{
	TableLogOnInfo tableLogonInfo = table.LogOnInfo;
	tableLogonInfo.ConnectionInfo = connectionInfo;
	table.ApplyLogOnInfo(tableLogonInfo);

	}
	}

So, the report accesses a bunch of tables all in the same schema, let's say SCHEMA1. In design mode all works fine. However, at runtime, somehow CR assigns a different schema (or schemas) to some of the tables. The problem is that there is another schema in the database - let's call it SCHEMA2 -  that contains some tables with the same names as in SCHEMA1. I used a session browser to see the open cursors that the CR runtime runs and I can see that it runs queries such as this one:

  SELECT owner, object_type

    FROM sys.all_objects

   WHERE OBJECT_NAME = 'TABLE1'

ORDER BY object_type ASC

In some cases this returns more than one row, and unfortunately the first row is for the wrong schema, i.e. SCHEMA2.

What can I do to fix this? I have to admit, I am freaking out a bit. So far, I tested about 100+ reports, they all seemed fine, until today, when I compared the results for one report and the rows returned by the different versions of CR didn't match. I looked then at the queries both runtimes executed, the old version was fine, but the new one had used the wrong schemas for some of the tables.

Thanks

[Former Member · Accepted solution]

Hello. I an not sure but mybe you should try to use synonyms?

Someting like this:

CREATE SYNONYM SAPUSER FOR ”OPS$SAP-DEV\SM1ADM”.SAPUSER;

SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'SAPUSER';

[former_member11696 · Accepted solution]

CR does not support synonyms.

Don

[costa-b · Accepted solution]

We already use synonyms for some of the tables.

As I said in my previous message, the CR runtime, instead of using the schema names attached to the tables names (i.e. the tables that have been added to the report), tries to discover the schema at runtime by running a query such as this one:

SELECT owner, object_type

    FROM sys.all_objects

   WHERE OBJECT_NAME = 'EMPLY_CRRNT_PSTN'

ORDER BY object_type ASC

If there is more than one row returned (and if synonyms are used there might be) then CR might use the wrong table.

What makes it confusing is that this happens when I set the connection information with the code above. It seems that the CrystalDecisions.CrystalReports.Engine.Table objects don't retain the schema name of their qualified name. After more research I found that there is another way to scan the tables in the report. I ended up using this code:

	private static void SetDbLogonForReport2(ConnectionInfo connectionInfo, ReportDocument reportDocument)
	{
	//Create a new Database Table to replace the reports current table.
	CrystalDecisions.ReportAppServer.DataDefModel.Table boTable =
	new CrystalDecisions.ReportAppServer.DataDefModel.Table();

	//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
	PropertyBag boMainPropertyBag = new PropertyBag();
	//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
	//In the main property bag (boMainPropertyBag)
	PropertyBag boInnerPropertyBag = new PropertyBag();

	//Set the attributes for the boInnerPropertyBag
	boInnerPropertyBag.Add("Server", connectionInfo.ServerName);
	boInnerPropertyBag.Add("Trusted_Connection", "False");

	//Set the attributes for the boMainPropertyBag
	boMainPropertyBag.Add("Database DLL", "crdb_oracle.dll");
	boMainPropertyBag.Add("QE_DatabaseName", "");
	boMainPropertyBag.Add("QE_DatabaseType", "");
	//Add the QE_LogonProperties we set in the boInnerPropertyBag Object
	boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
	boMainPropertyBag.Add("QE_ServerDescription", connectionInfo.ServerName);
	boMainPropertyBag.Add("QE_SQLDB", "False");
	boMainPropertyBag.Add("SSO Enabled", "False");

	//Create a new ConnectionInfo object
	CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =
	new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
	//Pass the database properties to a connection info object
	boConnectionInfo.Attributes = boMainPropertyBag;
	//Set the connection kind
	boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
	//**EDIT** Set the User Name and Password if required.
	boConnectionInfo.UserName = connectionInfo.UserID;
	boConnectionInfo.Password = connectionInfo.Password;
	//Pass the connection information to the table
	boTable.ConnectionInfo = boConnectionInfo;

	//Get the Database Tables Collection for your report
	CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables =
	reportDocument.ReportClientDocument.DatabaseController.Database.Tables;

	foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in boTables)
	{
	boTable.Name = table.Name;
	boTable.QualifiedName = table.QualifiedName;
	boTable.Alias = table.Alias;
	reportDocument.ReportClientDocument.DatabaseController.SetTableLocation(table, boTable);
	}

}

Is it good, bad? I don't know. This code is based on the output from Ludek's utility and other code that I found on the net. If you care to comment and you see something wrong I would appreciate it if you let me know. I am going to re-test all reports in the next few days using this code.

Thanks

[Former Member]

I am having the same problem.

The runtime try to find the schema name at sys.all_objects.

OCIStmtExecute succeeded: SELECT owner, object_type FROM sys.all_objects WHERE OBJECT_NAME='TABLE_NAME'

It's on CRforVS_13_0_13.