stored procedures

[ajorgensen]

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]http://www.jasperforge.org/components/com_joomlaboard/uploaded/images/stored_proc.jpg

[Giulio Toffoli]

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

[ajorgensen]

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]

 

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

[Giulio Toffoli]

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

[ajorgensen]

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.

[nuruthin]

Hi,

 

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

 

Thanks in advance,

 

Nuruthin

[BhaskarBV]

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]

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

[bklawans]

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

[BhaskarBV]

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

[bklawans]

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]

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

[ajorgensen]

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.

[latino]

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.

[nuruthin]

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

[latino]

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=28579

 

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]

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

[nuruthin]

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]

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]

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

[minh]

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?

[marianela]

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

[dharmas]

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

[dprogrammer]

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.