Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Passing table name as parameter to Report Designer

  1. #1
    Join Date
    Feb 2013
    Posts
    13

    Default Passing table name as parameter to Report Designer

    Hi everyone,

    I am struggling to figure out how I can use a parameter in report designer for a mySQL table name. I have a transformation that I am kicking off command line, and am passing in a handful of parameters. Everything works great for anything I use in my SQL where clause, but as soon as I try anything like "select * from ${TABLE_NAME} where id = ${ID}" the query fails. If I hardcode the table name, the query works, but my table names will be changing every time.

    If it's not possible for some reason, I need to figure out how to select all the information in spoon and pass the results to PRD. Any help would be greatly appreciated, thanks!

  2. #2
    Join Date
    Feb 2013
    Posts
    13

    Default

    I figured out one way to do it. This article helped immensely - http://wiki.bizcubed.com.au/xwiki/bi...te+dynamic+SQL

    I ended up setting up my full query (with table name variable) as a parameter in Spoon, which I then passed to PRD and then set up parameters similar to the way that article did it. I'm sure there is a reason Spoon can take a table name variable without a problem and Report Designer cannot, but at least there is a workaround.

  3. #3
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    PRD uses prepared statements for the query. Therefore the query can only contain value-parameters, never structural parameters. PRD is aimed at advanced business users, and reports are usually exposed to the public via the web. So any SQL injection is evil, and dangerous and has wide-ranging consequences.

    PDI, on the other hand, is aimed at database admins. They use SQL injection as a tool, not as a threat. The transformations run in a server that is shielded from the outside, and usually that stuff is not exposed to the general public. So the security on parameter handling can be less strict, so that you can get things done quickly.


    If you *want* controlled SQL injection, then use the query-scripting that is part of the SQL-datasources. Use the 'computeQuery' function to return your query, and make sure you mention all fields that your script uses in the 'computeQueryFields' function. If you dont, you will see funny results with the query-caching.

    Compute your query in a script, and you can insert your parameter wherever you want. Just be sure you have good checks in place that validates the possible values an outsider can pass in.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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.