View Full Version : writing my own database retrival program

01-01-2006, 10:34 PM
I have read the documentaions istallation guide,creating pentaho solutions and Quick start.Now I want to check one database retrival writing my own program and not using samples, using pentaho_demo 1.0.0..Can any one help me about this.

Post edited by: ashokkumar, at: 01/02/2006 09:07

01-02-2006, 04:49 PM
I'm not exactly sure what you are trying to accomplish. I assume you are wanting to run a query against a database that is not one of our sample databases? How are you wanting to deploy this query? As a portlet? As a call to a JSP? A different external program?

The info given in the Creating Pentaho Solutions shows you how to define a custom datasource to the platform, and the Datasource samples shows you the action sequence to run a query against your custom datasource. You can then execute an action sequence one of three ways, as outlined in the Creating Pentaho Solutions guide - as a web service, as a Java API call, or as a URL.

If you give me more details about the pieces that are missing for you, or what exactly you are trying to do, I might be able to be of more help :)


01-02-2006, 09:23 PM
Thankq for repyling.
first I want to check by writing a database retrival query, using the database loaded when pentah demo is installed.

I prefred jsp , as i amhaving some experence on it.

I want to use the preconfigured jboss that comes with this, as i am new to this.I know tomcat but since i have to make many modifications and no body here has worked on this product so that i can take there assitance.I am learnig jboss for it.

If this works i want to use customization, As i want to create a database of my own and and work on it.Hope u can help out on this.

Post edited by: ashokkumar, at: 01/03/2006 01:26

01-03-2006, 06:02 AM
here I would like to explain in detail about what i want to write the query and execute:

this is with the database present with the pentaho_demo.

I want to get all the mail id of people present under a manager if i give the manager name (or) mail-id present in a department if i enter the department.

I am using pentaho_demo.zip.
hope u will let me know regarding this.

01-04-2006, 06:28 AM
You could start by adding the table you want to the sample data database in the Pentaho demo.

This table is a Hypersonic database table that you can edit with a text editor. The table can be found in pentaho-demo/data/sampledata/sampledata.script

If you look at this file you can see that we create two tables in this database using 'CREATE MEMORY TABLE ' statements and populate them using 'INSERT INTO' statements.

You can create your own tables and data by adding things to this file. Stop the demo from running before you edit the database file.

James Dixon

01-05-2006, 05:37 AM
i have inserted my table statements in it and created a.jsp file . it is giving error. how to over come this.i have wrote the jsp file using testquery.jsp

01-05-2006, 07:50 AM
testquery.jsp is only for the pivot component.

The best way to see your data is to edit one of the SQL business rules.

1. edit pentaho-demo/pentaho-solutions/samples/rules/CurrentPositionTitles.xaction

2. Near the bottom of the file change the contents of the <query> setting

3. Execute your query by running http://localhost:8080/pentaho/ViewAction?solution=samples&path=rules&action=CurrentPositionTitles.xaction

You can pass parameters into the query. CurrentPositionTitles.xaction inlcudes a parameter called 'dept' which you can change if you want.


01-09-2006, 11:01 PM
I have followed your instructios and tried it.but it is giving the error :
"ConnectFactory.ERROR_0001 - Database connection could not be established to: SampleData(org.pentaho.component.SQLLookupRule)"

i have runned on the browser:

I have created the table in"sampledata.script" and modified the query in "CurrentPositionTitles.xaction".
I have modified those file by opening in notepad,it is giving erro.And then, After modifing if i keep the same query that is written before(default query which was runned when checked),then also it is giving the same error(it is not working after i modified).

01-10-2006, 05:09 AM
From the sounds of your problem, and after conversing with James, it seems likely that your Hypersonic script has a problem with it. If you could post it up here, perhaps we could have a look and see what the problem is.

First, stop hypersonic
Second, create a zip file containing the files in that folder.
Third, attach the .zip file to a reply in this forum.

I'll have a look at it.

Take care,


Post edited by: mbatchelor, at: 01/10/2006 09:32

01-11-2006, 06:56 AM
Here i am sending the two files in .zip formate.just check it and tell.
here i have changed the file name and location inoder to send u tha's all.copied that file and i am sending u with a different name

I want to make clear that ,even if i change the sample.script file, the default query is runnig fine.Once u modify and save it is not working even it is defult one.And even sample data are working.

And I am not modifing anything when pentaho is running. When Pentaho runs do you see a message in the database window that SampleData started ok? .
and the query is

01-11-2006, 07:38 AM
The problem is in the syntax for the create of the table SKYLUX.

