SmartGWT 6 with Vertica DB

[pankaj.jain]

Hi,

We are using SmartGWT version 6.1 and are trying to connect to Vertica database via the following settings in server.properties

sql.Vertica.database.type:generic
sql.Vertica.driver.networkProtocol:tcp
sql.Vertica.interface.credentialsInURL:true
sql.Vertica.driver.url:jdbc:vertica://xxx-xxxxx-xxxxxx.com:5433/xxxx?user=user1&password=pwd&schema=schemaName
sql.Vertica.driver.context:schemaName.
sql.Vertica.driver.name:java:comp/DefaultDataSource
sql.Vertica.interface.type:driverManager
sql.Vertica.driver:com.vertica.jdbc.Driver
sql.Vertica.driver.autoConfigured:true

Vertica JDBC driver does not support Scrollable Resultset, but whatever queries we are running are trying to get Scrollable Resultset which fails.
Is there a way it can be tackled on SmartGWT side? If not how can we use FORWARD_ONLY fetch statement?

[Isomorphic]

Take a look at operationBinding.sqlStrategy - the simpler ones require less capabilities from the DB. Let us know if you find one that Vertica can actually handle.

[manishsahu]

Hi, We tried different combinations to get the Paging like behavior on server side but it results in same error..

Error:

Stack Trace: com.sabre.apd.mi.smartgwt.server.logger.LoggingFault: Failed while running the report Query
at com.sabre.apd.mi.smartgwt.server.reports.ReportsDMI.fetch(ReportsDMI.java:60)
at com.sabre.apd.mi.smartgwt.server.reports.ReportsDMI$$FastClassByCGLIB$$af1f53cd.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:692)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at com.sabre.apd.mi.smartgwt.server.logger.FileLoggingInterceptor.interceptMethodInvocation(FileLoggingInterceptor.java:59)
at com.sabre.apd.mi.smartgwt.server.logger.WebAppFileLoggingInterceptor.interceptMethodInvocation(WebAppFileLoggingInterceptor.java:19)
at com.sabre.apd.mi.smartgwt.server.logger.FileLoggingInterceptor.invoke(FileLoggingInterceptor.java:24)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at com.sabre.apd.mi.smartgwt.server.logger.DBLoggingInterceptor.invokeUnderTrace(DBLoggingInterceptor.java:24)
at org.springframework.aop.interceptor.AbstractTraceInterceptor.invoke(AbstractTraceInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625)
at com.sabre.apd.mi.smartgwt.server.reports.ReportsDMI$$EnhancerByCGLIB$$c8f490a5.fetch(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:966)
at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:428)
at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:64)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2781)
at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:230)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:167)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:260)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:93)
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:106)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: [Vertica][JDBC](10500) Invalid operation for forward only ResultSets.
at com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.vertica.jdbc.common.SForwardResultSet.absolute(Unknown Source)
at org.apache.commons.dbcp.DelegatingResultSet.absolute(DelegatingResultSet.java:373)
at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2662)
at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1711)
at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:433)
at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:378)
at com.isomorphic.datasource.DataSource.execute(DataSource.java:2226)
at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:735)
at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:652)
at com.isomorphic.application.AppBase.execute(AppBase.java:493)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2785)
at com.sabre.apd.mi.smartgwt.server.reports.ReportsDMI.fetch(ReportsDMI.java:49)
... 55 more

ListGrid.setFetchMode(FetchMode.PAGED) always fails with above error, no matter what SQLPagingStrategy we specify in OperationBinding in ds.xml.
ListGrid.setFetchMode(FetchMode.BASIC) with setting SQLPagingStrategy as “sqLlimit” or “jdbcScroll” loads all data at one shot which we don’t want.

Kindly suggest how to achieve loading further pages only when user Scrolls down the grid with Custom Paging..

[Isomorphic]

When you say "no matter what SQLPagingStrategy we specify", we presume you haven't tried "dropAtServer"? The stacktrace you show above indicates that "jdbcScroll" was in use, and that strategy does require a scrollable resultset. "sqlLimit" can never work for a generic database, because it is inherently dependent upon the idiosyncracies of specific, known database products; if you had an operation binding that specified "sqlLimit" for this database, the framework would have fallen back to "jdbcScroll" (and warned you about it in the logs). "dropAtServer" simply calls resultSet.next() repeatedly until it reaches the record it is after - this strategy will definitely work with a forward-only resultset, though performance may be an issue depending on how many rows it has to read and discard to reach the page of data it needs.

[manishsahu]

The amount of data we are retrieving can be huge and memory becomes a challenge too using "dropAtServer". Since Vertica supports sqlLimit, we tried to write a dbType implementer which returns true for supportsSqlLimit. We used SQLDriver.iscImplementerForType to register it, but is there a recommended way to register custom dbTypes?

[Isomorphic]

There is no way to register a custom SQL generator as the needs are quite arcane (different subversions of SQLServer for one have different bugs). We do offer a Feature Sponsorship program, which would allow you to get official support for Vertica, or perhaps something of less scope, such as allowing a SQL template for how the "limit" feature is implemented when using the "generic" SQL driver.