PDA

View Full Version : how to do database acess



ashokkumar
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.

gmoran
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,
Gretchen

bbx
01-11-2006, 05:19 AM
Hello,

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 :



OracleDS
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=distant_serveur)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=DEV)(SRVR=DEDICATED)))
oracle.jdbc.driver.OracleDriver

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


jdbc/OracleDS
javax.sql.DataSource
java:/OracleDS


- and, I update web.xml :


Oracle Connection
jdbc/OracleDS
javax.sql.DataSource
Container


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.

bbx

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

mbatchelor
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:

jdbc:oracle:thin:@distant_serveur:1521:orcl

Hope this helps,

Marc

bbx
01-11-2006, 09:10 AM
Hye,

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.

bbx

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 ?

mbatchelor
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

bbx
01-11-2006, 10:21 AM
Hello,

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.

bbx

mbatchelor
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://forums.oracle.com/forums/thread.jspa?threadID=336398&tstart=30

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,

Marc

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

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

bbx
01-11-2006, 12:38 PM
Hello,

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,

bbx



Definition of an Oracle connection :

oracle-ds.xml contains :




jdbc/Oracle
jdbc:oracle:thin:@serveur:1521:service_name_or_SID
oracle.jdbc.driver.OracleDriver
user
passwd




web.xml contains :


Oracle Connection
jdbc/Oracle
javax.sql.DataSource
Container



jboss-web.xml contains :


jdbc/Oracle
javax.sql.DataSource
java:/Oracle



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

jdbc/Oracle

(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)

Somat_38
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.
Matthieu

http://forums.pentaho.org/archived_att/files/PentahoModifiedFiles.zip

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

fpohlman
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?

Regards
Florian

Somat_38
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.
Matthieu
http://forums.pentaho.org/archived_att/files/xactionfile.zip

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

mbatchelor
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.

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

fpohlman
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:

** BEGIN NESTED EXCEPTION **

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

STACKTRACE:

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


Is your MySQL server running on localhost on port 3306?

Regards
Florian

Somat_38
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.

Matthieu

mdamour
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,
Mike

fpohlman
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.
Florian

Somat_38
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 !

Matthieu

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

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

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

Please help me

espel
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:

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

http://www.host.com:8080/pentaho/ViewAction?&solution=samples&path=getting-started&action=SampleDataQuery1.xaction

and i get that:

Failed
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

dmoran
06-02-2006, 12:37 PM
espel,

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 1.0.0.0 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:

http://www.pentaho.org/index.php?option=com_content&task=view&id=135&Itemid=276

Let me know how it goes,
Doug