-
1. Re: SQL functions
rareddy Sep 27, 2012 5:00 PM (in response to snadji)That looks OK to me. Can you post the whole VDB?
-
2. Re: SQL functions
shawkins Sep 27, 2012 5:35 PM (in response to rareddy)It should be identifier type, not type identifier - so
{code}create function score (val integer) returns (score integer);{code}
-
3. Re: SQL functions
snadji Sep 27, 2012 6:21 PM (in response to shawkins)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);
]]>
</metadata>
</model>
<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
FROM
mssqlsource.mydb.dbo.table1 my_source_table
]]>
</metadata>
</model>
</vdb>
And the SQL function has the following signature:
ALTER FUNCTION [dbo].[myfunction](@s varbinary(16))
RETURNS varchar(64)
-
4. Re: SQL functions
shawkins Sep 27, 2012 8:24 PM (in response to snadji)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
snadji Sep 27, 2012 9:03 PM (in response to shawkins)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
shawkins Sep 28, 2012 8:01 AM (in response to snadji)Samier,
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.
Steve
-
7. Re: SQL functions
snadji Sep 28, 2012 1:23 PM (in response to shawkins)Steve,
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.
Thanks
-
8. Re: SQL functions
rareddy Sep 28, 2012 1:26 PM (in response to snadji)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
snadji Sep 28, 2012 1:35 PM (in response to rareddy)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
rareddy Sep 28, 2012 11:36 PM (in response to snadji)Samier,
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?
Ramesh..
-
11. Re: SQL functions
shawkins Sep 30, 2012 10:04 AM (in response to rareddy)Samier,
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
Steve
-
-
13. Re: SQL functions
shawkins Oct 1, 2012 10:50 AM (in response to snadji)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.
Steve
-
14. Re: SQL functions
snadji Oct 1, 2012 12:48 PM (in response to shawkins)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;