benu

Just found this post - I think this person is experiencing the same problem: http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=81225 -Ben

I have a 2-column report for which I would like to display some content *immediately* below the last queried data (the 2-column detail band). If I put the content in the summary band, it ends up on a separate page. None of the other bands work properly (column footer displays twice and only on half the page; page footer prints content at bottom of page; arbitrary group footer also looks funny). If I change it to a 1-column report, the behavior is as I expect and desire: the summary band prints immediately below the detail band. Why does the 2-column report behave differently? How can I fix this? Thanks, Ben

On a Jaspersoft webinar yesterday, I asked, "Do you plan to add support for e-mailing reports to a dynamic list of e-mail addresses in JasperServer? IOW, deliver to e-mails retrieved from your data source? " I was answered by one of the presenters, "This is not built-in right now, but it can be done using a JasperServer report bursting component with JasperETL." Can someone point me to some documentation of this capability? The use case is this: I have accounts that are owned by users; I want to be able to deliver information about those accounts to their owners via a report out of JasperServer. Obviously, this would need to be dynamically queried from my data source. Thanks! Ben

Thank you Lucian. We'll figure something out. May just end up writing a script that culls any errors from the logs and independently sends out alerts to administrators. These would be really nice features for JasperServer - would make it a more robust offering! -Ben

OK, slightly different issue, but related: is there a way to get JasperServer to retry e-mail transmission? I'm getting intermittent failures, and it would be nice to have JasperServer retry sending. Here's the error: 2010-07-02 00:00:05,195 ERROR ReportExecutionJob,quartzScheduler_Worker-1:308 - An error occurred while sending job error notification. org.springframework.mail.MailSendException; nested exception details (1) are: Failed message 1: com.sun.mail.smtp.SMTPSendFailedException: 421 4.7.0 Temporary System Problem. Try again later (). v20sm49682322qce.10 at com.sun.mail.smtp.SMTPTransport.issueSendCommand(SMTPTransport.java:1388) at com.sun.mail.smtp.SMTPTransport.finishData(SMTPTransport.java:1215) at com.sun.mail.smtp.SMTPTransport.sendMessage(SMTPTransport.java:586) at org.springframework.mail.javamail.JavaMailSenderImpl.doSend(JavaMailSenderImpl.java:403) at org.springframework.mail.javamail.JavaMailSenderImpl.send(JavaMailSenderImpl.java:342) at org.springframework.mail.javamail.JavaMailSenderImpl.send(JavaMailSenderImpl.java:338) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.sendMailNotification(ReportExecutionJob.java:1259) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:445) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:231) at org.quartz.core.JobRunShell.run(JobRunShell.java:195) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520) Also, any way to get JasperServer to alert (email) an administrator on occurrence of any errors? (yes, I recognize the irony of this request). Thanks! -Ben

Thanks for the reply, Lucian. There were no pertinent messages in the View|Messages screen, unfortunately. Also, we'll likely be migrating to a different hosted mail solution in the near future, so I don't want to spend the time troubleshooting Rackspace - it may become a non-issue. I'll try out the setup on our new mail server and re-open this issue if I'm still having trouble. Otherwise, routing mail through smtp.gmail.com is working great. JasperServer is a great product! Things have REALLY improved since I was working in it a couple years ago; same for iReport. Nice work! -Ben

Well, I didn't fix this, but I've got a workaround: I'm using one of our GMail accounts to do the e-mailing. This is not an ideal solution, but works. The strange thing is, it works great with GMail, but when I hook it up to Rackspace, it fails (and with no errors/logging). I know the SMTP credentials & config are right, as we use them with other automated e-mail services (e.g.: notification of failed backup jobs). I'm stumped. So, if anyone has a clue as to why authenticating against owa.mailseat.com:2525 would faile (this is the Rackspace SMTP endpoint), please reply to this post. And thanks! -Ben

