Community
Participate
Working Groups
Build ID: I20071213-1700 Steps To Reproduce: 1. Make a model element with a date field: <eClassifiers xsi:type="ecore:EClass" xmi:id="_xC6GM8mMEdyFYs0aM9mFfg" name="TestClass"> <eStructuralFeatures xsi:type="ecore:EAttribute" xmi:id="_xC6GNsmMEdyFYs0aM9mFfg" name="timestamp" eType="ecore:EDataType http://www.eclipse.org/emf/2002/Ecore#//EDate"/> </eClassifiers> 2. Create a container, session, transaction, 3. Create an instance of above class and do setTimestamp(new java.util.Date()); 4. Add instance to resource 5. commit transaction leads to [ERROR] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jan 31 14:32:36 CET 2008, 0)' at line 1 org.eclipse.net4j.db.DBException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jan 31 14:32:36 CET 2008, 0)' at line 1 at org.eclipse.emf.cdo.server.internal.db.ClassMapping.sqlUpdate(ClassMapping.java:178) at org.eclipse.emf.cdo.server.internal.db.ClassMapping.writeAttributes(ClassMapping.java:493) at org.eclipse.emf.cdo.server.internal.db.ClassMapping.writeRevision(ClassMapping.java:449) at org.eclipse.emf.cdo.server.internal.db.HorizontalClassMapping.writeRevision(HorizontalClassMapping.java:37) at org.eclipse.emf.cdo.server.internal.db.DBStoreWriter.writeRevision(DBStoreWriter.java:177) at org.eclipse.emf.cdo.internal.server.RevisionManager$AddRevisionOperation.phase1(RevisionManager.java:283) at org.eclipse.emf.cdo.internal.server.RevisionManager$AddRevisionOperation.phase1(RevisionManager.java:1) at org.eclipse.net4j.internal.util.transaction.Transaction.execute(Transaction.java:66) at org.eclipse.emf.cdo.internal.server.RevisionManager.addRevision(RevisionManager.java:64) at org.eclipse.emf.cdo.internal.server.protocol.CommitTransactionIndication.addRevisions(CommitTransactionIndication.java:279) at org.eclipse.emf.cdo.internal.server.protocol.CommitTransactionIndication.indicating(CommitTransactionIndication.java:123) at org.eclipse.net4j.signal.IndicationWithResponse.execute(IndicationWithResponse.java:46) at org.eclipse.net4j.signal.Signal.runSync(Signal.java:143) at org.eclipse.net4j.signal.Signal.run(Signal.java:124)Thread-4 [debug] INSERT INTO ArtifactContent VALUES (10, 1, 4, 1201786356874, 0, 2, 8, -1, Thu Jan 31 14:32:36 CET 2008, 0) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) at java.lang.Thread.run(Thread.java:619) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jan 31 14:32:36 CET 2008, 0)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)Thread-4 [debug] Rolling back transaction: View(1, TRANSACTION) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485) at org.eclipse.emf.cdo.server.internal.db.ClassMapping.sqlUpdate(ClassMapping.java:174) ... 16 more [ERROR] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jan 31 14:32:36 CET 2008, 0)' at line 1 More information: Problem is twofold: 1. Date is mapped to mysql column type DATE which is not quite correct, because java.util.Date stores also the time and thus the correct type would be DATETIME. 2. The statement is created with a wrong syntax. This is generated: INSERT INTO TestClass VALUES (14, 1, 4, 1201782690532, 0, 2, 12, -1, Jan 31 14:32:36 CET 2008); correct would be: INSERT INTO TestClass VALUES (14, 1, 4, 1201782690532, 0, 2, 12, -1, '2008-01-31 14:32:36'); -> use single quotes -> use format yyyy-mm-dd hh:mm:ss Also, I don't think that this is a MySQL specific problem. I doubt that this syntax works with any other database as the date is usually enclosed in single quotes ...
Created attachment 88881 [details] Suggested patch Here's a patch, that works for me but sadly affects the other databases as well and I did not test it with other databases. It fixes the issue of erroneous sql syntax by explicitly adding the date formatting to the mysql adapter. It also stores the date in a datetime field and uses getTimestamp() in the revision retrieval part in order to address the issue with not storing the time of day.
Reversioned due to graduation
see Bug 244290: there is also JDBC escape syntax to express dates and timestamps in a database-dependent way. Eike, in AttributeMapping around 212, it says: @Override protected Object getResultSetValue(ResultSet resultSet, int column) throws SQLException { // TODO Is getDate() correct? <--------------- return resultSet.getDate(column); } In MySQL this is equal to only reading the date, setting the time of day to 00:00:00. What is the semantics of the EDate data type? Is it date-only or date-plus-time? If the latter, then this should be changed to getDateTime() or getTimeStamp() and the timestamp should also be stored in the database. (I need to check and test column types as well, as I think I remember that the MySQL column type for Date mappings was also DATE, which ignores the time-of-day.
Created attachment 118772 [details] alternative patch Hi Stefan, I fixed this in my workspace some time ago, didn't know ther was a bugzilla already filled! I just overrode the DATE.appendValue() method. Seems to work fo MySQL (at least the exception is not thrown anymore), dont know if this would work for other databases.
Stefan, your old patch contains merge conflicts. Could you have a look at Vik's approach, too, and then decide how to fix it?
Created attachment 121928 [details] Merged Patch I merged both patches - Vik's patch did not include reading the timestamp from the result set. Also changed the default date mapping from DATE to TIMESTAMP (else HsqlDB would not store time of day), moved Vik's code from DATE to TIMESTAMP, added UnsupportedOperationException to DATE and TIME, and added testcase. Date is now stored and retrieved correctly in all three databases.
Created attachment 121933 [details] Patch v4 - ready to be committed Stefan, Vik, Well done!! ;-)
Committed to HEAD.
... and now even set the RESOLVED state ;)
Fix available in CDO 2.0.0 I200901090729
:-P fix does not work for prepared statements. Don't know why I missed that. Testcase testStoreDate of DBStoreTests fails if a delegate with prepared statements is used (tested derby and hsql)
Created attachment 124152 [details] Additional-Patch-for-prepared-statements Handled the special case directly in the PreparedStatementJDBCDelegate. Works now. Committed to HEAD.
Fixed again.
Fix available in CDO 2.0.0M5
Generally available.
