javacrystal-reportshana

How to open CR report based on HANA DB with Java


I have been trying to open a CR report by using SAP Crystal Report java libraries, but I am getting stuck on a tremendous issue.

After the first implement my code, I got an exception like this:

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Error finding JNDI name (THENAME)---- Error code:-2147467259 Error code name:failed

Then, I followed the solution given at this link: How to add a JNDI to a Crystal report bean?

Although all the executed steps look OK and somehow the report is correctly loaded, eventually it always falls in this exception:

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Unexpected ValueType: ValueType=[255]
---- Error code:-2147467259 Error code name:failed
    at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:740)
    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.an.a(SourceFile:108)
    at com.crystaldecisions.sdk.occa.report.application.b0.if(SourceFile:148)
    at com.crystaldecisions.sdk.occa.report.application.b0.b(SourceFile:95)
    at com.crystaldecisions.sdk.occa.report.application.bb.int(SourceFile:96)
    at com.crystaldecisions.proxy.remoteagent.UndoUnitBase.performDo(SourceFile:151)
    at com.crystaldecisions.proxy.remoteagent.UndoUnitBase.a(SourceFile:106)
    at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(SourceFile:2159)
    at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(SourceFile:543)
    at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(SourceFile:3898)
    at com.crystaldecisions.sdk.occa.report.application.DatabaseController.setTableLocation(SourceFile:2906)
    at it.fortresslab.reportmanager.launcher.ReportLauncherTest.shouldLaunchReportToo(ReportLauncherTest.java:65)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:147)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:127)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:90)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:55)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:102)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
    at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
    at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.lang.IllegalArgumentException: Unexpected ValueType: ValueType=[255]
    at com.crystaldecisions.reports.common.Utils.a(SourceFile:97)
    at com.crystaldecisions.reports.common.Utils.a(SourceFile:151)
    at com.businessobjects.reports.sdk.builder.ConnectionDatabaseBuilder.a(SourceFile:275)
    at com.businessobjects.reports.sdk.builder.ConnectionDatabaseBuilder.a(SourceFile:357)
    at com.businessobjects.reports.sdk.builder.EROMDatabaseBuilder.a(SourceFile:185)
    at com.businessobjects.reports.sdk.requesthandler.DatabaseRequestHandler.byte(SourceFile:1112)
    at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1166)
    at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:660)
    ... 95 more

Bearing in mind that my code is exactly as in the example at the link above, the exception is thrown when executing the following line:

clientDoc.getDatabaseController().setTableLocation(table, table);

Any idea of what that "Unexpected ValueType: ValueType=[255]" is all about?

Some info about my runtime environment

  • The report that I'm trying to open was made with SAP Crystal Report 2016 version 14.2.5.26.18
  • The jdbc driver is ngdbc-2.17.12
  • The code is built and launched with Java 11 (Amazon Corretto version 11.0.19.7.1).

Any help would be highly appreciated. Thanks.


