View Full Version : how to do database acess

12-27-2005, 02:09 AM
I want to how can i do modifications ,retrive data and other applications using PentahoBI.
please let me know.

12-27-2005, 11:41 AM
there are several ways to make modifications to the platform, depending on what it is you are trying to achieve.

To create custom solutions, see our Creating Pentaho Solutions Guide, available at http://www.pentaho.org/downloads/doc.

If you are trying to bridge to another application and include it's functionality in the platform, then you can create a custom component by extending org.pentaho.component.ComponentBase java class.

I would suggest you start by playing with the demo that is downloadable from http://www.pentaho.org/downloads/platform, and reading the available documentation. After that, if you send us details of what it is you are trying to accomplish, we are certainly willing to help!

best regards,

01-11-2006, 05:19 AM

I'm evaluating the Pentaho product. I would like to access to an Oracle database.
The documents "Creating Pentaho Solutions" explain how to configure. What I do :
- I put classe12.jar in pentaho-demo\\jboss\\server\\default\\lib
- I created an oracle-ds.xml so :


- aflter, I update jboss-web.xml like that :


- and, I update web.xml :

Oracle Connection

For testing an example, I modify PCI/datasources/SQL_Datasource. I just replace SampleData with Oracle in jndi tag. The error is :
Erreur : ConnectFactory.ERROR_0001 - [fr_1] Database connection could not be established to: Oracle (org.pentaho.component.SQLLookupRule)

I tested many solutions, but I don't understand my mystake. Very thanks to give a solution.


Post edited by: bbx, at: 01/11/2006 09:21

01-11-2006, 06:00 AM
Hi there,

It looks to me like your JDBC URL is suspect. For the Oracle Thin driver, I found many examples that look more like this:


Hope this helps,


01-11-2006, 09:10 AM

thank you for your response.
Infortunetely, I always have tha same mystake. If you, or someone, have a not idea ... thans to thonk about me.


PS : I read in Pentaho documentation, that the file server.xml must update with the declaration of database to declare the db to tomcat. Must I do it ?

PPS : someone try and success to connect into a Oracle db ?

01-11-2006, 09:39 AM
I'd be happy to help, but I need to know more about your configuration before I can be of assistance.

Are you using our pre-configured install, or are you installing yourself into Tomcat?
Can you successfullly connect to your Oracle database using some other JDBC product using the JDBC connection parameters you've given Pentaho? I suggest trying dbvisualizer - http://www.dbvis.com
Did you update the JDBC URL I posted with the correct Oracle SID? I just used ORCL as an example. That parameter must be set to the correct SID or else the connection will not work. Please see the Oracle JDBC driver documentation for more information

01-11-2006, 10:21 AM

there are the answers to your questions.

- I'm using the pre-configured install.
- the JDBC connection I gave is an extract of my tnsnames.ora and it's ok. I use other non-oracle tools (with xml & java), which connect on dbOracle with the same definition, wihout any pb. So, I suppose it is correct.
- I try that you said before with SID=DEV without success.

The problem can be in the JDBC connection or in the configuration files. Is my configuration good ? Or do I configure another file(s) ?
I will try again, with another definition of the JDBC definition. I would try another configuration files but, I have no idea about which files.

Very thanks for your help.


01-11-2006, 11:20 AM
Here are some examples I've found on the 'net for creating an Oracle datasource in JBoss using the Thin driver:


http://www.onjava.com/pub/a/onjava/2004/02/25/jbossjdbc.html#oracle - Ignore the Jaws stuff - this is just some information about the jdbc URL format, and the xxx-ds.xml definition.

I've also attached an example xxx-ds.xml file that I found on the internet that uses the thin driver.

As I recall, some servers had trouble dealing with the classes in a .zip file instead of a .jar. You may need to rename the file to classes12.jar.

If none of this helps, please post the following in a single .zip file:

a- The xxxx-ds.xml file
b- The modified pentaho web.xml
c- The jboss-web.xml
d- The JBoss log files.

Also, please let me know which version of Oracle you're using.

I hope this helps,


Post edited by: mbatchelor, at: 01/11/2006 15:21

