Mili,
See my comments below.
milimeter@163.com wrote:
> Thank you, Barry
>
> You know, I have to work with oracle and postgresql at the same time now. In
> company, I have to use oracle, and at home, I will use postgresql to develop
> application. So I hope my java codes can work anywhere (other coworker only
> use oracle, so I have to adapt all postgresql features to be fit to oracle).
>
> Now, someone wrote a code line as:
> ResultSet rs = .....
> int age = rs.getInt("age"); //here, feild "age" is a char(4) type
> The above codes can work well on oracle jdbc driver, but on postgresql, not
> work, because if age is 20, then in database it's "20 " but no "20". The
> reason is that jdbc driver of oracle automaticlly trim the returned result
> string; but postgresql not. There's so many places in codes of my company
> where get an integer from a char(...) feild, I can't change them all. So I
> hope jdbc driver of postgresql can also do it.
In general I think your code is broken. Either you should be using an
integer datatype or you should be using getString(). However according
to the jdbc spec, what you are doing should be possible and this is a
bug in the driver. Can you submit a patch for this?
>
> Another problem when programming jdbc codes:
> "INSERT INTO person VALUES ('100')" //here, the field is an integer, but I
> insert as a string
> The above SQL can work well on both oracle and postgresql, but:
> "INSERT INTO person VALUES ('')"
> can work only on oracle but not work on postgresql. On oracle, a default
> value will be inserted, but postgresql will report an error.
>
Here the problem is that Oracle is not following the ANSI Standard. The
standard says the '' = empty string which is how postgres correctly
interprets it. In Oracle '' = null, which is a violation of the spec.
Thus you are going to get different behavior in Oracle than in postgres.
This code really should be doing the following which is in compliance
with the sql spec and will work on both oracle and postgres:
INSERT INTO person VALUES(null)
thanks,
--Barry
