#1 2018-11-27 10:01:24

FLU73
Member
Registered: 2018-10-25

How use CDATA driver for SAP ERP ?

Hello,

I've downloaded the trial version of the JDBC driver from CDATA for SAP ERP.
https://www.cdata.com/drivers/sap/download/jdbc/
And an other file from SAP for JDBC connection. (as it's mentionned in the CDATA instructions)
https://support.sap.com/en/product/connectors/jco.html

But I don't see how to deal with with files with Reportserver.
I've put all the fies in the reportserver lib folder C:\Bitnami\reportserverenterprise-3.0.4-1\apache-tomcat\webapps\reportserver\WEB-INF\lib
But it's not working.
Maybe there is a file of the datasources folder to manage from the filesystem menu, or create a new datahelper, ...

NB : To remember, as a first attempt, I tried to connect to my SAP database by the mean of the jconn4.jar file (put in the same path)
But only the test connection is working, not the data selection, due to my Sybase product version (see my other post "Sybase connection error" : https://forum.reportserver.net/viewtopic.php?id=1263
That's the reason I decided to test the connection with a connector dedicated to SAP.

I've logged a ticket to the CDATA support, they have downloaded Reportserver on their side, but without success to use the driver :
their advice is : "I recommend taking this up with ReportServer's support specifically.  Just tell them that you have a JDBC driver with an external dependency"


To see how it works according my tests, here below is the example of how to manage the files to create a connection within Dbeaver :
I tried use the driver with Dbeaver as there is an example in the help documentation of this driver.
It's working well, but as you will see there are 2 jar files to manage :

1) cdata.jdbc.saperp.jar
this file is in the lib folder of the unzipped Cdata driver, it has been added in the driver library with the add button in the Dbeaver driver editor.
(in reallity, this is the parent folder that need to be added as this is a couple of files that is required : "cdata.jdbc.saperp.jar" and "cdata.jdbc.saperp.lic")

2) In addition of these files provided by CDATA, it's required to manage a java connector provided by SAP: sapjco3.jar

A SAP Jco folder as been created in the Dbeaver folder : C:\Program Files\DBeaver\plugins\SAP JCo
In this folder, I did a copy/paste of all the files that are in the unzipped folder downloaded from AP (sapjco3.jar, and oher files)

The path to this folder need to be added to the PATH environment variable.
The path to the jar file (....\sapjco3.jar) need to be added to your CLASSPATH environment variable.

In the folder from SAP there is a "sapjco3.dll" file, that can be put in the following folders :
C:\Windows\System32
C:\Windows\SysWOW64
(Not sure this last step need to be done. It's apparently not advised by SAP to do it at first, but it was my case due to my "trial & error" approach)

So, in the Dbeaver driver editor, this other folder SAP Jco need to be added in the library.
In summary, it needs 2 jar files to work, we can say the driver comes in 2 parts (from CDATA & SAP)

Once it's done, we can specify the driver settings :
class name: cdata.jdbc.saperp.SAPERPDriver
JDBL URL: jdbc:saperp:
connection properties (see your SAP GUI connection settings): Host, User, password, SystemId, SystemNumber, Client (mandant Nr).
example : jdbc:saperp:Host=<ser_name>;User=<My_user>;Password=<My_password>;Client=<Mandant_Nr>;SystemId=<your ID system>;SystemNumber=<your instance Nr>;ConnectionType=Classic;

Offline

#2 2018-11-27 18:48:16

FLU73
Member
Registered: 2018-10-25

Re: How use CDATA driver for SAP ERP ?

Finally, I succeed to use the driver by placing the jar files in the lib folder (C:\Bitnami\reportserverenterprise-3.0.4-1\apache-tomcat\webapps\reportserver\WEB-INF\lib)
then creating a groovy file to  execute a script to add the SAP_CDATA driver.
The groovy file has been create by adapting this one : http://www2.datenwerke.net/files/blog/2 … ort.groovy
(names, driver class, dummy query: select matnr from mara)

For the datasource, my URL is built as follow :
jdbc:saperp:Host=<ser_name>;User=<My_user>;Password=<My_password>;Client=<Mandant_Nr>;SystemId=<your ID system>;SystemNumber=<your instance Nr>;ConnectionType=Classic;

When doing the test connection, the result is successful ... but I've the message below when excecuting the query SELECT MATNR from MARA (Part Nr in table of materials) in a dynamic list :

27-Nov-2018 19:23:19.213 INFO [ajp-apr-8009-exec-7] net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke Intercepted NonFatalException
java.lang.reflect.UndeclaredThrowableException
    at com.sun.proxy.$Proxy218.prepareStatement(Unknown Source)
    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:224)
    at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.getStatement(JasperStyleParameterParser.java:145)
    at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:95)
    at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:284)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:430)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:422)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:154)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:124)
    at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.loadData(TableReportUtilityServiceImpl.java:322)
    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
    at sun.reflect.GeneratedMethodAccessor218.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
    at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
    at org.apache.coyote.ajp.AbstractAjpProcessor.process(AbstractAjpProcessor.java:870)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2527)
    at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2516)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at net.datenwerke.dbpool.proxy.ProxiedConnectionHandler.handleInvocation(ProxiedConnectionHandler.java:25)
    at com.google.common.reflect.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:79)
    ... 59 more
