Gaby38

Just to say that I did the modification suggested by Lucian : add "&autoReconnectForPools=true" to the URL in the MySql JDBC Datasource. Since this update, I didn't get any more the "Cannot deactivate transaction synchronization" error message. Before I used to get it at each morning login after a long inactivity. So thanks again Lucian for your advices.

Lucian, I also get very often the same error since I am using jasperserver. But as it is not blocking and occurs only the 1st time I connect after a long inactivity, I din't take care of that and didn't post anything about that. Remember that you helped me when I was getting troubles with a DB connection which I killed the shadow process of. As you said, I put in place a validation query and all ran OK except that famous "cannot deactivate transaction" that persists. But this validation query is for my work Database and not Jasperserver database. So I will also try to do what you are saying for the Mysql Database supporting my jasperserver metadata. I'll keep you aware of the results.

Sherman, Thanks for your answer. I created a JNDI datasource and put a "validationQuery" parameter ("SELECT 1 from DUAL") in it. I changed the Datasource in the report through jasperserver "EDIT" report menu. After some troubles due to the fact that JNDI is managed by TomCat so Tomcat must have Oracle jdbc driver in its path, the report was working. Then I killed the shadow process on the server and re-ran the report. With jndc I used to get a "broken pipe". With jndi, the report runs OK and I can see on server that a new shadow process has been launched. So it's perfect !!Thanks a lot !!

I use jasperserver on an Oracle Database which is updated every evening by an full import of the prod database. In order to drop all tables and constraints, I use a "drop user cascade" so I need to have no more users connected to the database. For that, I have a script which kills all shadow process. After this operation, jasperserver seems to think it is always connected and gets an error while trying to launch scheduled jobs accessing the database. How can I tell jasperserver to reconnect to the database after the update operation : tomcat restart ? Thanks for your answer

Lucianc, I up this post as promised to tell that I have discovered the reason of my trouble : your diagnostic was right : it is due to the break of the connection to the database. Every work day on evening there is an update of the database on server at home with the production database (copy through ADSL and drop user cascade + import). So as I need to have no more client process connected to the database, I have a script which "cleans" and kills the Oracle shadow process . Among these process, there must be the one attached to jasperserver and jasperserver doesn't appreciate! I apologize not to have thought to that before posting. Thanks for your help and the time you spent on that false problem.

Hi Lucianc, It is difficult to find the way in order to reproduce systematically this error. The night after your last answer, I got the error again. So I modified the schedule in order to have the job executed every minute. It ran all the night : I got a lot of reports by mail. No error. I am thinking abour one thing : in fact jasperserver is on a linux server at home. This server is of course connected to the LAN but also to internet via ADSL.So I can (and I do) access it from home or from out of home. Often when I do the login procedure from the other side than I last worked I get an error from spring about unmatched state. If I click "back" or I re-login then all is OK. For me it seems not abnormal because of the dual access (but perhaps am I wrong) and I don't take care of that. Is it the same kind of problem that I get with the above error? I don't know. So as it is difficult to reproduce, don't spend your time on this problem. As soon as I will find a systematic way to reproduce the problem, I'll up the thread. Thanks for your help and the time you spent about that Post edited by: Gaby38, at: 2008/05/25 19:28

