Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: how to make a Connection of Oracle Database to Pentaho BI

  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Exclamation how to make a Connection of Oracle Database to Pentaho BI

    Hi:
    i have some problem regrading the connnection of Oracle DB to Pentaho BI. I manage to connect to MYSQL by including the following coding (xml file) to pentaho-demo\jboss\server\default\deploy:

    <?xml version="1.0" encoding="UTF-8"?>

    <datasources>
    <local-tx-datasource>
    <jndi-name>management_dashboard</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/management_dashboard</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password></password>

    <exception-sorter-class-name>
    org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
    </exception-sorter-class-name>
    <metadata>
    <type-mapping>MySQL</type-mapping>
    </metadata>


    </local-tx-datasource>
    </datasources>

    what is the coding for the Oracle DB?

  2. #2
    Join Date
    Dec 2006
    Posts
    4

    Default able to give the customer the ability to createcustom reports,by drag-n-drop of table

    Using pentaho, will we be able to give the customer the ability to createcustom reports, by drag-n-drop of tables/columns etc.?

    can u send the answer for above question.
    Last edited by vsb; 12-19-2006 at 05:36 AM. Reason: Post This as new thread

  3. #3

    Default

    Put the classes12.jar or ojdbc14.jar from your Oracle installation folder into jboss/lib.

    Set up your datasource in jboss/server/default/deploy to use this driver as follows:

    <datasources>
    <local-tx-datasource>
    <jndi-name>ds_name</jndi-name>
    <connection-url>jdbcracle:thin:@localhost:1521:ORACLE_SID</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>user</user-name>
    <password>password</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>

    <metadata>
    <type-mapping>Oracle9i</type-mapping>
    </metadata>
    </local-tx-datasource>

    </datasources>

    Amend jndi-name, localhost, ORACLE_SID, user and password to suit your database setup. You may also need to amend the port if your database isn't on 1521.

    Hope this helps.

    Dan

  4. #4
    Join Date
    Dec 2006
    Posts
    22

    Default I tried the Oracle connection, still doesn't work

    Hi, I tried the way you suggested for connecting to Oracle, still doesn't work.

    One problem I observed is that, in step 2 of the report wizard, when I try to select a data source, I choose JNDI (not XQuery), then either I to the "ADD", or do the "EDIT" when "SampleData" is highlighted, in either case, I only have two selections for the Driver, one is

    org.hsqldb.jdbcDriver

    another is

    sun.jdbc.odbc.JdbcOdbcDriver

    There is no Oracle driver! I put class12.jar into pentaho-demo/jboss/lib directory.

    In pentaho-demo/jboss/server/deply directory, there are several data source related files, such as

    datasource1-ds.xml
    datasource2-ds.xml
    ...
    datasource5-ds.xml

    These files are almost the same except some name difference. I modelled after these files and your suggestion made a new file datasource6-ds.xml

    In this directory, there is also files:

    solution1-ds.xml
    ...
    solution5-ds.xml

    I modelled after these files and your suggestion, made a solution6-ds.xml

    I even made an oracledata.xml file, using your suggestion.

    But, when I re-started Pentaho server, I still only have two choices of driver, no Oracle driver.

    There must be some step missing here (I don't think Pentaho will automatically recognize there is a new datasource file in its jboss/server/deply directory. There must be some way to inform Pentaho about the new data source file)

  5. #5
    Join Date
    Dec 2006
    Posts
    22

    Default I get Oracle driver in step 2 of the report wizard

    I am able to get jdbc.oracle.driver.OracleDriver in step 2 (define connections) of the report wizrd when I add classes12.jar into

    pentaho-report-design-wizard-1.2.0.373.GA\lib\jdbc

    However, another problem happened: When I click "ADD" to add a database connection, everything is fine, the test was successful, but after I click "OK", no new data connection show up in the connection list.

    This is still the step 2 of the report wizard I am talking about.

    Any suggestion?

  6. #6
    Join Date
    Dec 2006
    Posts
    22

    Smile I went through Oracle connection stuff for Report design wizard

    When I added classes12.jar to

    Pentaho-report-design-wizard-1.2.0.373.GA\lib\jdbc

    and when I go into report design wizard step2, when I click "ADD" or "EDIT", I found that I have Oracle drivers in the selection list.



    When I further editted (this idea is by reading another message) the XML file jdbc.xml in

    pentaho-report-design-wizard-1.2.0.373.GA\resources\solutions\systemsimple-jndi

    added five lines mimic the other resources in the file, except it is Oracle specific, then the reource I defined actually appear in the selection panel of the step 2 of the Report design wizard.

    The five lines I added are:

    LocalOracleConnection/type=javax.sql.DataSource
    LocalOracleConnection/driver=oracle.jdbc.driver.OracleDriver
    LocalOracleConnection/url=jdbcracle:thin:@localhost:1521:Oracle_SID
    LocalOracleConnection/user=myusername
    LocalOracleConnection/password=mypassword

    Hope this information is useful for all that interested to use Oracle with Pentaho report design wizard.

    yuping

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.