stored procedures

0
In the iReport 1.2.4 doc, the only thing mentioned about stored procedures is that they can only be used by way of a temp table. When I "execute" one of our stored procedures in the query builder though, all the fields are grabbed, but only the first one is available to be added to the report. When the report is run with this one "expression" the first column of the result is correctly displayed. I'm curious why iReport doesn't recognize each field as unique to be used in the report...

Thanks! size=399]
ajorgensen's picture
Joined: Aug 1 2006 - 2:49am
Last seen: 12 years 11 months ago

28 Answers:

0
You have to set a different name for each field.

The store procedure can work in JasperReports if they are able to return a ResultSet. This is possible in SQL server, but not i.e. in Oracle.

The new JR pluggable QueryExecuters system opens a new frontier about this problem. Barry Klawns was able a couple of weeks ago to write a QueryExecuter to embed Store procedure support in JR for Oracle users.

Giulio
giulio's picture
21686
Joined: Jan 2 2007 - 4:15pm
Last seen: 4 months 2 weeks ago
0
Giulio, thanks for your help. We are using an Informix database. How do I specify a different name for each field in the query tool? i tried the statement: "execute procedure webagnsales('2005-07-01','2005-08-01',1,'','') as f1, f2, f3... f13" (and various other syntaxes) but "syntax error" was returned.

We modified the stored procedure to include column aliases, and it works. now, I guess my question is how to use aliases in the iReport query instead of having to modify all our old stored procedures.

Thanks again...
Post edited by: ajorgensen, at: 2006/08/08 19:08
ajorgensen's picture
Joined: Aug 1 2006 - 2:49am
Last seen: 12 years 11 months ago
0

Post edited by: ajorgensen, at: 2006/08/08 18:25
ajorgensen's picture
Joined: Aug 1 2006 - 2:49am
Last seen: 12 years 11 months ago
0
You have to edit each field from the object library on the right of the design window (right click on a fiels, then Edit).

Giulio
giulio's picture
21686
Joined: Jan 2 2007 - 4:15pm
Last seen: 4 months 2 weeks ago
0
Giulio- I see that, but the problem is the fields that are returned from the "Report Query" all have the same name (no aliases are used in the stored procedure). when I click OK on Report Query, only the first field is added in the object library under "Fields". The fields are recognized as unique in "Report Query" by field type (by pressing "Read Fields") but are not given different names in that list. If I add the list of fields manually under "Fields" and set the appropriate "Field class type", as soon as I change the query and press ok the list of fields is blown away and replaced with the original single first field.
ajorgensen's picture
Joined: Aug 1 2006 - 2:49am
Last seen: 12 years 11 months ago
0
Hi,

Can you tell me how could you generate report through database stored procedures without using temp tables.

Thanks in advance,

Nuruthin
nuruthin's picture
Joined: Aug 20 2006 - 10:28pm
Last seen: 12 years 10 months ago
0
HI All
I can execute reports using Oracle database.
Now I am trying to call Oracle stored procedure in IReport using Report Query.I am using IReport1.2.2 version.
I tried with
call TEST('001')
exec TEST('001') and
TEST('001')
But in all the cases i got Invalid SQL error.

Do we need to create temporary tables for this.But prior to all let me know whether IReport is supporting Stored procedures. If yes any body le me know how to call.
Help me in this regard

Thanks
Bhaskar
BhaskarBV's picture
Joined: Aug 22 2006 - 9:39pm
Last seen: 12 years 10 months ago
0
Hi
I am also trying on calling oracle stored procedure.If you would have got please let me know the process.
Because i was ended up with Invalid SQL error.

I am using IReport1.2.2 and trying in Report Query.
Any help would be appreciable.

Please help me in this regard

Bhaskar
BhaskarBV's picture
Joined: Aug 22 2006 - 9:39pm
Last seen: 12 years 10 months ago
0
I just created a project with a sample custom query executer that support Oracle stored procedures that return results via a cursor. You can find it on the Developer Forge section of this site, project "OracleStoredProcedures".

