Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: PREPARE syntax doesnt work in platform 2.0

  1. #1
    Join Date
    Apr 2007
    Posts
    2,010

    Default PREPARE syntax doesnt work in platform 2.0

    Hi,

    I presume reports should be backwards compatible in platform 2.0?

    Anyway it seems the PREPARE syntax is broken, this report which works in 1.7.0 fails in 2.0:

    16:43:23,786 ERROR [SQLLookupRule] Error Start: Pentaho Pentaho Platform Engine Core 2.0.0-M1
    16:43:23,864 ERROR [SQLLookupRule] ded7b9ab-7905-11dd-9aa9-99cb11105ad8:COMPONENT:context-11511896-1220370201146:COM-Rep-001GetData.xactionSQLBaseComp
    onent.ERROR_0006 - Could not execute COM-Rep-001GetData.xaction
    java.sql.SQLException: Non supported SQL92 token at position: 247: PREPARE
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)

  2. #2

    Default

    Hi, Dan.

    Could we trouble you to attach the report and xaction that you're trying to execute so we can trace the problem?

    Cheers,

    John

  3. #3
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    files attached to case: 00003889

    it could be because it's a sub action sequence?
    Last edited by codek; 09-03-2008 at 11:10 AM.

  4. #4

    Default

    Dan,

    Thanks for attaching the files, I will assign to an engineer and get back to you shortly. Subaction problem is definitely a possibility.

    John

  5. #5
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    many thanks! i'll continue playing with 2.0

    As well as a bi engineer, i'm also a tester by trade, so if i raise too much tell me to stop

  6. #6
    Join Date
    Oct 2006
    Posts
    817

    Default

    I tried to reproduce your problem, using a simplified version of your xactions, plus the latest code in the 2.0 line. I was unsuccessful in reproducing it. Can you supply the log? I'm hoping the SQL it's trying to execute will be present in the log.

  7. #7
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    log attached... if you want me to re-run with different log4j settings then just say!
    Attached Files Attached Files

  8. #8
    Join Date
    Oct 2006
    Posts
    817

    Default

    Action sequences aren't a strength of mine so my help (until backup arrives) is limited to some observations. The first query below is the text found in the xaction. The second query below is the log output of the same query. Notice that the template token has been replaced with "in ( {PREPARE:messageID} )"


    Original query in xaction (used as a subaction):

    Code:
    select count(distinct d.dispensary_id) as NUM_DISP_FILTERED
              from agg_special_message_sent asss,
                   scanner                  s,
                   scan_station             ss,
                   dispensary               d
            where asss.special_message_id = {messageID}
               and asss.scanner_id = s.scanner_id
               and ss.scan_station_id = s.scan_station_id
               and d.dispensary_id = ss.dispensary_id
               and asss.num_filtered > 0

    Query from pentaho.log:

    Code:
    select count(distinct d.dispensary_id) as NUM_DISP_FILTERED
              from agg_special_message_sent asss,
                   scanner                  s,
                   scan_station             ss,
                   dispensary               d
            where asss.special_message_id in ( {PREPARE:messageID} )
               and asss.scanner_id = s.scanner_id
               and ss.scan_station_id = s.scan_station_id
               and d.dispensary_id = ss.dispensary_id
               and asss.num_filtered > 0
    Are you saying that this exact set of action sequences (xactions) works in 1.7.x?

  9. #9
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    err, ok they do look different, I may have got mixed up between which version of the report i sent to you, however all of our reports use a similar construct - the data is looked up in a sub action sequence, and they all fail.

    nevertheless all of our reports work in 1.7.0.

    have you tried a simple case of using a subaction to get the data? if not shall I try one against sampleData?

  10. #10
    Join Date
    Oct 2006
    Posts
    817

    Default

    Using your example as a base, I've created a simple xaction exercising prepared SQL in a subaction. Please run this on your server to verify that it works. (It works perfectly on my setup.) This xaction uses SampleData that comes with the BI Server.
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    I get an error running that example in my 2.0 env:

    I tried enabling debug on the xaction but didnt get any more info. There's no exception in the log with my current log level.

    The xactions seem to open ok in the design studio.

    Error: RuntimeContext.ERROR_0012 - ActionDefinition for SubActionComponent did not execute successfully (org.pentaho.platform.engine.services.runtime.RuntimeContext)Debug: Starting execute of aegate/operations/commercial/COM-Rep-001.xaction (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: Getting runtime context and data (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: Loading action sequence definition file (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: audit: instanceId=d724b363-7f0a-11dd-a199-1535f8276972, objectId=org.pentaho.platform.engine.services.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: validateComponent validating component SubActionComponent (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Validating component for action COM-Rep-001.xaction (org.pentaho.platform.plugin.action.builtin.SubActionComponent)
    Debug: Executing action sequence (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Executing action definition: Iteration 0 (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Executing action definition: Iteration 0 (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: audit: instanceId=d724b363-7f0a-11dd-a199-1535f8276972, objectId=SubActionComponent, messageType=component_execution_started (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: execute pre-audit (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Setting component log level to DEBUG (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Initializing component (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: executeComponent starting audited execute (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: execute validation=true (org.pentaho.platform.plugin.action.builtin.SubActionComponent)
    Debug: executeComponent finished audited execute (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Error: RuntimeContext.ERROR_0012 - ActionDefinition for SubActionComponent did not execute successfully (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: audit: instanceId=d724b363-7f0a-11dd-a199-1535f8276972, objectId=org.pentaho.platform.engine.services.runtime.RuntimeContext, messageType=action_sequence_failed (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Error: SolutionEngine.ERROR_0007 - Action sequence execution failed (org.pentaho.platform.engine.services.solution.SolutionEngine)

  12. #12
    Join Date
    Oct 2006
    Posts
    817

    Default

    You probably made some tweaks to these xactions to make them work in your environment. Can you attach them back to this thread, along with your log file?

  13. #13
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    i didnt touch them, other than enable debug logging. I'm just running the standard demo, so the sampledata should work as expected.

    attached!
    Attached Files Attached Files

  14. #14
    Join Date
    Oct 2006
    Posts
    817

    Default

    Did you change the path in the SubActionComponent? Mine points to the etl folder since I dropped both COM-Rep xactions in that folder. Maybe the problem is that it can't find the GetData xaction.
    Code:
    <action-definition> 
      <component-name>SubActionComponent</component-name>
      <action-type>getData</action-type>
      <action-inputs> 
        <messageID type="long"/> 
      </action-inputs>
      <action-outputs/>
      <component-definition> 
        <solution><![CDATA[samples]]></solution>  
        <path><![CDATA[etl]]></path>  
        <action><![CDATA[COM-Rep-001GetData.xaction]]></action> 
      </component-definition> 
    </action-definition>

  15. #15
    Join Date
    Oct 2006
    Posts
    817

    Default

    It would help me get to the bottom of this very quickly if you could reproduce the problem using only SampleData. I could then debug the issue on my end with data that I actually have. Is that a possibility?

  16. #16
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    ok, your example works fine.

    So i then took each query from mine and added them in one by one, they all seem to work.

    And i'm now afraid to say that the original files I sent over work too, so i'm completely confused now. I can't identify what was different from when I had the original problem. Most of the other reports work too now so it looks like a wild goose chase - sorry! All i can think of is that i had some a version initially loaded into the repository which didnt match the version on the file system.

    Dan

  17. #17
    Join Date
    Oct 2006
    Posts
    817

    Default

    Glad to hear it's working now. And no problem about the wild goose chase--I learned some new stuff!

  18. #18

    Default

    Bomb Cambridgeshire thats what I say!!
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  19. #19
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    northern monkey!

    ;p)

  20. #20

    Default

    Thats what you say, but worryingly both me and haza were born in cambridge
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  21. #21
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    ah how weird, small world then.

    in that case that means im more northern than you in terms of birth!

  22. #22

    Default

    ya northern monkey!

    either that or all the great BI brains have a connection with Cambridge
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.