PDA

View Full Version : how to make a Connection of Oracle Database to Pentaho BI



dcsk
12-19-2006, 03:29 AM
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?

vsb
12-19-2006, 05:31 AM
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.

riffmeister
12-22-2006, 06:18 AM
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>jdbc:oracle: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

yuping
01-02-2007, 02:52 PM
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)

yuping
01-02-2007, 03:26 PM
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?

yuping
01-02-2007, 04:15 PM
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=jdbc:oracle: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