-
1. Re: Problem with calling a function from Oracle
rareddy Sep 25, 2009 3:45 PM (in response to rkishore999)Ram,
The problem here is Teiid handles the procedures different than the Functions. However there couple different options to solve this issue.
1) Depending upon the Oracle Driver you are using (specially with Data Direct) drivers, you can import functions as stored procedures and use them. However, when you use them in the "select" queries they need to be defined as the "sub-queries" that gets evaluated as a separate query than the main query, so the push down will not be supported along with the main query.
2) You can use what Teiid calls as "user defined functions" or UDFs. You can read more on this in Teiid documentation. Essentially, you would model a "UDF" function called "teiid_test" in the designer and mark it as the "must pushdown". What this does is, it establishes a Teiid level system function, but marked as "pushdown" to the source-connectors. In Designer this will produce a model file called "FunctionDefinitions.xmi". Check out Designer documentation for details. Once you are done you need to copy "FunctionDefinition.xmi" to "extensions" folder in the Teiid runtime.
Now, the respective connector needs to specify in its capabilities that this defined function is supported in order for engine to push this function to the connector. You can do this by editing the connector binding xml (ConfigurationInfo.def file inside the vdb under the respective "connector/properties" section), and provide a additional property called "getSupportedFunctions" and list all the supported functions by this connector. The problem here is that this is override function, so you would have to list all the functions that are in "OracleSQLTranslator" in addition to your own to function to correctly support all the functions. Otherwise you need to edit the "OracleSQLTranslator" and add your function support in there and patch the Teiid runtime with your changes.
I know the last part is ugly, we have several JIRA to make this process easy.
Let us know if this works and/or if you have any suggestions.
Thanks.
Ramesh.. -
2. Re: Problem with calling a function from Oracle
rkishore999 Sep 25, 2009 4:46 PM (in response to rkishore999)Ramesh,
Thanks for your prompt response. Does option 1 look something like this.select * from ( select TEIID_TEST('xxx') 'abc' from CAP_CASE_MV) a
I tried this. It didn't work.
I will try to work with option 2 and let you know. Meanwhile, can you please give me an example query of option 1 you were talking about?
Regards
Ram -
3. Re: Problem with calling a function from Oracle
shawkins Sep 25, 2009 5:09 PM (in response to rkishore999)Assuming NSGT.teiid_test is the fully qualified procedure name. Then if you just want a single invocation, you can use the exec syntax (optionally using named parameters):
exec NSGT.teiid_test('xxx')
or from a CallableStatement you can use the call syntax{? = call NSGT.teiid_test(?)}
If you want multiple results it could be called as a correlated subquery:select (exec NSGT.teiid_test(x.column)) from <some_table> as x
or it can even be invoked as a tableselect * NSGT.teiid_test where param = <some value>
See also http://docs.jboss.org/teiid/6.2/reference/en-US/html/sql_support.html#exec_command -
4. Re: Problem with calling a function from Oracle
rkishore999 Sep 25, 2009 7:51 PM (in response to rkishore999)Steve,
This is not working either. I have to qualify the function with schema as well as vdb something like "exec vdb_name.NSGT.teiid('XXX')" and then I get the following error:!ENTRY org.teiid.designer.dqp.ui 4 0 2009-09-25 14:32:28.484 !MESSAGE Connector worker process failed for atomic-request=3.30.2.187 !STACK 0 java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69) at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) !ENTRY org.teiid.designer.dqp.ui 4 0 2009-09-25 14:32:28.500 !MESSAGE Unexpected exception for request 3.30 !STACK 0 [MetaMatrixComponentException]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; 1 [AbstractMethodError]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89) at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110) at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69) ... 4 more !ENTRY net.sourceforge.sqlexplorer 4 4 2009-09-25 14:32:28.531 !MESSAGE Error processing query !STACK 0 com.metamatrix.jdbc.MMSQLException: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:123) at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:71) at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:433) at com.metamatrix.jdbc.MMStatement.execute(MMStatement.java:328) at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.processQuery(SqlExecProgress.java:192) at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.run(SqlExecProgress.java:121) at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121) Caused by: [MetaMatrixComponentException]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; 1 [AbstractMethodError]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89) at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110) at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData; at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69) ... 4 more
-
5. Re: Problem with calling a function from Oracle
rareddy Sep 26, 2009 8:27 PM (in response to rkishore999)What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.
Yes, you do have to fully qualify, but I do not think you need the VDB name.
Ramesh.. -
6. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 1:27 AM (in response to rkishore999)"rareddy" wrote:
2) You can use what Teiid calls as "user defined functions" or UDFs. You can read more on this in Teiid documentation. Essentially, you would model a "UDF" function called "teiid_test" in the designer and mark it as the "must pushdown". What this does is, it establishes a Teiid level system function, but marked as "pushdown" to the source-connectors. In Designer this will produce a model file called "FunctionDefinitions.xmi". Check out Designer documentation for details. Once you are done you need to copy "FunctionDefinition.xmi" to "extensions" folder in the Teiid runtime.
Now, the respective connector needs to specify in its capabilities that this defined function is supported in order for engine to push this function to the connector. You can do this by editing the connector binding xml (ConfigurationInfo.def file inside the vdb under the respective "connector/properties" section), and provide a additional property called "getSupportedFunctions" and list all the supported functions by this connector. The problem here is that this is override function, so you would have to list all the functions that are in "OracleSQLTranslator" in addition to your own to function to correctly support all the functions. Otherwise you need to edit the "OracleSQLTranslator" and add your function support in there and patch the Teiid runtime with your changes.
I know the last part is ugly, we have several JIRA to make this process easy.
Let us know if this works and/or if you have any suggestions.
Thanks.
Ramesh..
Ramesh,
I couldn't get first one going yet. Mean while Can you please give me some pointers about how to go with the 2nd option? What should I change in the Oracle SQLTranslator?
Regards
Ram -
7. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 1:55 AM (in response to rkishore999)"rareddy" wrote:
What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.
Yes, you do have to fully qualify, but I do not think you need the VDB name.
Ramesh..
Ramesh,
I'm using ojdbc14.jar. I infact tried with both classes12 and ojdbc14 jar files.
Regards
Ram -
8. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 2:45 AM (in response to rkishore999)"rkishore999" wrote:
"rareddy" wrote:
What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.
Yes, you do have to fully qualify, but I do not think you need the VDB name.
Ramesh..
Ramesh,
I'm using ojdbc14.jar. I infact tried with both classes12 and ojdbc14 jar files.
Regards
Ram
Ramesh,
It's not really the vdb name that it's looking to be qualified with. But it's model name. I tried running designer created vdb through the teiid server with no luck. I'm getting the following error when I try it out in the server.URL: mmfile:/C:/teiid/extensions/ojdbc14.jar com.metamatrix.jdbc.MMSQLException: Error Code:17023 Message:Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:123) at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:71) at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:442) at com.metamatrix.jdbc.MMStatement.executeQuery(MMStatement.java:348) at dk.client.JDBCClient.execute(JDBCClient.java:270) at dk.client.JDBCClient.main(JDBCClient.java:229) Caused by: [MetaMatrixComponentException] 17023: Error Code:17023 Message:Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} 1 [ConnectorException]Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} 2 [ConnectorException]Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} 3 [SQLException]Unsupported feature at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89) at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110) at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160) at com.metamatrix.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:129) at com.metamatrix.query.eval.Evaluator.evaluate(Evaluator.java:651) at com.metamatrix.query.eval.Evaluator.internalEvaluate(Evaluator.java:556) at com.metamatrix.query.eval.Evaluator.evaluate(Evaluator.java:514) at com.metamatrix.query.processor.relational.ProjectNode.updateTuple(ProjectNode.java:232) at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:192) at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261) at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110) at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: org.teiid.connector.api.ConnectorException: Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.handleError(ConnectorWorkItem.java:235) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:174) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69) ... 4 more Caused by: org.teiid.connector.api.ConnectorException: Error Code:17023 Message:{ ?= call TEIID_TEST(?)} Executing statement: {1} at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:92) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277) at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157) ... 6 more Caused by: java.sql.SQLException: Unsupported feature at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227) at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:537) at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(OraclePreparedStatement.java:9086) at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85) ... 8 more select (exec rowcol.NSGT.maestro_test('xyz')) ZZZ from CSP_CASE limit 1
Regards
Ram -
9. Re: Problem with calling a function from Oracle
shawkins Sep 27, 2009 9:13 AM (in response to rkishore999)Hi Ram,
getParameterMetaData works fine with the ojdbc6.jar (11.2.0.1.0) client jar. From a quick search, it seems like this was an Oracle problem until some release of ojdbc5.jar.
Thanks, -
10. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 11:35 AM (in response to rkishore999)Teve,
I have used ojdbc6.jar and still see the same error.
Regards
Ram -
11. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 11:45 AM (in response to rkishore999)Hi Steve,
I have used ojdbc6.jar. But I'm getting the same error still.
Regards
Ram -
12. Re: Problem with calling a function from Oracle
rkishore999 Sep 27, 2009 3:54 PM (in response to rkishore999)Steve/Ramesh,
I got it working but as non-pushdown function. I have created a FunctionDefinitions.xmi with non-pushdown function and created the function method in a class which in turn called the source function from Oracle.
I think this is not the right way to do. But I can keep going with this work around. I still want to keep working with the original issue. Thanks a lot for your valuable cooperation.
Regards
Ram -
13. Re: Problem with calling a function from Oracle
rareddy Sep 28, 2009 10:40 AM (in response to rkishore999)You could try building a test program which uses "getParameterMetaData()" call and use with both the driver versions. Be sure to test both function as well as procedure and see the results. That will surely tell you what is supported by your Oracle driver.
-
14. Re: Problem with calling a function from Oracle
rareddy Sep 28, 2009 11:54 AM (in response to rkishore999)For make your function "pushdown" do the following
1) In the Designer, in the "FunctionDefinitions.xmi" model, select your function, and in the properties tab, change the property under "Misc/Push Down" to "REQUIRED".
2) Save the model, and copy to the "FunctionDefinitions.xmi" file to "extensions" folder.
3) If you want to take "non" coding approach, edit the "ConfigurationInfo.def" file in your VDB, and add following property.<Connector Name="My Connector" ComponentType="Oracle Connector"> <Properties> <Property Name="getSupportedFunctions">+,-,*,/,ABS,ACOS,ASIN,ATAN,ATAN2,COS,EXP,FLOOR,CEILING,LOG,LOG10,MOD,POWER,SIGN,SIN,SQRT,TAN,ASCII,CHAR,CHR,CONCAT,||,INITCAP,LCASE,LENGTH,LEFT,LOCATE,LOWER,LPAD,LTRIM,REPLACE,RPAD,RIGHT,RTRIM,SUBSTRING,TRANSLATE,UCASE,UPPER,HOUR,MONTH,MONTHNAME,YEAR,DAY,DAYNAME,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,QUARTER,MINUTE,SECOND,QUARTER,WEEK,CAST,CONVERT,IFNULL,NVL,COALESCE,sdo_relate,sdo_nn,sdo_filter,sdo_nn_distance,sdo_within_distance,test_teiid</Property> Other Properties.. </Properties> </Connector>
4) Notice the your function at the end.
If you want to do programatically,
1) Extend "org.teiid.connector.jdbc.oracle.Oraclepabilities" class, overload the "getSupportedFunctions()" method, for example the new class name is "MyOracleCapabilities"@Override public List<String> getSupportedFunctions() { List<String> supportedFunctions = new ArrayList<String>(); supportedFunctions.addAll(super.getSupportedFunctions()); supportedFunctions.add("teiid_test"); return supportedFunctions; }
2) Now, you need to configure the Teiid to use the "MyOracleCapabilities" class, instead of default "OracleCapabilities" class, to do that edit the "ConfigurationInfo.def" file and following property to the connector binding.<Connector Name="My Connector" ComponentType="Oracle Connector"> <Properties> <Property Name="ExtensionCapabilityClass">MyOracleCapabilities</Property> <Property Name="ConnectorClassPath">extensionjar:patch.jar;extensionjar:ojdbc6.jar</Property> Other Properties.. </Properties> </Connector>
3) Provide the new class in a jar file (patch.jar) in "extensions" directory, and update the "ConfigurationInfo.def" as shown above with the "ConnectorClassPath" property.
4) restart.
above both techniques should work for your scenario.