01-11-2006, 11:24 AM
Here's the file I meant to attach. http://forums.pentaho.org/archived_att/files/oraclexml.zip

01-11-2006, 12:38 PM

thanks for your help (and so quick). I resolve my problem like that, like your advises, and it works very good ! :-)

I join the complete solution I used.

Very thanks again and best regards,


Definition of an Oracle connection :

oracle-ds.xml contains :


web.xml contains :

Oracle Connection

jboss-web.xml contains :


For test the dbconnection to Oracle, I used the file SQL_Datasource.xaction with changing the jndi tag like that :


(for tests, I used the data and tables, define in the file Pentaho\\pentaho-demo\\data\\sampledata\\sampledata.script. I executed the SQL code to create tables with the same data as the example file).
To testing the impact and the connection on the db, I droped the table and put only on record in the table DEPARTMENT_MANAGERS.
The query return only this tuple. So, all it's ok ! Yeahhhh)

02-08-2006, 07:37 AM
First, thank you all for develloping this product.

Now, back to difficulties, I'm afraid.
I have a few difficulties connecting pentaho to an external MySql database.

I tried to follow the guides given when connecting to oracle, and all guides given to connect JBoss to Mysql in the official Mysql manual, but for some reason, it still doesn't work.

Mysql works with other programs, like mysqlcc or DBDesigner.

I made a jndi "connection", following directions.
The Jconnector driver has been installed in the /pentaho-demo/jboss/server/default/lib/ directory, as suggested in the Mysql manual and your creating pentaho solution manual.
I'll include the web.xml, jboss-web.xml and xmysqlds-ds.xml files.
The xmysqlds-ds.xml file is located in the /pentaho-demo/jboss/server/default/deploy/

My version of pentaho is the demo version, i'm running on mandrake 10.2. The demo runs perfectly, except for the problem mentionned above.

The error message shown is :
ConnectFactory.ERROR_0001 - [fr_1] Database connection could not be established to: XMySqlDS (org.pentaho.component.SQLLookupRule)

Thank you in advance for your support.


Post edited by: somat_38, at: 02/08/2006 11:40

02-08-2006, 08:46 AM
Hello Mathieu,

I looked at your files and everything seems ok. Can you post a bit more of the stack trace and perhaps your xaction file?


02-09-2006, 12:28 AM
By the stack trace, do you mean this (sorry I'm french) ?

Erreur : ConnectFactory.ERROR_0001 - [fr_1] Database connection could not be established to: XMySqlDS (org.pentaho.component.SQLLookupRule)

Debug : [fr_21] Starting execute of samples/getting-started/Copy of Copy of Copy of HelloWorld.xaction (org.pentaho.solution.SolutionEngine)
Debug : [fr_30] Getting runtime context and data (org.pentaho.solution.SolutionEngine)
Debug : [fr_33] Loading action sequence definition file (org.pentaho.solution.SolutionEngine)
Debug : [fr_34] audit: instanceId=7c92c3d1-988b-11da-ae2d-f90acbbe3155, objectId=org.pentaho.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.runtime.RuntimeContext)
Debug : [fr_1] validateComponent validating component org.pentaho.component.SQLLookupRule (org.pentaho.runtime.RuntimeContext)
Debug : Validation du composant en cours pour laction {0} (org.pentaho.component.SQLLookupRule)
Debug : [fr_42] Executing action sequence (org.pentaho.runtime.RuntimeContext)
Debug : [fr_24] Executing action definition: Iteration {0}0 (org.pentaho.runtime.RuntimeContext)
Debug : [fr_34] audit: instanceId=7c92c3d1-988b-11da-ae2d-f90acbbe3155, objectId=org.pentaho.component.SQLLookupRule, messageType=component_execution_started (org.pentaho.runtime.RuntimeContext)
Debug : [fr_3] execute pre-audit (org.pentaho.runtime.RuntimeContext)
Debug : [fr_21] Setting component log level to DEBUG (org.pentaho.runtime.RuntimeContext)
Debug : [fr_11] Initializing component (org.pentaho.runtime.RuntimeContext)
Debug : [fr_4] executeComponent starting audited execute (org.pentaho.runtime.RuntimeContext)
Debug : Exécute la validation=true (org.pentaho.component.SQLLookupRule)
Erreur : ConnectFactory.ERROR_0001 - [fr_1] Database connection could not be established to: XMySqlDS (org.pentaho.component.SQLLookupRule)
Debug : Requête en cours - select Ville from Adresse (org.pentaho.component.SQLLookupRule)
Erreur : SQLBaseComponent.ERROR_0006 - Impossible dexécuter {0} (org.pentaho.component.SQLLookupRule)
Debug : [fr_15] executeComponent finished audited execute (org.pentaho.runtime.RuntimeContext)
Erreur : RuntimeContext.ERROR_0012 - [fr_33] ActionDefinition for org.pentaho.component.SQLLookupRule did not execute successfully (org.pentaho.runtime.RuntimeContext)
Erreur : SolutionEngine.ERROR_0007 - [fr_27] Action sequence execution failed (org.pentaho.solution.SolutionEngine)

