Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Parameter - Reports Designer (Oracle DB)

  1. #1

    Default Parameter - Reports Designer (Oracle DB)

    Hi,
    I am new to Pentaho Reports Development. I am trying to use Paramater ${selcty}
    the query is
    select country from country_master where country = 'SINGAPORE'
    for sample DB with Pentaho, query works fine,
    select country from country_master where country = ${selcty}

    but when i am using Oracle 9i DB, and when parameter used as above i have the error while previewing
    java.sql.SQLException: Invalid column type
    Searched the forum couldn't ind direct answer.
    Any help appreciated.
    Amin

  2. #2
    Join Date
    Feb 2009
    Posts
    321

    Default

    take a look to "parameters", define the correct datatype, try with "String" or "Object"

  3. #3

    Default Parameter - Reports Designer (Oracle DB)

    Quote Originally Posted by hernanthiebaut View Post
    take a look to "parameters", define the correct datatype, try with "String" or "Object"
    Thanks for the reply. I tried changing from String to Object also, still it doesn't work. Also tried using [param:selcty], it seems or Oracle DB the parameter is represented in different way rather than teh traditional ${selcty}

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

    Default

    All databases are parametrized in the same way in PRD - via PreparedStatements and the ${..} syntax. No exceptions.

    Can you post the full stacktrace and full error messag ethat your Oracle driver gives you?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5

    Default Parameter - Reports Designer (Oracle DB)

    Quote Originally Posted by Taqua View Post
    All databases are parametrized in the same way in PRD - via PreparedStatements and the ${..} syntax. No exceptions.

    Can you post the full stacktrace and full error messag ethat your Oracle driver gives you?
    Hi Taqua,
    Thanks for the reply.
    I'm pasting, the error trace, here.
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT
    TO_CHAR(A.TRAN_DATE,'DD/MM/YYYY')TRANDATE,
    FMIA."NAME_FAPP",
    FPR."GROSS_AMOUNT" AS AMOUNT_SCY
    FROM
    "FPIS_PURCHASE_REQ" FPR INNER JOIN "FPIS_FUNDS" B ON FPR."FUND" = B."FUND_CODE"
    AND FPR."FM" = B."FM_CODE"
    INNER JOIN "FPIS_INVESTOR_HOLDINGS_TRAN" A ON FPR."PURCHASE_REF" = A."REFERENCE_ID"
    AND A."ACCOUNT_ID" = FPR."ACCOUNT_ID"
    INNER JOIN "FPIS_MASTER_INV_ACCOUNT" FMIA ON FPR."ACCOUNT_ID" = FMIA."ACCOUNT_ID"
    INNER JOIN "FPIS_PORTFOLIO_CATEGORY" FPC ON FMIA."PORTFOLIO_CATEG" = FPC."CATEGCODE"
    INNER JOIN "FPIS_NAV" NAV ON A."FUND_CODE" = NAV."FUND"
    AND A."FM_CODE" = NAV."FM"
    AND A."TRAN_DATE" = NAV."NAV_DT"
    WHERE
    A.STATUS = 'AI'
    AND B.STATUS = 'AI'
    AND FPC.STATUS = 'AI'
    AND FPR.STATUS = 'AI'
    AND FMIA.STATUS = 'AI'
    AND FPR.AGGREGATED_YN = 'Y'
    AND A.FM_CODE = 'Aberdeen'
    AND FMIA."NAME_FAPP" = ${select_investor}
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:236)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:102)
    at java.lang.Thread.run(Thread.java:595)
    ParentException:
    java.sql.SQLException: Invalid column type
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
    at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6164)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1316)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2746)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3015)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:322)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:232)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:102)
    at java.lang.Thread.run(Thread.java:595)

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

    Default

    If I interpret that Thread here ( http://forums.oracle.com/forums/thre...83&tstart=4126 ) correctly, this is a bug in the Oracle JDBC driver, setting the column type to "OTHER" and then subsequently failing when trying to use that metadata to set the parameter value pointing to that column to <null>.

    You could try to upgrade your JDBC driver to the one for Oracle 10 - the driver is able to talk to a version 9 database as well. (At least if the JIRA guys are correct: http://confluence.atlassian.com/disp...ter+limitation )
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7

    Default Parameter - Reports Designer (Oracle DB)

    Hi Taqua,
    I have upgraded JDBC Driver 10G (classes12.jar) and i find the parameter works good.
    Thank you for the valuable suggestion.

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.