Error in Java in replaceConnection when report contains sql expression fields

[Former Member]

We are using the crystal java runtime in order to print reports. We connect to an oracle DB. As we want to support different installations, we need to change the schema name (and other connection details) using databaseController.replaceConnection() and databaseController.setTableLocation(). This works perfectly fine for most reports.

Unfortunately, when the report contains sql expression fields, when calling databaseController.replaceConnection() we get the following error. Is there any way to work around this?

(Note: The german error message "Fehler beim Parsen des Ausdrucks." means "Error when parsing expression").

Caused by: com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Fehler beim Parsen des Ausdrucks.---- Error code:-2147467259 Error code name:failed

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:2284)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:732)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:166)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:528)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:526)

  at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:524)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:423)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:351)

  at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)

  at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)

  at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)

  at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)

  at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)

  at com.crystaldecisions.sdk.occa.report.application.ds.a(SourceFile:186)

  at com.crystaldecisions.sdk.occa.report.application.FormulaFieldController.a(SourceFile:329)

  at com.crystaldecisions.sdk.occa.report.application.FormulaFieldController.check(SourceFile:288)

  at com.crystaldecisions.sdk.occa.report.application.FormulaFieldController.if(SourceFile:1030)

  at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(SourceFile:3929)

  at com.crystaldecisions.sdk.occa.report.application.DatabaseController.replaceConnection(SourceFile:3880)

  at com.werum.pasx.infrastructure.printing.printtechnology.crystal.AbstractCrystalReportsPrintTechnology.setConnection(AbstractCrystalReportsPrintTechnology.java:282)

  at com.werum.pasx.infrastructure.printing.printtechnology.crystal.AbstractCrystalReportsPrintTechnology.setDatabaseToReport(AbstractCrystalReportsPrintTechnology.java:181)

  ... 27 more

Caused by: com.crystaldecisions.reports.reportdefinition.ReportDefinitionException: Fehler beim Parsen des Ausdrucks.

  at com.crystaldecisions.reports.reportdefinition.SQLExpressionFieldDefinition.td(SourceFile:317)

  at com.crystaldecisions.reports.reportdefinition.o.ad(SourceFile:5314)

  at com.businessobjects.reports.sdk.requesthandler.FormulaRequestHandler.a(SourceFile:159)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1544)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:660)

  ... 47 more

Caused by: com.crystaldecisions.reports.common.QueryEngineException: Fehler beim Parsen des Ausdrucks.

  at com.crystaldecisions.reports.queryengine.Connection.if(SourceFile:1203)

  at com.crystaldecisions.reports.queryengine.ExpressionField.tk(SourceFile:161)

  at com.crystaldecisions.reports.reportdefinition.SQLExpressionFieldDefinition.td(SourceFile:294)

  ... 51 more

Caused by: com.businessobjects.reports.jdbinterface.common.DBException: Fehler beim Parsen des Ausdrucks.

  at com.crystaldecisions.reports.queryengine.driverImpl.jdbc.JDBCQueryDefinition.ParseExpression(Unknown Source)

  at com.crystaldecisions.reports.queryengine.Connection.if(SourceFile:1199)

  ... 53 more

[former_member183750]

Hi Norbert

I am not sure what version of CR you are using - but you always want to be using the latest one. See: where applying the later patch resolved that error.

So getting to the latest SP for your version will be the 1st thing to do.

And for now, one more  thing to keep in mind:

1217871 - What is the intended use of 'SQL Expression Fields' in Crystal Reports?

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

[Former Member]

Hi Ludek.

Thanks for your answer.

Unfortunately the problem occurs even when using the latest version of the crystal runtime (12.2.217) 😞

Our SQL expression contains a sub-select (select count(*) from ...), but it is not in any way schema/user dependent.

[former_member183750]

Hi Norbert

Note that in the KBA I referenced it says:

Using a SELECT statement in a SQL Expression field is not supported. Generally, an SQL Expression field cannot contain a SELECT statement because Crystal Reports can only process one SELECT statement per main report. If a SELECT statement is included in an SQL Expression field, an error message, similar to the following, may appear:

I'd suggest creating a stored proc for this. Offloading the processing on to the server will always result in better performance.

- Ludek

[Former Member]

I tried using an SQL command instead of the SQLexpression. Unfortunately when using the report in java, this just causes another error:

