I'm trying to build a simple SQL using an Oracle Database (ojdbc14-10.2.0.3.0.jar - oracle.jdbc.xa.client.OracleXADataSource).

The SQL " select * from dual where 1=? "

In the Input Schema, after a Retrieve click:
Error getting Input Schema for Read DB Task: java.sql.SQLException: Unsupported feature

In ED log file:
2017-04-28 10:39:05,567 [WebContainer : 4] EngagementDesigner ERROR - EngagementDesigner-3.1.0.2.013102007 - Error getting Input Schema for Read DB Task
java.sql.SQLException: Unsupported feature
at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMetaData.java:198)
at com.avaya.workflow.base.db.dataaccess.DataAccessAgent.getParameterMetaData(DataAccessAgent.java:178)
at com.roobroo.bpm.task.integration.DBTaskPropertyHandler.getInputSchema(DBTaskPropertyHandler.java:132)
at com.roobroo.bpm.task.integration.DBTaskPropertyHandler.getInputDBSchema(DBTaskPropertyHandler.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at com.roobroo.bpm.util.MyService.executeClicker(MyService.java:125)
at Proxy99a6f25d_b335_4c5f_ac1f_86f8b2748aff.executeClicker(Unknown Source)
at Proxy99a6f25d_b335_4c5f_ac1f_86f8b2748aff.executeClicker(Unknown Source)
at Proxy99a6f25d_b335_4c5f_ac1f_86f8b2748aff.executeClicker(Unknown Source)
at com.roobroo.bpm.util.TaskPropertyClicker.executeClicker(TaskPropertyClicker.java:20)
at com.roobroo.bpm.util.EJSLocalNSLTaskPropertyClicker_00cbef0c.executeClicker(EJSLocalNSLTaskPropertyClicker_00cbef0c.java)
at com.roobroo.bpm.util.EJSProxy$$TaskPropertyClicker.executeClicker(Unknown Source)
at com.avaya.workflow.ui.servlet.WorkflowServlet.doPropertyClick(WorkflowServlet.java:505)
at com.avaya.workflow.ui.servlet.WorkflowServlet.doPost(WorkflowServlet.java:172)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:595)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1230)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:136)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:97)
at com.ibm.ws.osgi.javaee.extender.runtime.web.OsgiDirectoryProtectionFilter.doFilter(OsgiDirectoryProtectionFilter.java:101)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.avaya.ems.mgmt.quantum.filter.SsoFilter.postSingleSignOnProcessor(SsoFilter.java:185)
at com.avaya.ems.mgmt.quantum.filter.SsoFilter.handleActiveSession(SsoFilter.java:212)
at com.avaya.ems.mgmt.quantum.filter.SsoFilter.performSso(SsoFilter.java:146)
at com.avaya.ems.mgmt.quantum.filter.SsoFilter.doFilter(SsoFilter.java:95)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:964)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1104)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:87)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:914)
at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1662)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture$1.run(AsyncChannelFuture.java:205)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1865)


ED version: 3.1.0.2.013102007

Breeze version:
Avaya Aura Engagement Development Platform Software Version Inventory

Release: 3.1.1.0.311006
Revision: 16544
Build: 38
Patch: 3.1.1.0.12311006
Call Processing: Not Installed
Management: 7.0.0.1.700101
SM Tools: 7.0.0.1.700101
DRS: 7.0.0.1.700101
CDR: Not Installed
NCS: Not Installed
Security Module: 7.0.0.1.700101
nginx: 1.8.0
Firewall: 7.0.0.1.700101
WebSphere AS: 8.5.5.4
SAL Agent: 7.0.0.9
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
VMware Tools: 9.4.11.42879 (build-2400950)
SDK API Version: 3.1.1

Regarding the query "SELECT * FROM DUAL WHERE 1=?" - what does the WHERE clause '1=?' do? Technically, this should be "WHERE =?" not "a constant=". If we assume a column name as "column1" then the query should be, "SELECT * FROM DUAL column1=?"

But if you can give the ful 'dual' table's description, it may be easier to triage... And the workflow also. Thanks!

A where clause with 1=? means that if you receive 1 it will return everything, another value will return nothing.

This is just an example, nothing works with question mark (?).

"select * from dual where dummy=?" results in error.

Please check the JDBC Source configuration and ensure that you have the custom property "generateSimpleParameterMetadata" set to "true".

After making configuration changes in JDBC Source, you would need to reboot the all servers in the cluster ( There is a warning message on the GUI).

Bill.Liu wrote:Please check the JDBC Source configuration and ensure that you have the custom property "generateSimpleParameterMetadata" set to "true".

The JDBC Source was configured since the creation with this parameter.
I just reboot the server, to see if there is some trouble that needs reboot, but still the same error.

I'm trying to use the ojdbc7.jar now, like the documentation, same error.

From Oracle, I got a paper in Metalink saying that this function was never implemented by Oracle, and has no plans to do so.

Hi, Please try to use the database that you have configured with your data-source. I hope this might solve your problem. If problem still persists then please share screenshots of your Datasource , Jndi configurations and read db task.


Thanks

Datasource configuration:
https://avaya.sharefile.com/d-s1e9cae786a44814b

Datasource test:
https://avaya.sharefile.com/d-s1f24b3646094700a

Query without parameter (input schema retrieves with success):
https://avaya.sharefile.com/d-s32d69feff514989a

Query with parameter (input schema doesn't retrieve)
https://avaya.sharefile.com/d-s35235bc2d8447e19

ojdbc7.jar is the one that had been tested by Avaya verification team and the DB tasks works properly with the Oracle DB during the testing.

BTW, when you change the driver, please follow the in instructions to install the new JDBC provider and create a new JDBC source to use it. besides, please do not forget to reboot the server.

Can you try a query with the real column name first, for example, "select firstname from customertable where phone=?" ?

Besides, the "SQLException: Unsupported feature....." error basically indicates that it is not a problem of the DB task. It is either the driver or the DB server that does not support the "generateSimpleParameterMetadata" feature with that query.

I tested a dummy table, with column "a".
Without the question mark it works fine. With the question mark, same error.

Hi, Please try setting ResultSetMetaDataOptions = 1 on JDBC datasource editor. Test the connection and restart.