[JDBC] Is it safe to use 8.0.14 server and 7.4 driver ?

From: Daniel Henrique Alves Lima <email_daniel_h(at)yahoo.com.br>
Date: Sat Jan 12 2008 - 10:15:48 EST

        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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Received on Sat Jan 12 10:03:06 2008