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