NUMBER(6) is not a valid SQL type.

To create this table, you must modify your SQL CREATE statement as follows:


Also, in your insert statements, you have a typo which will prevent hypersonic from being able to load this database - even if you fix the create statement.

On line 8 of your inserts, you have the following statement:


Note that the word MANAGER isn't quoted properly. For this to be valid SQL, you must surround the word MANAGER with single quotes:


May I suggest that you get the program dbvisualizer (http://www.dbvis.com). Create a connection to the hypersonic database (you'll need to follow their directions for creating a database connection). Then, when you want to create a table or do other DML, you can type/paste it into the SQL Commander window. If you have a syntax error, it will provide you with a meaningful error message.

For example, when I pasted in your creation code, I received the following error message:

10:26:33 [CREATE - 0 row(s), 0.051 secs] [Error Code: -16, SQL State: 37000] Wrong data type: NUMBER in statement [CREATE MEMORY TABLE SKYLUX(NAME VARCHAR(50) NOT NULL,SALARY NUMBER]
... 1 statement(s) executed, 0 row(s) affected, execution time 0.051 sec

Pasting in your insert statements one-at-a-time produced the following message on the 8th line:

10:37:39 [INSERT - 0 row(s), 0.010 secs] [Error Code: -28, SQL State: S0022] Column not found: MANAGER in statement [INSERT INTO SKYLUX VALUES('JUSTIN',40000,MANAGER,'BANGLORE')]
... 1 statement(s) executed, 0 row(s) affected, execution time 0.010 sec

Edit: Remove the "strong" tags from the output.

Post edited by: mbatchelor, at: 01/11/2006 11:40

01-12-2006, 07:45 AM
I have checked all the mistakes and corrected.But I think my problem is with file "CurrentPositionTitles.xaction"
I thought might b query is wrong and I have checked in all ppossible ways.by cahning the query parameters to my table:

like this all possible ways:but the same error.
this time I checked the "sampledata" And I want to know ,as u said that if there is error then why was the default query is running, as i first changed the "sample data file" and then i saw.Then it was not giving any error.please resolve this dout also.

01-12-2006, 08:27 AM
I'm afraid I can't help without some additional information. You said that you corrected the problems in the sampledata script file, but still the action sequence isn't working, correct?

To help troubleshoot this problem, please post a .zip file containing the following:

a- The sampledata files (as before)
b- The action sequence in question
c- The error log from the server

With this information, we should be able to see what's going on.

Take care,


01-13-2006, 04:10 AM
Here I am attachint both the files.juat see and tell.
I have tried the query in all possible ways.

And I want to know why default query is running if I type any error in the sampledata.script file.As u said earlier , it should also give error.

I think this I am making a small mistake.please inform me http://forums.pentaho.org/archived_att/files/sample.zip

01-13-2006, 05:27 AM
OK, I understand what your problem is now. You have a couple of syntax errors in your query.

Here's the query as you had it:

select distinct Position
from skylux
where salary='{sal}'
order by position

First, the value sal is an integer, so the parameter should not be surrounded by quotes.

Second, Position is a reserved word in Hypersonic. So, you must double-quote the column name. Here's what the final query should look like:

select distinct "POSITION"
from skylux
where salary={sal}
order by 1

I tested the following statement in dbVisualizer and I got one row back - MARKETING.

select distinct "POSITION"
from skylux
where salary=10000
order by 1

Take care,


01-30-2006, 10:29 PM
select distinct "POSITION"
from skylux
where salary={sal}
order by 1
this sysntax is not working, that means if we are giving parameter I am getting error.but it is running if i give value.what could be the problem.

01-31-2006, 05:49 PM
Are you satisfying the parameter with an input?

Can you post your action sequence so we can have a look?

02-02-2006, 09:27 AM
Based upon the action sequence attached to the post http://forums.pentaho.org/showthread.php?t=25876, the sal parameter is not declared as either an input to the action sequence or to the action definition. If the salary is to be specified as a parameter on the URL, then you will need to add something like the following to the input section of the action sequence.

<sal type="string">

In any case, you will need to declare sal is an input to the action definition.

<dept type="string"/>
<sal type="string"/>

Hope that helps....


Post edited by: adeshazor, at: 02/02/2006 14:12

02-08-2006, 06:01 AM
here iam posting the action sequence:please check it :


James Dixon







Post edited by: ashokkumar, at: 02/08/2006 10:04

02-08-2006, 06:39 AM
Could you post the action sequence as an attachment?


10-07-2008, 03:50 AM
This tutorial helps a lot