Date format is wrong for MySQL

[GoogleCodeExporter]

Hi, the date format is wrong for MySQL, so it's not possible to cut and paste 
the SQL into a database tool.

I've written a patch for it (attached).

Original issue reported on code.google.com by linze...@googlemail.com on 15 Feb 2011 at 2:16

Attachments:

• MySqlRdbmsSpecifics.java

[GoogleCodeExporter]

Thanks.  I will look into this later.

Original comment by arthur.b...@gmail.com on 15 Feb 2011 at 3:24

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

In your patch you put two tick marks around the date formats, but from the 
mysql reference http://dev.mysql.com/doc/refman/5.5/en/datetime.html it looks 
like only single ticks are needed, (similar to oracle and sql server).

Am I missing something?  Please explain why you used two ticks?

Also, it doesn't look like you accounted for java.sql.Timestamp...

Original comment by arthur.b...@gmail.com on 2 Mar 2011 at 2:21

[GoogleCodeExporter]

Based on http://dev.mysql.com/doc/refman/5.5/en/datetime.html,
Here is how I would think mysql formatting should work:

package net.sf.log4jdbc;

import java.text.SimpleDateFormat;

/**
 * RDBMS specifics for the MySql db.
 *
 * @author Arthur Blake
 */
class MySqlRdbmsSpecifics extends RdbmsSpecifics
{
  MySqlRdbmsSpecifics()
  {
    super();
  }

  String formatParameterObject(Object object)
  {
    if (object instanceof java.sql.Time)
    {
      return "'" + new SimpleDateFormat("HH:mm:ss").format(object) + "'";
    }
    else if (object instanceof java.sql.Date)
    {
      return "'" + new SimpleDateFormat("yyyy-MM-dd").format(object) + "'";
    }
    else if (object instanceof java.util.Date)  // (includes java.sql.Timestamp)
    {
      return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object) + "'";
    }
    else
    {
      return super.formatParameterObject(object);
    }
  }
}

I don't have a mysql db to test with at the moment.
Can any mysql users provide feedback?

Original comment by arthur.b...@gmail.com on 2 Mar 2011 at 2:58

[GoogleCodeExporter]

Hi Arthur,
both single and double quotes work with mysql dates (I tested this on linux 
with the command line and with the MySQL Workbench in Windows). Though it's 
probably  best to change them to double quotes if that's what the manual says.

Yes, well spotted, I forgot about Timestamp.

Keep up the good work!

Cheers,
Owen

Original comment by owenlind...@gmail.com on 15 Mar 2011 at 11:18

[GoogleCodeExporter]

I was going to test that myself on a mysql db but I never got around to it due 
to lack of time.  (would be great if someone could test it out really well for 
me... hint hint:) 

Note that I also purposefully left out the millisecond portion of the timestamp 
because the manual also says that it can be included but will always be ignored 
because mysql does not store that part.  I will include the above 
implementation in log4jdbc soon.

Original comment by arthur.b...@gmail.com on 15 Mar 2011 at 1:31

[GoogleCodeExporter]

BTW, all the examples in the manual at 
http://dev.mysql.com/doc/refman/5.5/en/datetime.html use single tick mark 
quotes, not double quotes.

Original comment by arthur.b...@gmail.com on 15 Mar 2011 at 1:32

[GoogleCodeExporter]

Issue 23 has been merged into this issue.

Original comment by arthur.b...@gmail.com on 15 Jun 2011 at 2:05

[GoogleCodeExporter]

code has been checked in the trunk and will be part of the final 1.2 release 
(coming soon!)  SVN Rev. 98

Original comment by arthur.b...@gmail.com on 20 Jun 2011 at 1:07

• Changed state: Fixed