Caused by: XcoreXsaperpX180X6886.sac: Malformed SQL Statement: Unexpected token encountered: [1].
Statement:SELECT TOP 50 START AT 1 * FROM (SELECT * FROM (SELECT MATNR FROM MARA
) wrappedQry) limitQry  /* token: 6749200a-35ae-4347-a290-dc5953cfbe92 */   /* currentuser: 6 */
    at XcoreXsaperpX180X6886.wra.a(Unknown Source)
    at XcoreXsaperpX180X6886.sq.<init>(Unknown Source)
    at cdata.jdbc.saperp.SAPERPPreparedStatement.<init>(Unknown Source)
    at cdata.jdbc.saperp.SAPERPJDBCClassFactory.createPreparedStatement(Unknown Source)
    at XcoreXsaperpX180X6886.boa.prepareStatement(Unknown Source)
    at cdata.jdbc.saperp.SAPERPConnection.prepareStatement(Unknown Source)
    at XcoreXsaperpX180X6886.boa.prepareStatement(Unknown Source)
    at cdata.jdbc.saperp.SAPERPConnection.prepareStatement(Unknown Source)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:469)
    ... 64 more

Is there something that can help in the lines :
Caused by: XcoreXsaperpX180X6886.sac: Malformed SQL Statement: Unexpected token encountered: [1].
Statement:SELECT TOP 50 START AT 1 * FROM (SELECT * FROM (SELECT MATNR FROM MARA
) wrappedQry) limitQry  /* token: 6749200a-35ae-4347-a290-dc5953cfbe92 */   /* currentuser: 6 */

Offline

#3 2018-11-28 07:57:27

eduardo
Administrator
Registered: 2016-11-01
Website

Re: How use CDATA driver for SAP ERP ?

Hi FLU73,

thanks for raising this thread, this seems interesting! If this works we may support this directly in a future reportserver version.

As you saw, the query generated by reportserver, based on your query, is:
SELECT TOP 50 START AT 1 * FROM (SELECT * FROM (SELECT MATNR FROM MARA
) wrappedQry) limitQry  /* token: 6749200a-35ae-4347-a290-dc5953cfbe92 */   /* currentuser: 6 */

This seems to be incorrect in your SAP ERP SQL dialect. Could you please modify this query in order for it to work? You may use an external sql tool for testing without reportserver. It may be necessary to further adapt the dialect (sql helper) files in order for your driver to work.

Could you please further post your current groovy script  ?
You can see examples of the sql helpers if you download the reportserver sources and take a look at:
net.datenwerke.rs.base.service.dbhelper.DatabaseHelper

Examples:
net.datenwerke.rs.base.service.dbhelper.db.mssql.MsSQL
net.datenwerke.rs.base.service.dbhelper.db.informix.Informix
net.datenwerke.rs.base.service.dbhelper.db.oracle.Oracle
net.datenwerke.rs.base.service.dbhelper.db.PostgreSQL