Currently iReport doesn't support query executers, but Giulio is working on it. Giulio, any estimates on when it will be available?

-Barry
bklawans's picture
720
Joined: Jul 6 2006 - 1:21pm
Last seen: 2 years 3 months ago
0
Thanks Barry.
Is there any alternative or other ways that we can use stored procedures.
Also any estimation on Query Executer Project.
Please let me know.

Thanks once again

-Bhaskar
BhaskarBV's picture
Joined: Aug 22 2006 - 9:39pm
Last seen: 12 years 10 months ago
0
ajorgensen wrote:
Giulio- I see that, but the problem is the fields that are returned from the "Report Query" all have the same name (no aliases are used in the stored procedure). when I click OK on Report Query, only the first field is added in the object library under "Fields". The fields are recognized as unique in "Report Query" by field type (by pressing "Read Fields") but are not given different names in that list. If I add the list of fields manually under "Fields" and set the appropriate "Field class type", as soon as I change the query and press ok the list of fields is blown away and replaced with the original single first field.


Are you saying that the columns don't come back with unique names? If so, that sounds like a bug in the Informix JDBC driver. I haven't used Informix for many, many years, but I remember that their C API gave columns coming back from a stored procedure unique (but meaningless) names. The C API was a very thin level above their wire protocol, so I'm guessing for some reason the JDBC driver is ignorning them and giving every column the same name.

I did a quick scan through a copy of the Informix JDBC driver manual and found a section on where their driver behaves "differently than specified by the JavaSoft specification", a nice way of saying "spec violation". Amoungh the differences is that DatabaseMetaData.getProcedureColumns() ignores column names. Sounds like a big problem with stored procedure column names in the driver to me.

The doc I was looking at was over 6 years old - is there a newer version of the driver available?

-Barry
Post edited by: bklawans, at: 2006/08/25 17:05
bklawans's picture
720
Joined: Jul 6 2006 - 1:21pm
Last seen: 2 years 3 months ago
0
BhaskarBV wrote:
Thanks Barry.
Is there any alternative or other ways that we can use stored procedures.
Also any estimation on Query Executer Project.
Please let me know.

Thanks once again

-Bhaskar