The xaction file is attached.
Thank you for your help.

Post edited by: somat_38, at: 02/09/2006 04:31

02-09-2006, 06:18 PM
Not quite.

By stack trace, I mean the server error log (located in the server/default/log directory. The server.log contains the stack trace telling us exactly what went wrong. Start your server, and navigate to/execute your action sequence. When you get your error, stop the server, and post the server.log file here.

02-10-2006, 12:13 AM
Ok here it is. http://forums.pentaho.org/archived_att/files/server.log.zip

02-10-2006, 02:22 AM
Hello Matthieu,

thank you for posting the log file. I looked at the file and found some interesting exceptions. You can not connect to your MySQL server because of the following error:

2006-02-10 09:11:59,518 ERROR [org.pentaho.component.SQLLookupRule] e84dd1ed-9a0c-11da-9721-43ac37e94c6a:COMPONENT:context-609479-1139559118891:Copy of Copy of Copy of HelloWorld.xaction ConnectFactory.ERROR_0001 - [fr_1] Database connection could not be established to: XMySqlDS
org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:


MESSAGE: java.net.ConnectException: Connection refused


java.net.SocketException: java.net.ConnectException: Connection refused

Is your MySQL server running on localhost on port 3306?


02-10-2006, 07:35 AM
I'm looking into it... since i'm pretty new to linux it'll take a while. I'll stay in touch.


02-10-2006, 08:11 AM
I have a strong feeling this is a MySQL configuration issue. Either MySQL is not running or it is not listening (configuration problem?).

Use the 'ps' command (with the appropriate flags) to see if MySQL is in fact running, if you do not see it you'll have to start it. Once you know it is running you can check if it is listening on port 3306 by trying to connect to that socket using 'telnet localhost 3306'. If the connection is refused the server is not running, if you get anything besides connection refused it is running. You may get garbage or a hanging prompt when you try to telnet, but that at least means the server is listening and alive.

Hope this helps,

02-12-2006, 12:17 AM
Hello Matthieu,

additionally you can use netstat to see if the mysqld is running and listening on the mentioned port.

Do something like:

netstat -anb
and you watch out for your port number and the process that is using this port, it should be mysqld or something similar.

Good luck.

02-13-2006, 06:47 AM
Thanks to you all, the problem is indeed Mysql, it is not listening to TCP/IP requests, despite the fact that it works fine otherwise.

EDIT : the problem lies with the --skipnetworking option, you need to disable it for pentaho to connect successfully.
Thank you all again for your continued support, everything works fine now. I'll call on you again if problems arise !


Post edited by: somat_38, at: 02/13/2006 10:48

Post edited by: somat_38, at: 02/14/2006 10:11

04-06-2006, 01:58 PM
Excuse me but I need an information....what can I do to disable this option?

Please help me

06-01-2006, 10:53 AM
hello :
Maybe you could help me with this one I'm having the same problem, I cannot connect to a mysql external db.
my system is a red hat linux 9 with pentaho-demo installed and running properly i think.
I wanted to try doing an sql query so i read the "creating pentaho solutions", I downloaded pentaho desing studio and modified "SampleDataQuery.xaction" to connect to my mysql database.
I opened the file with the desing studio and went to "Define process" and modified Database Connection with:

Connection: SampleDataQuery
user name: usr
password: pass
I executed with URL


and i get that:

Error: ConnectFactory.ERROR_0001 - Database connection could not be established to: com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/irm (org.pentaho.component.SQLLookupRule)

Debug: Starting execute of samples/getting-started/SampleDataQuery1.xaction (org.pentaho.solution.SolutionEngine)
Debug: Getting runtime context and data (org.pentaho.solution.SolutionEngine)
Debug: Loading action sequence definition file (org.pentaho.solution.SolutionEngine)
Debug: audit: instanceId=abea9dac-f19d-11da-b73f-bfaa2cc1e3b8, objectId=org.pentaho.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.runtime.RuntimeContext)
Debug: validateComponent validating component org.pentaho.component.SQLLookupRule (org.pentaho.runtime.RuntimeContext)
Debug: Validating component for action SampleDataQuery1.xaction (org.pentaho.component.SQLLookupRule)
Debug: Executing action sequence (org.pentaho.runtime.RuntimeContext)
Debug: Executing action definition: Iteration {0}0 (org.pentaho.runtime.RuntimeContext)
Debug: audit: instanceId=abea9dac-f19d-11da-b73f-bfaa2cc1e3b8, objectId=org.pentaho.component.SQLLookupRule, messageType=component_execution_started (org.pentaho.runtime.RuntimeContext)
Debug: execute pre-audit (org.pentaho.runtime.RuntimeContext)
Debug: Setting component log level to DEBUG (org.pentaho.runtime.RuntimeContext)
Debug: Initializing component (org.pentaho.runtime.RuntimeContext)
Debug: executeComponent starting audited execute (org.pentaho.runtime.RuntimeContext)
Debug: execute validation=true (org.pentaho.component.SQLLookupRule)
Error: ConnectFactory.ERROR_0001 - Database connection could not be established to: com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/irm (org.pentaho.component.SQLLookupRule)
Debug: Running query - select * from computers (org.pentaho.component.SQLLookupRule)
Error: SQLBaseComponent.ERROR_0006 - Could not execute SampleDataQuery1.xaction (org.pentaho.component.SQLLookupRule)
Debug: executeComponent finished audited execute (org.pentaho.runtime.RuntimeContext)
Error: RuntimeContext.ERROR_0012 - ActionDefinition for org.pentaho.component.SQLLookupRule did not execute successfully (org.pentaho.runtime.RuntimeContext)
Error: SolutionEngine.ERROR_0007 - Action sequence execution failed (org.pentaho.solution.SolutionEngine)

