MySQL TimeStamp SQLException

Classic Servoy
1

I created a relation from my own solution to columns of a table supporting a (Joomla) website.
One of the columns that I display is of Timestamp and it is prefilled with a value ‘0000-00-00 00:00:00’. Something that is not done by me and I can not influence this.

Servoy/Java does not seem to like that. Is there a way to get rid of this error?

The error is thrown when the form is loaded and comes from, I think, loading the related data…

2009-01-03 23:25:43,970 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 11 to TIMESTAMP.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1328)
at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:573)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6669)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5988)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
at com.servoy.j2db.dataprocessing.BufferedDataSet.(BufferedDataSet.java:33)
at com.servoy.j2db.dataprocessing.SQLEngine.performSelect(SQLEngine.java:69)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:907)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:899)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:23)
at com.servoy.j2db.dataprocessing.RelatedFoundSet.createRelatedFoundSets(RelatedFoundSet.java:344)
at com.servoy.j2db.dataprocessing.Zrb.Za(Zrb.java:366)
at com.servoy.j2db.dataprocessing.FoundSet.getRelatedFoundSet(FoundSet.java:801)
at com.servoy.j2db.dataprocessing.Record.getRelatedFoundSet(Record.java:118)
at com.servoy.j2db.dataprocessing.Record.Za(Record.java:112)
at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:226)
at com.servoy.j2db.dataprocessing.Zib.setRecord(Zib.java:85)
at com.servoy.j2db.dataprocessing.Zfb.Za(Zfb.java:358)
at com.servoy.j2db.dataui.Zwd.Za(Zwd.java:20)
at com.servoy.j2db.Zfe.Zb(Zfe.java:47)
at com.servoy.j2db.Zfe.contentsChanged(Zfe.java:93)
at com.servoy.j2db.dataprocessing.Zpb.Za(Zpb.java:1)
at com.servoy.j2db.dataprocessing.Zld.run(Zld.java:13)
at com.servoy.j2db.dataprocessing.Zpb.Za(Zpb.java:49)
at com.servoy.j2db.dataprocessing.FoundSet.fireFoundSetChanged(FoundSet.java:1523)
at com.servoy.j2db.FormController.refreshView(FormController.java:310)
at com.servoy.j2db.scripting.JSApplication.js_setValueListItems(JSApplication.java:1303)

Servoy 4.1, MySQL 5.0 and/or 5.1

2

Did you try with the latest Mysql JDBC driver?

3

Hi Marcel,

IT2Be:
I created a relation from my own solution to columns of a table supporting a (Joomla) website.
One of the columns that I display is of Timestamp and it is prefilled with a value ‘0000-00-00 00:00:00’. Something that is not done by me and I can not influence this.

Servoy/Java does not seem to like that. Is there a way to get rid of this error?

The error is thrown when the form is loaded and comes from, I think, loading the related data…

MySQL is notorious for allowing invalid dates, cutting off your text data or ‘rounding’ down your numbers to make them fit in the column without warning you…
Since version 5.x they do have a strict SQL mode that does enforce these constraints like every other RDBMS does but it’s not enabled by default :!:
So pretty much any default install of MySQL is suffering from this issue.

I suggest you use Servoy’s conversion functionality (in the dataprovider window) to check if the data is valid or else make it return NULL.

Hope this helps.

4

Did you try with the latest Mysql JDBC driver?

Yep, always… But the released one…

I suggest you use Servoy’s conversion functionality (in the dataprovider window) to check if the data is valid or else make it return NULL.

That was a good suggestion but it does not help.
Makes sense, because the error is thrown before the conversion takes place, when the query is executed.

5

Hi Marcel,

It looks like it’s a Java error so the JDBC driver can’t cast that value to a datetime.
Seems you need to set MySQL to Strict SQL mode and you probably have to ‘fix’ joomla to make sure it doesn’t insert/update invalid dates.
Or maybe easier, use a post insert/update database trigger that fixes the dates when invalid, i.e. set to NULL.

I know, it’s a pain.

6

Hmm, thanks Robert.

I’ll try your suggestions…