Обсуждение: jdbc problem

Hi, all

    I have a table: CREATE TABLE person (age char(4))
    After executing "select age from person" with jdbc, I extract the value
with:
    getInt("age")
    Now is the problem, the same code can succeed on oracle, but on
postgresql, because of not triming space automaticlly, it cause an error.

    So, I hope the newest jdbc driver for postgresql can automaticlly trim
space just like oracle. The same problem also exists when execute insert:
    Provided "age" is an integer, then "insert into person values ('30')" can
be executed correctly, but "insert into person values ('')" can not. On
oracle, a default value will be added. So jdbc driver for postgresql should
also do it.

    Yours,
    mili

Mili,

I don't understand your issues.  Can you explain them in a bit more
detail?  Perhaps with code examples?

thanks,
--Barry

milimeter@163.com wrote:
> Hi, all
>
>     I have a table: CREATE TABLE person (age char(4))
>     After executing "select age from person" with jdbc, I extract the value
> with:
>     getInt("age")
>     Now is the problem, the same code can succeed on oracle, but on
> postgresql, because of not triming space automaticlly, it cause an error.
>
>     So, I hope the newest jdbc driver for postgresql can automaticlly trim
> space just like oracle. The same problem also exists when execute insert:
>     Provided "age" is an integer, then "insert into person values ('30')" can
> be executed correctly, but "insert into person values ('')" can not. On
> oracle, a default value will be added. So jdbc driver for postgresql should
> also do it.
>
>     Yours,
>     mili
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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.

  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.

  I hope I have expressed myself clearly. If I can't, it's my fault.

  Thank you very much,
  mili


在 星期四 24 四月 2003 10:50，您写道：
> Mili,
>
> I don't understand your issues.  Can you explain them in a bit more
> detail?  Perhaps with code examples?
>
> thanks,
> --Barry
>
> milimeter@163.com wrote:
> > Hi, all
> >
> >     I have a table: CREATE TABLE person (age char(4))
> >     After executing "select age from person" with jdbc, I extract the
> > value with:
> >     getInt("age")
> >     Now is the problem, the same code can succeed on oracle, but on
> > postgresql, because of not triming space automaticlly, it cause an error.
> >
> >     So, I hope the newest jdbc driver for postgresql can automaticlly
> > trim space just like oracle. The same problem also exists when execute
> > insert: Provided "age" is an integer, then "insert into person values
> > ('30')" can be executed correctly, but "insert into person values ('')"
> > can not. On oracle, a default value will be added. So jdbc driver for
> > postgresql should also do it.
> >
> >     Yours,
> >     mili
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster

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