patricksand

Folks, I have an interesting question: how do I get a value from the database outside of the report query? In particular, how can I do this, using the same db connection before the report query runs and get the value into a variable? My first thought was to use an invisible subreport, but that doesn't appear to meet my needs (or I'm just clueless about using them).:dry: My second thought is to use a scriptlet and embed some code in the beforeReportInit() method. But I would need to capture the connection object and feed my own query in, get the results, and set a variable (the last part is easy). :S Any thoughts or experience? or clever suggestions? I'm going to look through the source code and the examples, but any replies that can speed this up will be greatly appreciated. Thanks in advance... (hey, if nothing else I ask good questions, right? ;) )

Ideally, you should be able to set a variable that increments and resets on group/subgroup boundaries. You could then use that to do the first page of the level (that is a print condition). You might want to create put a copy of the variable/field in the appropriate band, make it invisible by setting background color to foreground color, and using the aforementioned variable to determine when to do this. Unfortunately, I found that after the first initialization of a variable being reset on a group boundary (and using an increment of 1 on a page boundary), it resets the variable to NULL. I worked around this using a scriptlet and setting the variable to zero (essentially resetting it to the initial value) in the afterGroupInit() call... Look in the source files under demo/scriptlet for some good sample code. That's that zip file you can download with jasperreports... Kinda clunky...but it works... One thing to do with a scriptlet to make your life easy (other than getting the eclipse plug-in for iReports) is to compile it and put it in a jar, and add that jar to the CLASSPATH of the iReport tool. You'll typically get a ClassNotFound message when you save the .jrxml but it will go away when you compile it. Hope this points you in a good direction...

It's actually deceptively simple (at least in 1.3.2). Here's how to do it in iReport 1. In your variable or field you want to use, set the hyperlink value to the desired field or variable you want. Make sure it is set the hyperlink to Self... 2. Set the bookmark level to 1 (or whatever level you wish) That's it... When you run it, you may need to click on the Bookmarks tab in the PDF viewer to see the bookmarks, but they will be there.

It's actually deceptively simple (at least in 1.3.2). Here's how to do it in iReport 1. In your variable or field you want to use, set the hyperlink value to the desired field or variable you want. Make sure it is set the hyperlink to Self... 2. Set the bookmark level to 1 (or whatever level you wish) That's it... When you run it, you may need to click on the Bookmarks tab in the PDF viewer to see the bookmarks, but they will be there.

Hi... Having lots of fun with JasperReports and iReports 1.3.2 but I found what may be a bug. I created a variable that increments on each page but is reset on the group boundary. Yep, this is a relative page count per group; it's useful for setting pdf bookmarks at the start of each group. The problem is that setting the initial value to zero only works the on the first group value; subsequent resets on new group values sets the variable to NULL and not 0. Is this a bug or feature? My workaround was, since I was using a scriptlet anyway, to set the variable to zero in the afterGroupInit() method. Shouldn't a reset condition reset a variable to something sensible like the appropriate zero or blank value, or (better yet) the initial value? Or maybe we need to add a reset value field (in iReport) or method (in JasperReports) to deal with this? Thoughts and insights appreciated....

