1 2 Previous Next 20 Replies Latest reply on Oct 4, 2012 1:58 PM by yjma2001

    SQL functions

    Samier Nadji Newbie



      I am trying to use an SQL function in a dynamic VDB (Teiid 8.1) and getting the following error:

      The function 'mssqlsource.mydb.dbo.myfunction(param)' is an unknown form.  Check that the function name and number of arguments is correct.


      I tried defining the function in the source model by adding something like:

              <metadata type="NATIVE,DDL"><![CDATA[

                 CREATE FUNCTION SCORE (integer val) RETURNS (integer score);



      but that caused the following error:

      13:16:47,554 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 3) TEIID50036 VDB myvdb.1 model "mssqlsource" metadata failed to load. Reason:TEIID30386 org.teiid.query.parser.ParseException: Encountered "integer" at line 2, column 35.

      Was expecting one of:

          "in" ...

          "inout" ...

          "out" ...


      Is there something else I am missing?


        • 1. Re: SQL functions
          Ramesh Reddy Master

          That looks OK to me. Can you post the whole VDB?

          • 2. Re: SQL functions
            Steven Hawkins Master

            It should be identifier type, not type identifier - so

            {code}create function score (val integer) returns (score integer);{code}

            • 3. Re: SQL functions
              Samier Nadji Newbie

              After reversing the identifier/type, I get the following error:


              15:07:16,995 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID50036 VDB functions.1 model "mssqlsource" metadata failed to load. Reason:TEIID30386 org.teiid.query.parser.ParseException: Function myfunction is not valid.  Functions can not support result set return parameters, and must have a scalar return parameter.




              My VDB looks something like this:

              <vdb name="functions" version="1">

                <property name="UseConnectorMetadata" value="cached" />  

                <model visible="true" type="PHYSICAL" name="mssqlsource">

                  <property name="importer.useFullSchemaName" value="true"/>

                  <source name="mssqlserver1" translator-name="sqlserver" connection-jndi-name="java:/mssqlserver1"/>    

                  <metadata type="NATIVE,DDL"><![CDATA[

                         create function myfunction(val varbinary) returns (score varchar);





                <model visible="true" type="VIRTUAL" name="majic">

                  <metadata type="DDL"><![CDATA[

                    CREATE VIEW myview (               

                        FunctionResult string


                        AS  SELECT

                        mssqlsource.mydb.dbo.myfunction(my_source_table.col1) AS FunctionResult              


                        mssqlsource.mydb.dbo.table1 my_source_table         






              And the SQL function has the following signature:

              ALTER FUNCTION [dbo].[myfunction](@s varbinary(16))

              RETURNS varchar(64)

              • 4. Re: SQL functions
                Steven Hawkins Master

                Yes, I should have noticed that.  Using returns with a paren enclosed element list, with or without a preceeding table keyword indicates that a result set is being returned, which is only valid for procedures, so "returns (score integer)" is the same as "returns table (score integer)".  You'll want simply "returns integer".

                • 5. Re: SQL functions
                  Samier Nadji Newbie

                  I tried that and got:


                  17:56:52,747 ERROR [org.jboss.threads.executor] (teiid-async-threads - 1) Task execution failed for task org.teiid.jboss.VDBService$6@1625e29: org.teiid.core.TeiidRuntimeException: TEIID30389 Unexpected exception while loading "null.null" for UDF "myfunction(varbinary val) : string result"

                    at org.teiid.query.function.FunctionTree.createFunctionDescriptor(FunctionTree.java:325) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.query.function.FunctionTree.addFunction(FunctionTree.java:232) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.query.function.FunctionTree.<init>(FunctionTree.java:106) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.deployers.CompositeVDB.buildTransformationMetaData(CompositeVDB.java:84) [teiid-runtime-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.deployers.CompositeVDB.metadataLoadFinished(CompositeVDB.java:333) [teiid-runtime-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.deployers.VDBRepository.finishDeployment(VDBRepository.java:287) [teiid-runtime-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.runtime.AbstractVDBDeployer.metadataLoaded(AbstractVDBDeployer.java:166) [teiid-runtime-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.jboss.VDBService.access$800(VDBService.java:89) [teiid-jboss-integration-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.jboss.VDBService$6.run(VDBService.java:385) [teiid-jboss-integration-8.1.0.Final.jar:8.1.0.Final]

                            at org.jboss.threads.SimpleDirectExecutor.execute(SimpleDirectExecutor.java:33)

                            at org.jboss.threads.QueueExecutor.runTask(QueueExecutor.java:801)

                            at org.jboss.threads.QueueExecutor.access$100(QueueExecutor.java:45)

                            at org.jboss.threads.QueueExecutor$Worker.run(QueueExecutor.java:842)

                            at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_23]

                            at org.jboss.threads.JBossThread.run(JBossThread.java:122)

                  Caused by: java.lang.NullPointerException

                            at java.lang.Class.forName0(Native Method) [rt.jar:1.6.0_23]

                            at java.lang.Class.forName(Class.java:247) [rt.jar:1.6.0_23]

                            at org.teiid.query.function.UDFSource.getInvocationClass(UDFSource.java:44) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                            at org.teiid.query.function.FunctionTree.createFunctionDescriptor(FunctionTree.java:311) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                            ... 14 more

                  • 6. Re: SQL functions
                    Steven Hawkins Master



                    Sorry, that exception needs to be made clearer and for walking this through one error at a time.  The next issue is that a non-foreign function is expected to be backed by a java method.  Of course yours does not have the extension metadata declaring the class/method.  Use:


                    {code}create foreign function myfunction(val varbinary) returns varchar;{code}


                    Then Teiid will know you only indend to call that fuction on your source.



                    • 7. Re: SQL functions
                      Samier Nadji Newbie



                      Applying this change got the VDB loading!  


                      But I think I have one more question/issue. How can I specify the full schema path to my function? 


                      I am getting the following errors trying to get data using my function:


                      10:12:09,051 WARN  [org.teiid.CONNECTOR] (Worker26_QueryProcessorQueue575) Connector worker process failed for atomic-request=EbUSxp5XvSI/.5.0.62: org.teiid.translator.jdbc.JDBCExecutionException: 195 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 g_0."col1" AS c_0, (g_0."last_name") AS c_1, myfunction(g_0."col1") AS c_2 FROM "mydb"."mypath"."my_source_table" g_0]

                                at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:88)

                                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:261) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:425) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:170) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:167) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_23]

                                at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_23]

                                at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

                                at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_23]

                                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [rt.jar:1.6.0_23]

                                at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_23]

                      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 'myfunction' is not a recognized built-in function name.

                                at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

                                at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)




                      Doesn't look like I can specify the path in the metadata section like:

                      create foreign function mydb.mypath.myfunction(val varbinary) returns varchar;


                      And in the view definition, I can only reference myfunction by doing mysource.myfunction()  -- not using full path.



                      • 8. Re: SQL functions
                        Ramesh Reddy Master

                        Can you use it by mysource.myfunction() format? where "mysource" is the name of your model and rest is the function name

                        • 9. Re: SQL functions
                          Samier Nadji Newbie

                          If I use mysource.myfunction(), the VDB parses and loads OK. But when I use squirrel to access the VDB, I get the exception I listed above.


                          BTW, That's the only format I've been able to use to get the VDB to load.

                          • 10. Re: SQL functions
                            Ramesh Reddy Master



                            By looking at the exception again I believe it is working correctly. When you define a foreign function that means that the function is supported by the source and it is pushdown eligible to source, in your case it was a MSSQL database, where you are sending a "myfunction" to SQLServer, and that function is not found in the source and driver is reporting as such.


                            What was your expectation out of this?



                            • 11. Re: SQL functions
                              Steven Hawkins Master



                              There are two issues here.  One is that when you use a multi-part name we have a legacy function validation that prevents function names from containing '.'.  With that removed "create foreign function sourceschema.myfunction..."  will work and you can issue sql like "select mssqlsource.sourceschema.myfunction..." or "select sourceschema.myfunction..." assuming that the name is unambiguous.  The other issue is that you would expect setting the NAMEINSOURCE extension metadata would allow you to override the source name - for example:


                              create foreign function myfunction(val varbinary) returns varchar options (nameinsource 'sourceschema.myfunction');


                              However that logic is currently only enabled for translator pushdown functions and wasn't updated in Teiid 8.0 to account for DDL foreign functions.


                              I'll address both of these in https://issues.jboss.org/browse/TEIID-2234



                              • 13. Re: SQL functions
                                Steven Hawkins Master

                                This should work as you expect using either a function name containing your source schema or using the nameInSource extension property against trunk or in 8.2 Beta1 when it's released in about a week.



                                • 14. Re: SQL functions
                                  Samier Nadji Newbie

                                  Thanks Steve. 


                                  BTW, the Teiid documentaion for defining the "source function" has the identifier/type order reversed. The sample in doc shows:

                                  CREATE FUNCTION SCORE (integer val) RETURNS (integer score);

                                  should be -

                                  CREATE FUNCTION SCORE (val integer) RETURNS (score integer);


                                  It would also be nice if the doc inlcuded the example for the need to declare the extension metadata class/method.

                                  CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;

                                  1 2 Previous Next