I've just spun up a copy of the latest JasperServer - haven't used it since the v2 days - it's greatly improved! However, I can't get the SMTP e-mailing of reports using the scheduler to work. I believe I've configured js.quartz.properties properly, and have set the following accordingly in JASPER_ROOT/WEB-INF/applicationContext-report-scheduling.xml: <property name="javaMailProperties"> <props> <prop key="mail.smtp.auth">true </prop> <prop key="mail.smtp.starttls.enable">true </prop> </props> </property> (have tried with the TTL line present and absent) I'm getting very, very little helpful logging. In both the jasperserver.log and the catalina.out file I get the following trace, but not necessarily related to running the scheduled report: 2010-04-29 19:18:22,737 ERROR JSErrorPage_jsp,http-8080-Processor21:327 - org.springframework.webflow.engine.NoMatchingTransitionException: No transition found on occurence of event 'jobDetails' in state 'jobDetails' of flow 'reportJobFlow' -- valid transitional criteria are array<TransitionCriteria>[next, jobTrigger, jobParameters, jobOutput, save] -- likely programmer error, check the set of TransitionCriteria for this state at org.springframework.webflow.engine.TransitionableState.getRequiredTransition(TransitionableState.java:93) at org.springframework.webflow.engine.TransitionableState.handleEvent(TransitionableState.java:119) at org.springframework.webflow.engine.Flow.handleEvent(Flow.java:555) at org.springframework.webflow.engine.impl.FlowExecutionImpl.handleEvent(FlowExecutionImpl.java:386) at org.springframework.webflow.engine.impl.RequestControlContextImpl.handleEvent(RequestControlContextImpl.java:210) at org.springframework.webflow.engine.ViewState.handleEvent(ViewState.java:230) at org.springframework.webflow.engine.ViewState.resume(ViewState.java:196) at org.springframework.webflow.engine.Flow.resume(Flow.java:545) at org.springframework.webflow.engine.impl.FlowExecutionImpl.resume(FlowExecutionImpl.java:259) at org.springframework.webflow.executor.FlowExecutorImpl.resumeExecution(FlowExecutorImpl.java:163) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) (truncated - could post more if that'd be helpful) I think the biggest issue, here, is that I'm just not getting the kind of logging I need to see in order to know the source of the problem. Thanks in advance for any guidance! -Ben

Hi, Lucian, Your link to a fix is no longer valid - would you point me to the resolution of this matter, please? Thanks, Ben

Hi, Folks, I've been using stored procedures as data sources for my JasperReports for some time now and they've worked great. However, I'm now trying to use a stored procedure for a subreport, with the query on the main report passing a key value to a subreport parameter. The main report query is a simple select statement. The subreport is located in the detail section of the main report. When I run the report - for the first time! - it works: the subreport prints fine. The second and subsequent runs of the report fail with an "invalid SQL" error, source is the subreport. The reports are on JasperServer 1.2.1. The DB is MySQL. I'm using iReport 2.0 to design. Any thoughts? Thanks, Ben

I've been using MySQL stored procedures with about a 90% success rate in iReport (v1.2.6 - 2.0.0). The general syntax for the "query" is: CALL storedprocedurename(${param1},${param2}); Haven't seen a limit on parameters to pass, yet. The only problem I'm having is using stored procedures for subreports, which is a major problem for me. Ben

Hi, Lucian, Thanks. While running that update statement did allow me to run reports as as role ROLE_ADMINISTRATOR, ROLE_USER still had trouble. I think I saw a few other posts about these security issues, and trouble moving from JS 1.2.0 to JS 1.2.1. In any case, I opted to revert to JS 1.2.0, and managed to get my production enviornment up and running with the WAR file install method - finally. Thanks to all for your thoughtful & diligent work in helping me resolve these issues. Best regards, Ben

OK, I'm feeling a little embarrassed, now - I was using a JS 1.2.0 DB with a JS 1.2.1 WAR file. I ran the jasperserverPatch-1.2.0-1.2.1.ddl patch script against the DB, and was able to get logged in OK. I can navigate through the various folders and objects OK, however, I'm still getting an error when I click on my reports folder (which is a folder I created just below the root level). I've attached the error - any additional help would be greatly appreciated, and thanks again for the assistance thus far! Ben [file name=err.txt size=15350]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/err.txt[/file]

Hi, Sherman, Indeed, the "stdout_20070508.log" file contains this snippet: org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Unknown column 'this_4_.visible' in 'field list' (I've attached a file with a little more of the stdout file. Is this what you mean by "the stack"?) I just used mysqldump to transfer a fresh copy of the metabase from production to my dev environment, and I still get this error. It feels like something is wrong with the dev environment setup, as the metabase is unchanged from production to dev. I also just ensured that all privileges were assigned to both root and jasperserver in MySQL, and did a "flush privileges" - no luck. Thanks again for your assistance with this. Ben [file name=stdout_err.txt size=2636]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/stdout_err.txt[/file]

Thanks for jumping in.... I've tried both the jasperserver and root logins in my config file - both of which work fine for logging into MySQL directly via the command line mysql.exe tool. When you say to check the role, what do you mean exactly? Permissions for those users on objects within the DB? And, yes, here are the config. files (pasting the hibernate.properties and context.xml inline; attaching the applicationContext.xml). Thanks! -Ben ***************************************** (hibernate.properties) # Properties file with JDBC-related settings. # Applied by PropertyPlaceholderConfigurer from "applicationContext-*.xml". # Targeted at system administrators, to avoid touching the context XML files. # Property that determines the Hibernate dialect metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect metadata.hibernate.dataSource.jndiName=jdbc/jasperserver (context.xml) <Context path="/jasperserver" debug="5" reloadable="true" crossContext="true"> <!-- maxActive: Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. --> <!-- maxIdle: Maximum number of idle dB connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter. --> <!-- maxWait: Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. --> <!-- Look at your jdbc.properties defined in Maven2 settings.xml, something like: metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect metadata.jdbc.driverClassName=com.mysql.jdbc.Driver metadata.jdbc.url=jdbc:mysql://localhost:3306/jasperserver?autoReconnect=true metadata.jdbc.username=root metadata.jdbc.password=******** metadata.jndi=jdbc/jasperserver test.jdbc.driverClassName=com.mysql.jdbc.Driver test.jdbc.url=jdbc:mysql://localhost:3306/sugarcrm?autoReconnect=true test.jdbc.username=root test.jdbc.password=******** test.jndi=jdbc/sugarcrm --> <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="jasperserver" password="*********" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://127.0.0.1:3306/jasperserver?useUnicode=true&characterEncoding=UTF-8" /> </Context> [file name=applicationContext.xml size=29160]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/applicationContext.xml[/file]

Hi, Sherman, I made one change - "localhost:3306" -> "127.0.0.1:3306". This didn't help, and I still get the same error. Note that I can login with a valid user & PW, and get denied when I enter a bogus pairing. Thus, it would seem the access to the MS Metabase on MySQL is at least partially working. The things I find in the doc you linked me to (which is what I've been using all along) that I need to verify are: Username & PW for MySQL: VALID - can log in manually to the jasperserver metabase DB url: VALID - left arguments as default but for the DNS resolution as per above It would seem these are all that may differ from default in a specific implementation, the use of sample DBs notwithstanding (manual clearly states the sugarcrm and foodmart settings may be ignored). The only reference I find in web.xml to the JNDI/JDBC stuff is in the naming of the resource, which does match the context.xml. Also, the cefault context.xml makes reference to a "maven2 settings.xml" file, which I cannot find. The hibernate.properties file seems right, and matches my production environment. Other thoughts? Thanks again for your help. To restate my goal: I'd like to get JS running via the war file method so I have better portability and management of the individual primary components (Tomcat & MySQL). Ben

Hi, Sherman, I'm not exactly sure where the references to JNDI are, nor what they ought to be. I find some references in the context.xml file, the contents of which I've pasted below. Re: database configuration, I copied the JasperServer MySQL DB from our production machine (running in console mode rather than as a service, and with the bundled Tomcat and MySQL). The DB server on this DEV machine does not have the Foodmart nor SugarCRM DBs - could that be the problem? Thanks again, Ben ***************************************** <Context path="/jasperserver" debug="5" reloadable="true" crossContext="true"> <!-- maxActive: Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit. --> <!-- maxIdle: Maximum number of idle dB connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter. --> <!-- maxWait: Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely. --> <!-- Look at your jdbc.properties defined in Maven2 settings.xml, something like: metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect metadata.jdbc.driverClassName=com.mysql.jdbc.Driver metadata.jdbc.url=jdbc:mysql://localhost:3306/jasperserver?autoReconnect=true metadata.jdbc.username=root metadata.jdbc.password=****** metadata.jndi=jdbc/jasperserver test.jdbc.driverClassName=com.mysql.jdbc.Driver test.jdbc.url=jdbc:mysql://localhost:3306/sugarcrm?autoReconnect=true test.jdbc.username=root test.jdbc.password=****** test.jndi=jdbc/sugarcrm --> <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="root" password="*******" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/jasperserver?useUnicode=true&characterEncoding=UTF-8" /> <Resource name="jdbc/sugarcrm" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="root" password="*******" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/sugarcrm"/> <Resource name="jdbc/foodmart" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="root" password="********" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/foodmart"/> </Context>

Hi Sherman, Yes, here's the whole error stack I get in my browser. I don't see any errors in the catalina.log file, though the stdout log file has similar error info. Thanks for taking a look at this! Ben -------------------------------------- org.springframework.webflow.engine.ActionExecutionException: Exception thrown executing [AnnotatedAction@1450f1f targetAction = com.jaspersoft.jasperserver.war.action.RepositoryAction@1677737, attributes = map[[empty]]] in state 'initAction' of flow 'repositoryFlow' -- action execution attributes were 'map[[empty]]'; nested exception is com.jaspersoft.jasperserver.api.JSExceptionWrapper: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query Caused by: com.jaspersoft.jasperserver.api.JSExceptionWrapper: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2214) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095) at org.hibernate.loader.Loader.list(Loader.java:2090) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:979) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:969) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:962)