Brian Burridge posted an article on his site about [url=http://www.brianburridge.com/2006/06/04/how-to-call-stored-procedures-fr... oracle stored procedures from JasperReports that gives an alternate method, but it involves modifying the stored procedure to use a temp table.

I know Giulio is close to wrapping up a new release of iReport, but I can't remember if JRQueryExecuter support is in it or not.
bklawans's picture
720
Joined: Jul 6 2006 - 1:21pm
Last seen: 2 years 3 months ago
0
bklawans wrote:
ajorgensen wrote:
Giulio- I see that, but the problem is the fields that are returned from the "Report Query" all have the same name (no aliases are used in the stored procedure). when I click OK on Report Query, only the first field is added in the object library under "Fields". The fields are recognized as unique in "Report Query" by field type (by pressing "Read Fields") but are not given different names in that list. If I add the list of fields manually under "Fields" and set the appropriate "Field class type", as soon as I change the query and press ok the list of fields is blown away and replaced with the original single first field.


Are you saying that the columns don't come back with unique names? If so, that sounds like a bug in the Informix JDBC driver. I haven't used Informix for many, many years, but I remember that their C API gave columns coming back from a stored procedure unique (but meaningless) names. The C API was a very thin level above their wire protocol, so I'm guessing for some reason the JDBC driver is ignorning them and giving every column the same name.

I did a quick scan through a copy of the Informix JDBC driver manual and found a section on where their driver behaves "differently than specified by the JavaSoft specification", a nice way of saying "spec violation". Amoungh the differences is that DatabaseMetaData.getProcedureColumns() ignores column names. Sounds like a big problem with stored procedure column names in the driver to me.

The doc I was looking at was over 6 years old - is there a newer version of the driver available?

-Barry<br><br>Post edited by: bklawans, at: 2006/08/25 17:05


Yes, thats it... the column names don't come back unique. The driver sends the same name for each field and iReport only recognizes unique names, so it only makes the first field available.
ajorgensen's picture
Joined: Aug 1 2006 - 2:49am
Last seen: 12 years 11 months ago
0
Hi, did you solve your informix sp problem ? I had to recompile jasper & ireport source changing the metadata instructions and deleting the '(', ')' simbols in order to get unique and valid identifiers.
latino's picture
3
Joined: Sep 13 2006 - 4:56am
Last seen: 12 years 9 months ago
0
hi bklawans,

But how can I download your complete source code... I created a user for me in jasperforge.org.. but still could not validate my user name... I tried to connect the repository thru eclipse... but failed.. please tell how can I get the comple source..

Thankx in advance

Nuruthin
nuruthin's picture
Joined: Aug 20 2006 - 10:28pm
Last seen: 12 years 10 months ago
0
You don't need an account here in jasperports or ireport forums. I had a lot o trouble creating a user account too.

In the links below you'll get the product source code

http://sourceforge.net/project/showfiles.php?group_id=36382&package_id=2...

http://sourceforge.net/project/showfiles.php?group_id=64348

The zips contain ant files to help you compile the source, you've got to modify the lines containing the getColumn names or labels, for instance

change the next instruction (in ireport 1.2.3, check it in the version you're using)

rsmd.getColumnLabel(i)

for this one

rsmd.getColumnLabel(i).replaceFirst("\\(", "").replaceFirst("\\)", "_" + i)

You'll get expression_i columns (expression_1, expression_2, ...)

In jasperreports change the if line comparing the columns names too
...
latino's picture
3
Joined: Sep 13 2006 - 4:56am
Last seen: 12 years 9 months ago
0
Sorry, in the replaceFirst instruction you need a double backlash or java escape symbol before the '(', ')' symbols, the forum html doesn't show it ...
latino's picture
3
Joined: Sep 13 2006 - 4:56am
Last seen: 12 years 9 months ago
0
hi,



I followed your instruction to call the stored procedure. But nothing is happening when I call the procedure from the Data> Report query window. Testing the jdbc connection is showing successful. But not retrieving any data and even not showing any error... I am sure the procedure is not calling from the report.



regards
Nuruthin
Post edited by: nuruthin, at: 2006/09/17 06:24
nuruthin's picture
Joined: Aug 20 2006 - 10:28pm
Last seen: 12 years 10 months ago
0
Hi,


Could you please tell me is it possible to create a new report by using the classes you provided and without using 'oracleStoredProcedure.jrxml'?


Actually I want to create a new report which calls an oracle stored procedure using ireport and show the datas on report. Can this possible if I am using only the 3 classfiles that you created. I don't want to edit 'oracleStoredProcedure.jrxml' to make my reports.


It would be appreciate if you explain in detail how should we start a new report using your package...


Thankx in advance.
Nuruthin
nuruthin's picture
Joined: Aug 20 2006 - 10:28pm
Last seen: 12 years 10 months ago
0
Hi,


Actually my requirement is to call the jasper reports from open report portal, which is created through iReport. But I am getting the exception "Error creating report: net.sf.jasperreports.engine.JRException: No query executer factory class registered for plsql queries. Create a propery named net.sf.jasperreports.query.executer.factory.plsql. ". So which jar file should I update to overcome this error message.


Thanks,
Nuruthin
nuruthin's picture
Joined: Aug 20 2006 - 10:28pm
Last seen: 12 years 10 months ago
0
I would appreciate it you could help me with below problem:


I followed your instruction to setup my 1.2.8 version working with oracle procedure, however I was not sure what to do in below step:

4) Download and build this project
What do I have to download, and how do I build the project, I assumes this module is included in ver 1.2.8?

Without running configuration steps, when create a new report, for step 2 ie.
2) Add a parameter to your report called "cursor" with the type
"java.sql.ResultSet". Be sure that "is for prompting" is turned off.

