Discussion :

[MarENSI]

Bonjour tout le monde,

Je suis débutante dans le domaine du BI et je viens de commencer à faire la partie reporting en utilisant BIRT.

En effet, en visualisant le report, j'ai eu ce message d'erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
The following items have errors: 
 
 
Table table1: 
+ Cannot get the timestamp value column: 34.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get timestamp value in the result set.
SQL error #1: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
 ;
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP. 
odaconsumer.CannotGetTimestampFromColumn ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Cannot get the timestamp value column: 34.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get timestamp value in the result set.
SQL error #1: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
 ;
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
	at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1210)
	at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1174)
	at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
	at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)
	at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
	at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
	at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
	at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
	at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)
	at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
	at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:172)
	at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75)
	at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:920)
	at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
	at org.eclipse.birt.report.service.actionhandler.BirtRunAndRenderActionHandler.__execute(BirtRunAndRenderActionHandler.java:76)
	at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
	at org.eclipse.birt.report.presentation.aggregation.layout.EngineFragment.doService(EngineFragment.java:318)
	at org.eclipse.birt.report.presentation.aggregation.AbstractBaseFragment.service(AbstractBaseFragment.java:76)
	at org.eclipse.birt.report.servlet.BirtEngineServlet.__doGet(BirtEngineServlet.java:120)
	at org.eclipse.birt.report.servlet.BaseReportEngineServlet.doGet(BaseReportEngineServlet.java:185)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.eclipse.birt.report.servlet.BaseReportEngineServlet.service(BaseReportEngineServlet.java:116)
	at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
	at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
	at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126)
	at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:318)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:924)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:549)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the timestamp value column: 34.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get timestamp value in the result set.
SQL error #1: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
 ;
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:55)
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
	at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:96)
	at org.eclipse.birt.data.engine.odaconsumer.ResultSet.getTimestamp(ResultSet.java:350)
	at org.eclipse.birt.data.engine.odaconsumer.ResultSet.fetch(ResultSet.java:189)
	at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(OdiAdapter.java:153)
	at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:105)
	at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(ExpandableRowResultSet.java:63)
	at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(SmartCacheHelper.java:311)
	at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(SmartCacheHelper.java:283)
	at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initOdaResult(SmartCacheHelper.java:152)
	at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(SmartCacheHelper.java:77)
	at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(SmartCache.java:53)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(PassUtil.java:99)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(PassUtil.java:62)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSetCacheInResultSetPopulator(PassManager.java:320)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateDataSet(PassManager.java:282)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.prepareDataSetResultSet(PassManager.java:101)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(PassManager.java:128)
	at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(PassManager.java:77)
	at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(ResultSetPopulator.java:198)
	at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(CachedResultSet.java:90)
	at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:842)
	at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:427)
	at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1045)
	at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
	at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:159)
	at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98)
	at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
	at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265)
	at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1875)
	at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
	... 40 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot get timestamp value in the result set.
SQL error #1: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
 ;
    java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
	at org.eclipse.birt.report.data.oda.jdbc.ResultSet.getTimestamp(ResultSet.java:493)
	at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaResultSet.getTimestamp(OdaResultSet.java:658)
	at org.eclipse.birt.data.engine.odaconsumer.ResultSet.getTimestamp(ResultSet.java:346)
	... 68 more
Caused by: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 34 to TIMESTAMP.
	at com.mysql.jdbc.ResultSet.getTimestampFromBytes(ResultSet.java:6965)
	at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6999)
	at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6319)
	at org.eclipse.birt.report.data.oda.jdbc.ResultSet.getTimestamp(ResultSet.java:488)
	... 70 more

Est ce que quelqu'un pourrait m'aider à régler ce pb s'il vous plait??

Merci d'avance.

[MarENSI]

Bonjour tout le monde,

En cherchant sur internet, j'ai trouvé dans le site suivant: http://ondra.zizka.cz/stranky/progra...ava/index.texy
qu'il faut faire ceci:

When working with MySQL over JDBC and the driver encounters a zero DATE, TIME, or DATETIME value (that is, e.g, ‚0000–00–00‘ for DATE), an exception is thrown:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP.
In this case, using SQL commands like

SET GLOBAL sql_mode = 'NO_ZERO_DATE';

Est ce que quelqu'un pourrait me dire comment régler ce problème sachant que je travaille avec WAMP5?

Merci d'avance pour votre aide.

[slimlus]

Bonjour!

Deja, est-ce que dans ta BDD, as tu les bonnes dates? (esk elles sont affiché correctement?)
Si tu a rempli ta base a partir de java sans faire la conversion vers son equivalent mySql, ça pourrait etre le probleme!!

[MarENSI]

Bonjour, merci pour votre réponse

Bonjour!

Deja, est-ce que dans ta BDD, as tu les bonnes dates? (esk elles sont affiché correctement?)

Oui, les dates sont affichées correctement.

Si tu a rempli ta base a partir de java sans faire la conversion vers son equivalent mySql, ça pourrait etre le probleme!!

En effet, j'ai rempli ma base avec l'ETL Talend (code java)

Est ce que vous pouvez m'expliquer un peu l'histoire de conversion de type vers l'équivalent mysql svp??

Merci d'avance pour votre aide.

[slimlus]

Bonjour,

Je suis aussi sur mon premier projet TALEND, puis BIRT (mais un petit peu avancé (finalisation, appWeb...)

si dans un outil tel que MySql, tu as les bonnes dates alors, le problème n'est pas au niveau de java vers MySQL.
Si tu n'as pas fait de conversion de type, alors tu as surment des dates qui n'ont pas de sens dans ta BDD!!

Voilà, les fonctions que j'ai utilisé pour convertir les dates, la première convertit une String d en type 'Date' (peut-etre que dans ton cas c'est pas utile)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public static Date StringToDate(String sdate) 
	    {   Date d =new Date();
	    	if (sdate.contains("/"))
	    	{ System.out.println("StringTodate avc /  :");
	    	final SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	    	try 
	    		{
					d=formatter.parse(sdate);
				} catch (ParseException e) 
						{
						// TODO Auto-generated catch block
						e.printStackTrace();
						}
	    	}
	    	else if (sdate.contains("-"))
	    	{	 System.out.println("StringTodate avc -  :");
		    	final SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
		    	try 
		    		{
						d=formatter.parse(sdate);
					} catch (ParseException e) 
							{
							// TODO Auto-generated catch block
							e.printStackTrace();
							}
		    	}
			return d;
	    }

la seconde, convertit de date (java) en DATETIME (mysql)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
	public static java.sql.Date ToDBDateTime(String sDate) 
	{
	    Date d =StringToDate(sDate) ;
		 java.sql.Date sqlDate = new java.sql.Date(d.getTime());
		 return sqlDate;
 
	}

J'espère que ça t'aidera!! bonne chance pour la suite