stored procedures

Post edited by: ajorgensen, at: 2006/08/08 18:25

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.

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

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

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

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

Sorry, in the replaceFirst instruction you need a double backlash or java escape symbol before the '(', ')' symbols, the forum html doesn't show it ...

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

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?

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

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