Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: PDI Datasource: MySQL and date parameter passthrough issues

  1. #1
    Join Date
    Aug 2016
    Posts
    142

    Default PDI Datasource: MySQL and date parameter passthrough issues

    Hi,

    I've come across an unusual circumstance and I hope someone can help me

    I rarely use PDI as a datasource but when I do it is usually in order to join data from two different databases (in this case redshift and MySQL)

    My transformation is a simple enough one, I just have two table inputs and I append one to the other

    Each table input is using a parameter for dates which are being passed from PRD to the transformation.

    Before when I have done this, I have usually not had a problem however in this case the dates are not being passed properly to the MySQL based table input

    When I first open the report in PRD and preview using default values, it works fine. However if I change the parameters to a different date, the MySQL data comes back null (even if I select the date that was the default). It seems the action of using the parameter causes it to fail.

    I had a quick look at the log and I think this might show the problem. The first box is when the report ran with defaults after first open and the second is when I select a different date

    Code:
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: YDate From
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: CompoundDataRow{envDataRow=ReportEnvironmentDataRow{columnMap={env::hostColonPort=hostColonPort, env::pentahoBaseURL=pentahoBaseURL, env::requestURL=requestURL, env::roles=roles, env::roles-array=roles-array, env::serverBaseURL=serverBaseURL, env::solutionRoot=solutionRoot, env::username=username}}, dataRow=ReportParameterValues{linkedMap={}}}
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: 2017-09-17
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: ------------------------------
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: = 2017-09-17
    2017-09-18 13:46:40,839 [6084509] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: ------------------------------

    Code:
    2017-09-18 13:46:56,441 [6100111] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: YDate From
    2017-09-18 13:46:56,441 [6100111] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: CompoundDataRow{envDataRow=ReportEnvironmentDataRow{columnMap={env::hostColonPort=hostColonPort, env::pentahoBaseURL=pentahoBaseURL, env::requestURL=requestURL, env::roles=roles, env::roles-array=roles-array, env::serverBaseURL=serverBaseURL, env::solutionRoot=solutionRoot, env::username=username}}, dataRow=ReportParameterValues{linkedMap={}}}
    2017-09-18 13:46:56,442 [6100112] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: Sat Sep 16 00:00:00 BST 2017
    2017-09-18 13:46:56,442 [6100112] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: ------------------------------
    2017-09-18 13:46:56,442 [6100112] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: = Sat Sep 16 00:00:00 BST 2017
    2017-09-18 13:46:56,442 [6100112] DEBUG - org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator - On Validate Single Parameter: ------------------------------
    In the 2nd instance it appears that the format of the date being sent has changed. It looks like Redshift is interpreting this correctly but MySQL is not

    Anyone have any ideas on how to fix this issue?

    Thanks

  2. #2
    Join Date
    Aug 2016
    Posts
    142

    Default

    I have found the issue

    I was using parameters that were simply type date. It appears MySQL needs them as type date (SQL)

    It's the obvious solution but I didn't consider it as I have always used date with no problems until now

    The only thing now is that the report complains that the parameters are not a valid type but it will accept them if I run the report anyway, so not a big deal

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.