Can we query a table valued function created in MS SQL from CDV Studio

[Shirish Kamath]

Hi All,

We are trying to query a table valued function (created in MS SQL) from CDV Studio. We are getting the following error:

Query:

select * from /shared//dbo/GetPreferredTerminology("Unit")

must contain only one output cursor with unique fields or at least one scalar output. On line

1, column 15. [parser-2906438]

While I am able to get the output if I query from another client tool-

select * from [dbo].[GetPreferredTerminology]('Unit')

g/dL

g/g

g/kg

g/L

L/kg

mg/dL

mg/kg

mg/L

mL/kg

[Jeremy Akers 2]

Hi Shirish,

This error comes about because CIS needs to know the structure of the cursor that the procedure "GetPreferredTerminology" is going to return. For many procedures CIS is able to pull this information during introspection. However some procedures have multiple output cursors that they might return depending on the input value provided. In these cases, CIS isn't able to determine the structure of the output cursor because CIS actually has to *call* the procedure with some valid inputs in order to introspect the resulting cursor.

So I have good news and bad news: The good news is that it's fairly easy to solve the basic problem presented in your question. The bad news is that CIS doesn't fully support procedures that have variable output cursors (IE: The cursor's structure cannot change.)

So, for the good news: To rectify this you need to open the procedure "/shared//dbo/GetPreferredTerminology" in Studio and then click on the "Design By Example" button. At this point you will be prompted for the input parameter, where you can specify "Unit". CIS will now execute the procedure, introspect the structure of the resulting cursor, and prompt you to confirm that the structure is correct.

Now anytime you query this procedure and provide "Unit" as the input, CIS will be able to handle the resulting cursor because it knows what it should look like.

Now the bad news is that this is now the only structure that CIS can handle. If calling the procedure with some other input value causes it to return a cursor with a different structure (IE: More columns, or columns with different data types) then this will result in an error.

Let me know if that answer works for you.

-Jeremy

[Shirish Kamath]

Hi Jakers,Thank you so much for replying to my query. I tried doing as you suggested, but got a new error this time-An internal error has occurred.

Cause: 1 com.compositesw.cdms.webapi.WebapiException: An internal error has occurred.

Cause: 1

 1

 at com.compositesw.common.CompositeRuntimeException.rethrow(CompositeRuntimeException.java:141)

 at com.compositesw.server.request.HookProcedureRequest.invoke(HookProcedureRequest.java:83)

 at com.compositesw.cdms.webapi.service.WProcResultImpl$InvokerThread.run(WProcResultImpl.java:755)

 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

 at java.lang.Thread.run(Unknown Source)

Caused by: java.lang.ArrayIndexOutOfBoundsException: 1

 at com.compositesw.server.request.AbstractProcedureRequest.checkInputValues(AbstractProcedureRequest.java:124)

 at com.compositesw.server.request.AbstractProcedureRequest.prepareInvoke(AbstractProcedureRequest.java:105)

 at com.compositesw.server.request.TransactionCachedProcedureRequest.invoke(TransactionCachedProcedureRequest.java:63)

 at com.compositesw.server.request.HookProcedureRequest.invoke(HookProcedureRequest.java:79)

 ... 4 more  at com.compositesw.cdms.webapi.WebapiException_SOAPSerializer.doDeserialize(WebapiException_SOAPSerializer.java:58)

 at com.sun.xml.rpc.encoding.ObjectSerializerBase.deserialize(ObjectSerializerBase.java:209)

 at com.sun.xml.rpc.encoding.ReferenceableSerializerImpl.deserialize(ReferenceableSerializerImpl.java:172)

 at com.compositesw.cdms.webapi.service.WMetaDataSvc_procresult_getOutputValues_Fault_SOAPSerializer.deserializeDetail(WMetaDataSvc_procresult_getOutputValues_Fault_SOAPSerializer.java:54)

 at com.sun.xml.rpc.encoding.SOAPFaultInfoSerializer.doDeserialize(SOAPFaultInfoSerializer.java:151)

 at com.sun.xml.rpc.encoding.ObjectSerializerBase.deserialize(ObjectSerializerBase.java:209)

 at com.sun.xml.rpc.encoding.ReferenceableSerializerImpl.deserialize(ReferenceableSerializerImpl.java:172)

 at com.compositesw.cdms.webapi.service.WMetaDataSvc_Stub._readBodyFaultElement(WMetaDataSvc_Stub.java:24668)

 at com.sun.xml.rpc.client.StreamingSender._send(StreamingSender.java:238)

 at com.compositesw.cdms.webapi.service.WMetaDataSvc_Stub.procresult_getOutputValues(WMetaDataSvc_Stub.java:8523)

 at com.compositesw.cdms.webapi.WProcResult.getOutputParameterCount(WProcResult.java:36)

 at com.compositesw.ui.modeler.view.result.ComplexProgressTasks$ComplexExecuteQueryTask.displayQueryResult(ComplexProgressTasks.java:508)

 at com.compositesw.ui.modeler.view.result.ComplexProgressTasks$ComplexExecuteQueryTask.executeQuery(ComplexProgressTasks.java:266)

 at com.compositesw.ui.modeler.view.result.ComplexProgressTasks$ComplexExecuteQueryTask.goWork(ComplexProgressTasks.java:126)

 at com.compositesw.ui.modeler.view.result.LongTask$1.run(LongTask.java:59)

 at java.lang.Thread.run(Unknown Source) Version Information: Studio 7.0.3.00.00, Build ID Build: a939070-2015-12-16, Server 7.0.3.00.18 (hotfix)

[Jeremy Akers 2]

Hi Shirish,

 I think you may need to open a support case for that one. Not sure why you're getting that error message.-Jeremy

[Clayton Beatty]

Shirish,

 

Did you ever resolve this issue  I am encountering exactl;y the same thing as I try to query data in TDV from a SQL table valued (inline) function.

 

Same error to, when I attempt design by example.

 

 

 

Clay