ERROR sdk.JRCCommunicationAdapter -  detected an exception: java.lang.AssertionError

  at com.crystaldecisions.reports.common.asserts.CrystalAssert.ASSERT(Unknown Source)

  at com.crystaldecisions.reports.common.asserts.CrystalAssert.ASSERT(Unknown Source)

  at com.crystaldecisions.reports.queryengine.Rowset.zU(SourceFile:1314)

  at com.crystaldecisions.reports.queryengine.RowsetBase.a(SourceFile:363)

  at com.crystaldecisions.reports.queryengine.RowsetBase.a(SourceFile:499)

  at com.crystaldecisions.reports.queryengine.b.yq(SourceFile:920)

  at com.crystaldecisions.reports.queryengine.QueryInfo.vJ(SourceFile:241)

  at com.crystaldecisions.reports.datafoundation.DFQuery.for(SourceFile:628)

  at com.crystaldecisions.reports.datalayer.a.do(SourceFile:1621)

  at com.crystaldecisions.reports.datalayer.a.a(SourceFile:1404)

  at com.crystaldecisions.reports.dataengine.m.b(SourceFile:334)

  at com.crystaldecisions.reports.dataengine.j.b(SourceFile:515)

  at com.crystaldecisions.reports.dataengine.m.o(SourceFile:408)

  at com.crystaldecisions.reports.dataengine.m.a(SourceFile:173)

  at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:114)

  at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:95)

  at com.crystaldecisions.reports.dataengine.j.case(SourceFile:1080)

  at com.crystaldecisions.reports.dataengine.h.<init>(SourceFile:108)

  at com.crystaldecisions.reports.dataengine.DataContext.a(SourceFile:254)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:4660)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.a(SourceFile:4574)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.new(SourceFile:2652)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.byte(SourceFile:2610)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.try(SourceFile:2282)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.int(SourceFile:2442)

  at com.crystaldecisions.reports.dataengine.DataProcessor2.I(SourceFile:1013)

  at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.fo(SourceFile:526)

  at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.if(SourceFile:613)

  at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.a(SourceFile:578)

  at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.X(SourceFile:387)

  at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.moveToPageN(SourceFile:339)

  at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.a(SourceFile:1090)

  at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.byte(SourceFile:218)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1908)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:660)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:166)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:528)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:526)

  at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:524)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:423)

  at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:351)

  at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)

  at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)

  at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)

  at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)

  at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)

  at com.crystaldecisions.sdk.occa.report.application.ds.a(SourceFile:186)

  at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(SourceFile:1558)

  at com.crystaldecisions.sdk.occa.report.application.ReportSource.getPage(SourceFile:767)

  at com.businessobjects.crystalreports.viewer.core.rs.RSRecordSource.openInputStream(Unknown Source)

  at com.businessobjects.crystalreports.viewer.core.TSLVReader.a(Unknown Source)

  at com.businessobjects.crystalreports.viewer.core.TSLVReader.run(Unknown Source)

  at java.lang.Thread.run(Thread.java:744)

[former_member11696]

Hi Norbert,

How about going back to the basics and taking CR our of the picture completely?

Take your SQL and paste it into a SQL Tool that supports your database, does it work? If not fix it first, then once it works they then format it for CR and test again.

Don

[Former Member]

Yes, the SQL works of course - the reports works (in the crystal editor as well as using Java) as long as the schema/user name used by the database connection does not change. It is really unfortunate that this is so dfficult to achieve!

In any case - we have now adapted our data model to include the information we need (without further computation by the report) to work around this problem. This works fine.

I'm not very happy with this, as it mixes the concerns of report designing and preparing the report data 😞

[former_member11696]

Hi Norbert,

I asked around and the SQL Expression should use the same log on info as provide by the Data Source connection info so it should not require a separate connection. Same for a Command Object.

So as long as you are updating the Connection property bag with the new log on info, which does require a Schema and User name, unless you created the Report to use Trusted Authentication, that new log on info should work.

So nothing else to suggest since no code snippet provided....

Thanks again

Don

[Former Member]

Hi Don.

I've spent way too much time on this issue already and we have a workaround, so I'm ready to give up.

On the chance that its a trivial problem, here's the code we use for replacing the connection:

IConnectionInfo newConnectionInfo = new ConnectionInfo();

PropertyBag propertyBag = new PropertyBag();

propertyBag.put("Trusted_Connection", "b(false)");

propertyBag.put(PropertyBagHelper.CONNINFO_SERVER_TYPE, "JDBC (JNDI)");

propertyBag.put(PropertyBagHelper.CONNINFO_JDBC_DATABASECLASSNAME, "foo");

propertyBag.put(PropertyBagHelper.CONNINFO_JDBC_CONNECTION_URL, "foo");

propertyBag.put("Use JDBC", "true");

propertyBag.put(PropertyBagHelper.CONNINFO_URI, "foo");

propertyBag.put(PropertyBagHelper.CONNINFO_DATABASE_DLL, "foo");

newConnectionInfo.setAttributes(propertyBag);

newConnectionInfo.setUserName("foo");

newConnectionInfo.setPassword("foo");

newConnectionInfo.setKind(ConnectionInfoKind.SQL);

int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;

databaseController.replaceConnection(oldConnectionInfo, newConnectionInfo, null, replaceParams);

Tables tables = databaseController.getDatabase().getTables();

for (int i = 0; i < tables.size(); i++) {

  ITable table = tables.getTable(i);

  table.setQualifiedName(table.getName());

  databaseController.setTableLocation(tables.getTable(i), table);

}

[former_member11696]

Moved to Java SDK forum