Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: SQL SERVER. How to change dynamically schema

  1. #1

    Default SQL SERVER. How to change dynamically schema

    I want to create a report from a SQL SERVER instance.

    The instance has many "databases". Each one for a specific project but all with identical tables and columns. I mean, same SQL works in every "database"...

    For example, this sentence
    SELECT TEST, RESULT FROM TESTS

    works with
    SELECT TEST, RESULT FROM project1.TESTS
    and
    SELECT TEST, RESULT FROM project2.TESTS

    How can I change dinamically the <projectN> ?

    Initially I thought that declaring it as a parameter and using it as
    USE ${p_project};
    SELECT TEST, RESULT FROM TESTS

    But this way don't work. I receive an error during the execution of the Sentence.
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: USE ${P_PROJECT};
    ...
    ...
    java.sql.SQLException: [Microsoft][SQL Server Native Client 10.0][SQL Server]Sintaxis incorrecta cerca de '@P1'.

    If I use directly a name
    USE project1;
    SELECT TEST, RESULT FROM TESTS
    it works perfectly, so the syntax is correct.

    So, where is the problem? What am I doing wrong?

    Thanks

  2. #2
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    I think there is such possibility by using this
    "Select * from ${project}.table" approach inside the scripting-datasource or something similar.. (update: seems to be called "Custom JDBC datasource")
    Never did this however, but I think there were some blog-entries about this topic around.

    Maybe this entry will help you:
    http://www.sherito.org/2011/07/acces...mic-table.html

    HTH,

    Tom

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

    Default

    If you happen to use PRD-3.9 or 3.9.1, then the SQL datasource has scripting build in. So it is now even easier to rewrite SQL queries when needed. If your script handles <null> values for your parameter, then you get that sort of dynamic modification even in design-mode.

    http://www.sherito.org/2011/11/penta...tasources.html
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4

    Default

    Effectively.
    Rewriting the query; adding the database to the SQL [not using db;] but including the name on each from clause.
    Thank you.
    Last edited by jpruizmoyano; 02-06-2013 at 04:16 AM.

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.