[shawkins]

Which doc page are you looking at?  https://docs.jboss.org/author/display/TEIID/DDL+Metadata looks correct (although needs updated for 8.2).  Yes there should also be a cross-reference on that page to https://docs.jboss.org/author/display/TEIID/Support+for+User-Defined+Functions+%28Non-Pushdown%29

Thanks,

Steve

[snadji]

I was looking at https://docs.jboss.org/author/display/TEIID/Source+Supported+Functions under the "User Defined Functions" topic.

[shawkins]

That has been corrected and more cross links have been added.

Thank you,

Steve

[yjma2001]

Hi Steven:

I was following your instuction "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" to try to use the sql function in my virtual views, but I always got failure. I was using teiid 8.2 alpha2. Could you please take a look my vdb to see what I am missing?

1) Without foreign function defined, directly to access the function from query: my vdb looks like:

    <model visible="true" type="PHYSICAL" name="ds">
        <property name="importer.useFullSchemaName" value="true"/>
        <source name="ds" translator-name="sqlserver" connection-jndi-name="java:/ds"/>    
    </model>
   
    <model visible="true" type="VIRTUAL" name="testview">
        <metadata type="DDL"><![CDATA[
              
            CREATE VIEW cnt1 (               
                id1       string
            )
   AS  SELECT
                ds.mydb.dbo.hex(testT.id) AS id1
            FROM
                ds.mydb.dbo.contact    testT               
        ]]>
        </metadata>
    </model>

but got following error at vdb loading time
  
  15:16:29,941 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1) TEIID31080 View testview.cnt1 validation error: QueryResolverException-TEIID30068 The function 'ds.mydb.dbo.hex(testT.id)' is an unknown form.  Check that the function name and number of arguments is correct.
  15:16:29,945 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID40073 The metadata for the VDB test.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

(2) Define foreign function with schename like by using the shcema name in the function name

    <model visible="true" type="PHYSICAL" name="ds">
        <property name="importer.useFullSchemaName" value="true"/>
        <source name="ds" translator-name="sqlserver" connection-jndi-name="java:/ds"/>    
        <metadata type="NATIVE,DDL"><![CDATA[
           create foreign function mydb.dbo.hex(val varbinary) returns varchar;
         ]]>
        </metadata>         
    </model>
   
    <model visible="true" type="VIRTUAL" name="testview">
        <metadata type="DDL"><![CDATA[
              
            CREATE VIEW cnt1 (               
                id1       string
            )
   AS  SELECT
                ds.mydb.dbo.hex(testT.id) AS id1
            FROM
                ds.mydb.dbo.contact    testT               
        ]]>
        </metadata>
    </model>

Again. it failed at loading time, got following error:

15:45:08,779 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4) TEIID31073 Invalid functions; Translator metadata load ds []
15:45:08,780 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40073 The metadata for the VDB test.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
15:45:08,780 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40003 VDB test.1 is set to FAILED

(3) Define foreign function with nameinsource defined:
    <model visible="true" type="PHYSICAL" name="ds">
        <property name="importer.useFullSchemaName" value="true"/>
        <source name="ds" translator-name="sqlserver" connection-jndi-name="java:/ds"/>    
        <metadata type="NATIVE,DDL"><![CDATA[
           create foreign function hex(val varbinary) returns varchar options (nameinsource 'mydb.dbo.hex');
         ]]>
        </metadata>         
    </model>
   
    <model visible="true" type="VIRTUAL" name="testview">
        <metadata type="DDL"><![CDATA[
              
            CREATE VIEW cnt1 (               
                id1       string
            )
   AS  SELECT
                ds.hex(testT.id) AS id1
            FROM
                ds.mydb.dbo.contact    testT               
        ]]>
        </metadata>
    </model>
 
This time VDB loaded successfully, but when I trye to access the virtual view from squirrel, got following error:
  15:35:11,107 WARN  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue48) Connector worker process failed for atomic-request=ue+s6th+oGZd.5.0.4: org.teiid.translator.jdbc.JDBCExecutionException: 195 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 hex(g_0."id") AS c_0 FROM "mydb"."dbo"."contact" g_0]
  at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:88)
  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:255) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:457) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:177) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:174) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_06]
  at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_06]
  at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:250) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.2.0.Alpha2.jar:8.2.0.Alpha2]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_06]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_06]
  at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_06]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 'hex' 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)

15:35:11,127 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue49) TEIID30020 Processing exception 'TEIID30504 ds: 195 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 hex(g_0."id") AS c_0 FROM "mydb"."dbo"."contact" g_0]' for request ue+s6th+oGZd.5.  Exception type org.teiid.core.TeiidProcessingException thrown from com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216). Enable more detailed logging to see the entire stacktrace.

[rareddy]

I believe Steve's fixes will be in the next revision 8.2 Beta1 which is panned for next week. Otherwise, you can get the latest source from svn and build locally.

[yjma2001]

Thanks a lot. we tried at teiid 8.2 nightly build (1674), SQL function invoking works ( test (2) and (3) above).