somehow I could not see java.sql.ResultSet in dropdown list, I did manually type it in, is it correct?

How do I map a jasper report field to the output cursor or the result return from oracle procedure?
minh's picture
1
Joined: Feb 6 2007 - 2:44pm
Last seen: 12 years 4 months ago
0
Hi everyone¡¡

I'm facing the same problem that you were talking about here...
I was trying to understand what to do in order to call a StoredProcedure from Oracle that returns a cursor with a lot of rows to display in the report...but sadly I'm so confused that I really lost track here..
Please.... Does anyone can provide me the steps to follow to achieve this requirement? Pleaseee¡¡¡ I really need help...

Thanks a lot in advanced... Regards for everyone...

Marianela
:S
marianela's picture
Joined: Jan 7 2007 - 10:08am
Last seen: 12 years 5 months ago
0

so...where can i found that JR pluggable QueryExecuters system ?

dharmas's picture
Joined: Feb 4 2007 - 8:38pm
Last seen: 2 years 9 months ago
0
Hi Barry, I did a search for your project but could not find it. Is it still available for download? It can solve a major road block for our project if it supports writing reports on store procs that return resuls via cursors.
dprogrammer's picture
Joined: Jul 19 2006 - 7:37am
Last seen: 1 year 1 month ago
0
Its on JasperForge. Try http://jasperforge.org/plugins/project/project_home.php?group_id=173 It looks like the release packages and wiki content got confused during the recent JasperForge migration - the wiki content is for a different project... You should be able to access the source repository and anonymously check out the source to build. I've started work on a significantly improved version, and have the core JasperReports part all working, but I'm having trouble getting the iReport plugin to work with iReport 3.1. -Barry
bklawans's picture
720
Joined: Jul 6 2006 - 1:21pm
Last seen: 2 years 3 months ago
0

bklawans
Wrote:

I've started work on a significantly improved version, and have the core JasperReports part all working, but I'm having trouble getting the iReport plugin to work with iReport 3.1. -Barry

 

Wow - now that's very interesting! How is it coming along? I've been searching and searching for a way to retrieve data from an Oracle stored procedure. I've attempted to use your OracleStoredProcedures project but I need to freshen up my Java build skills as I haven't successfully been able to get it to work as of yet. (Even if it did work it doesn't have a fields provider class does it? - I guess you'd design your report with a SQL statement and switch to plsql later right?).

It seems one could use the data direct JDBC Oracle driver as it supposedely returns a cursor result in the same way as SQL server and my sql - but its pricey.

If you're solution is available I would be very interested whether you release it as open source or as a commericial product.

Thank you

pjsmith's picture
Joined: Jul 22 2009 - 11:27am
Last seen: 3 years 10 months ago
0

The next version of iReport (3.5.3), planned for the end of this month includes the Barry's query executer for Oracle. I moved the Barry's code in the JasperReports Extensions project.
To use it, you will just have to set the query language to plsql.

Giulio

giulio's picture
21686
Joined: Jan 2 2007 - 4:15pm
Last seen: 4 months 2 weeks ago
0
Hi! I need to build a report that calls Oracle Stored Procedure. I\'m using iReport 3.5.3 and, in preview of iReport, all works fine. However, in my application (even if I use all jars of iReport), when I try to run this report, I received this error: net.sf.jasperreports.engine.JRException: No query executer factory class registered for plsql queries. How can I fix this? Thanks so much for any help and sorry for my English.
bruno_cobra's picture
Joined: Aug 4 2009 - 6:30am
Last seen: 9 years 10 months ago
Feedback
randomness