A bit of an update... While searching through the forum, I found the following response to a similar problem in Oracle (trying to return a value through a procedure argument): And as we already told you, JasperReports cannot deal with such stored procedures. It can only use SELECT queries or at least stored procedures that return result sets and have no OUT parameters at all. [OUT parameters are like Sybase OUTPUT parameters/arguments to stored procedures] Sigh...:dry: I suspect it is also true for Sybase. Can anybody from JasperSoft verify this? Not a show-stopper but something they should consider fixing in a new release to allow better use of SQL...:( You may also want to allow the return value of the stored procedure to be captured in a variable or parameter--a status or row count returned is a typical part of such code and is typically done for a good business reason. A lot of stored procedure code is done like this: Code:declare @status int . . . exec @status = proc @arg1, @arg2, ... if ( @status <> 0 ) ... Thanks in advance...

I'm trying to get a value back from a stored procedure in Sybase... Here's my code... In the database, the stored procedure gps_test1: Code:CREATE PROCEDURE dbo.gps_test1 @cls int = 0, @nr_svrs int = -1 output AS BEGIN SELECT * FROM master..sysservers WHERE srvclass = @cls select @nr_svrs = @@rowcount END (for a class, typically 0 for the server name, or 7 for connecting servers, it lists the database server information) The @nr_svrs int parameter is the return value I want...(the number of servers found) Now in my report I have defined the following parameters: $P{svr_class} java.lang.Integer with default of 0 (okay, new java.lang.Integer(0)) $P{nr_svrs} java.lang.Integer with default of -1 (ditto, new java.lang.Integer(-1)) and the report query is: Code:[code]exec gps_test1 $P{svr_class}, $P{nr_svrs} output Things execute nicely(:woohoo:), but $P{nr_svrs} stays set to -1 instead of being set to 1 (with svr_class set to 0) or 3 (when svr_class is set to 7)... Since Sybase supports output values through arguments, and it works in standard SQL like: Code:[code]declare @in int declare @out int select @in = 0, @out = -1 exec gps_test1 @in, @out output select @in, @out How can I get iReports or JasperReports to support returning a value to a parameter from a stored procedure argument returned? This would help me sell my firm on switching to this product, since it greatly simplifies the coding for the database down to defining parameters, setting values, and getting values to use along with the results returned. This shifts the focus to designing the form and the contents, which is were the value adds from JR and our folks can shine, respectively. Or is there a way to use a report variable as an argument and get it set from the returned argument value? Any help greatly appreciated...

One thing I'm going to try is to put the xalan and xerces jar files (or rather a copy that comes with iReport 1.3.2 into the ${JAVA_HOME}/lib/endorsed directory. This is based on some documentation on the Apache xalan-j FAQ that I have reproduced below. This could explain the parsing problem and why even having it explicitly listed in the CLASSPATH doesn't work. Amazing what a Google search can find... I'll report back later on how it works out (may be a while, have lots of other things to do with iReport for the next week or so...). Of course, others can try this out and report back, and earn my eternal gratitude and a hearty "10Q" (think about it and repeat it quickly). :side: Here is the link to the FAQ: http://xml.apache.org/xalan-j/faq.html and this is the relevant part: I'm having a problem running Xalan-Java on JDK/JRE 1.4 or higher JDK/JRE 1.4, and JDK/JRE 5.0 is packaged with an old version of Xalan-Java. The JDK/JRE will attempt to use this version instead of any on the classpath. Unfortunately, this causes problems when attempting to use a newer version of Xalan-Java. You can always determine which version of Xalan-Java you are running by using the EnvironmentCheck class or by using the xalan:checkEnvironment extension function. It is highly recommended that you use this method to verify the version of Xalan-Java you are running, especially before opening a bug report. To use a newer version of Xalan-Java and override the one packaged with the JDK: * use the Endorsed Standards Override Mechanism. Place the xalan.jar, serializer.jar, xercesImpl.jar and xml-apis.jar in the <java-home>/lib/endorsed directory, where <java-home> is where the runtime software is installed. The following methods do not work: * Using the CLASSPATH environment variable or using -classpath to place the new classes in the classpath. * Using the -jar option to explicitly execute the classes inside the new jar files. Post edited by: patricksand, at: 2007/04/05 14:01

Just a follow up: I finally got it to work when I removed the Java 1.4 EE SDK and installed the Java 6u1 SE runtime (1.6.0.1). A separate thread of mine on the 1.3.2 version describes the problems and workaround in more detail: http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&id=23380&catid=9

What I found that works is the following: 1. use '$P!{<parameter>}' instead of $P{<parameter>} for strings and $P!{<parameter>} for non-strings...no clear documentation, but I found it searching the forum. One word of caution: declare a default value for any parameter so that you can get definitions of any rows returned from the SP; even parameters associated with OUTPUT only SP arguments. 2. For non-parameter values, just declare @var type exec <stored_proc> @var and use it in the normal way on a SP call I'm not sure if you can set the variable to a value, but it works great for a return value... Hope this helps Post edited by: patricksand, at: 2007/04/05 14:43

Finally! I got it to work... Consider this a workaround...B) I installed the latest version of Java 6u1: java version "1.6.0_01" Java SE Runtime Environment (build 1.6.0_01-b06) Java HotSpot Client VM (build 1.6.0_01-b06, mixed mode, sharing) It finally compiles reports without bombing! And, after configuring up some viewers, I could preview the reports! :woohoo: Finally works as advertised! What versions of the xalan and xerces jar files are in the distro? I wonder if they have a compatibility issue with Java 1.5.0.11 or 1.4? We are standardized at this shop on Java 1.4; I can probably push them up on this application but it will require certification and distribution on systems...if we can make the magic happen on 1.4, it would be copacetic...

Well, I tried that (just removed 1.4 EE SDK and installed the 1.5.0.11 SE) and still have the problem. If I back off to the iReports 1.3.1, I can create and see reports/templates, but I can't compile or execute them within iReport--it gives that error... Very frustrating!

okay...there should be a butload of backslashes there...but you get the drift...I've attached things here as a text file: [file name=classpath.txt size=662]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/classpath.txt[/file]

