Topic
12 replies Latest Post - ‏2011-12-06T20:11:16Z by SystemAdmin
SystemAdmin
SystemAdmin
532 Posts
ACCEPTED ANSWER

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
    250 Posts
    ACCEPTED ANSWER

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

    ‏2010-09-16T14:08:36Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

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

      ‏2010-09-19T12:02:16Z  in response to DGawron
      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
        250 Posts
        ACCEPTED ANSWER

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

        ‏2010-09-21T15:09:49Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

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

          ‏2010-09-25T14:50:59Z  in response to DGawron
          Can i see a sample model to do so?

          Thanks,
          Ketaki
        • SystemAdmin
          SystemAdmin
          532 Posts
          ACCEPTED ANSWER

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

          ‏2010-09-25T14:53:05Z  in response to DGawron
          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
            ACCEPTED ANSWER

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

            ‏2010-09-27T12:20:20Z  in response to SystemAdmin
            I solved this by providing the Package name as well, so this above error has gone.
            • SystemAdmin
              SystemAdmin
              532 Posts
              ACCEPTED ANSWER

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

              ‏2010-09-27T12:24:22Z  in response to SystemAdmin
              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
                250 Posts
                ACCEPTED ANSWER

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

                ‏2010-09-28T15:10:22Z  in response to SystemAdmin
                Please post a sample model so I can see exactly what you are doing.
                • SystemAdmin
                  SystemAdmin
                  532 Posts
                  ACCEPTED ANSWER

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

                  ‏2010-09-30T07:04:06Z  in response to DGawron
                  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
                    250 Posts
                    ACCEPTED ANSWER

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

                    ‏2010-10-06T17:11:56Z  in response to SystemAdmin
                    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
                      ACCEPTED ANSWER

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

                      ‏2011-08-25T08:05:56Z  in response to DGawron
                      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
                      ACCEPTED ANSWER

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

                      ‏2011-12-06T20:11:16Z  in response to DGawron
                      Thank You. Your explanation to this problem was clear and comprehensive.