Sybase connection error :

FLU73 · 2018-10-25 08:28:50

Hello,

When testing a connection to a Sybase DB (ASE 15.7), I getthe following error :

Query could not be executed: Incorrect syntax near the keyword 'dummy'.

- Driver jconn4 is properly installed in the following paths :
C:\reportserver-3.0.4-1\apps\reportserver\reportserver-conf\lib
and
C:\reportserver-3.0.4-1\apache-tomcat\webapps\reportserver\WEB-INF\lib

- Tests have been done with URL like :
jdbc:sybase:Tds:<server_name>:<Port>
or
jdbc:sybase:Tds:<server_name>:<Port>?ServiceName=<database_name>

- another topic about Sybase deal with of dbsrv17.exe :
"To start the sybase server, you can start the program dbsrv17.exe. Here you can choose your "SERVICENAME" and select the database to be reached."
see: https://forum.reportserver.net/viewtopic.php?id=881
I did not find dbsrv17.exe ... maybe it was used with old version 3.0.2-6

- here below is the detail of error message :

net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Query could not be executed: Incorrect syntax near the keyword 'dummy'. 
 at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:276)
 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.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:111)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl.testConnection(DatasourceTesterRPCServiceImpl.java:98)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.CGLIB$testConnection$0(<generated>)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45$$FastClassByGuice$$efc710cc.invoke(<generated>)
 at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.testConnection(<generated>)
 at sun.reflect.GeneratedMethodAccessor71.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.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.CGLIB$processCall$4(<generated>)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45$$FastClassByGuice$$efc710cc.invoke(<generated>)
 at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.processCall(<generated>)
 at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.CGLIB$processCall$5(<generated>)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45$$FastClassByGuice$$efc710cc.invoke(<generated>)
 at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.processCall(<generated>)
 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.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.CGLIB$service$18(<generated>)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45$$FastClassByGuice$$efc710cc.invoke(<generated>)
 at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
 at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
 at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
 at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$8ed6fa45.service(<generated>)
 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: com.sybase.jdbc4.jdbc.SybSQLException: Incorrect syntax near the keyword 'dummy'. 
 at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4113)
 at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3230)
 at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
 at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:302)
 at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:284)
 at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2656)
 at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2642)
 at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:280)
 at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
 at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:271)
 ... 89 more

Thanks for your support.

FLU73 · 2018-10-31 13:44:08

Hello,
I did some little progress these last days ... after going deeper in the administration guide & Blog.
The error listed above was due to the dummy query which is used to test the connection.
As there are different Sybase products,probably the Sybase datasource managed in ReportServer 3.0.4 is not convenient for my Sybase ASE 15.7.
Maybe the default Sybase connection would work fine with Sybase IQ, Sybase SQL Anywhere, ...

So, I tried to implement a new data source helper "DATASOURCE_HELPER_SYBASE2", by the mean of the Script tool available in the Enterprise Edition.
I've modified the Sybase groovy file that I found on the forum : http://www2.datenwerke.net/files/blog/2 … ort.groovy
(Replaced "Sybase" by "Sybase2" and the other modifications listed below)
Then put my new file in fileserver\bin and run the command : exec -g AddSybaseSupport2.groovy
The JAR file "Jconn4" for the Sybase driver have been put only in ...\apache-tomcat\webapps\reportserver\WEB-INF\lib

Now the connection test is successful but executing a query in a report return an error : "Query could not be executed: Incorrect syntax near '1'. "
In addition of error message there is a pop-up window where I see the columns of the table used for this report, but no rows are displayed.

The modifications in the groovy files are as follow :
.....
@Override
public String createDummyQuery() {
return 'select 1 MATNR from SAPSR3.MARA';
.....
This last line allows to have a successful connection test

other modifications are :
.....
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()); **instead of : buf.append(queryBuilder.getLimit() < 1 ? 1 : queryBuilder.getLimit()); I got an error due to "1" ("Query could not be executed: Incorrect syntax near '1'. ")**
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry");
....
and
....
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()); **instead of : buf.append(queryBuilder.getLimit() < 1 ? 1 : queryBuilder.getLimit());
buf.append(" START AT ");
buf.append(queryBuilder.getOffset() + 1); ** + 1 is the cause of the error, if I remove it I get an error due to a nul offset.
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry");

I also tried a variant with :
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2OffsetQuery;
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2LimitQuery;
....
and
....
@Override
public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
return new DB2LimitQuery(nestedQuery, queryBuilder);
}

@Override
public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
return new DB2OffsetQuery(nestedQuery, queryBuilder, columnNamingService);
}

I got an error message concerning "over".

As mentionned in the blog about adding firbird datasource : "ReportServers default implementation creates queries with LIMIT and OFFSET keywords at the end, so we need to provide a different implementation for firebird."
So, it seems to deal with pagination techniques, wich are not easy to manage with some of Sybase products.
I found some links about that :
https://stackoverflow.com/questions/426 … ybase-15-7
https://stackoverflow.com/questions/775 … pagination

But it's beyond my knowledge ....
Is it just a mistake in the syntax : queryBuilder.getOffset() + 1
or Offset implementation technique as discussed in the 2 links above ?

eduardo · 2018-11-02 12:36:27

Hi FLU73,

yes, we have tested sybase with sql anywhere.
What is the query you get where you have the syntax error? Maybe some sql profiler tool can show you the sql you are generating where you get the sql syntax error.

Regards,
Eduardo

FLU73 · 2018-11-08 16:39:37

Hi Eduardo,

My query was really simple : SELECT * FROM SAPSR3.KNA1

Is a tool like "prefix" could help to debug when you say "Sql profiler tool can show you the sql you are generating where you get the sql syntax error." ?
(https://stackify.com/prefix-download/)
I never used such tool.

As we can read on SAP forum : SELECT statement using functions and the Pagination Queries Using limit and offset feature have been introduced on SAP ASE 16.0 SP02 PL05
It's a pity for me as we are running on ASE 15.7

Alternative to limit and offset exist but too much technical for me.
These techniques are discussed here :
https://forums.whirlpool.net.au/archive/1226436
From what I understood there 2 possibilities : "cursor" and "temp tables"

An example with "cursors" is mentionned here
https://github.com/ifad/activerecord-sybase-adapter

An example with "temporary tables" is mentionned here
http://mdbitz.com/2009/10/05/pagination … -database/

May be this book could also help : Tips, Tricks & Recipes for Sybase ASE - 2nd edition (updated for ASE 15.0)
http://www.sypron.nl/ttr/
but I don't see really axplicit in the table of content
http://www.sypron.nl/ttr/ttr2_contents.pdf

Another option would be to investigate here :
http://my.isug.com/blog/jrnl/ar/paul-ve … ase-part-i

and maybe post on this "SAP technical user group" about the convenient code to do the same that in the one created for Syabase SQL Anywhere.

I dont't if there is any chance to get an answer as the script for the database helper is based on groovy, ....
In addition, it's required to have a subscription to get an acces to this user group.

ReportServer Forum

#1 2018-10-25 08:28:50

Sybase connection error :

#2 2018-10-31 13:44:08

Re: Sybase connection error :

#3 2018-11-02 12:36:27

Re: Sybase connection error :

#4 2018-11-08 16:39:37

Re: Sybase connection error :

Board footer