Is it safe to use 8.0.14 server and 7.4 driver ?

Поиск
Список
Период
Сортировка
От Daniel Henrique Alves Lima
Тема Is it safe to use 8.0.14 server and 7.4 driver ?
Дата
Msg-id 1200150949.5713.29.camel@c906c2a4.virtua.com.br
обсуждение исходный текст
Ответы Re: Is it safe to use 8.0.14 server and 7.4 driver ?
Список pgsql-jdbc
    Hi everybody ! First of all: I apologize for my bad English and i will
appreciate any help.

    I'm migrating a postgresql server from 7.3.15 to 8.0.14 and i've found
a issue about using new drivers and column order retrieval in left outer
joins. The only way that i found to avoid break old applications is to
use the driver pg74.216.jdbc3.jar (the driver
pg73jdbc3.jar has a "set autocommit" issue) to access a 8.0.14 server.
I've already tried some driver's "compatibility" options but they didn't
work for me.

    To illustrate my one of my problems i've created some tables and i've
inserted some data into them. After that, i've run a query in psql
prompt:

test_db => select p.*, c.* from parent p left outer join child c on
(c.p_id = p.p_id) order by p.p_id;
 p_id | p_name | p_id | c_name
------+--------+------+--------
    1 | John   |    1 | Alice
    1 | John   |    1 | Sam
    2 | Paul   |      |
    3 | Mary   |    3 | Lucas
(4 rows)

    When i run this query using old jdbc driver (7.4), i got the expected
result (for me), retrieving data through
resultSet.getObject(columnName):

1; John; 1; Alice
1; John; 1; Sam
2; Paul; 2; null
3; Mary; 3; Lucas

    When i run this query using new jdbc driver (8.0-8.2), something
happens:

1; John; 1; Alice
1; John; 1; Sam
null; Paul; null; null
3; Mary; 3; Lucas


    Which one of these behaviors is the right one ? Thanks in advance  !

=================
== Source code ==
=================

create table parent (
  p_id integer not null,
  p_name varchar(30) not null
);

alter table parent add constraint parent_pk primary key (p_id);
alter table parent add constraint parent_name_uk unique (p_name);


create table child (
  p_id integer not null,
  c_name varchar(30) not null
);


alter table child add constraint child_parent_fk foreign key (p_id)
references parent(p_id);
alter table child add constraint child_name_uk unique (p_id, c_name);


insert into parent (p_id, p_name) values (1, 'John');
insert into parent (p_id, p_name) values (2, 'Paul');
insert into parent (p_id, p_name) values (3, 'Mary');


insert into child (p_id, c_name) values (1, 'Sam');
insert into child (p_id, c_name) values (1, 'Alice');
insert into child (p_id, c_name) values (3, 'Lucas');

----------------------

connection =
driver.connect("jdbc:postgresql://127.0.0.1/test_db?charSet=UTF-8",
props);
statement = connection.createStatement();

resultSet = statement.executeQuery("select p.*, c.* from parent p left
outer " + "join child c on (c.p_id = p.p_id) order by p.p_id");

while (resultSet.next()) {
  System.out.println(resultSet.getObject("p_id") + "; "
  + resultSet.getString("p_name") + "; "+ resultSet.getObject("p_id")
  + "; "+ resultSet.getString("c_name"));
}


--
"If there must be trouble, let it be in my day,
 that my child may have peace."

Thomas Paine


В списке pgsql-jdbc по дате отправления:

Предыдущее
От: sulfinu@gmail.com
Дата:
Сообщение: unnecessary exception patch
Следующее
От: Håkan Jacobsson
Дата:
Сообщение: Re: JBoss-related question