Thread: getIndexInfo() fails
Hi, folks! I try to use the above method in a project to find out whether an index has to be created or not. ResultSet rs = dbInfo.getIndexInfo(null, null, tableName,false, true); What really drives me nuts is that the ResultSet is always empty. I even tried it the hard way and nested four for loops that cycled through possible arguments ( null, "", "%" for the strings and true and false for the booleans) and even tried to use the indexName instead of the tableName. Nothing. It also doesn't matter which JDBC version I use (currently, it's jdbc7.1-1.2.jar for PostgreSQL 7.1.2 and J2SDK1.3). I double-checked the table name I pass and the table in question really has an index associated to it (that's what psql says). Any idea what could go wrong here? Thanks in advance. Matthias -- PADDLE-PG
Matthias, Try using the latest jar from jdbc.postgresql.org I am pretty sure there were a lot of changes in that section of the driver. Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Matthias Jordan Sent: Wednesday, January 30, 2002 9:24 AM To: pgsql-jdbc@postgresql.org Subject: [JDBC] getIndexInfo() fails Hi, folks! I try to use the above method in a project to find out whether an index has to be created or not. ResultSet rs = dbInfo.getIndexInfo(null, null, tableName,false, true); What really drives me nuts is that the ResultSet is always empty. I even tried it the hard way and nested four for loops that cycled through possible arguments ( null, "", "%" for the strings and true and false for the booleans) and even tried to use the indexName instead of the tableName. Nothing. It also doesn't matter which JDBC version I use (currently, it's jdbc7.1-1.2.jar for PostgreSQL 7.1.2 and J2SDK1.3). I double-checked the table name I pass and the table in question really has an index associated to it (that's what psql says). Any idea what could go wrong here? Thanks in advance. Matthias -- PADDLE-PG ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Update on my problem - yes it was "grant" on some of the tables that was stopping me from writing to them! Now I just have to get the Macintosh clients happy with the machine's address and convince the ftp server to let the tomcat user connect to the JSP folder... Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Calling getIndexInfo against a postgresql-7.1 db with the 7.2 jdbc driver (or with 7.3dev built 2002-02-09 ), I get the
following
exception:
Exception in thread "main" java.lang.NullPointerException
at org.postgresql.jdbc2.ResultSet.getBytes(Unknown source)
at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(Unknown source)
at foo.bar(foo.java:253)
at foo.baz(foo.java:167)
at foo.main(foo.java:46)
I decided to poke around in the source code and found the following piece of questionable-looking code in
org/postgresql/jdbc2/DatabaseMetaData.jar:
...
if (columnNameRS.next())
tuple[8] = columnNameRS.getBytes(1);
else
tuple[8] = "".getBytes();
tuple[8] = columnNameRS.getBytes(1);
...
Now, being willing to bet that that last line shouldn't be there, I commented it out and tried it again - and bingo! it
workedfine.
I see Dave Cramer's uid at the top of DatabaseMetaData.jar, so cc'ing him.
Thanks,
--Cormac Twomey
I'd have to agree with your bet, thanks a lot
Dave
-----Original Message-----
From: Cormac Twomey [mailto:cormac@bpallen.com]
Sent: Monday, February 18, 2002 7:40 PM
To: pgsql-jdbc@postgresql.org; Dave@micro-automation.net
Subject: [JDBC] getIndexInfo() throws NullPointerException
Calling getIndexInfo against a postgresql-7.1 db with the 7.2 jdbc
driver (or with 7.3dev built 2002-02-09 ), I get the following
exception:
Exception in thread "main" java.lang.NullPointerException
at org.postgresql.jdbc2.ResultSet.getBytes(Unknown source)
at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(Unknown
source)
at foo.bar(foo.java:253)
at foo.baz(foo.java:167)
at foo.main(foo.java:46)
I decided to poke around in the source code and found the following
piece of questionable-looking code in
org/postgresql/jdbc2/DatabaseMetaData.jar:
...
if (columnNameRS.next())
tuple[8] = columnNameRS.getBytes(1);
else
tuple[8] = "".getBytes();
tuple[8] = columnNameRS.getBytes(1);
...
Now, being willing to bet that that last line shouldn't be there, I
commented it out and tried it again - and bingo! it worked fine.
I see Dave Cramer's uid at the top of DatabaseMetaData.jar, so cc'ing
him.
Thanks,
--Cormac Twomey
Ok, I have one further issue, which I'm much less certain of - some of the indexes that I'm trying to obtain info about are multi-column indexes, but the resultset for getIndexInfo is only returning the column name of one of those columns. For theother columns, it's returning an empty string. So, for example, given the following index: create index foo_abc_idx on foo (a, b, c) calling getIndexInfo on table foo, will return 3 results (as I expected) but with the following columns: ORDINAL_POSITION | COLUMN_NAME ------------------------------ 1 | c 2 | 3 | The documentation for this call is sparse enough, so I'm not sure I'm not doing something wrong.... ideas? Thanks, --Cormac ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "'Cormac Twomey'" <cormac@bpallen.com>; <pgsql-jdbc@postgresql.org>; <Dave@micro-automation.net> Sent: Monday, February 18, 2002 4:54 PM Subject: Re: [JDBC] getIndexInfo() throws NullPointerException > I'd have to agree with your bet, thanks a lot > > Dave > > -----Original Message----- > From: Cormac Twomey [mailto:cormac@bpallen.com] > Sent: Monday, February 18, 2002 7:40 PM > To: pgsql-jdbc@postgresql.org; Dave@micro-automation.net > Subject: [JDBC] getIndexInfo() throws NullPointerException > > > Calling getIndexInfo against a postgresql-7.1 db with the 7.2 jdbc > driver (or with 7.3dev built 2002-02-09 ), I get the following > exception: > > Exception in thread "main" java.lang.NullPointerException > at org.postgresql.jdbc2.ResultSet.getBytes(Unknown source) > at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(Unknown > source) > at foo.bar(foo.java:253) > at foo.baz(foo.java:167) > at foo.main(foo.java:46) > > I decided to poke around in the source code and found the following > piece of questionable-looking code in > org/postgresql/jdbc2/DatabaseMetaData.jar: > > ... > if (columnNameRS.next()) > tuple[8] = columnNameRS.getBytes(1); > else > tuple[8] = "".getBytes(); > tuple[8] = columnNameRS.getBytes(1); > ... > > Now, being willing to bet that that last line shouldn't be there, I > commented it out and tried it again - and bingo! it worked fine. > > I see Dave Cramer's uid at the top of DatabaseMetaData.jar, so cc'ing > him. > > Thanks, > --Cormac Twomey > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
All this stuff is fairly new, so there are probably bugs in it, I will have a look at it. I suspect nobody ever tried multiple index's on a table Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Cormac Twomey Sent: Monday, February 18, 2002 8:51 PM To: Dave@micro-automation.net; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] getIndexInfo() throws NullPointerException Ok, I have one further issue, which I'm much less certain of - some of the indexes that I'm trying to obtain info about are multi-column indexes, but the resultset for getIndexInfo is only returning the column name of one of those columns. For the other columns, it's returning an empty string. So, for example, given the following index: create index foo_abc_idx on foo (a, b, c) calling getIndexInfo on table foo, will return 3 results (as I expected) but with the following columns: ORDINAL_POSITION | COLUMN_NAME ------------------------------ 1 | c 2 | 3 | The documentation for this call is sparse enough, so I'm not sure I'm not doing something wrong.... ideas? Thanks, --Cormac ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "'Cormac Twomey'" <cormac@bpallen.com>; <pgsql-jdbc@postgresql.org>; <Dave@micro-automation.net> Sent: Monday, February 18, 2002 4:54 PM Subject: Re: [JDBC] getIndexInfo() throws NullPointerException > I'd have to agree with your bet, thanks a lot > > Dave > > -----Original Message----- > From: Cormac Twomey [mailto:cormac@bpallen.com] > Sent: Monday, February 18, 2002 7:40 PM > To: pgsql-jdbc@postgresql.org; Dave@micro-automation.net > Subject: [JDBC] getIndexInfo() throws NullPointerException > > > Calling getIndexInfo against a postgresql-7.1 db with the 7.2 jdbc > driver (or with 7.3dev built 2002-02-09 ), I get the following > exception: > > Exception in thread "main" java.lang.NullPointerException > at org.postgresql.jdbc2.ResultSet.getBytes(Unknown source) > at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(Unknown > source) > at foo.bar(foo.java:253) > at foo.baz(foo.java:167) > at foo.main(foo.java:46) > > I decided to poke around in the source code and found the following > piece of questionable-looking code in > org/postgresql/jdbc2/DatabaseMetaData.jar: > > ... > if (columnNameRS.next()) > tuple[8] = columnNameRS.getBytes(1); > else > tuple[8] = "".getBytes(); > tuple[8] = columnNameRS.getBytes(1); > ... > > Now, being willing to bet that that last line shouldn't be there, I > commented it out and tried it again - and bingo! it worked fine. > > I see Dave Cramer's uid at the top of DatabaseMetaData.jar, so cc'ing > him. > > Thanks, > --Cormac Twomey > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Your patch has been applied DAve -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Cormac Twomey Sent: Monday, February 18, 2002 8:51 PM To: Dave@micro-automation.net; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] getIndexInfo() throws NullPointerException Ok, I have one further issue, which I'm much less certain of - some of the indexes that I'm trying to obtain info about are multi-column indexes, but the resultset for getIndexInfo is only returning the column name of one of those columns. For the other columns, it's returning an empty string. So, for example, given the following index: create index foo_abc_idx on foo (a, b, c) calling getIndexInfo on table foo, will return 3 results (as I expected) but with the following columns: ORDINAL_POSITION | COLUMN_NAME ------------------------------ 1 | c 2 | 3 | The documentation for this call is sparse enough, so I'm not sure I'm not doing something wrong.... ideas? Thanks, --Cormac ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "'Cormac Twomey'" <cormac@bpallen.com>; <pgsql-jdbc@postgresql.org>; <Dave@micro-automation.net> Sent: Monday, February 18, 2002 4:54 PM Subject: Re: [JDBC] getIndexInfo() throws NullPointerException > I'd have to agree with your bet, thanks a lot > > Dave > > -----Original Message----- > From: Cormac Twomey [mailto:cormac@bpallen.com] > Sent: Monday, February 18, 2002 7:40 PM > To: pgsql-jdbc@postgresql.org; Dave@micro-automation.net > Subject: [JDBC] getIndexInfo() throws NullPointerException > > > Calling getIndexInfo against a postgresql-7.1 db with the 7.2 jdbc > driver (or with 7.3dev built 2002-02-09 ), I get the following > exception: > > Exception in thread "main" java.lang.NullPointerException > at org.postgresql.jdbc2.ResultSet.getBytes(Unknown source) > at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(Unknown > source) > at foo.bar(foo.java:253) > at foo.baz(foo.java:167) > at foo.main(foo.java:46) > > I decided to poke around in the source code and found the following > piece of questionable-looking code in > org/postgresql/jdbc2/DatabaseMetaData.jar: > > ... > if (columnNameRS.next()) > tuple[8] = columnNameRS.getBytes(1); > else > tuple[8] = "".getBytes(); > tuple[8] = columnNameRS.getBytes(1); > ... > > Now, being willing to bet that that last line shouldn't be there, I > commented it out and tried it again - and bingo! it worked fine. > > I see Dave Cramer's uid at the top of DatabaseMetaData.jar, so cc'ing > him. > > Thanks, > --Cormac Twomey > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster