Problem with null date

[jamel.essoussi]

Hi,

I use spring 2.5.6 with jdbc connector 5.1.2 and when select null (0000-00-00 00:00:00) date from mysql DB I have a conversion error

Can you help me please

[andrew007]

I think you can set the behavior in the JDBC url:

url = “jdbc:mysql://localhost:3306/myDB?zeroDateTimeBehavior=convertToNull”

[jamel.essoussi]

Yes I have tried this mecanism but I always have the problem.

it may be necessary to use an other DataSource other than

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://10.0.3.44/"/>
		<property name="username" value="sa"/>
		<property name="password" value="admindb"/>   
	</bean>

[jamel.essoussi]

Thanks for your reply

[andrew007]

Can you post the exception you have?

[jamel.essoussi]

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT  operator.id as operator_id, operator.name as operator_name, company.id as company_id, company.name as company_name, company.ct_comp_id as company_webcas_id, voip.ip_callagent as voip_callagent_ip, voip.port_callagent as voip_callagent_port, voip.ext as voip_extension, hardware_config.default as hardware_config_default, hardware_config.id as hardware_config_id, hardware_config.insert_date as hardware_config_insert_date, hardware_config.name as hardware_config_name, user.id as user_id, user.login as user_login, user.pwd as user_pwd, hardware.admin_status as hardware_admin_status, hardware.admin_msg as hardware_admin_msg, hardware.admin_date as hardware_admin_date, hardware.tech_status as hardware_tech_status, hardware.tech_msg as hardware_tech_msg, hardware.tech_date as hardware_tech_date, hardware.id as hardware_id, hardware.ip_pri as hardware_ip_pri, hardware.mac as hardware_mac, hardware.model as hardware_model, hardware.type as hardware_type, hardware.subtype as hardware_subtype, hardware.subtype as hardware_line_id  FROM ape.hardware LEFT JOIN ape.operator on ape.operator.id=ape.hardware.ido LEFT JOIN ape.company on ape.company.id=ape.hardware.idc LEFT JOIN ape.hardware_config on ape.hardware_config.id=ape.hardware.id_config LEFT JOIN ape.user on ape.user.id=ape.hardware_config.user_id LEFT JOIN ape.voip on ape.voip.hw_id=ape.hardware.id WHERE hardware.id = 88]; SQL state [null]; error code [0]; Invalid Date Conversion; nested exception is java.sql.SQLException: Invalid Date Conversion
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
        at com.ictelecom.jape.common.managers.springjdbc.dao.impl.PhoneJdbcDao.getPhone(PhoneJdbcDao.java:121)
        at com.ictelecom.jape.common.managers.springjdbc.service.impl.PhoneServiceImpl.getPhone(PhoneServiceImpl.java:38)
        at com.ictelecom.jape.jxmlrpc.server.handler.PhoneHandler.get(PhoneHandler.java:51)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.xmlrpc.server.ReflectiveXmlRpcHandler.invoke(ReflectiveXmlRpcHandler.java:112)
        at org.apache.xmlrpc.server.ReflectiveXmlRpcHandler.execute(ReflectiveXmlRpcHandler.java:103)
        at org.apache.xmlrpc.server.XmlRpcServerWorker.execute(XmlRpcServerWorker.java:43)
        at org.apache.xmlrpc.server.XmlRpcServer.execute(XmlRpcServer.java:83)
        at org.apache.xmlrpc.server.XmlRpcStreamServer.execute(XmlRpcStreamServer.java:191)
        at org.apache.xmlrpc.webserver.Connection.run(Connection.java:205)
        at org.apache.xmlrpc.util.ThreadPool$Poolable$1.run(ThreadPool.java:63)
Caused by: java.sql.SQLException: Invalid Date Conversion
        at com.ictelecom.jape.common.managers.springjdbc.dao.impl.PhoneJdbcDao$PhoneRowMapper.mapRow(PhoneJdbcDao.java:226)
        at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:448)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
        ... 16 more

[andrew007]

Which data type are you using for the Date?
It seems to me that there is something wrong with the mapping of the Date...
Did you use a TimeStamp?

[andrew007]

I saw that you are using DriverManagerDataSource...
Remember that: "DriverManagerDataSource does not pool connections and is therefore not intended for production"

[jamel.essoussi]

I use the datetime,

Should I use annoter DataSource ?

Regards

[andrew007]

I think that you have two diffent points to check. The first one is the DataSource you are using (DriverManagerDataSource) which is not a good option for production. You can consider DBCP or C3P0 for example.
The second point is to check your PhoneRowMapper and how you handle the mapping of the Date.

Caused by: java.sql.SQLException: Invalid Date Conversion
        at com.ictelecom.jape.common.managers.springjdbc.dao.impl.PhoneJdbcDao$PhoneRowMapper.mapRow(PhoneJdbcDao.java:226)

The two problems should not be related.