Csv.writeResultSet uses wrong formats when exporting from Oracle Timestamp and Date columns

[Johannes Brodwall]

I've tried using Csv.writeResultSet to export parts of an Oracle database to I can later use it with CSVREAD. I encountered two issues:

1. Oracle formats Timestamp columns like this on getString: "2009-6-30.16.17. 21. 996802000"

2. Oracle formats Date columns like this on getString: "2008-12-31 00:00:00.0"

These formats will not work when selecting into H2 columns with the same types. The timestamp is totally messed up and the Date contains a time.

It could be argued that this is a problem with the Oracle driver. However, ResultSet.getString doesn't specify anything about the format of the string. I got the following patch of org.h2.tools.Csv#writeResultSet to work:

            while (rs.next()) {

                for (int i = 0; i < columnCount; i++) {

                    if (meta.getColumnType(i+1) == Types.TIMESTAMP && rs.getTimestamp(i+1)!=null) {

                        // Workaround of timestamp format bug in Oracle driver

                        row[i] = rs.getTimestamp(i+1).toString();

                    } else if (meta.getColumnType(i+1) == Types.DATE && rs.getTimestamp(i+1)!=null) {

                        // Workaround of date == datetime bug in Oracle driver

                        row[i] = rs.getDate(i+1).toString();

                    } else {

                        row[i] = rs.getString(i+1);

                    }

                }

                writeRow(row);

                rows++;

            }

It's not pretty, but if you want to use Csv.write to export from Oracle, it seems to be the only possibility...

~Johannes

[Thomas Mueller]

Hi,

Thanks! I will commit a similar patch. It's not pretty, but as you wrote it might be the only solution.

Regards,

Thomas

[Johannes Brodwall]

I just upgraded to H2 version 1.3.163 and replaced my patched code with the new version of Cvs. It works now. Thanks!

(By the way, the Csv.getInstance() method name made me think that Csv was a singleton at first, which both made me suspicious that it might not be thread safe and made me accidentally create two Csv-instances. "Csv.createInstance()" would be clearer (or even "new Csv()", heaven forfend!). But you may not want to break backwards compability)

~Johannes

[Thomas Mueller]

Hi,

the Csv.getInstance() method name made me think that Csv was a singleton at first

You are right. I will make the constructor public, and deprecate Csv.getInstance().

Regards,

Thomas