Solution

  • After working a bit on this, I am glad to tell everyone that I finally managed to get it worked. I am going to share a quick guide on how to do.

    Pre-requisite

    1. The database connection in the report MUST be done via "JDBC (JNDI)". Do not even try to use ODBC, ADO or any other data source of any kind. Bear in mind that you are going to open the report with Java, which by definition does use JDBC driver. Also, don't forget that ALL subreport must be configured
    2. In case you decide to use another data source type anyway, please DO NOT try to change to JDBC programmatically. I saw some tutorial or articles, even on this platform, and it does NOT work. In particular, when you have to do with complex report, which contains more than one subreport, the implementation is not that easy and, even worse, it's extremely slow. Therefore, I suggest you don't even try to go for this approach
    3. Make sure that Crystal Report loads and uses the JDBC Driver for HANA, which are normally distributed with SAP or you might find it online. The jar of the driver must be configured in CRReport.xml. If you install SAP B1 Client (32 bit), the path of the file is the following: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\CRReport.xml The full path of the jar must be added to the tag <Classpath>
      enter image description here

    EDIT --> VERY IMPORTANT
    4) The Java report launcher, unfortunately, does NOT work under Java 11, highly likely because it is NOT yet supported by Crystal Report 12. The Java version must be 1.8. The version I have personally used in OpenJDK 1.8.0_202.

    Crystal Report JDBC Datasource connection
    Follow these steps to create your JDBC connection on HANA DB for your report:

    1. Select "Database"->"Setup data source path" (Note: my Crystal Report is in Italian, sorry for any translation mistakes)
      Menu for data source setup
    2. On the window which appears in popup, select "Create new connection"->JDBC (JNDI)->"Create new connection link"
      Create new connection
    3. Then add the connection requested parameters:
      Connection parameters
      Bear in mind that if you want to connect to a specific schema, the parameter to be used is "currentSchema" and NOT "databaseName". Then click on "Continue".
    4. Fill in the other requested info.
      enter image description here
      Bear in mind that the "Database name" represents the name of the HANA database instance which you are going to connect to, and NOT the schema you are going to use which contains all the tables/view/etc...
      Then click on OK. From that moment on, you can use your new JDBC connection to realize and complete your CR report.

    JAVA Application
    After following thoroughly the steps above, now you are ready to implement the Java application to load the report. Here is the simple JUnit test I made, which loads the report and export it as PDF. That is not all my own work, as I just put together and simplified some examples I had found online:

    package mypackage;
    
    import com.crystaldecisions.sdk.occa.report.application.*;
    import com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat;
    import org.junit.jupiter.api.Test;
    
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    
    public class ReportLauncherTest {
    
        private static final String TEST_REPORT_RPT = "MyReport.rpt";
        private static final String TEST_REPORT_FULL_PATH_PDF =  "C:\\MyReport.pdf";
        private static final String PARAMETER_NAME_1 = "param1";
        private static final String PARAMETER_NAME_2 = "param2";
    
        private static final Integer PARAMETER_VALUE_1 = 1;
        private static final Integer PARAMETER_VALUE_2 = 2;
    
        private static final String DB_USERNAME = "USERNAME";
        private static final String DB_PASSWORD = "PASSWORD";
    
        @Test
        public void shouldLaunchReportAndExportInPDF() throws Exception {
            ReportClientDocument reportClientDoc = new ReportClientDocument();
            reportClientDoc.open(ReportLauncherTest.class.getClassLoader().getResource(TEST_REPORT_RPT).getPath(), 0);
            reportClientDoc.getDatabaseController().logon(DB_USERNAME, DB_PASSWORD);
            DataDefController dataDC = reportClientDoc.getDataDefController();
            dataDC.getParameterFieldController().setCurrentValue("", PARAMETER_NAME_1, PARAMETER_VALUE_1);
            dataDC.getParameterFieldController().setCurrentValue("", PARAMETER_NAME_2, PARAMETER_VALUE_2);
    
            ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream)reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);
            writeToFileSystem(byteArrayInputStream, TEST_REPORT_FULL_PATH_PDF);
        }
    
    
        private void writeToFileSystem(ByteArrayInputStream byteArrayInputStream, String exportFile) throws Exception {
            //Use the Java I/O libraries to write the exported content to the file system.
            byte byteArray[] = new byte[byteArrayInputStream.available()];
            //Create a new file that will contain the exported result.
            File file = new File(exportFile);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(byteArrayInputStream.available());
            int x = byteArrayInputStream.read(byteArray, 0, byteArrayInputStream.available());
            byteArrayOutputStream.write(byteArray, 0, x);
            byteArrayOutputStream.writeTo(fileOutputStream);
            //Close streams.
            byteArrayInputStream.close();
            byteArrayOutputStream.close();
            fileOutputStream.close();
        }
    }
    

    Bear in mind that the code needs for some dependencies which i am going to list below: enter image description here

    I suppose you would be able to find most of them online. Some of the jar can be downloaded for free, other I am afraid will be available on SAP website only if you have a subscription.

    I hope you liked the tutorial.

    Any feedback would be highly appreciated.

    Thanks.