Lucianc, I up this post because, without doing nothing on the report (as far as I remember), I got the error on this night: Code:Job: Journal Concerto (ID: 1) Report unit: /reports/Concerto/ScheduleJobs/LogOfDay Quartz Job: ReportJobs.job_1 Quartz Trigger: ReportJobs.trigger_3_20 Error executing SQL statement for : LogOfDay_jrxml_1211225303119 com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : LogOfDay_jrxml_1211225303119 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:75) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:656) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1212) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:849) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:420) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:610) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:326) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:737) at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:258) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:307) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:285) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:156) at org.quartz.core.JobRunShell.run(JobRunShell.java:195) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520) Caused by: java.sql.SQLException: Il n'y a plus de données à lire dans le socket at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1118) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) ... 17 moreIt is the same error I spoke about in my previous posts. I don't know why this night (it is 22h in France) it was triggered. Yesterday night(it's a daily report), I received the report without problem and I did nothing on this report till now. There is a french sentence in the error message. I translate for you : "Il n'y a plus de données à lire dans le socket" that means "There is no more data to be read in the socket" I re-scheduled the execution for 1 minute later and, this time, the report execution was OK and the mail has been generated with the correct pdf output without I do nothing on the report else than re schedule it. In previous days I had to run "off schedule" the report in order to have it executed at scheduled time. Thanks for your help.

Lucianc, I up this post because, without doing nothing on the report (as far as I remember), I got the error on this night: Code:Job: Journal Concerto (ID: 1) Report unit: /reports/Concerto/ScheduleJobs/LogOfDay Quartz Job: ReportJobs.job_1 Quartz Trigger: ReportJobs.trigger_3_20 Error executing SQL statement for : LogOfDay_jrxml_1211225303119 com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : LogOfDay_jrxml_1211225303119 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:75) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:656) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1212) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:849) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:420) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:610) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:326) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:737) at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:258) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:307) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:285) at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:156) at org.quartz.core.JobRunShell.run(JobRunShell.java:195) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520) Caused by: java.sql.SQLException: Il n'y a plus de données à lire dans le socket at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1118) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) ... 17 moreIt is the same error I spoke about in my previous posts. I don't know why this night (it is 22h in France) it was triggered. Yesterday night(it's a daily report), I received the report without problem and I did nothing on this report till now. There is a french sentence in the error message. I translate for you : "Il n'y a plus de données à lire dans le socket" that means "There is no more data to be read in the socket" I re-scheduled the execution for 1 minute later and, this time, the report execution was OK and the mail has been generated with the correct pdf output without I do nothing on the report else than re schedule it. In previous days I had to run "off schedule" the report in order to have it executed at scheduled time. Thanks for your help.

Lucianc, I don't understand but I cannot now reproduce it!! Certainly there was another problem that I fixed in a hidden way. I am sure having got that twice. Thanks for your prompt answer and I apologize for the trouble. Forget my post !

Hi everybody, I get some troubles for the first scheduled execution of a sheduled report with a sql query based on date of the day Code:SELECT Typ_Action_Jou as Action, to_date(Dat_Creation_Jou,'yyyymmdd') as Jour, Heu_Creation_Jou as Heure ,Lib_Texte_Jou as Libelle,Lib_User_Jou as Agent from journal where Dat_Creation_Jou =( select to_number(to_char(sysdate,'YYYYMMDD')) from dual) and Typ_Action_Jou in ('AD','BA','FA','FM','IN','PA','PE','PS','SC') Order By Typ_Action_Jou,Dat_Creation_Jou,Heu_Creation_Jou,Lib_User_Jou I get an "error while executing SQL statement" due to the fact that "there is nothing to read on the input stream" (sorry I deleted the exact message in jasperserver. If I program a new schedule (let us say 5 minutes after), the same error occurs. If I execute the report off schedule all is OK. If I reprogram a new schedule time 2 minutes after without doing anything else the scheduled execution is ok and will remain OK all days later untill I edit the report. If I edit the report then the same "cycle" begins that means that while I don't execute once the report off schedule I will get the same error. After the 1st execution all is ok till the next edit operation. It's not blocking but I would understand what is the reason of the trouble. Has anybody an idea about that?? Thanks for your collaboration

Sorry, it's true I was not clear : what I meant was as there was Two separate PCs the database was seen as "ORASID@localhost" from jasperserver point of view and "ORASID@server.domain.com" from iReport point of view. So I got some troubles at the beginning! I already got "Unknown Source" on other times but I don't exactly remember what was the real trouble and what I did to fix it. Sorry

Sorry, it's true I was not clear : what I meant was as there was Two separate PCs the database was seen as "ORASID@localhost" from jasperserver point of view and "ORASID@server.domain.com" from iReport point of view. So I got some troubles at the beginning! I already got "Unknown Source" on other times but I don't exactly remember what was the real trouble and what I did to fix it. Sorry

What is your development architecture? Because, at the beginning, I got the same kind of trouble because I developped Jasper reports with iReports on a windows XP PC and my jasperserver was on another server running Linux with the database in use. So it is mandatory to have the same "vision" of the database from iReport and from jasperserver hosts ( for example in one case it's a 'localhost' database and in the other case it's a 'server' database).If not it will run in a case and not in the other.

I think that my previous proposal doesn't work because unfortunately it seems that start_date and end_date informations are not updated by jasperserver and remains "null". Sorry

Hi Sergio, I think that one way to do that is to search in the jasperserver database. As far as I could see, there is a table JIReportJob with some attributes of the job and a id key Job Trigger to another table JIReportJobTrigger which holds a "start_date" and a "end_date". So you should find the stuff to do the required list of jobs. I worked "on paper" that means I never had a glance till now what is happening in this table when a scheduled job is executing. Hope it helps !

umair, I got the same problem with a report of mine. I "workarounded" the problem by compressing the design in order to have only one page !! I think it's a bug or a misunderstanding from our part from html JasperReport subtilities!! It's sure that the design of the report is very "sensitive" in case of html view. There is somewhere in the jasper forum a kind of FAQ on this theme.

Umair, I think your problem comes from the fact you defined the parameter type as "string" and not as "com.jaspersoft.jasperserver.api.metadata.user.domain.User" as said. This is not in the combo list : you must enter it by overwriting in the combo. In my case, under iReport this class is not defined so you got a "Problem". But it is defined in jasperserver and if you run the report from jasperserver there is no problem. I tested on one report before posting my reply.

lucianc wrote : If you need to send several values for a report parameter/input control, you should put each value individually in the URL: http://...&param=value1&param=value2&param=value3 OK lucianc, but it was my 1rst report in Jasperserver and I started with an "stand-alone" report with parameters passed by parameter form and a listofvalues object. After, I focalized on the fact to call the same report from another report via hyperlink and to keep the listofvalues object. After a lot of tries, I came to a list of values between brackets and I had to replace brackets by parenthesis to introduce the list in sql code. Sure, it's not the simplest way to do that !!!

lucianc wrote : If you need to send several values for a report parameter/input control, you should put each value individually in the URL: http://...&param=value1&param=value2&param=value3 OK lucianc, but it was my 1rst report in Jasperserver and I started with an "stand-alone" report with parameters passed by parameter form and a listofvalues object. After, I focalized on the fact to call the same report from another report via hyperlink and to keep the listofvalues object. After a lot of tries, I came to a list of values between brackets and I had to replace brackets by parenthesis to introduce the list in sql code. Sure, it's not the simplest way to do that !!!

In my case, I connect to the mysql meta database of jasper and i run a query on resource tables : Select JIResource.name,JIResourceFolder.uri,JIResource.label,JIResource.description from JIResource inner join JIResourceFolder on JIResourceFolder.id=JIResource.Parent_Folder where JIResource.id in ( Select id from JIReportUnit) It's quite rough but efficient. You need a select login access to the mysql server and database.

In iReport, you edit your jrxml code. In the left frame, you add a parameter with name "LoggedInUser", you check the "Use as prompt" box and you put "com.jaspersoft.jasperserver.api.metadata.user.domain.User" in the combo parameter type (perhaps a problem with a class not found but don't care) Then you add in your report a textField and you define its expression definition as "$P{LoggedInUser}.getFullName()" When you run the report, the text field contains the logged user name.

In iReport, you edit your jrxml code. In the left frame, you add a parameter with name "LoggedInUser", you check the "Use as prompt" box and you put "com.jaspersoft.jasperserver.api.metadata.user.domain.User" in the combo parameter type (perhaps a problem with a class not found but don't care) Then you add in your report a textField and you define its expression definition as "$P{LoggedInUser}.getFullName()" When you run the report, the text field contains the logged user name.

I got the same kind of problem with ListofValues. In fact, in an URL you can only transmit string. So jasperserver "casts" to string and the result is not the one expected. For instance, in my case of ListOfValues I got "[6,7,8]" as list with brackets and not parenthesis as used in SQL. So check the type of your parameters : if they are not strings, they won't work with parameters transmitted via URL.If you want your report work as well as with parameter form or URL, the best way I know is to use only string parameters and, for your case, use "to_date" or "to_char" functions to manipulate date types in SQL. Hope it helps

Try that : <parameter name="LoggedInUser" isForPrompting="true" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User" and after in the definition of a Text Field : $P{LoggedInUser}.getFullName() (extract from French jasperserver forum: answer by Christopher André from Jaspersoft Corporation)

I also got this problem while migrating from iReport 2.0.4 to iReport 2.0.5. I fixed it, according to previous posts, by replacing in jasperserver/WEB-INF/lib the file : jasperreports-2.0.2.jar by the file: jasperreports-2.0.5.jar from "C:Program FilesJasperSoftiReport-2.0.5lib" (install directory of new release of iReport) and restarting the tomcat server. I think it should be the same thing for you. PS: I didn't try to run the report from iReport. Post edited by: Gaby38, at: 2008/04/01 18:34