Is it safe to use 8.0.14 server and 7.4 driver ?

From: Daniel Henrique Alves Lima <email_daniel_h(at)yahoo(dot)com(dot)br>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Is it safe to use 8.0.14 server and 7.4 driver ?
Date: 2008-01-12 15:15:48
Message-ID: 1200150949.5713.29.camel@c906c2a4.virtua.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Håkan Jacobsson 2008-01-12 18:59:07 Re: JBoss-related question
Previous Message sulfinu 2008-01-11 13:43:18 unnecessary exception patch