Okay...I backed out of 1.3.2 to 1.3.1 and can at least create reports...but when I try to compile or run them (PDF Preview, for example) from a live source (a database connection that works fine)...I get the following: Compiling to file... .classic_landscape.jasper -> C:Program FilesJasperSoftiReport-1.3.1classic_landscape.java Error compiling the report Java source. java.lang.NoClassDefFoundError: org/apache/commons/digester/Digester at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:107) at it.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:535) at java.lang.Thread.run(Thread.java:595) Compilation running time: 32 Sure looks like a classpath problem but I don't see how, since I specifically include the digester jar in my %CLASSPATH% Environment is Windows XP and I just don't get it. I've checked through the forums, google, and what not and found nothing useful... Here's what I've got set up: (let me know if you need any other info) I'm running Java 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05), I've set JAVA_HOME to d:SunAppserverjdkjre (where the Java SDK is located) my classpath is set to: d:SunAppserverlib;d:SunAppServerjdkjrelibextjasperreports-1.3.1.jar;c: Program FilesJasperSoftiReport-1.3.1libcommons-digester-1.7.jar;c:Sybasej Connect-5_5classesjconn2.jar;c:SybaseJconnect-5_5classes;d:SunAppServerj dkbin;c:sybaseASEPMonclass.zip;c:sybaseASEP3pclass.zip;c:sybaseASEP_Win 323pclass.zip;c:sybaseASEP_Win32monclass.zip;C:NOTESLUTSCLASSES;.;C:PROG RA~1IBMSQLLIBjavadb2java.zip;C:PROGRA~1IBMSQLLIBjavadb2jcc.jar;C:PROGR A~1IBMSQLLIBbin;C:PROGRA~1IBMSQLLIBjavadb2jcc_license_cisuz.jar;C:PROGR A~1IBMSQLLIBjavadb2jcc_license_cu.jar;C:PROGRA~1IBMSQLLIBjavacommon.jar I also used the ClassPath option in the iReport tool and have set it to the jasperreports jar, the bean, and two commons jars (including the digester). So we should have things covered six ways from Sunday, right? But still I get a "can't find digester" error... If I don't get some help soon, I'm going to tell my manager that this product is just too twitchy to use in a production environment and we need to stick with our existing product (that we pay for). Sorry, but we can't have a multi-billion dollar company fail on reporting obligations because a product is twitchy.

I just downloaded and installed the windows version on my box (have Java 5-the SDK), changed the startup to iReports.bat, and added tools.jar in the proper spot. I start it up and when I go to create a new report, or use the report wizard, it always ends at file creation/save time with the following exception chain. I've removed it, rebooted, reinstalled, rebooted, and tried this several times. Help?! Exception in thread "AWT-EventQueue-0" java.lang.NoClassDefFoundError: org/apach e/commons/digester/Digester at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:620) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:12 4) at java.net.URLClassLoader.defineClass(URLClassLoader.java:260) at java.net.URLClassLoader.access$100(URLClassLoader.java:56) at java.net.URLClassLoader$1.run(URLClassLoader.java:195) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at java.lang.ClassLoader.loadClass(ClassLoader.java:299) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268) at java.lang.ClassLoader.loadClass(ClassLoader.java:251) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319) at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:620) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:12 4) at java.net.URLClassLoader.defineClass(URLClassLoader.java:260) at java.net.URLClassLoader.access$100(URLClassLoader.java:56) at java.net.URLClassLoader$1.run(URLClassLoader.java:195) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268) at java.lang.ClassLoader.loadClass(ClassLoader.java:251) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319) at it.businesslogic.ireport.gui.JReportFrame.<init>(JReportFrame.java:42 7) at it.businesslogic.ireport.gui.MainFrame.openNewReportWindow(MainFrame. java:7629) at it.businesslogic.ireport.gui.MainFrame.newReport(MainFrame.java:7517) at it.businesslogic.ireport.gui.MainFrame.jMenuItemNewDocumentActionPerf ormed(MainFrame.java:7441) at it.businesslogic.ireport.gui.MainFrame.jButtonNewActionPerformed(Main Frame.java:7258) at it.businesslogic.ireport.gui.MainFrame.access$3700(MainFrame.java:98) at it.businesslogic.ireport.gui.MainFrame$38.actionPerformed(MainFrame.j ava:2111) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:18 49) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.jav a:2169) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel .java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258 ) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonL istener.java:234) at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:2 31) at java.awt.Component.processMouseEvent(Component.java:5488) at javax.swing.JComponent.processMouseEvent(JComponent.java:3126) at java.awt.Component.processEvent(Component.java:5253) at java.awt.Container.processEvent(Container.java:1966) at java.awt.Component.dispatchEventImpl(Component.java:3955) at java.awt.Container.dispatchEventImpl(Container.java:2024) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212 ) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) at java.awt.Container.dispatchEventImpl(Container.java:2010) at java.awt.Window.dispatchEventImpl(Window.java:1774) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.EventQueue.dispatchEvent(EventQueue.java:463) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchTh read.java:242) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre ad.java:163) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149) at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)