[TEIIDDES-2942] Procedure input parameter has result in the generated DDL

Type: Bug
Resolution: Done
Priority: Critical
Fix Version/s: 10.0.2
Affects Version/s: 10.0.2
Component/s: Dynamic VDBs
Labels:
- release_notes
- verified_jbdsis-9.0.3

Steps to Reproduce:
1. import project
2. preview procedure

I have a procedure with one input parameter. When I want to preview this procedure or deploy dynamic VDB, server shows me error that:

TEIID31100 Parsing error: Encountered "(4000) [*]result[*]) RETURNS" at line 2, column 52.
Procedure testProc RESULT param p1 must be of type OUT.

I noticed that generated dynamic VDB contains 'result' in this parameter but this parameter has set direction to IN. When I delete the result from dynamic VDB, the procedure works fine.

Actual DDL:

CREATE VIRTUAL PROCEDURE testProc (p1 string(4000) result) RETURNS TABLE (xml_out xml) OPTIONS ("REST:METHOD" 'GET', "REST:URI" 'test/{p1}')
	AS
 BEGIN
	SELECT XMLELEMENT(NAME test, XMLFOREST(ProcedureModel.testProc.p1 AS elem1, 'elem2' AS elem2)) AS xml_out;
END;

expected DDL:

CREATE VIRTUAL PROCEDURE testProc (p1 string(4000)) RETURNS TABLE (xml_out xml) OPTIONS ("REST:METHOD" 'GET', "REST:URI" 'test/{p1}')
	AS
 BEGIN
	SELECT XMLELEMENT(NAME test, XMLFOREST(ProcedureModel.testProc.p1 AS elem1, 'elem2' AS elem2)) AS xml_out;
END;

- - Sort By Name
  - Sort By Date
  - Ascending
  - Descending
  - Thumbnails
  - List
  - Download All

ddlProject.zip
40 kB
2016/10/20 7:49 AM

is incorporated by

TEIIDDES-2699 Issues with Dynamic VDB import/export

Closed

Barry LaFond added a comment - 2016/11/01 11:54 AM

10.0.x: https://github.com/Teiid-Designer/teiid-designer/commit/a1aa75325e5d6e5e01bba74afe9e4b7f617e3b9f

added type check to prevent exception

Barry LaFond added a comment - 2016/11/01 11:54 AM 10.0.x: https://github.com/Teiid-Designer/teiid-designer/commit/a1aa75325e5d6e5e01bba74afe9e4b7f617e3b9f added type check to prevent exception

Matus Makovy added a comment - 2016/10/31 8:09 AM

When I try to preview the procedure I get this exeception:

java.lang.ClassCastException: org.teiid.designer.metamodels.relational.impl.ProcedureImpl cannot be cast to org.teiid.designer.metamodels.relational.Table
	at org.teiid.designer.runtime.preview.DependentObjectHelper.findSourceTables(DependentObjectHelper.java:121)
	at org.teiid.designer.runtime.preview.DependentObjectHelper.calculateDependentObjects(DependentObjectHelper.java:86)
	at org.teiid.designer.runtime.preview.DependentObjectHelper.<init>(DependentObjectHelper.java:74)
	at org.teiid.designer.runtime.preview.PreviewManager.getVdbSourceModelInfos(PreviewManager.java:588)
	at org.teiid.designer.runtime.preview.PreviewManager.createDynamicVdb(PreviewManager.java:352)
	at org.teiid.designer.runtime.preview.PreviewManager.generateDynamicVdb(PreviewManager.java:132)
	at org.teiid.designer.runtime.preview.PreviewManager.getDynamicVdbStatus(PreviewManager.java:107)
	at org.teiid.designer.runtime.ui.preview.PreviewDataWorker.internalRun(PreviewDataWorker.java:327)
	at org.teiid.designer.runtime.ui.preview.PreviewDataWorker.run(PreviewDataWorker.java:209)
	at org.teiid.designer.runtime.ui.preview.PreviewTableDataContextAction.run(PreviewTableDataContextAction.java:99)
	at org.eclipse.jface.action.Action.runWithEvent(Action.java:473)
	at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:595)
	at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:511)
	at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:420)
	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4230)
	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1491)
	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1514)
	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1499)
	at org.eclipse.swt.widgets.Widget.notifyListeners(Widget.java:1299)
	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4072)
	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3698)
	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1127)
	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337)
	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1018)
	at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:156)
	at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:694)
	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337)
	at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:606)
	at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)
	at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:139)
	at org.jboss.reddeer.eclipse.core.UITestApplication.start(UITestApplication.java:47)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)
	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:669)
	at org.eclipse.equinox.launcher.Main.basicRun(Main.java:608)
	at org.eclipse.equinox.launcher.Main.run(Main.java:1515)
	at org.eclipse.equinox.launcher.Main.main(Main.java:1488)

