Topic
  • 15 replies
  • Latest Post - ‏2015-04-09T20:25:19Z by DEV-Kamran
SystemAdmin
SystemAdmin
532 Posts

Pinned topic Stored procedure call from Portlet Factory and get the result Value

‏2010-08-30T06:15:06Z |
Hello All,
I am trying to call the SQL stored procedure that return a result as String.
Attached along is the stored procedure.

My Sql call Builder's SQl statement is as follows:
{call sp_opg_addTransaction( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)}

In the Parameters i have given the last parameter as OUTPUT param.

And in result set binding i have specified alternate SQL as SELECT '' AS Result

I get Errors like:
An error has occurred.
The error message returned was: "Error in method dBServiceAddPaymentTransactions_InputPage_NextAction. Error in method dBServiceAddPaymentTransactions_ShowResults. Error in method AddPaymentTransactionsExecute. Error in method addTransactionInvoke. Error in method addTransactionInvokeBase. Error in method addTransactionTransform.executeForCaching. MicrosoftSQLServer 2000 Driver for JDBCValue can not be converted to requested type.".
An error has occurred.
The error message returned was: "Error in method MakePayment. Error in method DBConsumerAddPaymentTransactionsWithArgs. Error in method DBConsumer.executeOperation. Error in method AddPaymentTransactionsExecute. Error in method addTransactionInvoke. Error in method addTransactionInvokeBase. Error in method addTransactionTransform.executeForCaching. Invalid XML element name specified: ".

Please let me know what needs to be done to make this work.

