Thread: Patch for broken JDBC's getColumn()
Hi, Attached is a patch for JDBC's getColumn() function that was broken / flawed in the following ways: 1. Only returned columns that had a default value defined, rather than all columns in a table 2. Used 2 * N + 1 queries to find out attributes, comments and typenames for N columns. By using some outer join syntax it is possible to retrieve all necessary information in just one SQL statement. This means this version is only suitable for PostgreSQL >= 7.1. Don't know whether that's a problem. I've tested this function with current sources and 7.1.3 and patched both jdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, as I have no JDK 1.1 available. Note the discussion in http://fts.postgresql.org/db/mw/msg.html?mid=1029626 regarding differences in obtaining comments on database object in 7.1 and 7.2. I was unable to use the following syntax (or similar ones): select ..., description from ... left outer join col_description(a.attrelid, a.attnum) description order by c.relname, a.attnum; (the error was parse error at or near '(') so I had to paste the actual code for the col_description function into the left outer join. Maybe someone who is more knowledgable about outer joins might provide me with a better SQL statement. Please review. Regards, Jeroen
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
> Hi,
>
> Attached is a patch for JDBC's getColumn() function that was broken /
> flawed in the following ways:
>
> 1. Only returned columns that had a default value defined, rather than all
> columns in a table
> 2. Used 2 * N + 1 queries to find out attributes, comments and typenames
> for N columns.
>
> By using some outer join syntax it is possible to retrieve all necessary
> information in just one SQL statement. This means this version is only
> suitable for PostgreSQL >= 7.1. Don't know whether that's a problem.
>
> I've tested this function with current sources and 7.1.3 and patched both
> jdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, as
> I have no JDK 1.1 available.
>
> Note the discussion in http://fts.postgresql.org/db/mw/msg.html?mid=1029626
> regarding differences in obtaining comments on database object in 7.1 and
> 7.2. I was unable to use the following syntax (or similar ones):
>
> select
> ...,
> description
> from
> ...
> left outer join col_description(a.attrelid, a.attnum) description
> order by
> c.relname, a.attnum;
>
> (the error was parse error at or near '(') so I had to paste the actual
> code for the col_description function into the left outer join. Maybe
> someone who is more knowledgable about outer joins might provide me with a
> better SQL statement.
>
> Please review.
>
> Regards,
>
>
> Jeroen
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. > Hi, > > Attached is a patch for JDBC's getColumn() function that was broken / > flawed in the following ways: > > 1. Only returned columns that had a default value defined, rather than all > columns in a table > 2. Used 2 * N + 1 queries to find out attributes, comments and typenames > for N columns. > > By using some outer join syntax it is possible to retrieve all necessary > information in just one SQL statement. This means this version is only > suitable for PostgreSQL >= 7.1. Don't know whether that's a problem. > > I've tested this function with current sources and 7.1.3 and patched both > jdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, as > I have no JDK 1.1 available. > > Note the discussion in http://fts.postgresql.org/db/mw/msg.html?mid=1029626 > regarding differences in obtaining comments on database object in 7.1 and > 7.2. I was unable to use the following syntax (or similar ones): > > select > ..., > description > from > ... > left outer join col_description(a.attrelid, a.attnum) description > order by > c.relname, a.attnum; > > (the error was parse error at or near '(') so I had to paste the actual > code for the col_description function into the left outer join. Maybe > someone who is more knowledgable about outer joins might provide me with a > better SQL statement. > > Please review. > > Regards, > > > Jeroen [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied and attached. Seems the line was in jdbc1 and jdbc2. Thanks. > > -----Original Message----- > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > Sent: Thursday, September 06, 2001 05:20 > > To: Jeroen van Vianen > > Cc: pgsql-patches@postgresql.org; pgsql-jdbc@postgresql.org > > Subject: Re: [PATCHES] Patch for broken JDBC's getColumn() > > > > > > > > Patch applied. Thanks. > > Thanks. However, I seem to have left a single debug statement in there :-( > > Here's a patch to remove it. > > Thanks, > > > Jeroen > [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 Index: src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v retrieving revision 1.30 diff -c -r1.30 DatabaseMetaData.java *** src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/09/06 03:20:30 1.30 --- src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/09/06 12:50:17 *************** *** 1973,1980 **** "order by" + " c.relname, a.attnum"); - System.out.println(sql); - java.sql.ResultSet r = connection.ExecSQL(sql.toString()); while (r.next()) { byte[][] tuple = new byte[18][0]; --- 1973,1978 ---- Index: src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v retrieving revision 1.34 diff -c -r1.34 DatabaseMetaData.java *** src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/09/06 03:20:30 1.34 --- src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/09/06 12:50:20 *************** *** 1973,1980 **** "order by" + " c.relname, a.attnum"); - System.out.println(sql); - java.sql.ResultSet r = connection.ExecSQL(sql.toString()); while (r.next()) { byte[][] tuple = new byte[18][0]; --- 1973,1978 ----