I meant to also respond to the point about editing the hibernate.properties file - it (correctly, I believe) reads: metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect metadata.hibernate.dataSource.jndiName=jdbc/jasperserver (as well as some #-prefixed comment lines above those). Would love some help getting past this error - can anyone think of anything else? Thanks, Ben

Thanks for your suggestions. I've been using the install guide you recommended, and had already deleted the .war file per another posting on the forum related to these issues (/jasperserver folder getting overwritten by .war file expansion). I removed (renamed) the tomcat/conf/Catalina/localhost/jasperserver.xml file and verified the MySQL connectivity info in the META-INF/context.xml file - everything looks good there. I can also log into MySQL and access the jasperserver DB via the mysql.exe command line tool - using the same credentials and TCP/IP port (3306). I still get the same error as originally posted. Would it be helpful for me to post additional log data/error messages? Thanks again. -Ben

Hi! I'm seeking help getting my JS 1.2.0 installation going with the .war file method. I've installed MySQL & Tomcat successfully and they run as services just fine on my dev machine. I've restored a recent copy of the JasperServer DB from our production JI machine to my dev machine. I believe I got all the MySQL references set properly, as I can log in to the JI interface, but immediately after login, I get this error: 17:36:32,593 ERROR JSErrorPage_jsp,http-8080-Processor23:146 - com.jaspersoft.jasperserver.api.JSExceptionWrapper: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2214) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095) <and a lot more> Anyone have some thoughts? I'm guessig I missed something in one of the config files somewhere. Thanks! Ben