Thanks,
Ketaki
Updated on 2011-12-06T20:11:16Z at 2011-12-06T20:11:16Z by SystemAdmin
  • DGawron
    DGawron
    251 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-16T14:08:36Z  
    If the STP is only returning a single OUT parameter, then you don't need to have it generate a schema. Schema generation is only useful (and valid) when the STP returns a result set. Set the "Schema Generation" input to omit the schema. If model regen is successful, then there should be a variable named <sql-call-builder-name>TransformXmlShadow in the webApp tree. At run-time this variable will contain all of the STP's return values (the declared OUT parameters as well as any result sets).
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-19T12:02:16Z  
    • DGawron
    • ‏2010-09-16T14:08:36Z
    If the STP is only returning a single OUT parameter, then you don't need to have it generate a schema. Schema generation is only useful (and valid) when the STP returns a result set. Set the "Schema Generation" input to omit the schema. If model regen is successful, then there should be a variable named <sql-call-builder-name>TransformXmlShadow in the webApp tree. At run-time this variable will contain all of the STP's return values (the declared OUT parameters as well as any result sets).
    Now how do you get the TransformXmlShadow made available to models through the Service Operation?

    As of now i have used a action list - which returns the :

    Return!${Variables/addTransactionTransformXmlShadow/CallableStatementResultData/Output@Position=11}

    And Service Operation calls this Action List with Operation Results as ${Results/returnValue}

    But i am not able to get this working as the TransformXmlShadow is null

    So can you let me know how to make the TransformXmlShadow variable avaialble to other models?

    Thanks,
    Ketaki
  • DGawron
    DGawron
    251 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-21T15:09:49Z  
    Now how do you get the TransformXmlShadow made available to models through the Service Operation?

    As of now i have used a action list - which returns the :

    Return!${Variables/addTransactionTransformXmlShadow/CallableStatementResultData/Output@Position=11}

    And Service Operation calls this Action List with Operation Results as ${Results/returnValue}

    But i am not able to get this working as the TransformXmlShadow is null

    So can you let me know how to make the TransformXmlShadow variable avaialble to other models?

    Thanks,
    Ketaki
    If the shadow variable is null, then this probably means that you forgot to execute the SQL DataService that invokes the stored procedure. In the action referenced by the Service Op builder you need to make sure the SQL DataService is executed and the results are returned. That will cause the shadow variable to be populated unless there is an exception thrown while invoking the stored procedure.

    You also need to make sure the value you return matches the declared schema for the Service Op. If the returned data does not match, then page automation won't be able to display the result. I think you'll need to do a bit more than simply return the OUT parameter's value. You'll need to wrap it in appropriate XML.
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-25T14:50:59Z  
    • DGawron
    • ‏2010-09-21T15:09:49Z
    If the shadow variable is null, then this probably means that you forgot to execute the SQL DataService that invokes the stored procedure. In the action referenced by the Service Op builder you need to make sure the SQL DataService is executed and the results are returned. That will cause the shadow variable to be populated unless there is an exception thrown while invoking the stored procedure.

    You also need to make sure the value you return matches the declared schema for the Service Op. If the returned data does not match, then page automation won't be able to display the result. I think you'll need to do a bit more than simply return the OUT parameter's value. You'll need to wrap it in appropriate XML.
    Can i see a sample model to do so?

    Thanks,
    Ketaki
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-25T14:53:05Z  
    • DGawron
    • ‏2010-09-21T15:09:49Z
    If the shadow variable is null, then this probably means that you forgot to execute the SQL DataService that invokes the stored procedure. In the action referenced by the Service Op builder you need to make sure the SQL DataService is executed and the results are returned. That will cause the shadow variable to be populated unless there is an exception thrown while invoking the stored procedure.

    You also need to make sure the value you return matches the declared schema for the Service Op. If the returned data does not match, then page automation won't be able to display the result. I think you'll need to do a bit more than simply return the OUT parameter's value. You'll need to wrap it in appropriate XML.
    I am Still getting errors like:
    An error has occurred.
    The error message returned was: "Error in method dBServiceGetPayDueDetailOperation_InputPage_NextAction. Error in method dBServiceGetPayDueDetailOperation_ShowResults. Error in method GetPayDueDetailOperationExecute. Error in method getPayDueDetailInvoke. Error in method getPayDueDetailInvokeBase. Error in method getPayDueDetailTransform.executeForCaching. ORA-06550: line 1, column 7: PLS-00201: identifier 'ONLS.GETPAYDUEDETAIL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ".
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-27T12:20:20Z  
    I am Still getting errors like:
    An error has occurred.
    The error message returned was: "Error in method dBServiceGetPayDueDetailOperation_InputPage_NextAction. Error in method dBServiceGetPayDueDetailOperation_ShowResults. Error in method GetPayDueDetailOperationExecute. Error in method getPayDueDetailInvoke. Error in method getPayDueDetailInvokeBase. Error in method getPayDueDetailTransform.executeForCaching. ORA-06550: line 1, column 7: PLS-00201: identifier 'ONLS.GETPAYDUEDETAIL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ".
    I solved this by providing the Package name as well, so this above error has gone.
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-27T12:24:22Z  
    I solved this by providing the Package name as well, so this above error has gone.
    I am still not able to get the out parameter results from Service Operation.

    In the Service Operation i have defined Operation Results as a Schema, which i created,
    but i get this message:
    Target operation result schema for operation "GetPayDueDetailOperation" is not available.

    In SQL Call i have choosen Omit generation Step in Result Set Handling.

    Please let me know how can the service operation return the output that can be used by other models.

    Thanks,
    Ketaki
  • DGawron
    DGawron
    251 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-28T15:10:22Z  
    I am still not able to get the out parameter results from Service Operation.

    In the Service Operation i have defined Operation Results as a Schema, which i created,
    but i get this message:
    Target operation result schema for operation "GetPayDueDetailOperation" is not available.

    In SQL Call i have choosen Omit generation Step in Result Set Handling.

    Please let me know how can the service operation return the output that can be used by other models.

    Thanks,
    Ketaki
    Please post a sample model so I can see exactly what you are doing.
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-09-30T07:04:06Z  
    • DGawron
    • ‏2010-09-28T15:10:22Z
    Please post a sample model so I can see exactly what you are doing.
    Hello,
    I am attaching my DB Service model.

    Here specifically i would like to get the Output results from following Service Operations:
    AddPaymentTransactions
    getbilledunbilledtransactionsOperation
    GetPayDueDetailOperation

    Other models call the DB Service operation through the DB Consumer Model.

    Thanks,
    Ketaki
  • DGawron
    DGawron
    251 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2010-10-06T17:11:56Z  
    Hello,
    I am attaching my DB Service model.

    Here specifically i would like to get the Output results from following Service Operations:
    AddPaymentTransactions
    getbilledunbilledtransactionsOperation
    GetPayDueDetailOperation

    Other models call the DB Service operation through the DB Consumer Model.

    Thanks,
    Ketaki
    I think you are missing a few key details of how SQL Call handles stored procedure results. Rather than trying to fix your model, which is a bit involved, I've included a sample model that illustrates one general way to return OUT parameters as the result of a Service Operation. Hopefully this will be instructive to you any anyone else that has the same need. The key points to note are:

    • SQL Call stores OUT parameter values in a variable called the shadow variable. This variable will also contain all of the returned result sets, if any. In my sample model there are only 2 OUT parameters.

    • SQL Call does not generate a schema for the shadow variable. This is possibly an oversight in how the builder was designed, but that's how it works at the present. The net result is that if you need to return OUT parameters as the result of a Service Operation, then you need to define a result schema by hand. That's just what I did in the model. I grabbed a copy of the shadow variable's content and used it as sample data to generate the result schema for the Service Operation.

    • In this sample you cannot simply point Service Operation directly at the SQL Call data service. Service Operation needs to know the schema of the data it returns, but SQL has not defined a result schema. Therefore the solution is to point Service Operation at an Action List (named invokeSTPProxy in the sample) that takes the same inputs as SQL Call, invokes the SQL Call data service, then returns the content of SQL Call's shadow variable. To make all this work Service Operation is explicitly told about the input and out schemas used by the Action List (look at the inputs in the Operation Inputs and Operation Results groups).

    You should be able to use the same general pattern to implement the Service Operations required in your provider model.
  • gvimbai
    gvimbai
    2 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2011-08-25T08:05:56Z  
    • DGawron
    • ‏2010-10-06T17:11:56Z
    I think you are missing a few key details of how SQL Call handles stored procedure results. Rather than trying to fix your model, which is a bit involved, I've included a sample model that illustrates one general way to return OUT parameters as the result of a Service Operation. Hopefully this will be instructive to you any anyone else that has the same need. The key points to note are:

    • SQL Call stores OUT parameter values in a variable called the shadow variable. This variable will also contain all of the returned result sets, if any. In my sample model there are only 2 OUT parameters.

    • SQL Call does not generate a schema for the shadow variable. This is possibly an oversight in how the builder was designed, but that's how it works at the present. The net result is that if you need to return OUT parameters as the result of a Service Operation, then you need to define a result schema by hand. That's just what I did in the model. I grabbed a copy of the shadow variable's content and used it as sample data to generate the result schema for the Service Operation.

    • In this sample you cannot simply point Service Operation directly at the SQL Call data service. Service Operation needs to know the schema of the data it returns, but SQL has not defined a result schema. Therefore the solution is to point Service Operation at an Action List (named invokeSTPProxy in the sample) that takes the same inputs as SQL Call, invokes the SQL Call data service, then returns the content of SQL Call's shadow variable. To make all this work Service Operation is explicitly told about the input and out schemas used by the Action List (look at the inputs in the Operation Inputs and Operation Results groups).

    You should be able to use the same general pattern to implement the Service Operations required in your provider model.
    Hi DGawron.

    I have been trying to follow your example to use a store procedure that has parameters. I am able to use stored procedures that don't have parameters. I notice that in the ActiionList builder: invokeSTPProxy, you use the action - "Assignment!DataServices/invokeSTP/execute/Inputs=${Arguments/parameters}". I have tried doing that but it's not working for me. Everything else seems to be the same.
  • SystemAdmin
    SystemAdmin
    532 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2011-12-06T20:11:16Z  
    • DGawron
    • ‏2010-10-06T17:11:56Z
    I think you are missing a few key details of how SQL Call handles stored procedure results. Rather than trying to fix your model, which is a bit involved, I've included a sample model that illustrates one general way to return OUT parameters as the result of a Service Operation. Hopefully this will be instructive to you any anyone else that has the same need. The key points to note are:

    • SQL Call stores OUT parameter values in a variable called the shadow variable. This variable will also contain all of the returned result sets, if any. In my sample model there are only 2 OUT parameters.

    • SQL Call does not generate a schema for the shadow variable. This is possibly an oversight in how the builder was designed, but that's how it works at the present. The net result is that if you need to return OUT parameters as the result of a Service Operation, then you need to define a result schema by hand. That's just what I did in the model. I grabbed a copy of the shadow variable's content and used it as sample data to generate the result schema for the Service Operation.

    • In this sample you cannot simply point Service Operation directly at the SQL Call data service. Service Operation needs to know the schema of the data it returns, but SQL has not defined a result schema. Therefore the solution is to point Service Operation at an Action List (named invokeSTPProxy in the sample) that takes the same inputs as SQL Call, invokes the SQL Call data service, then returns the content of SQL Call's shadow variable. To make all this work Service Operation is explicitly told about the input and out schemas used by the Action List (look at the inputs in the Operation Inputs and Operation Results groups).

    You should be able to use the same general pattern to implement the Service Operations required in your provider model.
    Thank You. Your explanation to this problem was clear and comprehensive.
  • DEV-Kamran
    DEV-Kamran
    3 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2015-04-09T16:22:59Z  
    • DGawron
    • ‏2010-10-06T17:11:56Z
    I think you are missing a few key details of how SQL Call handles stored procedure results. Rather than trying to fix your model, which is a bit involved, I've included a sample model that illustrates one general way to return OUT parameters as the result of a Service Operation. Hopefully this will be instructive to you any anyone else that has the same need. The key points to note are:

    • SQL Call stores OUT parameter values in a variable called the shadow variable. This variable will also contain all of the returned result sets, if any. In my sample model there are only 2 OUT parameters.

    • SQL Call does not generate a schema for the shadow variable. This is possibly an oversight in how the builder was designed, but that's how it works at the present. The net result is that if you need to return OUT parameters as the result of a Service Operation, then you need to define a result schema by hand. That's just what I did in the model. I grabbed a copy of the shadow variable's content and used it as sample data to generate the result schema for the Service Operation.

    • In this sample you cannot simply point Service Operation directly at the SQL Call data service. Service Operation needs to know the schema of the data it returns, but SQL has not defined a result schema. Therefore the solution is to point Service Operation at an Action List (named invokeSTPProxy in the sample) that takes the same inputs as SQL Call, invokes the SQL Call data service, then returns the content of SQL Call's shadow variable. To make all this work Service Operation is explicitly told about the input and out schemas used by the Action List (look at the inputs in the Operation Inputs and Operation Results groups).

    You should be able to use the same general pattern to implement the Service Operations required in your provider model.

    HI DGarwan, 

                          This Model is empty, Could you please upload the update model of SampleStpOutParamProvider 

  • DGawron
    DGawron
    251 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2015-04-09T16:50:02Z  

    HI DGarwan, 

                          This Model is empty, Could you please upload the update model of SampleStpOutParamProvider 

    Check your browser settings.  I just downloaded the model above and it has content.

  • DEV-Kamran
    DEV-Kamran
    3 Posts

    Re: Stored procedure call from Portlet Factory and get the result Value

    ‏2015-04-09T20:25:19Z  
    • DGawron
    • ‏2015-04-09T16:50:02Z

    Check your browser settings.  I just downloaded the model above and it has content.

    Could you please send me the zip file again. I really need this model. I will be grateful to you.