Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Dynamic SQL injection

  1. #1
    Join Date
    Dec 2009
    Posts
    12

    Default Dynamic SQL injection

    Hi,
    I have a report requirement which has fixed layout and appearance. But the SQL query for data input is somewhat different depending upon the user input. One way to solve it is to write so many prpt file in report designer as are different combinations of SQL queries based on user input. But this would be kind of not-so-elegant solution as the layout and appaerance of all the reports would be same and ideally one report should serve all.
    So, Is there any way / API to inject different SQL query into report at runtime while calling it within a java / servlet? We are not using BI server, just standalone Tomcat to host prpt files.

    Any help would be highly appreciated.

    --Manish

  2. #2
    Join Date
    Jun 2007
    Posts
    260

    Default

    Manish

    I don't understand when you say "We are not using BI server". Do you perhaps mean you are not using the Pentaho User Console?

    Anyways, if your SQL queries are very similar (eg SELECT .... WHERE region='region A' for user 1, SELECT .... WHERE region='region B' for user 2 then you can use parameters in Report Designer.

    If your requirements are more complex than that, then you will have to use other techniques, like creating custom interfaces with the logic built in, or the CDF. I'm not sure Pentaho will support all deployment scenarios out of the box.

  3. #3
    Join Date
    Dec 2009
    Posts
    12

    Default

    "I don't understand when you say "We are not using BI server". Do you perhaps mean you are not using the Pentaho User Console?" --- We are using standalone Tomcat server to deploy war application consisting of servlet within which we call prpt files (created through reports designer) to render the output on the browser. so, we don't use BI server or user console as we have our own custom UI interface.

    We are aware of dynamic paramterization of reports queries but doesn't solve the different SQL queries requirement in our case. Take for example- select * from tableA where colA = {colvale} and colC is null, select * from tableA where colB={colB} and colA is null etc...so, there is different where clause depending upon user input. So, it would be very useful if we could dynamically inject where clause in default SQL query defined the prpt file.

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

    Default

    You can use a combination of "query::name" attibute-expression and the "SQL Datasource (Advanced)". Ordinary SQL-datasources expect a query name (some sort of alias to hide the ugly SQL from the common users). The Advanced-SQL datasource interprets the query-name directly as SQL, and thus if your attribute-expression produces a valid SQL string, you can use that to run the report.

    Some nice to know limitations on that:

    That attribute-expression can only access parameter values. It cannot access other expressions or functions. The query-result must return the same column names, or your report fields wont know where to read the values to print from. If you dynamically add columns, use aliases.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Dec 2009
    Posts
    12

    Default

    I couldn't find much documentation for "query::name" attribute expression and advanced SQL datasources. Could you please point some refereneces for these?
    Today, we tried java API (use of SQLReportDataFactory and connectionProvider) to supply SQL query to the report at runtime in servlet as follows and it worked:

    DriverConnectionProvider provider = new DriverConnectionProvider();
    provider.setDriver("org.hsqldb.jdbcDriver");
    provider.setProperty("user", "sa");
    provider.setProperty("password", "");
    provider.setUrl("jdbc:hsqldb:file:data/libraryinfo");
    SQLReportDataFactory dataFactory = new SQLReportDataFactory(provider);
    String sqlQuery = "SELECT * FROM LIBRARYINFO";
    dataFactory.setQuery("default", sqlQuery);
    report.setDataFactory(dataFactory);

    Just curious to know if this is efficient approach while dealing with dynamic SQL query?

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

    Default

    Well, this manual approach is a maintenance nightmare. Imaging you have to deal with 10, 100, 1000 reports this way.

    A attribute-expression can be added by hitting the green + on the attribute of your choice. So select the master-report in the structure-tree, go to the "attributes" table (below the structure tree) and select the "name" attribute in the "query" group (thats what I mean with query::name). Hit the plus. Insert a expression, for now just this formula:

    ="SELECT * FROM LIBRARYINFO"

    In the data-tab, remove your existing SQL- datasource, and add the "JDBC Custom" Datasource instead. (Under "Advanced").

    Done.


    In PRD, the "Advanced" section of the sample reports in the Welcome pane also contains a working example (called "JDBC Custom Datasource").
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Dec 2009
    Posts
    5

    Default

    Hi,
    I used same approach as you suggested but i just want to know after this how to call it from code and also in report designer is another way to verify that the query is returning correct row

    Thanks
    Rijwan

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.