]
Steve Tran commented on TEIIDDES-2811:
--------------------------------------
You're right, I created a Procedure in Teiid Designer, but when I am in the Data
Source Explorer in Eclipse (JBDS), it's listed them as User-Defined Functions.
Right-clicking and selecting "run" results in an NPE at first, but on 2nd try,
it displays the parameter screen. After filling in the appropriate parameters, and
pressing "OK", it throws the error I quoted above.
{quote}
select ServiceTier.datasource.PGT ('xpo')
org.teiid.runtime.client.TeiidClientException:
org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function
'ServiceTier.datasource.PGT('xpo')' is an unknown form. Check that the
function name and number of arguments is correct.
Elapsed Time: 0 hr, 0 min, 0 sec, 29 ms.
{quote}
Running a User-Defined uses the wrong syntax
--------------------------------------------
Key: TEIIDDES-2811
URL:
https://issues.jboss.org/browse/TEIIDDES-2811
Project: Teiid Designer
Issue Type: Bug
Affects Versions: 9.0.3
Environment: Red Hat JBoss Data Virtualization 6.2.2 on EAP patched to version
6.4.5,
JBoss Developer Studio 8.1.0GA with Teiid Designer plugin
9.0.3.Final.v20150810-1438-B1157
64-bit Windows 7 environment
Reporter: Steve Tran
Assignee: Barry LaFond
Priority: Minor
Attachments: Capture.PNG
After deploying my VDB, and viewing it inside the Datasource Explorer inside of JBDS,
running a User-Defined Function through the GUI does not work because it's executing
the wrong query.
Here's what JDV is doing.
{code}
17:17:29,178 INFO [org.teiid.PLANNER] (Worker0_QueryProcessorQueue46)
============================================================================
USER COMMAND:
EXEC datasource.PGT('XPO')
----------------------------------------------------------------------------
OPTIMIZE:
EXEC datasource.PGT('XPO')
----------------------------------------------------------------------------
GENERATE CANONICAL:
EXEC datasource.PGT('XPO')
CANONICAL PLAN:
Project(groups=[datasource.PGT], props={PROJECT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR]})
Source(groups=[datasource.PGT], props={VIRTUAL_COMMAND=EXEC
datasource.PGT('XPO')})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[datasource.PGT], props={PROJECT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR]})
Access(groups=[datasource.PGT], props={SOURCE_HINT=null, MODEL_ID=Schema
name=datasource, nameInSource=null, uuid=mmuuid:a521401c-37c6-4d3c-909d-55599421a456})
Source(groups=[datasource.PGT], props={VIRTUAL_COMMAND=EXEC
datasource.PGT('XPO')})
============================================================================
EXECUTING RaiseAccess
AFTER:
Project(groups=[datasource.PGT], props={PROJECT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR]})
Access(groups=[datasource.PGT], props={SOURCE_HINT=null, MODEL_ID=Schema
name=datasource, nameInSource=null, uuid=mmuuid:a521401c-37c6-4d3c-909d-55599421a456})
Source(groups=[datasource.PGT], props={VIRTUAL_COMMAND=EXEC
datasource.PGT('XPO')})
============================================================================
EXECUTING AssignOutputElements
AFTER:
Project(groups=[datasource.PGT], props={PROJECT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR], OUTPUT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR]})
Access(groups=[datasource.PGT], props={SOURCE_HINT=null, MODEL_ID=Schema
name=datasource, nameInSource=null, uuid=mmuuid:a521401c-37c6-4d3c-909d-55599421a456,
OUTPUT_COLS=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR]})
Source(groups=[datasource.PGT], props={VIRTUAL_COMMAND=EXEC
datasource.PGT('XPO'), OUTPUT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR]})
============================================================================
EXECUTING CalculateCost
AFTER:
Project(groups=[datasource.PGT], props={PROJECT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR], OUTPUT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR], EST_CARDINALITY=-1.0})
Access(groups=[datasource.PGT], props={SOURCE_HINT=null, MODEL_ID=Schema
name=datasource, nameInSource=null, uuid=mmuuid:a521401c-37c6-4d3c-909d-55599421a456,
OUTPUT_COLS=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR],
EST_CARDINALITY=-1.0})
Source(groups=[datasource.PGT], props={VIRTUAL_COMMAND=EXEC
datasource.PGT('XPO'), OUTPUT_COLS=[datasource.PGT.AUDIT_CODE,
datasource.PGT.CLIENT_NBR], EST_COL_STATS={datasource.PGT.AUDIT_CODE=[-1.0, -1.0],
datasource.PGT.CLIENT_NBR=[-1.0, -1.0]}, EST_CARDINALITY=-1.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Project(groups=[datasource.PGT])
Access(groups=[datasource.PGT])
Source(groups=[datasource.PGT])
============================================================================
EXECUTING CollapseSource
AFTER:
Project(groups=[datasource.PGT])
Access(groups=[datasource.PGT], props={SOURCE_HINT=null, MODEL_ID=Schema
name=datasource, nameInSource=null, uuid=mmuuid:a521401c-37c6-4d3c-909d-55599421a456,
OUTPUT_COLS=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR], EST_CARDINALITY=-1.0,
ATOMIC_REQUEST=EXEC datasource.PGT('XPO')})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
ProjectNode(0) output=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR]
[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR]
AccessNode(1) output=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR] EXEC
datasource.PGT('XPO')
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR]
[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR]
AccessNode(1) output=[datasource.PGT.AUDIT_CODE, datasource.PGT.CLIENT_NBR] EXEC
datasource.PGT('XPO')
============================================================================
{code}
And here's the query that actually gets run.
{code}
select ServiceTier.datasource.PGT ('ABC')
org.teiid.runtime.client.TeiidClientException:
org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function
'ServiceTier.datasource.PGT('ABC')' is an unknown form. Check that the
function name and number of arguments is correct.
Elapsed Time: 0 hr, 0 min, 0 sec, 18 ms.
{code}
The problem is "select ServiceTier.datasource.PGT ('ABC')" should be
"exec ServiceTier.datasource.PGT ('ABC')".