Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

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


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

  • 1 year later...
×
×
  • Create New...