Обсуждение: ResultSet.getInt problem

Hi,

I'm using PostgreSQL 8.1.2 in a FreeBSD 5.3. Using with JAVA servlet
application and driver postgresql-8.1-405.jdbc2ee.jar.

I make the query in Java so the DB returns a table something like (I
passed the query manually to the DB to be sure it was OK):

 id | name | domain | ...
17 | minplan | minplan.gov.ar | ...
  8 | msal | msal.gov.ar | ...
10 | trabajo | trabajo.gov.ar | ...
(3 rows)

Now in Java I get a code like this:

try {
  ResultSet rs= con.select(query);
  while (rs.next()) {
    // I get the results.
    id= rs.getInt("id");
    out.println(id); // prints 0 in every row!!
    name= rs.getString("name"); // ok
    domain= rs.getString("domain"); // ok
    ...

Now if I change getInt("id") for the other overload getInt(1), it works
fine!
(it prints the correct id for every column)

Thanks in advance,
Tom;

On Fri, 17 Feb 2006, "Tomás A. Rossi" wrote:

> ResultSet rs= con.select(query);
> while (rs.next()) {
>   id= rs.getInt("id");
>   out.println(id); // prints 0 in every row!!
>   name= rs.getString("name"); // ok
>   ...
>
> Now if I change getInt("id") for the other overload getInt(1), it works fine!
> (it prints the correct id for every column)
>

I'm not sure what's going wrong here.  What do you get if you add some
additional debugging code along the lines of the following:

ResultSetMetaData rsmd = rs.getMetaData();
for (int i=1; i<=rsmd.getColumnCount(); i++) {
     System.out.println("["+rsmd.getColumnName()+"]");
}

Kris Jurka

Kris Jurka escribió:

>
>
> On Fri, 17 Feb 2006, "Tomás A. Rossi" wrote:
>
>> ResultSet rs= con.select(query);
>> while (rs.next()) {
>>   id= rs.getInt("id");
>>   out.println(id); // prints 0 in every row!!
>>   name= rs.getString("name"); // ok
>>   ...
>>
>> Now if I change getInt("id") for the other overload getInt(1), it
>> works fine!
>> (it prints the correct id for every column)
>>
>
> I'm not sure what's going wrong here.  What do you get if you add some
> additional debugging code along the lines of the following:
>
> ResultSetMetaData rsmd = rs.getMetaData();
> for (int i=1; i<=rsmd.getColumnCount(); i++) {
>     System.out.println("["+rsmd.getColumnName()+"]");
> }
>
> Kris Jurka

The query formed is:

"SELECT * FROM sso.organismos o LEFT OUTER JOIN motivos_bloqueos m ON(
o.id_motivo_ultimo_bloqueo=m.id) WHERE no(o.baja_fisica) AND nombre ~*
'a' AND activo ORDER BY nombre"

The table is:

CREATE TABLE sso.organismos
(
  id serial NOT NULL,
  nombre text NOT NULL,
  dominio text NOT NULL,
  activo bool NOT NULL DEFAULT 't',
  fecha_ultima_baja timestamp,
  bloqueado bool NOT NULL DEFAULT 'f',
  cant_bloqueos_heredados int4,
  fecha_ultimo_bloqueo timestamp,
  detalle_ultimo_bloqueo text,
  id_motivo_ultimo_bloqueo int4,
  baja_fisica bool,
  descripcion text,
  CONSTRAINT organismos_pkey PRIMARY KEY (id),
  CONSTRAINT "$1" FOREIGN KEY (id_motivo_ultimo_bloqueo)
      REFERENCES sso.motivos_bloqueos (id)
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT organismos_nombre_key UNIQUE (nombre)
)

Your debug code printed:
"[id] [nombre] [dominio] [activo] [fecha_ultima_baja] [bloqueado]
[cant_bloqueos_heredados] [fecha_ultimo_bloqueo]
[detalle_ultimo_bloqueo] [id_motivo_ultimo_bloqueo] [baja_fisica]
[descripcion] [id] [motivo] [baja_fisica]"

Seems that two columns has the same name (id) or something like that?
How to workaround this?
This didn't happen when using postgres 7.3 with driver 7.3.

Thank you very much,
Tom;

On Fri, 17 Feb 2006, "Tomás A. Rossi" wrote:

> "SELECT * FROM sso.organismos o LEFT OUTER JOIN motivos_bloqueos m ON(
> o.id_motivo_ultimo_bloqueo=m.id) WHERE no(o.baja_fisica) AND nombre ~* 'a'
> AND activo ORDER BY nombre"
>
> Your debug code printed:
> "[id] [nombre] [dominio] [activo] [fecha_ultima_baja] [bloqueado]
> [cant_bloqueos_heredados] [fecha_ultimo_bloqueo] [detalle_ultimo_bloqueo]
> [id_motivo_ultimo_bloqueo] [baja_fisica] [descripcion] [id] [motivo]
> [baja_fisica]"
>
> Seems that two columns has the same name (id) or something like that? How to
> workaround this?
> This didn't happen when using postgres 7.3 with driver 7.3.
>

Previous driver was retrieving the first id, but the newer one is
retrieving the second id.  The behavior of duplicate names is undefined
and this change was part of a performance enhancement put in to speed up
searches by name.  You need to alias one of the columns to a different
name.

SELECT o.id AS oid, m.id AS mid, ... FROM ...

Then you may safely say getInt("oid").

Kris Jurka

Kris Jurka escribió:

>
>
> On Fri, 17 Feb 2006, "Tomás A. Rossi" wrote:
>
>> "SELECT * FROM sso.organismos o LEFT OUTER JOIN motivos_bloqueos m
>> ON( o.id_motivo_ultimo_bloqueo=m.id) WHERE no(o.baja_fisica) AND
>> nombre ~* 'a' AND activo ORDER BY nombre"
>>
>> Your debug code printed:
>> "[id] [nombre] [dominio] [activo] [fecha_ultima_baja] [bloqueado]
>> [cant_bloqueos_heredados] [fecha_ultimo_bloqueo]
>> [detalle_ultimo_bloqueo] [id_motivo_ultimo_bloqueo] [baja_fisica]
>> [descripcion] [id] [motivo] [baja_fisica]"
>>
>> Seems that two columns has the same name (id) or something like that?
>> How to workaround this?
>> This didn't happen when using postgres 7.3 with driver 7.3.
>>
>
> Previous driver was retrieving the first id, but the newer one is
> retrieving the second id.  The behavior of duplicate names is
> undefined and this change was part of a performance enhancement put in
> to speed up searches by name.  You need to alias one of the columns to
> a different name.
>
> SELECT o.id AS oid, m.id AS mid, ... FROM ...
>
> Then you may safely say getInt("oid").
>
> Kris Jurka

Thank you! Sorry for my ignorance about SQL and stuff, I'm a little
newbie to databases.

Tom;