to_date addition....

[GoogleCodeExporter]

We LOVE to copy and paste the sql right from the log4jdbc logs.  It would
be awesome if we could swap in a certain logger that added to_date('the
date', 'the format') so any SQL with Dates we could cut and paste into an
sql editor and run.  I frequently just hand off the sql to dbas and they
shoot back I don't have the to_data and I have to explain that is just a
log...I actually passed in a Date object to oracle's adapter.....it would
be awesome if we had a logger for this so it was all cut and paste and run.

Original issue reported on code.google.com by dean.hil...@gmail.com on 18 Dec 2009 at 8:49

[GoogleCodeExporter]

Yes, I think this feature is very useful when we use Oracle database.

Original comment by ihuangwj on 4 Mar 2010 at 2:40

[GoogleCodeExporter]

The attachment is the rewrite net.sf.log4jdbc.OracleRdbmsSpecifics.
Use this, when we pass a Date object, we can cut and paste and run the logger 
sql.

Original comment by ihuangwj on 5 Mar 2010 at 5:13

Attachments:

• OracleRdbmsSpecifics.java

[GoogleCodeExporter]

I'd like to incorporate a variation of your patch.  But you lost the 
millisecond 
precision of the timstamp.  Was that intentional?  I haven't used Oracle in a 
long 
time...  Can you recode it to include the milliseconds in format, like the 
superclass?
Thanks.

Original comment by arthur.b...@gmail.com on 20 Mar 2010 at 1:21

[GoogleCodeExporter]

please forget my last comment, I figured out, it's 'ff3'  Also, the date 
handling was 
kind of wrong to start with (I was dropping the entire time portion of the 
object when 
using Date objects.)  That has been refactored a bit in the base class.  Please 
see SVN 
rev. 69

Please test for me on Oracle.  I don't have Oracle set up anywhere to test with!
File another issue if there are any problems.
Thanks!!

Original comment by arthur.b...@gmail.com on 20 Mar 2010 at 1:54

• Changed state: Fixed

[GoogleCodeExporter]

Thanks for your good job! I will try later. ;)

Original comment by ihuangwj on 26 Mar 2010 at 3:09

[GoogleCodeExporter]

Hello,
first, thanks to all constributers for this great tool. 

I'd like to send you a patch that was required in my environment for making the
oracle specifics working.

443,444d442
<     rdbmsSpecifics.put("oracle.jdbc.OracleDriver",
<                     new OracleRdbmsSpecifics());

Obviously, in my environmen, the oracle driver is registered as
oracle.jdbc.OracleDriver and not oracle.jdbc.driver.OracleDriver

following I've copied from the OracleDriver Sourcecode. It shows how the driver 
is
registered when loading the oracle.jdbc.driver.OracleDriver class:

      defaultDriver = new oracle.jdbc.OracleDriver();
      DriverManager.registerDriver(defaultDriver);

We use driver version 11.1.0.7.0-Production from ojdbc6.jar

THanks 

Kuno

Original comment by kuno.bae...@gmail.com on 27 May 2010 at 8:57

[GoogleCodeExporter]

Hello again,

here is another patch for \net\sf\log4jdbc\OracleRdbmsSpecifics.java, 
independent of
the one, I've added before.

In my understanding, the current implementation of OracleRdmsSpecifics is wrong.
Oracle can't  even compile the generated SQL. See following sample:

select  TO_DATE ('03/03/2010 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') from 
dual

Line above won't work in Oracle, since the oracle DATE type doens't support
milliseconds, as far as I understood it.

However, following works:

select  TO_TIMESTAMP ('03/03/2010 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') 
from dual
select  TO_DATE ('03/03/2010 00:00:00', 'mm/dd/yyyy hh24:mi:ss') from dual

As a concequence, we need to distinguish between Date and Timestamp type. See 
patch

18,20d17
< import java.sql.Timestamp;
< import java.text.DateFormat;
< import java.text.SimpleDateFormat;
30,34c27
<
<     protected static final DateFormat oracleDateFormat =
<         new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
<
<     OracleRdbmsSpecifics()
---
>   OracleRdbmsSpecifics()
41,50c34
<     if (object instanceof Timestamp)
<     {
<         // Use Oracle's to_timestamp function to insure it comes across as a 
timestamp
<         //
<         // for example:
<         //   to_date('12/31/2011 23:59:59.150, 'mm/dd/yyyy hh24:mi:ss.ff3')
<         return "to_timestamp('" + dateFormat.format(object) + "', "
<                         + "'mm/dd/yyyy hh24:mi:ss.ff3')";
<     }
<     else if (object instanceof Date)
---
>     if (object != null && object instanceof Date)
52,57c36,41
<         // Use Oracle's to_date function to insure it comes across as a date
<         //
<         // for example:
<         //   to_date('12/31/2011 23:59:59, 'mm/dd/yyyy hh24:mi:ss')
<         return "to_date('" + oracleDateFormat.format(object) + "', "
<                         + "'mm/dd/yyyy hh24:mi:ss')";
---
>       // Use Oracle's to_date function to insure it comes across as a date
>       //
>       // for example:
>       //   to_date('12/31/2011 23:59:59.150, 'mm/dd/yyyy hh24:mi:ss.ff3')
>       return "to_date('" + dateFormat.format(object) + "', " +
>         "'mm/dd/yyyy hh24:mi:ss.ff3')";

Additionally, I've removed the object != null test, since this instanceof 
implicit
checks for null and returns false if the object is null. Therefore, a precedent 
check
for null isn't required.

I've tested the patch for DATE bind variables only.

Thanks
Kuno

Original comment by kuno.bae...@gmail.com on 27 May 2010 at 10:31

[GoogleCodeExporter]

here are the entire files including the patches delivered in the comments of 
today.

Original comment by kuno.bae...@gmail.com on 27 May 2010 at 10:48

Attachments:

• DriverSpy.java
• OracleRdbmsSpecifics.java

[GoogleCodeExporter]

and here the more readable unified diff of the changes. 

Original comment by kuno.bae...@gmail.com on 27 May 2010 at 12:40

Attachments:

• DriverSpy.patch
• OracleRdbmsSpecifics.patch

[GoogleCodeExporter]

Thank you!  Your patches are out of date with the tips, but I incorporated your 
ideas.  
Checked in at revision: 87.  Please test this for me!

Original comment by arthur.b...@gmail.com on 28 May 2010 at 12:38

[GoogleCodeExporter]

Sorry, the patches were based on beta 1 release. Thanks for fixing it. I've 
reviewed
your changes in the source code. It looks ok. I'll occasionally test it.

Kuno

Original comment by kuno.bae...@gmail.com on 31 May 2010 at 7:22