I made the tests to verify the mysqld is runnig with ps and telnet host 3306 and it is running.

I'll send you the server log file.
If you need something more or anything plz let me know.
escuse me my ignorance but im pretty new at this :s

Thank you very much for helping ! http://www.pentaho.org/components/com_simpleboard/uploaded/images/mysql.jpg http://forums.pentaho.org/archived_att/files/myserver.log

Post edited by: espel, at: 06/01/2006 14:55

06-02-2006, 12:37 PM

From the server.log you posted I see that the server cannot find the mySQL JDBC Driver:

2006-06-01 15:37:25,762 ERROR [org.pentaho.component.SQLLookupRule] Error Start: Pentaho build 156
2006-06-01 15:37:25,762 ERROR [org.pentaho.component.SQLLookupRule] abea9dac-f19d-11da-b73f-bfaa2cc1e3b8:COMPONENT:context-6989294-1149187045729:SampleDataQuery1.xaction ConnectFactory.ERROR_0001 - Database connection could not be established to: com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/irm
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1332)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1181)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
at java.lang.Class.forName0(Native Method)

Make sure that you have added the mysql-connector-java-3.1.12-bin.jar to the \\jboss\\server\\default\\lib directory.

There is a tech tip on the Pentaho web site that has more info about using mySQL:


Let me know how it goes,