etc

Regards,
Eduardo

Offline

#4 2018-11-28 13:12:14

FLU73
Member
Registered: 2018-10-25

Re: How use CDATA driver for SAP ERP ?

Hello,

Finally, it seems to work better by using the groovy script like below.
But maybe the performance of queries will be not optimized ... It should be not a worry as the CDATA connector has itself an integrated "cache" function for performance optimization.
On my SAP, I need to implement the special function Z_CUSTOM_READ_TABLE as the standard one returns only results in a buffer of 512 bytes.
Should be easy to implement as CDATA provides instructions to do it.

For the moment, I used a little query like : SELECT MATNR FROM MARA WHERE MTART = 'DIEN'
This query returns 12 articles Nr which are in DIEN type.

The groovy script is :

package databasehelper;

import net.datenwerke.rs.scripting.service.scripting.scriptservices.GlobalsWrapper;
import net.datenwerke.rs.base.service.dbhelper.DatabaseHelper
import net.datenwerke.rs.base.service.dbhelper.hooks.DatabaseHelperProviderHook
import net.datenwerke.rs.base.service.dbhelper.queries.LimitQuery
import net.datenwerke.rs.base.service.dbhelper.queries.OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.queries.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder



class SAP_CDATA extends DatabaseHelper {

    public static final String DB_NAME = "SAP_CDATA";
    public static final String DB_DRIVER = "cdata.jdbc.saperp.SAPERPDriver";
    public static final String DB_DESCRIPTOR = "DBHelper_SAP_CDATA";

    @Override
    public String getDescriptor() {
        return DB_DESCRIPTOR;
    }

    @Override
    public String getDriver() {
        return DB_DRIVER;
    }

    @Override
    public String getName() {
        return DB_NAME;
    }

    @Override
    public String createDummyQuery() {
        return 'select MATNR from MARA';
    }

}


def HOOK_NAME = "DATASOURCE_HELPER_SAP_CDATA"

def callback =  [
            provideDatabaseHelpers : {
                return Collections.singletonList(new SAP_CDATA());
            }
        ] as DatabaseHelperProviderHook;

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)

Offline

#5 2018-11-28 13:20:28

eduardo
Administrator
Registered: 2016-11-01
Website

Re: How use CDATA driver for SAP ERP ?

Hi FLU73,

1. but how did you correct the incorrect query ?
SELECT TOP 50 START AT 1 * FROM (SELECT * FROM (SELECT MATNR FROM MARA
) wrappedQry) limitQry  /* token: 6749200a-35ae-4347-a290-dc5953cfbe92 */   /* currentuser: 6 */

2. Please send us your exact reportserver version.

Regards,
Eduardo

Offline

#6 2018-11-28 15:51:11

FLU73
Member
Registered: 2018-10-25

Re: How use CDATA driver for SAP ERP ?

Hi Eduardo,

Reportserver version is RS3.0.4-6004 (2018-08-27-10-01-33)

I only changed my groovy script, by removing the followings line :

    @Override
    public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
        return new LimitQuery(nestedQuery, queryBuilder){
            @Override
            public void appendToBuffer(StringBuffer buf) {
                buf.append("SELECT TOP ");
                buf.append(queryBuilder.getLimit() < 1 ? 1 : queryBuilder.getLimit());
                buf.append(" * FROM (");
                nestedQuery.appendToBuffer(buf);
                buf.append(") limitQry");
            }
        }
    }

    @Override
    public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
        return new OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
            @Override
            public void appendToBuffer(StringBuffer buf) {
                buf.append("SELECT TOP ");
                buf.append(queryBuilder.getLimit() < 1 ? 1 : queryBuilder.getLimit());
                buf.append(" START AT ");
                buf.append(queryBuilder.getOffset() + 1);
                buf.append(" * FROM (");
                nestedQuery.appendToBuffer(buf);
                buf.append(") limitQry");
            }
        }
    }

Offline

Board footer

Powered by FluxBB