-
1. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
blafond Jul 27, 2010 8:44 AM (in response to rakeshsagar)Rakesh,
1) Locate your FunctionDefinitions.xmi file in your "workspace/.metadata/.plugins/org.teiid.designer.udf/org.teiid.designer.udf" folder
2) Copy and paste this file into the Model Project folder location on your file system. (or DND it from your file explorer to the project folder in Designer)
3) Select your project folder and perform "Refresh"
4) Open/Edit your VDB
5) Select "Add" button and browse to your project folder and select "FunctionDefinitions.xmi" model
6) "FunctionDefinitions.xmi" should now be added to your VDB.
7) Go out to file system and delete the Copy/Pasted version of your "FunctionDefinitions.xmi". Designer can't handle 2 exact versions of this model and the UDF's view contains the real version (note this will all change in 7.1... see below)
Function models will return in 7.1 as first-class models which can be created, edited and added to VDB's just like any other model. In addition you can have multiple function models in your workspace and you aren't limited to the single name "FunctionDefinitions.xmi".
Barry LaFond
Teiid Designer Project
-
2. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Jul 27, 2010 9:13 AM (in response to blafond)Thanks for the infor Barry.
I had successfully added the FunctionDefinitions.xmi to the VDB and deployed the VDB.
But when I connect to JBoss using Database Development Perspective and view the sources, I am unable to find the Function which I had created in the view.
Can you please suggest on how to view the UserDefinedFunction and how to call that function in the SQL Scrap Book
Thanks
Rakesh
-
3. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Jul 27, 2010 10:40 AM (in response to rakeshsagar)Rakesh,
As Barry mentioned, the support for User Defined Function (UDF) definitions in Designer is coming in 7.1 in a integrated way where you can create and add and deploy etc. through Designer. Mean while if you want to test your VDB, you need to go through some manual steps in configuring the VDB correctly and then deploying it.
1) You add the "FunctionDefinitions.xmi" file per Barry's instructions.
2) Now edit the "vdb.xml" file inside your VDB in "META-INF" directory and add the following XML code fragment.
<model name="FunctionDefinition" type="FUNCTION" path="FunctionDefinition.xmi"/>
note "path" is relative to the vdb file.
3) Now save this file, and make sure that the edited file is inside the VDB. Note VDB is a simple zip file with different extension.
4) Add your Java library (JAR file) that you wrote your function to "lib" directory of the JBoss AS. For ex: "<jboss-as>/server/default/lib"
5) Start your JBoss AS
6) Deploy your VDB and query.
That should work.
Ramesh..
-
4. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
blafond Jul 27, 2010 10:55 AM (in response to rareddy)Rakesh,
The steps I provided should have already edited the "vdb.xml" file, so you should only have to perform Ramesh's steps 4, 5 and 6.
Barry
-
5. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Jul 28, 2010 3:37 AM (in response to blafond)Ramesh/Barry,
I had followed the steps you have mentioned and deployed the VDB to JBoss.
When I try to execute the Function using the following query the get an error
select celsiusToFahrenheit(36.5) from Account
Error Code:ERR.015.008.0039 Message:Remote org.teiid.api.exception.query.QueryResolverException: Error Code:ERR.015.008.0039 Message:The function 'celsiusToFahrenheit(36.5)' is an unknown form. Check that the function name and number of arguments is correct.
Please help
Thanks
Rakesh
-
6. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Aug 2, 2010 2:18 AM (in response to blafond)Hi Ramesn,
Please help me in resolving the error.
-
7. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Aug 2, 2010 10:58 AM (in response to rakeshsagar)Rakesh,
Teiid runtime is not able to resolve your "celsiusToFahrenheit(36.5)", this may be because you XMI file is not included in the VDB or not correctly configured. Can you post the contents of your vdb.xml file inside your VDB file?
Thanks.
Ramesh.. -
8. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Aug 2, 2010 11:07 AM (in response to rareddy)Hi Ramesh,
The following is the vdb.xml file
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb version="1" name="SecAdmin">
<model visible="true" type="PHYSICAL" name="Oracle" path="/TeiidDemo/Oracle.xmi">
<property value="3782716273" name="checksum"/>
<property value="false" name="builtIn"/>
<property value="4257944198.INDEX" name="indexName"/>
<source translator-name="oracle" connection-jndi-name="d7bda03c-babe-4f8a-8547-f474cd0fd791__TeiidDemo_Oracle" name="Oracle"/>
</model>
<model visible="true" type="VIRTUAL" name="Oracle_DerbyView" path="/TeiidDemo/Oracle_DerbyView.xmi">
<property value="3728161245" name="checksum"/>
<property value="false" name="builtIn"/>
<property value="3247640314.INDEX" name="indexName"/>
<property value="/TeiidDemo/Derby.xmi" name="imports"/>
<property value="/TeiidDemo/Oracle.xmi" name="imports"/>
<validation-error path="Product_Region/Prd_ID" severity="WARNING">Missing or invalid Precision on column with a numeric datatype (See validation Preferences)</validation-error>
<validation-error path="Product_Region" severity="WARNING">Possible cross-join: Group/s '[Oracle.HR.REGIONS, Derby.ADMIN.PRODUCT]' are not joined either directly or transitively to other groups through a join criteria. Check all queries in the transformation.</validation-error>
<validation-error path="getCount/NewProcedureResult/success" severity="WARNING">Missing or invalid Precision on column with a numeric datatype (See validation Preferences)</validation-error>
<validation-error path="Product_Region/Region_ID" severity="WARNING">Missing or invalid Precision on column with a numeric datatype (See validation Preferences)</validation-error>
</model>
<model visible="true" type="VIRTUAL" name="OracleView" path="/TeiidDemo/OracleView.xmi">
<property value="434717216" name="checksum"/>
<property value="false" name="builtIn"/>
<property value="4175173737.INDEX" name="indexName"/>
<property value="/TeiidDemo/Oracle.xmi" name="imports"/>
<validation-error path="Job_Region/Region" severity="WARNING">Missing or invalid Precision on column with a numeric datatype (See validation Preferences)</validation-error>
<validation-error path="Job_Region" severity="WARNING">Possible cross-join: Group/s '[Oracle.HR.REGIONS, Oracle.HR.JOBS]' are not joined either directly or transitively to other groups through a join criteria. Check all queries in the transformation.</validation-error>
</model>
<model visible="true" type="FUNCTION" name="FunctionDefinitions" path="/TeiidDemo/FunctionDefinitions.xmi">
<property value="3022741943" name="checksum"/>
<property value="false" name="builtIn"/>
<property value="4276406684.INDEX" name="indexName"/>
</model>
<model visible="true" type="PHYSICAL" name="Derby" path="/TeiidDemo/Derby.xmi">
<property value="235117127" name="checksum"/>
<property value="false" name="builtIn"/>
<property value="3947045235.INDEX" name="indexName"/>
<source translator-name="derby" connection-jndi-name="d7bda03c-babe-4f8a-8547-f474cd0fd791__TeiidDemo_Derby" name="Derby"/>
</model>
</vdb>Thanks
Rakesh
-
9. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Aug 2, 2010 11:38 AM (in response to rakeshsagar)hmm, check to make sure your VDB has the "/TeiidDemo/FunctionDefinitions.xmi" file inside it. Also, make sure the signature you have for the function "celsiusToFahrenheit", in XMI file match to the SQL usage. Is JAR in lib directory?
Also, are you seeing this after deploying the VDB into runtime, and querying from JDBC client or execution inside the Designer? Inside the Designer, the tooling as I mentioned is not complete so may not work.
Ramesh..
-
10. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Aug 3, 2010 2:46 AM (in response to rareddy)Hi Ramesh,
The VDB has the /TeiidDemo/FunctionDefinitions.xmi file inside it and alos the signature is correct.
I JAR file is inside JBOSS_HOME/server/default/lib directory
I get the error when I use the Designer and JDBC connections.
I am attaching the VDB used for your reference as it will be helpful for you.
Thanks
Rakesh
-
SecAdmin.vdb.zip 29.4 KB
-
-
11. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Aug 3, 2010 10:52 AM (in response to rakeshsagar)Rakesh,
Thanks for attaching the VDB. There are couple issues I found
- On your Function "celciusToFahrenheit", you have not specified the "invocation class" and "invocation method" fields. These define which java routine to invoke at runtime.
- https://jira.jboss.org/browse/TEIID-1182. We will try to fix this by 7.1 final, mean while you can manually copy the "FunctionDefinition.xmi" file in the root location of the VDB and edit the "vdb.xml" and correct the "path" attribute to point correct location.
Thanks
Ramesh..
-
12. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Aug 3, 2010 3:34 PM (in response to rareddy)Rakesh,
I have this issue fixed, however unfortunately it will only make it to the upcoming "7.1.0-CR2" build. If you want can build the Teiid server locally from the source from the trunk.
If not follow the workaround I mentioned in the previous message until CR2 build is out.
Thank you.
Ramesh..
-
13. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rakeshsagar Aug 4, 2010 9:40 AM (in response to rareddy)Hi Ramesh,
Sorry for bothering you again
I followed the steps you have mentioned and getting the following error. Alos find the attached VDB File.
2010-08-04 19:02:14,718 WARN [org.teiid.PROCESSOR] (WorkManager(2)-3_QueryProcessorQueue1) Processing exception 'Error Code:ERR.015.008.0039 Message:The function 'celsiusToFahrenheit(36.0)' is an unknown form. Check that the function name and number of arguments is correct.' for request nRmV1UThStS2.1. Exception type org.teiid.api.exception.query.QueryResolverException thrown from org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:447). Enable more detailed logging to see the entire stacktrace.
2010-08-04 19:02:14,733 WARNING [org.teiid.client.sockets] (http-127.0.0.1-8080-3) Unable to read: unexpected exception
java.lang.ClassCastException: org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:257)
at java.io.ObjectInputStream.readExternalData(ObjectInputStream.java:1792)
at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1751)
at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1329)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:351)
at org.teiid.net.socket.Message.readExternal(Message.java:51)
at java.io.ObjectInputStream.readExternalData(ObjectInputStream.java:1792)
at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1751)
at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1329)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:351)
at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:95)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:345)
at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:122)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:265)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:353)
at org.teiid.jdbc.StatementImpl.sendRequestMessageAndWait(StatementImpl.java:850)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:413)
at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:329)
at com.ca.teiid.server.TeiidSampleServiceImpl.executeQuery(TeiidSampleServiceImpl.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:562)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:188)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:224)
at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
2010-08-04 19:02:14,733 SEVERE [org.teiid.jdbc] (http-127.0.0.1-8080-3) Error trying to execute a statement select celsiusToFahrenheit(36.0) from account.
org.teiid.jdbc.TeiidSQLException: [SingleInstanceCommunicationException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
1 [ClassCastException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:109)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:69)
at org.teiid.jdbc.StatementImpl.sendRequestMessageAndWait(StatementImpl.java:852)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:413)
at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:329)
at com.ca.teiid.server.TeiidSampleServiceImpl.executeQuery(TeiidSampleServiceImpl.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:562)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:188)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:224)
at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.util.concurrent.ExecutionException: [SingleInstanceCommunicationException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
1 [ClassCastException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
at org.teiid.client.util.ResultsFuture.convertResult(ResultsFuture.java:100)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.convertResult(SocketServerInstanceImpl.java:323)
at org.teiid.client.util.ResultsFuture.get(ResultsFuture.java:116)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:354)
at org.teiid.jdbc.StatementImpl.sendRequestMessageAndWait(StatementImpl.java:850)
... 33 more
Caused by: [SingleInstanceCommunicationException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
1 [ClassCastException]org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
at org.teiid.net.socket.SocketServerInstanceImpl.exceptionOccurred(SocketServerInstanceImpl.java:212)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:269)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:353)
... 34 more
Caused by: java.lang.ClassCastException: org.teiid.api.exception.query.QueryResolverException cannot be cast to org.teiid.core.TeiidException
at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:257)
at java.io.ObjectInputStream.readExternalData(ObjectInputStream.java:1792)
at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1751)
at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1329)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:351)
at org.teiid.net.socket.Message.readExternal(Message.java:51)
at java.io.ObjectInputStream.readExternalData(ObjectInputStream.java:1792)
at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1751)
at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1329)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:351)
at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:95)
at java.io.ObjectInputStream.readObject(ObjectInputStream.java:345)
at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:122)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:265)
... 35 more-
SecAdmin.vdb.zip 29.5 KB
-
-
14. Re: How to add FunctionDefinitions.xmi to VDB using Designer 7.0
rareddy Aug 4, 2010 11:33 AM (in response to rakeshsagar)Not quite right, my suggestion to work around was to put it in the root of the VDB file. See attached file.
I apologize for the bug. I thank your patience for trying this feature out, but rest assured in the next release this feature will work as advertised.
Thanks you.
Ramesh..
-
SecAdminNew.vdb.zip 29.6 KB
-