Matus Makovy added a comment - 2016/10/31 8:09 AM When I try to preview the procedure I get this exeception: java.lang.ClassCastException: org.teiid.designer.metamodels.relational.impl.ProcedureImpl cannot be cast to org.teiid.designer.metamodels.relational.Table at org.teiid.designer.runtime.preview.DependentObjectHelper.findSourceTables(DependentObjectHelper.java:121) at org.teiid.designer.runtime.preview.DependentObjectHelper.calculateDependentObjects(DependentObjectHelper.java:86) at org.teiid.designer.runtime.preview.DependentObjectHelper.<init>(DependentObjectHelper.java:74) at org.teiid.designer.runtime.preview.PreviewManager.getVdbSourceModelInfos(PreviewManager.java:588) at org.teiid.designer.runtime.preview.PreviewManager.createDynamicVdb(PreviewManager.java:352) at org.teiid.designer.runtime.preview.PreviewManager.generateDynamicVdb(PreviewManager.java:132) at org.teiid.designer.runtime.preview.PreviewManager.getDynamicVdbStatus(PreviewManager.java:107) at org.teiid.designer.runtime.ui.preview.PreviewDataWorker.internalRun(PreviewDataWorker.java:327) at org.teiid.designer.runtime.ui.preview.PreviewDataWorker.run(PreviewDataWorker.java:209) at org.teiid.designer.runtime.ui.preview.PreviewTableDataContextAction.run(PreviewTableDataContextAction.java:99) at org.eclipse.jface.action.Action.runWithEvent(Action.java:473) at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:595) at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:511) at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:420) at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84) at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4230) at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1491) at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1514) at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1499) at org.eclipse.swt.widgets.Widget.notifyListeners(Widget.java:1299) at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4072) at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3698) at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1127) at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337) at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1018) at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:156) at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:694) at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:337) at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:606) at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150) at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:139) at org.jboss.reddeer.eclipse.core.UITestApplication.start(UITestApplication.java:47) at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:669) at org.eclipse.equinox.launcher.Main.basicRun(Main.java:608) at org.eclipse.equinox.launcher.Main.run(Main.java:1515) at org.eclipse.equinox.launcher.Main.main(Main.java:1488)

Barry LaFond added a comment - 2016/10/20 11:29 PM - edited

10.0.x:

https://github.com/Teiid-Designer/teiid-designer/commit/46d93354e3ee1a1fc3086383df5eb33a7f3432cc
https://github.com/Teiid-Designer/teiid-designer/commit/710aa164b426deaa64e7556e95d909f15bef0996 (fixed test and added missing constant change)

Barry LaFond added a comment - 2016/10/20 11:29 PM - edited 10.0.x: https://github.com/Teiid-Designer/teiid-designer/commit/46d93354e3ee1a1fc3086383df5eb33a7f3432cc https://github.com/Teiid-Designer/teiid-designer/commit/710aa164b426deaa64e7556e95d909f15bef0996 (fixed test and added missing constant change)

Steven Hawkins added a comment - 2016/10/20 3:31 PM

> So the exporter will treat OUT's as straight OUT parameters and if a RETURN parameter exists, then it'll be treated as an OUT parameter with a "result" added.

Yes, that is correct.

Steven Hawkins added a comment - 2016/10/20 3:31 PM > So the exporter will treat OUT's as straight OUT parameters and if a RETURN parameter exists, then it'll be treated as an OUT parameter with a "result" added. Yes, that is correct.

Barry LaFond added a comment - 2016/10/20 3:27 PM - edited

rhn-engineering-shawkins So Designer allows a) and b) above.

A procedure can only have have 1 out parameter marked as a return

Designer has 4 direction options: OUT, INOUT, OUT and RETURN, so there's no way to set an OUT parameter to be returned. There can only be 1 RETURN parameter, else validation ERROR

So the exporter will treat OUT's as straight OUT parameters and if a RETURN parameter exists, then it'll be treated as an OUT parameter with a "result" added.

Lastly.. if a Result Set does NOT exist and a RETURN parameter exists... we have the option to either export the parameter and NO RESULT SET, or.... use the RETURN parameter info and export a RESULT SET with a single column and datatype? Should we NOT do the latter?

Barry LaFond added a comment - 2016/10/20 3:27 PM - edited rhn-engineering-shawkins So Designer allows a) and b) above. A procedure can only have have 1 out parameter marked as a return Designer has 4 direction options: OUT, INOUT, OUT and RETURN, so there's no way to set an OUT parameter to be returned. There can only be 1 RETURN parameter, else validation ERROR So the exporter will treat OUT's as straight OUT parameters and if a RETURN parameter exists, then it'll be treated as an OUT parameter with a "result" added. Lastly.. if a Result Set does NOT exist and a RETURN parameter exists... we have the option to either export the parameter and NO RESULT SET, or.... use the RETURN parameter info and export a RESULT SET with a single column and datatype? Should we NOT do the latter?

Steven Hawkins added a comment - 2016/10/20 2:50 PM

A procedure can have both a return and a result set.
A procedure can have any number of out, in, in/out parameters
A procedure can only have have 1 out parameter marked as a return

