Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Parameters in JDBC-connection

  1. #1
    Join Date
    Jan 2013
    Posts
    100

    Default Parameters in JDBC-connection

    I want to use a report template for multiple data sources. How this can be done? In PDI, for example, I specify parameters directly in JDBC-connection and run transformation with the necessary data sources. Here is this possible?

  2. #2
    Join Date
    Jan 2013
    Posts
    100

    Default

    Up.

  3. #3
    Join Date
    May 2013
    Posts
    24

    Default

    I'm with you on that: currently I'm using a script that unzips the report, replaces the JNDI names in the datasources xml file, recompresses it and stores it in the tenant folder in the repository.
    It works, but it is clearly a temporary workaround until proper connection parameters are implemented

  4. #4
    Join Date
    Jan 2013
    Posts
    100

    Default

    Quote Originally Posted by PietroB View Post
    I'm with you on that: currently I'm using a script that unzips the report, replaces the JNDI names in the datasources xml file, recompresses it and stores it in the tenant folder in the repository.
    It works, but it is clearly a temporary workaround until proper connection parameters are implemented
    I now use as a data source PDI transformation. But it also has problems.
    http://forums.pentaho.com/showthread...850#post387850

  5. #5

    Default

    Check this post: http://forums.pentaho.com/showthread...mic-Datasource

    I have had some success with it.

  6. #6
    Join Date
    Jan 2013
    Posts
    100

    Default

    Hi. I tried your method (see. Pictures). But when running error occurs:

    Code:
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT    DISTINCT Year
    FROM Date
        at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:210)
        at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
        at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:125)
        at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:75)
        at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:432)
        at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:326)
        at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getValues(DefaultListParameter.java:102)
        at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.computeValidListValue(DefaultReportParameterValidator.java:318)
        at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validateSingleParameter(DefaultReportParameterValidator.java:256)
        at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validate(DefaultReportParameterValidator.java:140)
        at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.validateParameter(ParameterReportControllerPane.java:677)
        at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.reinit(ParameterReportControllerPane.java:500)
        at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportControllerPane.setReport(ParameterReportControllerPane.java:398)
        at org.pentaho.reporting.engine.classic.core.modules.gui.base.ParameterReportController$ReportUpdateHandler.propertyChange(ParameterReportController.java:92)
        at java.beans.PropertyChangeSupport.fire(PropertyChangeSupport.java:335)
        at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:328)
        at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:263)
        at java.awt.Component.firePropertyChange(Component.java:8382)
        at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane.setReportJob(PreviewPane.java:1112)
        at org.pentaho.reporting.designer.core.editor.preview.ReportPreviewComponent.updatePreview(ReportPreviewComponent.java:232)
        at org.pentaho.reporting.designer.core.editor.ReportRendererComponent.showPreview(ReportRendererComponent.java:471)
        at org.pentaho.reporting.designer.core.ReportDesignerFrame$FrameViewController.setPreviewVisible(ReportDesignerFrame.java:836)
        at org.pentaho.reporting.designer.core.actions.global.ShowPreviewPaneAction.actionPerformed(ShowPreviewPaneAction.java:76)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
        at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:289)
        at java.awt.Component.processMouseEvent(Component.java:6505)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
        at java.awt.Component.processEvent(Component.java:6270)
        at java.awt.Container.processEvent(Container.java:2229)
        at java.awt.Component.dispatchEventImpl(Component.java:4861)
        at java.awt.Container.dispatchEventImpl(Container.java:2287)
        at java.awt.Component.dispatchEvent(Component.java:4687)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
        at java.awt.Container.dispatchEventImpl(Container.java:2273)
        at java.awt.Window.dispatchEventImpl(Window.java:2719)
        at java.awt.Component.dispatchEvent(Component.java:4687)
        at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:723)
        at java.awt.EventQueue.access$200(EventQueue.java:103)
        at java.awt.EventQueue$3.run(EventQueue.java:682)
        at java.awt.EventQueue$3.run(EventQueue.java:680)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
        at java.awt.EventQueue$4.run(EventQueue.java:696)
        at java.awt.EventQueue$4.run(EventQueue.java:694)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:693)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
    Caused by: java.sql.SQLException: Driver Manager returned no connection. Your java-implementation is weird.
        at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.DriverConnectionProvider.createConnection(DriverConnectionProvider.java:146)
        at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.getConnection(SimpleSQLReportDataFactory.java:144)
        at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:195)
        ... 59 more
    What am I doing wrong?
    Attached Images Attached Images    

  7. #7

    Default

    I am currently using 3.9 of the report designer and 4.5 of the server and it works fine. In my testing of version 5 I get the same errors you do. I am trying to fix it but no luck yet. For version 5 I have been testing the following Groovy script:

    import org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.DriverConnectionProvider;
    import java.sql.*;

    def init(dataRow)
    {
    String s_dbServer = dataRow.get("s_databaseServer");

    String s_connectionURL = "jdbc:sqlserver://";
    s_connectionURL = s_connectionURL.concat(s_dbServer);
    s_connectionURL = s_connectionURL.concat(";DataBaseName=DATABASENAME");

    // this method is called once when the data-source is first used.
    DriverConnectionProvider connectionProvider = new DriverConnectionProvider();
    connectionProvider.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    connectionProvider.setUrl(s_connectionURL);
    connectionProvider.setProperty("user", "USERNAME");
    connectionProvider.setProperty("password", "PASSWORD");
    dataFactory.setConnectionProvider(connectionProvider);
    }

    def shutdown()
    {
    // place all shutdown logic here. If you use any persistent resources
    // like files or connections make sure you close them here.

    // this method is called once during the data-source shut-down. It
    // will be called after all query scripts have been shut down.
    }

    If you get the other version working let me know, that way I won't have to update all my reports. I don't have version 5 in Production yet, I have only done some limited testing with it.

  8. #8
    Join Date
    Oct 2014
    Posts
    1

    Default

    Here's my experience (PRD version 5.1.0.0-752 ) as compared to PDI (PDI version 5.1.0.0-752). In PDI I have been able to specify a schema as a variable
    e.g. select <columns> from ${schema_name} where <conditions> etc. This allows me to support mutliple instances of the same schemas(multi vs. single tenant) and not require
    the dev/qa environment to completely mirror production.
    I use JNDI pointing to the server but not to an individual schema. Works great and the schema name can be passed as a param
    Trying to do the same thing in PRD has proven to be rather difficult
    e.g. I can define a query
    select column_a,column_b from `foo`.`table_bar` where column_a LIKE ${rpt_param}.
    rpt_param is defined as a parameter w/in PRD.
    In order to maintain the same approach as PDI, I can't change the query to
    select column_a,column_b from `${schemaname}`.`table_bar` where column_a LIKE ${rpt_param}
    and define schemaname as a parameter with a default value of foo
    Trying a variable schema name results in report validation error

    The only way I have gotten this to work is to embed the following query script:
    function computeQuery (query, queryName, dataRow)
    {
    var schemaname = dataRow.get("schemaname");
    if (schemaname == null) {
    schemaname = 'foo'; // this is the default schema.
    }
    query = query.replace('`foo`',schemaname);
    return query;
    }

    It seems that during the initial validation process the parameters are not available so the definition doesn't validate. Hence the null check in the script
    This approach works but requires for each report to have the query script function which means the report writer needs to remember to do this for every static query

    It'd be awfully nice to have the same facility as PDI

    The alternative I'm aware of:
    a) change the JNDI name in the global script section
    This implies that I need to add a JNDI connection to BI-Server running on Tomcat every time a new schema is added (which means a maintenance window sequence). Granted this doesn't happen that often but I shouldn't have to do that considering that I have scheduled reports running all hours of the day
    b) supply direct JDBC connectors in the global script section - ok but now I need to have the port/username/pwd etc. embedded in the report defintion or perhaps extract it somewhere on the BI server. I don't want the report creators to know details about the prod. db

    I'd like to see what other approaches have been taken.

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

    Default

    @Kouki: The message you received literally means that the driver you use is weird. This message only comes up if we were able to successfully load the JDBC driver and ask it for a connection, but instead of either returning a valid connection or throwing an exception (in case some properties are wrong, for instance, or the database host rejects logins etc), it returned <null>, which is so far off the JDBC standard that it is embarrassing for the author of that JDBC driver.

    After checking the sources for the opensource MS-SQL driver, I can confirm that they are weird indeed: The JDTS driver will happily return <null> if the URL you specify does not match its expected result. So triple check your code and make sure that your URLs are correct and that you throw an error if the parameter on which you select the connection does not match your expected values.


    @loki
    JNDI in Pentaho terms is not the same as JNDI in Tomcat terms.

    When running on the server, we take the JNDI names defined in the report and try to find a Pentaho datasource definition first before falling back to real JNDI. So you if you login as admin on your BI-server you can add new datasources without having to restart your tomcat. So dont be afraid of server restarts, just simply do not use JNDI, use the Pentaho datasource definitions instead. Heck, these connection definitions are stored in a SQL database, so you could even automate the whole process of setting up datasources. Its so much better than raw JNDI.

    (If you are on 4.8 or older, the database administration is in the separate "Administration Console".)
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  10. #10
    Join Date
    Jan 2013
    Posts
    100

    Default

    Quote Originally Posted by Taqua View Post
    @Kouki: The message you received literally means that the driver you use is weird. This message only comes up if we were able to successfully load the JDBC driver and ask it for a connection, but instead of either returning a valid connection or throwing an exception (in case some properties are wrong, for instance, or the database host rejects logins etc), it returned <null>, which is so far off the JDBC standard that it is embarrassing for the author of that JDBC driver.

    After checking the sources for the opensource MS-SQL driver, I can confirm that they are weird indeed: The JDTS driver will happily return <null> if the URL you specify does not match its expected result. So triple check your code and make sure that your URLs are correct and that you throw an error if the parameter on which you select the connection does not match your expected values.
    Sorry, I do not quite understand your answer. It is possible to create a JDBC-connection in PRD with parameters (as in PDI)? Or do anything like that.
    About global scripting. I checked this code and all the url is correct
    Attached Images Attached Images  

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.