PreparedStatement.setDouble() problem with italian formatted numbers

[mwarren2]

I have a new Filemaker Server 8 Advanced database, which comes with a DataDirect Sequelink driver, which in general has proved to be very good, a big advance on the old Filemaker drivers.

But I have one very serious problem with it, which makes it almost unuseable, and I'm wondering if anybody can help me solve it.

The problem is setDouble().

Our company is based in Italy, so our Filemaker database is formatted for the italian locale. Thus dates and numbers appear in the database in local format.

e.g. 25th November is 25/11/2007, with the month after the day dd/MM/yyyy, and italian numbers appear like this: 345,46 with comma for decimal point, instead of dot american style (345.46)

PreparedStatement.setDate() works perfectly, taking a java.sql.Date, and making it appear in the database in italian format.

setDouble() unfortunately does not. It takes a Double object and it sticks it into the database where it appears in american format e.g. 345.46 and the database thinks this number is a hundred times bigger than it actually is because dots separate thousands in italian formats.

This is very unfortunate to say the least. There is a workaround, but it involves completely abandoning PreparedStatements, which in today's modern programming world is like going back to the stone age. In addition it will mean that I won't be able to use the Spring jdbc classes for insertions. The only workaround is to use a normal Statement and insert Strings into the number fields, which is a real hack, and means writing hundreds of horribly hacked sql code lines.

Can anybody help with this? It looks like nothing less than a bug, but perhaps I'm missing something.

I'd be very grateful for any help on this, as it is holding back a large and complicated project. Any ideas?

[ave!]

Actually, it's worse in Japanese locale. I have reported about ten bugs related PreparedStatement and some of them are not fixed yet.

Anyway, my workaround for this problem is defining alternative accessor methods in my domain objects.

import java.text.*;

import java.util.*;



public class YourDomain {

  /* the field you want to insert */

  private Double dbl;

  private NumberFormat nf = NumberFormat.getInstance(Locale.ITALY);

  

  /* normal accessors */

  public getDbl() { return dbl; }

  public setDbl(Double dbl) { this.dbl = dbl; }

  

  /* getter for FM JDBC */

  public getDblStr() {

    return (this.dbl == null ? ""  nf.format(this.dbl));

  }

}

Now you can use this special accessor with PreparedStatement#setString().

PreparedStatement stmt = ...;

stmt.setString(yourDomain.getDblStr());

I'm not sure which spring class you are using but I think you can get the basic idea.

And, of course, you can have an alternative setter method for retrieving data from database if you need to.

It still is tedious but may be better than using normal Statement.

Hope this helps,

AVE!