Steven Hawkins added a comment - 2016/10/20 2:50 PM A procedure can have both a return and a result set. A procedure can have any number of out, in, in/out parameters A procedure can only have have 1 out parameter marked as a return

Barry LaFond added a comment - 2016/10/20 2:23 PM

rhn-engineering-shawkins So in Designer
1) we have both OUT and RETURN directions and they should behave just as an OUT direction
2) an OUT direction adds the result to the parameter
3) A Procedure can have both an OUT parameter AND a Result Set

Barry LaFond added a comment - 2016/10/20 2:23 PM rhn-engineering-shawkins So in Designer 1) we have both OUT and RETURN directions and they should behave just as an OUT direction 2) an OUT direction adds the result to the parameter 3) A Procedure can have both an OUT parameter AND a Result Set

Barry LaFond added a comment - 2016/10/20 2:23 PM - edited

rhn-engineering-shawkins So in Designer
1) we have both OUT and RETURN directions and they should behave just as an OUT direction
2) can there be multiple OUT/RETURN parameters or just one?
3) an OUT direction adds the result to the parameter
4) A Procedure can have both an OUT parameter AND a Result Set

Barry LaFond added a comment - 2016/10/20 2:23 PM - edited rhn-engineering-shawkins So in Designer 1) we have both OUT and RETURN directions and they should behave just as an OUT direction 2) can there be multiple OUT/RETURN parameters or just one? 3) an OUT direction adds the result to the parameter 4) A Procedure can have both an OUT parameter AND a Result Set

Steven Hawkins added a comment - 2016/10/20 2:07 PM

> Is this legal?

Yes that is legal.

> And what do we export if both exist?

The syntax issue is that return must be an out parameter:

out p1 string(4000) result

omitting the out means that it's defaulted to an in parameter.

Steven Hawkins added a comment - 2016/10/20 2:07 PM > Is this legal? Yes that is legal. > And what do we export if both exist? The syntax issue is that return must be an out parameter: out p1 string(4000) result omitting the out means that it's defaulted to an in parameter.

Barry LaFond added a comment - 2016/10/20 1:43 PM

rhn-engineering-shawkins Designer currently allows setting a procedure parameter to RETURN direction and have a Result Set defined. Couldn't find any restrictions/info in the BNF doc. Is this legal? and if not, we can easily add a validation rule. And what do we export if both exist?

Barry LaFond added a comment - 2016/10/20 1:43 PM rhn-engineering-shawkins Designer currently allows setting a procedure parameter to RETURN direction and have a Result Set defined. Couldn't find any restrictions/info in the BNF doc. Is this legal? and if not, we can easily add a validation rule. And what do we export if both exist?

Assignee:: Barry LaFond

Reporter:: Matej Kralik

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Created:: 2016/10/20 7:50 AM

Updated:: 2021/10/24 5:59 AM

Resolved:: 2016/11/01 11:54 AM

Details

Description

Attachments

Attachments

Issue Links

Easy Agile Planning Poker

Activity

Collapse comment: Barry LaFond added a comment - 2016/11/01 11:54 AM

Expand comment: Barry LaFond added a comment - 2016/11/01 11:54 AM

Collapse comment: Matus Makovy added a comment - 2016/10/31 8:09 AM

Expand comment: Matus Makovy added a comment - 2016/10/31 8:09 AM

Collapse comment: Barry LaFond added a comment - 2016/10/20 11:29 PM, Edited by Barry LaFond - 2016/10/24 9:31 AM

Expand comment: Barry LaFond added a comment - 2016/10/20 11:29 PM, Edited by Barry LaFond - 2016/10/24 9:31 AM

Collapse comment: Steven Hawkins added a comment - 2016/10/20 3:31 PM

Expand comment: Steven Hawkins added a comment - 2016/10/20 3:31 PM

Collapse comment: Barry LaFond added a comment - 2016/10/20 3:27 PM, Edited by Barry LaFond - 2016/10/20 3:31 PM

Expand comment: Barry LaFond added a comment - 2016/10/20 3:27 PM, Edited by Barry LaFond - 2016/10/20 3:31 PM

Collapse comment: Steven Hawkins added a comment - 2016/10/20 2:50 PM

Expand comment: Steven Hawkins added a comment - 2016/10/20 2:50 PM

Collapse comment: Barry LaFond added a comment - 2016/10/20 2:23 PM

Expand comment: Barry LaFond added a comment - 2016/10/20 2:23 PM

Collapse comment: Barry LaFond added a comment - 2016/10/20 2:23 PM, Edited by Barry LaFond - 2016/10/20 2:44 PM

Expand comment: Barry LaFond added a comment - 2016/10/20 2:23 PM, Edited by Barry LaFond - 2016/10/20 2:44 PM

Collapse comment: Steven Hawkins added a comment - 2016/10/20 2:07 PM

Expand comment: Steven Hawkins added a comment - 2016/10/20 2:07 PM

Collapse comment: Barry LaFond added a comment - 2016/10/20 1:43 PM

Expand comment: Barry LaFond added a comment - 2016/10/20 1:43 PM

People

Dates