Access to stored procedure output in action

Help
Peter Prib
2009-04-13
2013-04-05
  • Peter Prib
    Peter Prib
    2009-04-13

    I have tried the following with no success.  How can it be done.  I notice the

        <action name="recommendation" >
            <c_title>Recommendation</c_title>
            <icon>images/icon-help-contextual-light.gif</icon>
            <actionScript name="getRecommendation" type="serverAction">
                <parameterList>
                    <parameter name="action" type="raw">
                        <value>executeSQL</value>
                    </parameter>
                    <parameter name="SQL" type="fixed">
                        <value>
                            <![CDATA[
                                call HEALTH_HI_REC(8020000,?HI_ID?,'',0,'',-2,''
                                    ,?!name=recommendation&type=DB2_PARAM_OUT?)
                            ]]>
                        </value>
                    </parameter>
                </parameterList>
                <followOnAction condition="true" negCondition="true">
                    <task>
                        <assignLocalParameter name="ERROR_MSG" type="returnObject">
                            <value>getRecommendation.returnValue.STMTMSG</value>
                        </assignLocalParameter>
                        <alert>Error: ?ERROR_MSG?</alert>
                    </task>
                </followOnAction>
                <followOnAction condition="true">
                    <task>
                        <assignLocalParameter name="MESSAGE" type="returnObject">
                            <value>getRecommendation.recommendation</value>
                        </assignLocalParameter>
                        <assignLocalParameter name="MESSAGE1" type="returnObject">
                            <value>returnValue.resultSet.0.data.0.0</value>
                        </assignLocalParameter>
                        <assignLocalParameter name="MESSAGE2" type="returnObject">
                            <value>returnValue.resultSet.0.data.0</value>
                        </assignLocalParameter>
                        <assignLocalParameter name="MESSAGE3" type="returnObject">
                            <value>returnValue.resultSet.0.data</value>
                        </assignLocalParameter>
                        <alert>test ?recommendation?
                               MESSAGE: ?MESSAGE?
                               MESSAGE1: ?MESSAGE1?
                               MESSAGE2: ?MESSAGE2?
                               MESSAGE3: ?MESSAGE3?
                        </alert>
                    </task>
                </followOnAction>
            </actionScript>
        </action>

    I notice the ad hoc screen works

    Statement 1   SUCCEEDED

    call HEALTH_HI_REC(8020000,4,'',0,'',-2,'',?)

    Time:  2.437s / 51.975µs    (Total / Data server)  100% overhead
    Parameters
    recommendation:
    <?xml version="1.0" encoding="UTF-8" ?><DB2_Health schemaVersion="8020000" xml:lang="en" timestamp="2009-04-13T21:53:29Z" hostname="ISI-SYD-PAP.isiau.com.au" instance="DB2_01" xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/healthrec" ><RecommendationSet><Problem><HealthIndicator hiShortName="db2.mon_heap_util" hiIdentifier="4" hiValue="100" hiAlertState="alarm"

     
    • Hi Peter,

      I have updated the execute documentation with the return object and some examples here:

      https://apps.sourceforge.net/mediawiki/db2mc/index.php?title=Action_-_Connection_Required_-_executeSQL#Return_Data

      As a note the Ad Hoc always passes SQL to the executeSQL action as an array (SQL[0], SQL[1]) so it's output looks a little different if you ever want to use something like 'firebug' (http://getfirebug.com/) if look at the raw web calls.   (JSONLint is also really good at formating return JSON object (http://www.jsonlint.com/) if you ever want to look at what is coming back)

      Now for a direct answer to your question if you use this to get the parameter back it should work:

                      <assignLocalParameter name="MESSAGE" type="returnObject">
                          <value>returnValue.parameters.recommendation</value>
                      </assignLocalParameter>

      Hope this helps.

      Matthew Vandenbussche

       
      • Peter Prib
        Peter Prib
        2009-04-15

        Yes it worked after the following revision

                                                <assignLocalParameter name="MESSAGE" type="returnObject">
                                <value>getRecommendation.returnValue.parameters.recommendation</value>
                            </assignLocalParameter>

         
    • Peter Prib
      Peter Prib
      2009-04-15

      I am now having difficulties with

                  <followOnAction condition="true">
                      <task>
                          <assignLocalParameter name="MESSAGE" type="returnObject">
                              <value>getRecommendation.returnValue.parameters.recommendation</value>
                          </assignLocalParameter>
                          <action  name="displayMessage" type="form">
                              <parameterList>
                                  <parameter name="MESSAGE" type="blockValue">
                                      <value>MESSAGE</value>
                                  </parameter>
                              <parameter name="MESSAGE1" type="fixed">
                                      <value>MESSAGE</value>
                                  </parameter>
                              </parameterList>
                              <message><![CDATA[
                                  <div style='width:500px'>
                                      <h1>Help</h1>
                                      ?MESSAGE?
                                      ?MESSAGE1?
                                  </div>
                                  ]]>
                              </message>
                          </action>
                      </task>
                  </followOnAction>

      message returns undefined and message1 returns MESSAGE

      By the way is there a way of locking the tab  whilst it responses.  I have noticed sometimes it takes a while and it it possible to hit the actionicon several times rresulting in the action happening more than once

       
    • Locking the screen:

      There is no way to lock an individual tab, in most cases I resoned that you would want a lock you would want no other actions occurring during that time so you can only lock the entire screen. (I am open to changing if given a good argument)

      You can lock the screen by adding the attribute lockScreen="true" to your action tag. The screen will be locked at the beginning of the action call and be unlock at the end if it was the action to lock the screen.

      http://apps.sourceforge.net/mediawiki/db2mc/index.php?title=XML_Complex_Type_-_Action

      There are also two TASK that can lock or unlock the screen at any time

      http://apps.sourceforge.net/mediawiki/db2mc/index.php?title=XML_Complex_Type_-_Task_Grouping

      <unlockScreen/>
      <lockScreen/>

      As for your problem, by what I am reading the output is correct for what is defined

      1: line 3: You assignLocalParameter, this is correct but its' scope is only within the current action as soon as you enter another action it is like calling a function and the local variable is no longer present.
      Solution: use the tag assignSharedConstant instead

      2: line 8: When assigning a parameter with the same name as a shared parameter the local parameter will be used. In this instance if you are calling the action from within a table when using the type blockValue the script processor will try to fill the value with the value of column 'MESSAGE' if it can not find the column it will fill the value with the default which you have not specified so it will be 'undefined'.
      Solutions: if using assignSharedConstant as above you can assess the variable directly or if you wish to reassign it to a new variable that you can access it with the type='constant'

      1 :<followOnAction condition="true">
      2 :<task>
      3 :<assignLocalParameter name="MESSAGE" type="returnObject">
      4 :       <value>getRecommendation.returnValue.parameters.recommendation</value>
      5 : </assignLocalParameter>
      6 : <action name="displayMessage" type="form">
      7 :    <parameterList>
      8 :        <parameter name="MESSAGE" type="blockValue">
      9 :            <value>MESSAGE</value>
      10:        </parameter>
      11:        <parameter name="MESSAGE1" type="fixed">
      12:            <value>MESSAGE</value>
      13:       </parameter>
      14:    </parameterList>
      15:    <message><![CDATA[
      16:        <div style='width:500px'> 
      17:            <h1>Help</h1>
      18:            ?MESSAGE?
      19:            ?MESSAGE1?
      20:        </div>
      21:]]>
      22:    </message>
      23: </action>
      24: </task>
      25: </followOnAction>

      Regards,
      Matthew Vandenbussche

       
      • Peter Prib
        Peter Prib
        2009-04-16

        I have discovered if I use assignSharedConstant or

        <parameter name="MESSAGE" type="returnObject">
                                        <value>getRecommendation.returnValue.parameters.recommendation</value>
                                    </parameter>

        then action does nothing.  It probably is a bug.  The object type is a CLOB.  That is to say once I add either above the action fails to operate.

        On tab lock or unlock I would suggest that if a action is being performed for the tab there should be the ability to lock the tab screen much the same way as the selection/refresh has the waiting icon.  If the action takes time unless the action throws up a message you aren't aware that the action has been accepted and are likely to click again.  What happens if you double click?

         
    • Sorry I did not get to this today,

      Tomorrow I am going to mock up a test case and see what is going on. There is no reason why it should not work, I actually do not differentiate between data types. It is possible that if you are outputting XML directly in this case it will not be encoded and thus may cause problems within the TE.

      In any case I will look into it further.

      If you double click, I believe the action will be executed twice.  Under the current framework there is nothing to stop it. hum... another thing I will look into (it is a long list)

      That is a good enough argument I will add it in... do you want it on the Window (tab) or on the Panel? Maybe both?

      I should have it done for the 3.5 release in about 2-3 weeks.

      Regards,
      Matthew Vandenbussche

       
      • Peter Prib
        Peter Prib
        2009-04-16

        If you are doing one and it not much overhead to do the other then I would do both.  If you have to chose I would do panel as it give more flexibilty.

        The problem is in the assignment as removing the variable from the output display it still has the same issue.

         
    • Hi Peter,

      I have tracked down the bug, it has to do with my use of decodeURIComponent, because I was initially URL encoding all items as you have seen this is bitting me more and more. As I have said in some of your other bugs reports I am working to fix this for 3.5 (mostly by completely removing the need for decodeURIComponent). It looked like a good design decisions a year ago but it is causes more problems then it would possible prevent.

      Regards,
      Matthew Vandenbussche