Thread: What needs to be done?
Hello, I read in comp.lang.java.databases that help is needed with development of the JDBC driver. Can someone please provide some pointers to what needs to be done? What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL 7.1 support? I've seen a lot of postings about BLOB problems, and JDBC-standard BLOB support is on the overall todo list (http://www.postgresql.org/docs/todo.html). Is that still open for development? Is there anyone who has already looked at JDBC-standard BLOB support? If so, what are the challenges and complications? I can't promise anything yet, but I'll certainly consider helping with PostgreSQL/JDBC development. I'm fluent in Java and have developed a database driver before (for Oracle in a proprietary product). I'm about to spend quite a lot of time on developing a web application in Java on top of PostgreSQL, so I certainly have an interest in good JDBC support. If you're not a developer but a user of the driver, what are your current complaints or wish list items? Regards, René Pijlman
Rene, Certainly the blob support needs to be done. That seems to be high on the list Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Rene Pijlman Sent: August 1, 2001 3:52 PM To: pgsql-jdbc@postgresql.org Subject: [JDBC] What needs to be done? Hello, I read in comp.lang.java.databases that help is needed with development of the JDBC driver. Can someone please provide some pointers to what needs to be done? What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL 7.1 support? I've seen a lot of postings about BLOB problems, and JDBC-standard BLOB support is on the overall todo list (http://www.postgresql.org/docs/todo.html). Is that still open for development? Is there anyone who has already looked at JDBC-standard BLOB support? If so, what are the challenges and complications? I can't promise anything yet, but I'll certainly consider helping with PostgreSQL/JDBC development. I'm fluent in Java and have developed a database driver before (for Oracle in a proprietary product). I'm about to spend quite a lot of time on developing a web application in Java on top of PostgreSQL, so I certainly have an interest in good JDBC support. If you're not a developer but a user of the driver, what are your current complaints or wish list items? Regards, René Pijlman ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, 1 Aug 2001, Rene Pijlman wrote: > Hello, > > I read in comp.lang.java.databases that help is needed with > development of the JDBC driver. Can someone please provide some > pointers to what needs to be done? > > What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL > 7.1 support? > > I've seen a lot of postings about BLOB problems, and > JDBC-standard BLOB support is on the overall todo list > (http://www.postgresql.org/docs/todo.html). Is that still open > for development? Is there anyone who has already looked at > JDBC-standard BLOB support? If so, what are the challenges and > complications? The broken BLOB support is a complete showstopper for PostgreSQL in some environments, so that feels like a high priority. As for JDBC 2.0, has anyone tried some sort of test suite for compliance? I know that some differences from the SQL standards make it impossible for PostgreSQL to be truly JDBC 2.0 compliant at the time, but it would be nice to know if we are as close to compliance as we can be. /Anders _____________________________________________________________________ A n d e r s B e n g t s s o n ndrsbngtssn@yahoo.se Stockholm, Sweden _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Wednesday 01 August 2001 20:52, Rene Pijlman wrote: > Hello, > > I read in comp.lang.java.databases that help is needed with > development of the JDBC driver. Can someone please provide some > pointers to what needs to be done? > > What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL > 7.1 support? > > I've seen a lot of postings about BLOB problems, and > JDBC-standard BLOB support is on the overall todo list > (http://www.postgresql.org/docs/todo.html). Is that still open > for development? Is there anyone who has already looked at > JDBC-standard BLOB support? If so, what are the challenges and > complications? > > I can't promise anything yet, but I'll certainly consider > helping with PostgreSQL/JDBC development. I'm fluent in Java and > have developed a database driver before (for Oracle in a > proprietary product). I'm about to spend quite a lot of time on > developing a web application in Java on top of PostgreSQL, so I > certainly have an interest in good JDBC support. > > If you're not a developer but a user of the driver, what are > your current complaints or wish list items? Hi, I am working in a client application that uses JDBC to access several databases. The problem is that, as the PostgreSQL JDBC driver doesn't follow JDBC Standard I had to write some specific code for use it with PostgreSQL DB. It would be very interesting to have a JDBC 2.0 compliant driver. I would surely try it and give some feedback!!! Ricardo Maia > > Regards, > René Pijlman > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote: >The problem is that, as the PostgreSQL JDBC driver doesn't >follow JDBC Standard I had to write some specific code for >use it with PostgreSQL DB. So what exactly are the deviations from the standard that you encountered? Regards, René Pijlman
For example when I call the method: DatabaseMetaData.getTypeInfo() I whould expect to see the SQL Type BLOB mapped as an oid. see attach Ricardo Maia On Wednesday 01 August 2001 23:29, Rene Pijlman wrote: > On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote: > >The problem is that, as the PostgreSQL JDBC driver doesn't > >follow JDBC Standard I had to write some specific code for > >use it with PostgreSQL DB. > > So what exactly are the deviations from the standard that you > encountered? > > Regards, > René Pijlman > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Attachment
Examples please? We need to know what is broken/missing in order for it to be fixed. I know there are people out there who would be glad to fix bugs in the JDBC driver if they know about them. Please post the problems you encountered to the jdbc mail list. thanks, --Barry Ricardo Maia wrote: > On Wednesday 01 August 2001 20:52, Rene Pijlman wrote: > >>Hello, >> >>I read in comp.lang.java.databases that help is needed with >>development of the JDBC driver. Can someone please provide some >>pointers to what needs to be done? >> >>What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL >>7.1 support? >> >>I've seen a lot of postings about BLOB problems, and >>JDBC-standard BLOB support is on the overall todo list >>(http://www.postgresql.org/docs/todo.html). Is that still open >>for development? Is there anyone who has already looked at >>JDBC-standard BLOB support? If so, what are the challenges and >>complications? >> >>I can't promise anything yet, but I'll certainly consider >>helping with PostgreSQL/JDBC development. I'm fluent in Java and >>have developed a database driver before (for Oracle in a >>proprietary product). I'm about to spend quite a lot of time on >>developing a web application in Java on top of PostgreSQL, so I >>certainly have an interest in good JDBC support. >> >>If you're not a developer but a user of the driver, what are >>your current complaints or wish list items? >> > > Hi, > > I am working in a client application that uses JDBC to access several > databases. The problem is that, as the PostgreSQL JDBC driver doesn't follow > JDBC Standard I had to write some specific code for use it with PostgreSQL DB. > > It would be very interesting to have a JDBC 2.0 compliant driver. > I would surely try it and give some feedback!!! > > Ricardo Maia > > >>Regards, >>René Pijlman >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
Please send us all of the issues you have or know about. Just providing
examples of some of the problems will only get fixes for some of the
problems. What would be really useful is a list of all the issues you
know about. That way they can end up on the TODO list and get addressed.
thanks,
--Barry
Ricardo Maia wrote:
> For example when I call the method:
>
> DatabaseMetaData.getTypeInfo()
>
> I whould expect to see the SQL Type BLOB mapped as an oid.
>
> see attach
>
> Ricardo Maia
>
>
> On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
>
>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
>>
>>>The problem is that, as the PostgreSQL JDBC driver doesn't
>>>follow JDBC Standard I had to write some specific code for
>>>use it with PostgreSQL DB.
>>>
>>So what exactly are the deviations from the standard that you
>>encountered?
>>
>>Regards,
>>René Pijlman
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>>------------------------------------------------------------------------
>>
>>package databasetest;
>>
>>import java.sql.*;
>>
>>public class GetTypesInfo {
>>
>> public static void main(String args[ ]) {
>>
>> String url = "jdbc:postgresql://127.0.0.1/test";
>>
>> Connection con;
>>
>> DatabaseMetaData dbmd;
>>
>> try {
>> Class.forName("org.postgresql.Driver");
>> } catch(java.lang.ClassNotFoundException e) {
>> System.err.print("ClassNotFoundException: ");
>> System.err.println(e.getMessage());
>> }
>>
>> try {
>> con = DriverManager.getConnection(url,"bobby", "tareco");
>>
>> dbmd = con.getMetaData();
>>
>> ResultSet rs = dbmd.getTypeInfo();
>>
>> while (rs.next()) {
>>
>> String typeName = rs.getString("TYPE_NAME");
>>
>> short dataType = rs.getShort("DATA_TYPE");
>>
>> String createParams = rs.getString("CREATE_PARAMS");
>>
>> int nullable = rs.getInt("NULLABLE");
>>
>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
>>
>> if(dataType != java.sql.Types.OTHER)
>> {
>> System.out.println("DBMS type " + typeName + ":");
>> System.out.println(" java.sql.Types: " + typeName(dataType));
>> System.out.print(" parameters used to create: ");
>> System.out.println(createParams);
>> System.out.println(" nullable?: " + nullable);
>> System.out.print(" case sensitive?: ");
>> System.out.println(caseSensitive);
>> System.out.println("");
>> }
>> }
>>
>> con.close();
>> } catch(SQLException ex) {
>> System.err.println("SQLException: " + ex.getMessage());
>> }
>> }
>>
>>
>> public static String typeName(int i)
>> {
>> switch(i){
>> case java.sql.Types.ARRAY: return "ARRAY";
>> case java.sql.Types.BIGINT: return "BIGINT";
>> case java.sql.Types.BINARY: return "BINARY";
>> case java.sql.Types.BIT: return "BIT";
>> case java.sql.Types.BLOB: return "BLOB";
>> case java.sql.Types.CHAR: return "CHAR";
>> case java.sql.Types.CLOB: return "CLOB";
>> case java.sql.Types.DATE: return "DATE";
>> case java.sql.Types.DECIMAL: return "DECIMAL";
>> case java.sql.Types.DISTINCT: return "DISTINCT";
>> case java.sql.Types.DOUBLE: return "DOUBLE";
>> case java.sql.Types.FLOAT: return "FLOAT";
>> case java.sql.Types.INTEGER: return "INTEGER";
>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
>> case java.sql.Types.NULL: return "NULL";
>> case java.sql.Types.NUMERIC: return "NUMERIC";
>> case java.sql.Types.OTHER: return "OTHER";
>> case java.sql.Types.REAL: return "REAL";
>> case java.sql.Types.REF: return "REF";
>> case java.sql.Types.SMALLINT: return "SMALLINT";
>> case java.sql.Types.STRUCT: return "STRUCT";
>> case java.sql.Types.TIME: return "TIME";
>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
>> case java.sql.Types.TINYINT: return "TINYINT";
>> case java.sql.Types.VARBINARY: return "VARBINARY";
>> case java.sql.Types.VARCHAR: return "VARCHAR";
>> default: return "";
>> }
>> }
>>}
>>
>>
>>------------------------------------------------------------------------
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>> GetTypesInfo.java
>>
>> Content-Type:
>>
>> text/x-java
>> Content-Encoding:
>>
>> base64
>>
>>
>> ------------------------------------------------------------------------
>> Part 1.3
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> binary
>>
>>
I actually think the response for 'oid' is correct. It reports the oid
as java type integer (which is the real datatype of the value stored).
A column of type oid can be used for may different things. It can be
used for blobs, but not all columns of type oid are used for blobs.
Another use of a column of type oid is to store foreign keys from one
table to another. Since all tables have a builtin column named 'oid' of
type oid, it is very convenient to use this value in foreign keys on
other tables. Assuming that oid = blob would break those applications.
I hope everyone that uses postgresql and jdbc understands that BLOB
support is one area with many problems, some of which can be fixed in
the JDBC code, but others that will require better support in the
underlying database.
thanks,
--Barry
Ricardo Maia wrote:
> For example when I call the method:
>
> DatabaseMetaData.getTypeInfo()
>
> I whould expect to see the SQL Type BLOB mapped as an oid.
>
> see attach
>
> Ricardo Maia
>
>
> On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
>
>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
>>
>>>The problem is that, as the PostgreSQL JDBC driver doesn't
>>>follow JDBC Standard I had to write some specific code for
>>>use it with PostgreSQL DB.
>>>
>>So what exactly are the deviations from the standard that you
>>encountered?
>>
>>Regards,
>>René Pijlman
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>>------------------------------------------------------------------------
>>
>>package databasetest;
>>
>>import java.sql.*;
>>
>>public class GetTypesInfo {
>>
>> public static void main(String args[ ]) {
>>
>> String url = "jdbc:postgresql://127.0.0.1/test";
>>
>> Connection con;
>>
>> DatabaseMetaData dbmd;
>>
>> try {
>> Class.forName("org.postgresql.Driver");
>> } catch(java.lang.ClassNotFoundException e) {
>> System.err.print("ClassNotFoundException: ");
>> System.err.println(e.getMessage());
>> }
>>
>> try {
>> con = DriverManager.getConnection(url,"bobby", "tareco");
>>
>> dbmd = con.getMetaData();
>>
>> ResultSet rs = dbmd.getTypeInfo();
>>
>> while (rs.next()) {
>>
>> String typeName = rs.getString("TYPE_NAME");
>>
>> short dataType = rs.getShort("DATA_TYPE");
>>
>> String createParams = rs.getString("CREATE_PARAMS");
>>
>> int nullable = rs.getInt("NULLABLE");
>>
>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
>>
>> if(dataType != java.sql.Types.OTHER)
>> {
>> System.out.println("DBMS type " + typeName + ":");
>> System.out.println(" java.sql.Types: " + typeName(dataType));
>> System.out.print(" parameters used to create: ");
>> System.out.println(createParams);
>> System.out.println(" nullable?: " + nullable);
>> System.out.print(" case sensitive?: ");
>> System.out.println(caseSensitive);
>> System.out.println("");
>> }
>> }
>>
>> con.close();
>> } catch(SQLException ex) {
>> System.err.println("SQLException: " + ex.getMessage());
>> }
>> }
>>
>>
>> public static String typeName(int i)
>> {
>> switch(i){
>> case java.sql.Types.ARRAY: return "ARRAY";
>> case java.sql.Types.BIGINT: return "BIGINT";
>> case java.sql.Types.BINARY: return "BINARY";
>> case java.sql.Types.BIT: return "BIT";
>> case java.sql.Types.BLOB: return "BLOB";
>> case java.sql.Types.CHAR: return "CHAR";
>> case java.sql.Types.CLOB: return "CLOB";
>> case java.sql.Types.DATE: return "DATE";
>> case java.sql.Types.DECIMAL: return "DECIMAL";
>> case java.sql.Types.DISTINCT: return "DISTINCT";
>> case java.sql.Types.DOUBLE: return "DOUBLE";
>> case java.sql.Types.FLOAT: return "FLOAT";
>> case java.sql.Types.INTEGER: return "INTEGER";
>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
>> case java.sql.Types.NULL: return "NULL";
>> case java.sql.Types.NUMERIC: return "NUMERIC";
>> case java.sql.Types.OTHER: return "OTHER";
>> case java.sql.Types.REAL: return "REAL";
>> case java.sql.Types.REF: return "REF";
>> case java.sql.Types.SMALLINT: return "SMALLINT";
>> case java.sql.Types.STRUCT: return "STRUCT";
>> case java.sql.Types.TIME: return "TIME";
>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
>> case java.sql.Types.TINYINT: return "TINYINT";
>> case java.sql.Types.VARBINARY: return "VARBINARY";
>> case java.sql.Types.VARCHAR: return "VARCHAR";
>> default: return "";
>> }
>> }
>>}
>>
>>
>>------------------------------------------------------------------------
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>> GetTypesInfo.java
>>
>> Content-Type:
>>
>> text/x-java
>> Content-Encoding:
>>
>> base64
>>
>>
>> ------------------------------------------------------------------------
>> Part 1.3
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> binary
>>
>>
Anders, What aspects of BLOB support do you consider broken? Are these aspects that are broken in the JDBC layer or are 'broken' at the server layer? thanks, --Barry Anders Bengtsson wrote: > On Wed, 1 Aug 2001, Rene Pijlman wrote: > > >>Hello, >> >>I read in comp.lang.java.databases that help is needed with >>development of the JDBC driver. Can someone please provide some >>pointers to what needs to be done? >> >>What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL >>7.1 support? >> >>I've seen a lot of postings about BLOB problems, and >>JDBC-standard BLOB support is on the overall todo list >>(http://www.postgresql.org/docs/todo.html). Is that still open >>for development? Is there anyone who has already looked at >>JDBC-standard BLOB support? If so, what are the challenges and >>complications? >> > > The broken BLOB support is a complete showstopper for PostgreSQL in some > environments, so that feels like a high priority. > > As for JDBC 2.0, has anyone tried some sort of test suite for compliance? > I know that some differences from the SQL standards make it impossible for > PostgreSQL to be truly JDBC 2.0 compliant at the time, but it would be > nice to know if we are as close to compliance as we can be. > > /Anders > _____________________________________________________________________ > A n d e r s B e n g t s s o n ndrsbngtssn@yahoo.se > Stockholm, Sweden > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > >
This appeared on the JDBC list. Do we need to address this? > I actually consider the biggest problem the fact the the 'official' > postgres jdbc website is very much out of date > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > I feel that either someone needs to maintain this page; or someone needs > to create a new website and get the jdbc.postgresql.org DNS entry to > point to the new site, or the page should just be decommisioned. At > this point I think it is doing more harm than good. -- 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
I actually consider the biggest problem the fact the the 'official' postgres jdbc website is very much out of date (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). I feel that either someone needs to maintain this page; or someone needs to create a new website and get the jdbc.postgresql.org DNS entry to point to the new site, or the page should just be decommisioned. At this point I think it is doing more harm than good. thanks, --Barry Rene Pijlman wrote: > Hello, > > I read in comp.lang.java.databases that help is needed with > development of the JDBC driver. Can someone please provide some > pointers to what needs to be done? > > What are the open issues? Is it JDBC 2.0 compliance? PostgreSQL > 7.1 support? > > I've seen a lot of postings about BLOB problems, and > JDBC-standard BLOB support is on the overall todo list > (http://www.postgresql.org/docs/todo.html). Is that still open > for development? Is there anyone who has already looked at > JDBC-standard BLOB support? If so, what are the challenges and > complications? > > I can't promise anything yet, but I'll certainly consider > helping with PostgreSQL/JDBC development. I'm fluent in Java and > have developed a database driver before (for Oracle in a > proprietary product). I'm about to spend quite a lot of time on > developing a web application in Java on top of PostgreSQL, so I > certainly have an interest in good JDBC support. > > If you're not a developer but a user of the driver, what are > your current complaints or wish list items? > > Regards, > René Pijlman > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Bruce, I am willing to make my site the "official" site. For now we could just repoint the dns to jdbc.fastcrypt.com, or I could build them on my site, and ftp them into the postgres site? I think we do need to address it. A lot of people go there for answers. Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Bruce Momjian Sent: August 1, 2001 10:41 PM To: Barry Lind Cc: Rene Pijlman; pgsql-jdbc@postgresql.org; PostgreSQL-development Subject: [JDBC] Re: What needs to be done? This appeared on the JDBC list. Do we need to address this? > I actually consider the biggest problem the fact the the 'official' > postgres jdbc website is very much out of date > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > I feel that either someone needs to maintain this page; or someone needs > to create a new website and get the jdbc.postgresql.org DNS entry to > point to the new site, or the page should just be decommisioned. At > this point I think it is doing more harm than good. -- 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Let's see what people say on hackers. > Bruce, > > I am willing to make my site the "official" site. For now we could just > repoint the dns to jdbc.fastcrypt.com, or I could build them on my site, > and ftp them into the postgres site? > > I think we do need to address it. A lot of people go there for answers. > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Bruce Momjian > Sent: August 1, 2001 10:41 PM > To: Barry Lind > Cc: Rene Pijlman; pgsql-jdbc@postgresql.org; PostgreSQL-development > Subject: [JDBC] Re: What needs to be done? > > > > This appeared on the JDBC list. Do we need to address this? > > > I actually consider the biggest problem the fact the the 'official' > > postgres jdbc website is very much out of date > > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > > > I feel that either someone needs to maintain this page; or someone > needs > > to create a new website and get the jdbc.postgresql.org DNS entry to > > point to the new site, or the page should just be decommisioned. At > > this point I think it is doing more harm than good. > > -- > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > -- 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
So how whould I map the BLOB java type in the corresponding SQL type?
I want to create a table with a BLOB attribute, but I want that my code can
run for PostgreSQL, Oracle and other BD that handles BLOBs.
So first I had to map the BLOB in the corresponding BD SQL type and then
create the table with an attribute of that SQL type.
Ricardo Maia
On Thursday 02 August 2001 03:16, Barry Lind wrote:
> I actually think the response for 'oid' is correct. It reports the oid
> as java type integer (which is the real datatype of the value stored).
> A column of type oid can be used for may different things. It can be
> used for blobs, but not all columns of type oid are used for blobs.
> Another use of a column of type oid is to store foreign keys from one
> table to another. Since all tables have a builtin column named 'oid' of
> type oid, it is very convenient to use this value in foreign keys on
> other tables. Assuming that oid = blob would break those applications.
>
> I hope everyone that uses postgresql and jdbc understands that BLOB
> support is one area with many problems, some of which can be fixed in
> the JDBC code, but others that will require better support in the
> underlying database.
>
> thanks,
> --Barry
>
> Ricardo Maia wrote:
> > For example when I call the method:
> >
> > DatabaseMetaData.getTypeInfo()
> >
> > I whould expect to see the SQL Type BLOB mapped as an oid.
> >
> > see attach
> >
> > Ricardo Maia
> >
> > On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
> >>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
> >>>The problem is that, as the PostgreSQL JDBC driver doesn't
> >>>follow JDBC Standard I had to write some specific code for
> >>>use it with PostgreSQL DB.
> >>
> >>So what exactly are the deviations from the standard that you
> >>encountered?
> >>
> >>Regards,
> >>René Pijlman
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >>
> >>
> >>------------------------------------------------------------------------
> >>
> >>package databasetest;
> >>
> >>import java.sql.*;
> >>
> >>public class GetTypesInfo {
> >>
> >> public static void main(String args[ ]) {
> >>
> >> String url = "jdbc:postgresql://127.0.0.1/test";
> >>
> >> Connection con;
> >>
> >> DatabaseMetaData dbmd;
> >>
> >> try {
> >> Class.forName("org.postgresql.Driver");
> >> } catch(java.lang.ClassNotFoundException e) {
> >> System.err.print("ClassNotFoundException: ");
> >> System.err.println(e.getMessage());
> >> }
> >>
> >> try {
> >> con = DriverManager.getConnection(url,"bobby", "tareco");
> >>
> >> dbmd = con.getMetaData();
> >>
> >> ResultSet rs = dbmd.getTypeInfo();
> >>
> >> while (rs.next()) {
> >>
> >> String typeName = rs.getString("TYPE_NAME");
> >>
> >> short dataType = rs.getShort("DATA_TYPE");
> >>
> >> String createParams = rs.getString("CREATE_PARAMS");
> >>
> >> int nullable = rs.getInt("NULLABLE");
> >>
> >> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
> >>
> >> if(dataType != java.sql.Types.OTHER)
> >> {
> >> System.out.println("DBMS type " + typeName + ":");
> >> System.out.println(" java.sql.Types: " +
> >> typeName(dataType)); System.out.print(" parameters used to create:
> >> ");
> >> System.out.println(createParams);
> >> System.out.println(" nullable?: " + nullable);
> >> System.out.print(" case sensitive?: ");
> >> System.out.println(caseSensitive);
> >> System.out.println("");
> >> }
> >> }
> >>
> >> con.close();
> >> } catch(SQLException ex) {
> >> System.err.println("SQLException: " + ex.getMessage());
> >> }
> >> }
> >>
> >>
> >> public static String typeName(int i)
> >> {
> >> switch(i){
> >> case java.sql.Types.ARRAY: return "ARRAY";
> >> case java.sql.Types.BIGINT: return "BIGINT";
> >> case java.sql.Types.BINARY: return "BINARY";
> >> case java.sql.Types.BIT: return "BIT";
> >> case java.sql.Types.BLOB: return "BLOB";
> >> case java.sql.Types.CHAR: return "CHAR";
> >> case java.sql.Types.CLOB: return "CLOB";
> >> case java.sql.Types.DATE: return "DATE";
> >> case java.sql.Types.DECIMAL: return "DECIMAL";
> >> case java.sql.Types.DISTINCT: return "DISTINCT";
> >> case java.sql.Types.DOUBLE: return "DOUBLE";
> >> case java.sql.Types.FLOAT: return "FLOAT";
> >> case java.sql.Types.INTEGER: return "INTEGER";
> >> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
> >> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
> >> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
> >> case java.sql.Types.NULL: return "NULL";
> >> case java.sql.Types.NUMERIC: return "NUMERIC";
> >> case java.sql.Types.OTHER: return "OTHER";
> >> case java.sql.Types.REAL: return "REAL";
> >> case java.sql.Types.REF: return "REF";
> >> case java.sql.Types.SMALLINT: return "SMALLINT";
> >> case java.sql.Types.STRUCT: return "STRUCT";
> >> case java.sql.Types.TIME: return "TIME";
> >> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
> >> case java.sql.Types.TINYINT: return "TINYINT";
> >> case java.sql.Types.VARBINARY: return "VARBINARY";
> >> case java.sql.Types.VARCHAR: return "VARCHAR";
> >> default: return "";
> >> }
> >> }
> >>}
> >>
> >>
> >>------------------------------------------------------------------------
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 5: Have you checked our extensive FAQ?
> >>
> >>http://www.postgresql.org/users-lounge/docs/faq.html
> >>
> >> GetTypesInfo.java
> >>
> >> Content-Type:
> >>
> >> text/x-java
> >> Content-Encoding:
> >>
> >> base64
> >>
> >>
> >> ------------------------------------------------------------------------
> >> Part 1.3
> >>
> >> Content-Type:
> >>
> >> text/plain
> >> Content-Encoding:
> >>
> >> binary
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I would like to see array support added to the jdbc driver. Mike >Examples please? We need to know what is broken/missing in order for it >to be fixed. I know there are people out there who would be glad to fix >bugs in the JDBC driver if they know about them. Please post the problems >you encountered to the jdbc mail list. > >thanks, >--Barry
[Answering as Anders Norwegian brother :-] * Barry Lind <barry@xythos.com> wrote: | | Anders, | | What aspects of BLOB support do you consider broken? Are these | aspects that are broken in the JDBC layer or are 'broken' at the | server layer? We should have support for the bytea datatype, so applications are not required to wrap blob operations into a transaction. This has been a showstopper for using PostgreSQL with the Turbine framework at Apache for a long time. If we get that to work with PostgreSQL we will attract more users and be a step closer to world domination ;-) -- Gunnar Rønning - gunnar@polygnosis.com Senior Consultant, Polygnosis AS, http://www.polygnosis.com/
* Barry Lind <barry@xythos.com> wrote: | | I actually think the response for 'oid' is correct. It reports the Well, maybe one could check if the oid is a foreign key refering to the lo table. -- Gunnar Rønning - gunnar@polygnosis.com Senior Consultant, Polygnosis AS, http://www.polygnosis.com/
> I actually consider the biggest problem the fact the the 'official' > postgres jdbc website is very much out of date > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > I feel that either someone needs to maintain this page; or someone needs > to create a new website and get the jdbc.postgresql.org DNS entry to > point to the new site, or the page should just be decommisioned. At > this point I think it is doing more harm than good. Just a followup. Peter has replied to a few people stating he is very busy and wants someone to take over the jdbc.postgresql.org website. Marc, Vince, and others are working on it now. -- 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
On Wed, Aug 01, 2001 at 10:19:01PM +0200, Anders Bengtsson wrote: > > As for JDBC 2.0, has anyone tried some sort of test suite for compliance? > I know that some differences from the SQL standards make it impossible for > PostgreSQL to be truly JDBC 2.0 compliant at the time, but it would be > nice to know if we are as close to compliance as we can be. > I have run the JDBC test suite[1] against the jdbc driver that comes with the postgresql 7.1.2 release and the driver that is in CVS. About 17% of the tests fail in both cases. From a glance, it looks like most of those failures are from unimplemented methods, either because support hasn't been added to the driver or there isn't any backend support. There are some weird failures as well (i.e.: a test fails once, but succeeds on later runs). Once I have combed through the results (and there are a lot of results!), I will post a report here. Liam [1] http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html http://java.sun.com/products/jdbc/download.html#jdbctestsuite The instructions are fairly straightforward, but application server stuff is a bit vague. Using j2ee as the application server is what they expect even though it doesn't say so in the docs. I can provide instructions. -- Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com
Ricardo,
There are many other issues with postgres blobs that will not allow you
to acheive your goal easily. You are going to need different
implementations per database type to deal with the differences between
blob implementations across different databases. The one big hurdle you
will have with postgres blobs is the fact that when you delete the row
containing the blob, it doesn't delete the blob. You have to issue a
separate delete blob request. This is very different than what happens
in Oracle for example. This can be automated by adding triggers to the
table to do this, but by now you are very far from having a single code
base (at least the code that creates the tables and triggers) that
supports all of the different databases.
thanks,
--Barry
Ricardo Maia wrote:
>
> So how whould I map the BLOB java type in the corresponding SQL type?
>
> I want to create a table with a BLOB attribute, but I want that my code can
> run for PostgreSQL, Oracle and other BD that handles BLOBs.
>
> So first I had to map the BLOB in the corresponding BD SQL type and then
> create the table with an attribute of that SQL type.
>
> Ricardo Maia
>
> On Thursday 02 August 2001 03:16, Barry Lind wrote:
>
>>I actually think the response for 'oid' is correct. It reports the oid
>>as java type integer (which is the real datatype of the value stored).
>>A column of type oid can be used for may different things. It can be
>>used for blobs, but not all columns of type oid are used for blobs.
>>Another use of a column of type oid is to store foreign keys from one
>>table to another. Since all tables have a builtin column named 'oid' of
>>type oid, it is very convenient to use this value in foreign keys on
>>other tables. Assuming that oid = blob would break those applications.
>>
>>I hope everyone that uses postgresql and jdbc understands that BLOB
>>support is one area with many problems, some of which can be fixed in
>>the JDBC code, but others that will require better support in the
>>underlying database.
>>
>>thanks,
>>--Barry
>>
>>Ricardo Maia wrote:
>>
>>>For example when I call the method:
>>>
>>>DatabaseMetaData.getTypeInfo()
>>>
>>>I whould expect to see the SQL Type BLOB mapped as an oid.
>>>
>>>see attach
>>>
>>>Ricardo Maia
>>>
>>>On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
>>>
>>>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
>>>>
>>>>>The problem is that, as the PostgreSQL JDBC driver doesn't
>>>>>follow JDBC Standard I had to write some specific code for
>>>>>use it with PostgreSQL DB.
>>>>>
>>>>So what exactly are the deviations from the standard that you
>>>>encountered?
>>>>
>>>>Regards,
>>>>René Pijlman
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>>
>>>>
>>>>------------------------------------------------------------------------
>>>>
>>>>package databasetest;
>>>>
>>>>import java.sql.*;
>>>>
>>>>public class GetTypesInfo {
>>>>
>>>> public static void main(String args[ ]) {
>>>>
>>>> String url = "jdbc:postgresql://127.0.0.1/test";
>>>>
>>>> Connection con;
>>>>
>>>> DatabaseMetaData dbmd;
>>>>
>>>> try {
>>>> Class.forName("org.postgresql.Driver");
>>>> } catch(java.lang.ClassNotFoundException e) {
>>>> System.err.print("ClassNotFoundException: ");
>>>> System.err.println(e.getMessage());
>>>> }
>>>>
>>>> try {
>>>> con = DriverManager.getConnection(url,"bobby", "tareco");
>>>>
>>>> dbmd = con.getMetaData();
>>>>
>>>> ResultSet rs = dbmd.getTypeInfo();
>>>>
>>>> while (rs.next()) {
>>>>
>>>> String typeName = rs.getString("TYPE_NAME");
>>>>
>>>> short dataType = rs.getShort("DATA_TYPE");
>>>>
>>>> String createParams = rs.getString("CREATE_PARAMS");
>>>>
>>>> int nullable = rs.getInt("NULLABLE");
>>>>
>>>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
>>>>
>>>> if(dataType != java.sql.Types.OTHER)
>>>> {
>>>> System.out.println("DBMS type " + typeName + ":");
>>>> System.out.println(" java.sql.Types: " +
>>>>typeName(dataType)); System.out.print(" parameters used to create:
>>>>");
>>>> System.out.println(createParams);
>>>> System.out.println(" nullable?: " + nullable);
>>>> System.out.print(" case sensitive?: ");
>>>> System.out.println(caseSensitive);
>>>> System.out.println("");
>>>> }
>>>> }
>>>>
>>>> con.close();
>>>> } catch(SQLException ex) {
>>>> System.err.println("SQLException: " + ex.getMessage());
>>>> }
>>>> }
>>>>
>>>>
>>>> public static String typeName(int i)
>>>> {
>>>> switch(i){
>>>> case java.sql.Types.ARRAY: return "ARRAY";
>>>> case java.sql.Types.BIGINT: return "BIGINT";
>>>> case java.sql.Types.BINARY: return "BINARY";
>>>> case java.sql.Types.BIT: return "BIT";
>>>> case java.sql.Types.BLOB: return "BLOB";
>>>> case java.sql.Types.CHAR: return "CHAR";
>>>> case java.sql.Types.CLOB: return "CLOB";
>>>> case java.sql.Types.DATE: return "DATE";
>>>> case java.sql.Types.DECIMAL: return "DECIMAL";
>>>> case java.sql.Types.DISTINCT: return "DISTINCT";
>>>> case java.sql.Types.DOUBLE: return "DOUBLE";
>>>> case java.sql.Types.FLOAT: return "FLOAT";
>>>> case java.sql.Types.INTEGER: return "INTEGER";
>>>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
>>>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
>>>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
>>>> case java.sql.Types.NULL: return "NULL";
>>>> case java.sql.Types.NUMERIC: return "NUMERIC";
>>>> case java.sql.Types.OTHER: return "OTHER";
>>>> case java.sql.Types.REAL: return "REAL";
>>>> case java.sql.Types.REF: return "REF";
>>>> case java.sql.Types.SMALLINT: return "SMALLINT";
>>>> case java.sql.Types.STRUCT: return "STRUCT";
>>>> case java.sql.Types.TIME: return "TIME";
>>>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
>>>> case java.sql.Types.TINYINT: return "TINYINT";
>>>> case java.sql.Types.VARBINARY: return "VARBINARY";
>>>> case java.sql.Types.VARCHAR: return "VARCHAR";
>>>> default: return "";
>>>> }
>>>> }
>>>>}
>>>>
>>>>
>>>>------------------------------------------------------------------------
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 5: Have you checked our extensive FAQ?
>>>>
>>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>>
>>>>GetTypesInfo.java
>>>>
>>>>Content-Type:
>>>>
>>>>text/x-java
>>>>Content-Encoding:
>>>>
>>>>base64
>>>>
>>>>
>>>>------------------------------------------------------------------------
>>>>Part 1.3
>>>>
>>>>Content-Type:
>>>>
>>>>text/plain
>>>>Content-Encoding:
>>>>
>>>>binary
>>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
Why can't the JDBC Driver deal with the delete of the Blob? From the user
point of view the BLOB is an attribute of that row and should be
inserted/deleted with the rest of the row.
The fact that postgres uses another entity to store the blob is an
implementation issue ...
Regards,
Ricardo
On Thursday 02 August 2001 17:37, Barry Lind wrote:
> Ricardo,
>
> There are many other issues with postgres blobs that will not allow you
> to acheive your goal easily. You are going to need different
> implementations per database type to deal with the differences between
> blob implementations across different databases. The one big hurdle you
> will have with postgres blobs is the fact that when you delete the row
> containing the blob, it doesn't delete the blob. You have to issue a
> separate delete blob request. This is very different than what happens
> in Oracle for example. This can be automated by adding triggers to the
> table to do this, but by now you are very far from having a single code
> base (at least the code that creates the tables and triggers) that
> supports all of the different databases.
>
> thanks,
> --Barry
>
> Ricardo Maia wrote:
> > So how whould I map the BLOB java type in the corresponding SQL type?
> >
> > I want to create a table with a BLOB attribute, but I want that my code
> > can run for PostgreSQL, Oracle and other BD that handles BLOBs.
> >
> > So first I had to map the BLOB in the corresponding BD SQL type and then
> > create the table with an attribute of that SQL type.
> >
> > Ricardo Maia
> >
> > On Thursday 02 August 2001 03:16, Barry Lind wrote:
> >>I actually think the response for 'oid' is correct. It reports the oid
> >>as java type integer (which is the real datatype of the value stored).
> >>A column of type oid can be used for may different things. It can be
> >>used for blobs, but not all columns of type oid are used for blobs.
> >>Another use of a column of type oid is to store foreign keys from one
> >>table to another. Since all tables have a builtin column named 'oid' of
> >>type oid, it is very convenient to use this value in foreign keys on
> >>other tables. Assuming that oid = blob would break those applications.
> >>
> >>I hope everyone that uses postgresql and jdbc understands that BLOB
> >>support is one area with many problems, some of which can be fixed in
> >>the JDBC code, but others that will require better support in the
> >>underlying database.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Ricardo Maia wrote:
> >>>For example when I call the method:
> >>>
> >>>DatabaseMetaData.getTypeInfo()
> >>>
> >>>I whould expect to see the SQL Type BLOB mapped as an oid.
> >>>
> >>>see attach
> >>>
> >>>Ricardo Maia
> >>>
> >>>On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
> >>>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
> >>>>>The problem is that, as the PostgreSQL JDBC driver doesn't
> >>>>>follow JDBC Standard I had to write some specific code for
> >>>>>use it with PostgreSQL DB.
> >>>>
> >>>>So what exactly are the deviations from the standard that you
> >>>>encountered?
> >>>>
> >>>>Regards,
> >>>>René Pijlman
> >>>>
> >>>>---------------------------(end of
> >>>> broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> >>>> commands go to majordomo@postgresql.org
> >>>>
> >>>>
> >>>>-----------------------------------------------------------------------
> >>>>-
> >>>>
> >>>>package databasetest;
> >>>>
> >>>>import java.sql.*;
> >>>>
> >>>>public class GetTypesInfo {
> >>>>
> >>>> public static void main(String args[ ]) {
> >>>>
> >>>> String url = "jdbc:postgresql://127.0.0.1/test";
> >>>>
> >>>> Connection con;
> >>>>
> >>>> DatabaseMetaData dbmd;
> >>>>
> >>>> try {
> >>>> Class.forName("org.postgresql.Driver");
> >>>> } catch(java.lang.ClassNotFoundException e) {
> >>>> System.err.print("ClassNotFoundException: ");
> >>>> System.err.println(e.getMessage());
> >>>> }
> >>>>
> >>>> try {
> >>>> con = DriverManager.getConnection(url,"bobby", "tareco");
> >>>>
> >>>> dbmd = con.getMetaData();
> >>>>
> >>>> ResultSet rs = dbmd.getTypeInfo();
> >>>>
> >>>> while (rs.next()) {
> >>>>
> >>>> String typeName = rs.getString("TYPE_NAME");
> >>>>
> >>>> short dataType = rs.getShort("DATA_TYPE");
> >>>>
> >>>> String createParams = rs.getString("CREATE_PARAMS");
> >>>>
> >>>> int nullable = rs.getInt("NULLABLE");
> >>>>
> >>>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
> >>>>
> >>>> if(dataType != java.sql.Types.OTHER)
> >>>> {
> >>>> System.out.println("DBMS type " + typeName + ":");
> >>>> System.out.println(" java.sql.Types: " +
> >>>>typeName(dataType)); System.out.print(" parameters used to create:
> >>>>");
> >>>> System.out.println(createParams);
> >>>> System.out.println(" nullable?: " + nullable);
> >>>> System.out.print(" case sensitive?: ");
> >>>> System.out.println(caseSensitive);
> >>>> System.out.println("");
> >>>> }
> >>>> }
> >>>>
> >>>> con.close();
> >>>> } catch(SQLException ex) {
> >>>> System.err.println("SQLException: " + ex.getMessage());
> >>>> }
> >>>> }
> >>>>
> >>>>
> >>>> public static String typeName(int i)
> >>>> {
> >>>> switch(i){
> >>>> case java.sql.Types.ARRAY: return "ARRAY";
> >>>> case java.sql.Types.BIGINT: return "BIGINT";
> >>>> case java.sql.Types.BINARY: return "BINARY";
> >>>> case java.sql.Types.BIT: return "BIT";
> >>>> case java.sql.Types.BLOB: return "BLOB";
> >>>> case java.sql.Types.CHAR: return "CHAR";
> >>>> case java.sql.Types.CLOB: return "CLOB";
> >>>> case java.sql.Types.DATE: return "DATE";
> >>>> case java.sql.Types.DECIMAL: return "DECIMAL";
> >>>> case java.sql.Types.DISTINCT: return "DISTINCT";
> >>>> case java.sql.Types.DOUBLE: return "DOUBLE";
> >>>> case java.sql.Types.FLOAT: return "FLOAT";
> >>>> case java.sql.Types.INTEGER: return "INTEGER";
> >>>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
> >>>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
> >>>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
> >>>> case java.sql.Types.NULL: return "NULL";
> >>>> case java.sql.Types.NUMERIC: return "NUMERIC";
> >>>> case java.sql.Types.OTHER: return "OTHER";
> >>>> case java.sql.Types.REAL: return "REAL";
> >>>> case java.sql.Types.REF: return "REF";
> >>>> case java.sql.Types.SMALLINT: return "SMALLINT";
> >>>> case java.sql.Types.STRUCT: return "STRUCT";
> >>>> case java.sql.Types.TIME: return "TIME";
> >>>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
> >>>> case java.sql.Types.TINYINT: return "TINYINT";
> >>>> case java.sql.Types.VARBINARY: return "VARBINARY";
> >>>> case java.sql.Types.VARCHAR: return "VARCHAR";
> >>>> default: return "";
> >>>> }
> >>>> }
> >>>>}
> >>>>
> >>>>
> >>>>-----------------------------------------------------------------------
> >>>>-
> >>>>
> >>>>
> >>>>---------------------------(end of
> >>>> broadcast)--------------------------- TIP 5: Have you checked our
> >>>> extensive FAQ?
> >>>>
> >>>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>>>
> >>>>GetTypesInfo.java
> >>>>
> >>>>Content-Type:
> >>>>
> >>>>text/x-java
> >>>>Content-Encoding:
> >>>>
> >>>>base64
> >>>>
> >>>>
> >>>>-----------------------------------------------------------------------
> >>>>- Part 1.3
> >>>>
> >>>>Content-Type:
> >>>>
> >>>>text/plain
> >>>>Content-Encoding:
> >>>>
> >>>>binary
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
----------------------
Critical Software, SA
Urbanização Quinta da Fonte
Lote 15, TZ, r/c H
3030 Coimbra
Telef.: 239 708 520
Telem.: 938 314 605
----------------------
Ricardo,
There are actually a couple of reasons why the jdbc driver can't do this:
1) The client doesn't know that the column being deleted is a blob. All
it can know is that the data type of the column is oid. Oids can be
used for many reasons, one of which is blobs. The code can't assume
that just because a column is of type oid that it represents a blob.
2) The fact that the delete of the blob is separate from the delete of
the row is actually a useful feature. The postgres blob feature
essentially treats the blob as an independent object from the table row
that holds a pointer to it. Thus you can have multiple rows of data in
the same or even different tables point to the same blob. Because of
this feature, you can't assume that when any one row is deleted that the
corresponding blob should be deleted (that decision requires an
understanding of the application data model).
Postgres as of 7.1 has 'toast' which provides a different mechanism for
storing large objects. 'toast' doesn't have the 'multiple rows can
reference the same blob' feature, and therefore 'toast' does delete the
large object when the row is deleted. However 'toast' has other
deficiencies that prevent it from being used in the JDBC driver for
BLOBs. It is my hope that in the future with some additional
functionality on the server that the JDBC driver can have a reasonable
BLOB implementation that uses the new 'toast' functionality, and the
current blob implementation is deprecated.
thanks,
--Barry
Ricardo Maia wrote:
> Why can't the JDBC Driver deal with the delete of the Blob? From the user
> point of view the BLOB is an attribute of that row and should be
> inserted/deleted with the rest of the row.
>
> The fact that postgres uses another entity to store the blob is an
> implementation issue ...
>
> Regards,
>
> Ricardo
>
> On Thursday 02 August 2001 17:37, Barry Lind wrote:
>
>>Ricardo,
>>
>>There are many other issues with postgres blobs that will not allow you
>>to acheive your goal easily. You are going to need different
>>implementations per database type to deal with the differences between
>>blob implementations across different databases. The one big hurdle you
>>will have with postgres blobs is the fact that when you delete the row
>>containing the blob, it doesn't delete the blob. You have to issue a
>>separate delete blob request. This is very different than what happens
>>in Oracle for example. This can be automated by adding triggers to the
>>table to do this, but by now you are very far from having a single code
>>base (at least the code that creates the tables and triggers) that
>>supports all of the different databases.
>>
>>thanks,
>>--Barry
>>
>>Ricardo Maia wrote:
>>
>>>So how whould I map the BLOB java type in the corresponding SQL type?
>>>
>>>I want to create a table with a BLOB attribute, but I want that my code
>>>can run for PostgreSQL, Oracle and other BD that handles BLOBs.
>>>
>>>So first I had to map the BLOB in the corresponding BD SQL type and then
>>>create the table with an attribute of that SQL type.
>>>
>>>Ricardo Maia
>>>
>>>On Thursday 02 August 2001 03:16, Barry Lind wrote:
>>>
>>>>I actually think the response for 'oid' is correct. It reports the oid
>>>>as java type integer (which is the real datatype of the value stored).
>>>>A column of type oid can be used for may different things. It can be
>>>>used for blobs, but not all columns of type oid are used for blobs.
>>>>Another use of a column of type oid is to store foreign keys from one
>>>>table to another. Since all tables have a builtin column named 'oid' of
>>>>type oid, it is very convenient to use this value in foreign keys on
>>>>other tables. Assuming that oid = blob would break those applications.
>>>>
>>>>I hope everyone that uses postgresql and jdbc understands that BLOB
>>>>support is one area with many problems, some of which can be fixed in
>>>>the JDBC code, but others that will require better support in the
>>>>underlying database.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>Ricardo Maia wrote:
>>>>
>>>>>For example when I call the method:
>>>>>
>>>>>DatabaseMetaData.getTypeInfo()
>>>>>
>>>>>I whould expect to see the SQL Type BLOB mapped as an oid.
>>>>>
>>>>>see attach
>>>>>
>>>>>Ricardo Maia
>>>>>
>>>>>On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
>>>>>
>>>>>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
>>>>>>
>>>>>>>The problem is that, as the PostgreSQL JDBC driver doesn't
>>>>>>>follow JDBC Standard I had to write some specific code for
>>>>>>>use it with PostgreSQL DB.
>>>>>>>
>>>>>>So what exactly are the deviations from the standard that you
>>>>>>encountered?
>>>>>>
>>>>>>Regards,
>>>>>>René Pijlman
>>>>>>
>>>>>>---------------------------(end of
>>>>>>broadcast)--------------------------- TIP 1: subscribe and unsubscribe
>>>>>>commands go to majordomo@postgresql.org
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>-
>>>>>>
>>>>>>package databasetest;
>>>>>>
>>>>>>import java.sql.*;
>>>>>>
>>>>>>public class GetTypesInfo {
>>>>>>
>>>>>>public static void main(String args[ ]) {
>>>>>>
>>>>>> String url = "jdbc:postgresql://127.0.0.1/test";
>>>>>>
>>>>>> Connection con;
>>>>>>
>>>>>> DatabaseMetaData dbmd;
>>>>>>
>>>>>> try {
>>>>>> Class.forName("org.postgresql.Driver");
>>>>>> } catch(java.lang.ClassNotFoundException e) {
>>>>>> System.err.print("ClassNotFoundException: ");
>>>>>> System.err.println(e.getMessage());
>>>>>> }
>>>>>>
>>>>>> try {
>>>>>> con = DriverManager.getConnection(url,"bobby", "tareco");
>>>>>>
>>>>>> dbmd = con.getMetaData();
>>>>>>
>>>>>> ResultSet rs = dbmd.getTypeInfo();
>>>>>>
>>>>>> while (rs.next()) {
>>>>>>
>>>>>> String typeName = rs.getString("TYPE_NAME");
>>>>>>
>>>>>> short dataType = rs.getShort("DATA_TYPE");
>>>>>>
>>>>>> String createParams = rs.getString("CREATE_PARAMS");
>>>>>>
>>>>>> int nullable = rs.getInt("NULLABLE");
>>>>>>
>>>>>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
>>>>>>
>>>>>> if(dataType != java.sql.Types.OTHER)
>>>>>> {
>>>>>> System.out.println("DBMS type " + typeName + ":");
>>>>>> System.out.println(" java.sql.Types: " +
>>>>>>typeName(dataType)); System.out.print(" parameters used to create:
>>>>>>");
>>>>>> System.out.println(createParams);
>>>>>> System.out.println(" nullable?: " + nullable);
>>>>>> System.out.print(" case sensitive?: ");
>>>>>> System.out.println(caseSensitive);
>>>>>> System.out.println("");
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> con.close();
>>>>>> } catch(SQLException ex) {
>>>>>> System.err.println("SQLException: " + ex.getMessage());
>>>>>> }
>>>>>>}
>>>>>>
>>>>>>
>>>>>>public static String typeName(int i)
>>>>>>{
>>>>>> switch(i){
>>>>>> case java.sql.Types.ARRAY: return "ARRAY";
>>>>>> case java.sql.Types.BIGINT: return "BIGINT";
>>>>>> case java.sql.Types.BINARY: return "BINARY";
>>>>>> case java.sql.Types.BIT: return "BIT";
>>>>>> case java.sql.Types.BLOB: return "BLOB";
>>>>>> case java.sql.Types.CHAR: return "CHAR";
>>>>>> case java.sql.Types.CLOB: return "CLOB";
>>>>>> case java.sql.Types.DATE: return "DATE";
>>>>>> case java.sql.Types.DECIMAL: return "DECIMAL";
>>>>>> case java.sql.Types.DISTINCT: return "DISTINCT";
>>>>>> case java.sql.Types.DOUBLE: return "DOUBLE";
>>>>>> case java.sql.Types.FLOAT: return "FLOAT";
>>>>>> case java.sql.Types.INTEGER: return "INTEGER";
>>>>>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
>>>>>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
>>>>>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
>>>>>> case java.sql.Types.NULL: return "NULL";
>>>>>> case java.sql.Types.NUMERIC: return "NUMERIC";
>>>>>> case java.sql.Types.OTHER: return "OTHER";
>>>>>> case java.sql.Types.REAL: return "REAL";
>>>>>> case java.sql.Types.REF: return "REF";
>>>>>> case java.sql.Types.SMALLINT: return "SMALLINT";
>>>>>> case java.sql.Types.STRUCT: return "STRUCT";
>>>>>> case java.sql.Types.TIME: return "TIME";
>>>>>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
>>>>>> case java.sql.Types.TINYINT: return "TINYINT";
>>>>>> case java.sql.Types.VARBINARY: return "VARBINARY";
>>>>>> case java.sql.Types.VARCHAR: return "VARCHAR";
>>>>>> default: return "";
>>>>>> }
>>>>>>}
>>>>>>}
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>-
>>>>>>
>>>>>>
>>>>>>---------------------------(end of
>>>>>>broadcast)--------------------------- TIP 5: Have you checked our
>>>>>>extensive FAQ?
>>>>>>
>>>>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>>>>
>>>>>>GetTypesInfo.java
>>>>>>
>>>>>>Content-Type:
>>>>>>
>>>>>>text/x-java
>>>>>>Content-Encoding:
>>>>>>
>>>>>>base64
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>- Part 1.3
>>>>>>
>>>>>>Content-Type:
>>>>>>
>>>>>>text/plain
>>>>>>Content-Encoding:
>>>>>>
>>>>>>binary
>>>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>
>
Bruce Momjian wrote: >Peter has replied to a few people stating he is very >busy and wants someone to take over the jdbc.postgresql.org website. >Marc, Vince, and others are working on it now. Do you need help? Regards, René Pijlman
On Wed, 1 Aug 2001, Barry Lind wrote: > Anders, > > What aspects of BLOB support do you consider broken? Are these aspects > that are broken in the JDBC layer or are 'broken' at the server layer? Now I've looked at the code and located the problem: The method setBinaryStream(...) in PreparedStatement always assumes that it's a BLOB that we want to write, but it should really be able to write any kind of field. It should for instance be possible to write a VARCHAR from an InputStream, but currently you will end up with an integer (the OID) in the field instead of the data. I was first surprised to find that getBinaryStream(...) in ResultSet *does* support both BLOBs and ordinary values, but then realized that it can do this because it knows the type of the field. In PreparedStatement nothing is known about the fields. I'm not sure where this problem belongs. It is not impossible for the JDBC driver to find out about the field types, but it may be slow to do so. /Anders _____________________________________________________________________ A n d e r s B e n g t s s o n ndrsbngtssn@yahoo.se Stockholm, Sweden _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Thu, 2 Aug 2001, Barry Lind wrote: > There are actually a couple of reasons why the jdbc driver can't do this: > > 1) The client doesn't know that the column being deleted is a blob. All > it can know is that the data type of the column is oid. Oids can be > used for many reasons, one of which is blobs. The code can't assume > that just because a column is of type oid that it represents a blob. I'm thinking that it should be possible to create some kind of compatability mode for the driver. If you knew for sure that you we're only using OIDs for BLOBs, then that assumption would be safe (?). Would something like that be possible to create, or am I missing something here? Of course, this could add too much complexity to the driver. /Anders _____________________________________________________________________ A n d e r s B e n g t s s o n ndrsbngtssn@yahoo.se Stockholm, Sweden _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
we are currently evaluating several solutions, and, once we've fully figured out what we are going to do, will announce such ... at the time, I can imagine that help will be much appreciated :) On Thu, 2 Aug 2001, Rene Pijlman wrote: > Bruce Momjian wrote: > >Peter has replied to a few people stating he is very > >busy and wants someone to take over the jdbc.postgresql.org website. > >Marc, Vince, and others are working on it now. > > Do you need help? > > Regards, > Ren� Pijlman >
Would someone summarize what items need to be added to the TODO list.
> Ricardo,
>
> There are many other issues with postgres blobs that will not allow you
> to acheive your goal easily. You are going to need different
> implementations per database type to deal with the differences between
> blob implementations across different databases. The one big hurdle you
> will have with postgres blobs is the fact that when you delete the row
> containing the blob, it doesn't delete the blob. You have to issue a
> separate delete blob request. This is very different than what happens
> in Oracle for example. This can be automated by adding triggers to the
> table to do this, but by now you are very far from having a single code
> base (at least the code that creates the tables and triggers) that
> supports all of the different databases.
>
> thanks,
> --Barry
>
> Ricardo Maia wrote:
>
> >
> > So how whould I map the BLOB java type in the corresponding SQL type?
> >
> > I want to create a table with a BLOB attribute, but I want that my code can
> > run for PostgreSQL, Oracle and other BD that handles BLOBs.
> >
> > So first I had to map the BLOB in the corresponding BD SQL type and then
> > create the table with an attribute of that SQL type.
> >
> > Ricardo Maia
> >
> > On Thursday 02 August 2001 03:16, Barry Lind wrote:
> >
> >>I actually think the response for 'oid' is correct. It reports the oid
> >>as java type integer (which is the real datatype of the value stored).
> >>A column of type oid can be used for may different things. It can be
> >>used for blobs, but not all columns of type oid are used for blobs.
> >>Another use of a column of type oid is to store foreign keys from one
> >>table to another. Since all tables have a builtin column named 'oid' of
> >>type oid, it is very convenient to use this value in foreign keys on
> >>other tables. Assuming that oid = blob would break those applications.
> >>
> >>I hope everyone that uses postgresql and jdbc understands that BLOB
> >>support is one area with many problems, some of which can be fixed in
> >>the JDBC code, but others that will require better support in the
> >>underlying database.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Ricardo Maia wrote:
> >>
> >>>For example when I call the method:
> >>>
> >>>DatabaseMetaData.getTypeInfo()
> >>>
> >>>I whould expect to see the SQL Type BLOB mapped as an oid.
> >>>
> >>>see attach
> >>>
> >>>Ricardo Maia
> >>>
> >>>On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
> >>>
> >>>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
> >>>>
> >>>>>The problem is that, as the PostgreSQL JDBC driver doesn't
> >>>>>follow JDBC Standard I had to write some specific code for
> >>>>>use it with PostgreSQL DB.
> >>>>>
> >>>>So what exactly are the deviations from the standard that you
> >>>>encountered?
> >>>>
> >>>>Regards,
> >>>>Ren? Pijlman
> >>>>
> >>>>---------------------------(end of broadcast)---------------------------
> >>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >>>>
> >>>>
> >>>>------------------------------------------------------------------------
> >>>>
> >>>>package databasetest;
> >>>>
> >>>>import java.sql.*;
> >>>>
> >>>>public class GetTypesInfo {
> >>>>
> >>>> public static void main(String args[ ]) {
> >>>>
> >>>> String url = "jdbc:postgresql://127.0.0.1/test";
> >>>>
> >>>> Connection con;
> >>>>
> >>>> DatabaseMetaData dbmd;
> >>>>
> >>>> try {
> >>>> Class.forName("org.postgresql.Driver");
> >>>> } catch(java.lang.ClassNotFoundException e) {
> >>>> System.err.print("ClassNotFoundException: ");
> >>>> System.err.println(e.getMessage());
> >>>> }
> >>>>
> >>>> try {
> >>>> con = DriverManager.getConnection(url,"bobby", "tareco");
> >>>>
> >>>> dbmd = con.getMetaData();
> >>>>
> >>>> ResultSet rs = dbmd.getTypeInfo();
> >>>>
> >>>> while (rs.next()) {
> >>>>
> >>>> String typeName = rs.getString("TYPE_NAME");
> >>>>
> >>>> short dataType = rs.getShort("DATA_TYPE");
> >>>>
> >>>> String createParams = rs.getString("CREATE_PARAMS");
> >>>>
> >>>> int nullable = rs.getInt("NULLABLE");
> >>>>
> >>>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
> >>>>
> >>>> if(dataType != java.sql.Types.OTHER)
> >>>> {
> >>>> System.out.println("DBMS type " + typeName + ":");
> >>>> System.out.println(" java.sql.Types: " +
> >>>>typeName(dataType)); System.out.print(" parameters used to create:
> >>>>");
> >>>> System.out.println(createParams);
> >>>> System.out.println(" nullable?: " + nullable);
> >>>> System.out.print(" case sensitive?: ");
> >>>> System.out.println(caseSensitive);
> >>>> System.out.println("");
> >>>> }
> >>>> }
> >>>>
> >>>> con.close();
> >>>> } catch(SQLException ex) {
> >>>> System.err.println("SQLException: " + ex.getMessage());
> >>>> }
> >>>> }
> >>>>
> >>>>
> >>>> public static String typeName(int i)
> >>>> {
> >>>> switch(i){
> >>>> case java.sql.Types.ARRAY: return "ARRAY";
> >>>> case java.sql.Types.BIGINT: return "BIGINT";
> >>>> case java.sql.Types.BINARY: return "BINARY";
> >>>> case java.sql.Types.BIT: return "BIT";
> >>>> case java.sql.Types.BLOB: return "BLOB";
> >>>> case java.sql.Types.CHAR: return "CHAR";
> >>>> case java.sql.Types.CLOB: return "CLOB";
> >>>> case java.sql.Types.DATE: return "DATE";
> >>>> case java.sql.Types.DECIMAL: return "DECIMAL";
> >>>> case java.sql.Types.DISTINCT: return "DISTINCT";
> >>>> case java.sql.Types.DOUBLE: return "DOUBLE";
> >>>> case java.sql.Types.FLOAT: return "FLOAT";
> >>>> case java.sql.Types.INTEGER: return "INTEGER";
> >>>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
> >>>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
> >>>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
> >>>> case java.sql.Types.NULL: return "NULL";
> >>>> case java.sql.Types.NUMERIC: return "NUMERIC";
> >>>> case java.sql.Types.OTHER: return "OTHER";
> >>>> case java.sql.Types.REAL: return "REAL";
> >>>> case java.sql.Types.REF: return "REF";
> >>>> case java.sql.Types.SMALLINT: return "SMALLINT";
> >>>> case java.sql.Types.STRUCT: return "STRUCT";
> >>>> case java.sql.Types.TIME: return "TIME";
> >>>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
> >>>> case java.sql.Types.TINYINT: return "TINYINT";
> >>>> case java.sql.Types.VARBINARY: return "VARBINARY";
> >>>> case java.sql.Types.VARCHAR: return "VARCHAR";
> >>>> default: return "";
> >>>> }
> >>>> }
> >>>>}
> >>>>
> >>>>
> >>>>------------------------------------------------------------------------
> >>>>
> >>>>
> >>>>---------------------------(end of broadcast)---------------------------
> >>>>TIP 5: Have you checked our extensive FAQ?
> >>>>
> >>>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>>>
> >>>>GetTypesInfo.java
> >>>>
> >>>>Content-Type:
> >>>>
> >>>>text/x-java
> >>>>Content-Encoding:
> >>>>
> >>>>base64
> >>>>
> >>>>
> >>>>------------------------------------------------------------------------
> >>>>Part 1.3
> >>>>
> >>>>Content-Type:
> >>>>
> >>>>text/plain
> >>>>Content-Encoding:
> >>>>
> >>>>binary
> >>>>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
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
This is what I think needs to be done wrt large objects and binary data support (and hopefully what I plan to do sometime before 7.2 beta, but if anyone else feels up to it, feel free to do any of these things yourself): Add support for the postgresql binary datatype 'bytea'. This means adding the logic to encode/decode binary data into the ascii escape sequences used by postgresql. This also means that the getBytes()/setBytes() methods will be changed to interact with the bytea datatype instead of the current mapping to large objects. This is a non backwardly compatable change in functionality that makes the driver more compliant with the spec. Second I plan to change the getBinaryStream()/setBinaryStream() methods to likewise work on the bytea datatype instead of large objects. Given that toast allows bytea values to be upto 1G in size a stream interface makes sense. This change also breaks backward compatibilty, but is more spec compliant. The spec implies that these methods are for accessing regular binary data (i.e. bytea), and that the getBlob().getBinaryStream() is for binary large object access. Third, I plan to change the getCharacterStream()/setCharacterStream() methods to work against text datatypes (text, char, varchar) instead of large objects. Same reason and same consequences as for the binary stream methods. That will leave getBlob()/setBlob() and getClob()/setClob() as the supported way of accessing large objects (along with the LargeObject class itself). Which my reading of the spec says is correct. Now in the long run, I would even like to change getBlob()/setBlob()/getClob()/setClob() methods to no longer support the old large object functionality of postgresql but to move these to support a 'toast' version of large objects (once the corresponding access methods to toasted columns exist so that toasted columns can really be treated as large objects). This would solve the problem with deletes not deleting the large objects. At that time the only way to access the old large object functionality would be through the functionality provided by the LargeObject class. As you can probably guess I don't like the current implementation of large objects in postgresql (and I haven't even gotten into the security issues they have). I believe that 'toast' will provide the functionality of large objects in the future in a way that is compatable with other databases and the JDBC Blob/Clob interface. Until the time that toast is ready, I believe we need to make the above changes and document very clearly the issues with the current large object functionality. thanks, --Barry Gunnar Rønning wrote: > [Answering as Anders Norwegian brother :-] > > * Barry Lind <barry@xythos.com> wrote: > | > | Anders, > | > | What aspects of BLOB support do you consider broken? Are these > | aspects that are broken in the JDBC layer or are 'broken' at the > | server layer? > > We should have support for the bytea datatype, so applications are not > required to wrap blob operations into a transaction. This has been > a showstopper for using PostgreSQL with the Turbine framework at Apache > for a long time. If we get that to work with PostgreSQL we will attract > more users and be a step closer to world domination ;-) > > >
Barry Lind <barry@xythos.com> writes:
> This is what I think needs to be done wrt large objects and binary data
> support ...
> [ much snipped ]
> As you can probably guess I don't like the current implementation of
> large objects in postgresql
Yup, I got that ;-).
While these seem like good changes in the long run, I'm concerned about
breaking existing client apps wholesale. Is it feasible to have a
backwards-compatibility mode? I wouldn't even insist that it be the
default behavior --- but adding a one-line "set backwards-compatible
mode" kind of call seems better than major rewrites, for apps that
depend on the old behavior.
regards, tom lane
If people feel that backwards compatibiliy is important I would suggest it be done in the following way: A new connection parameter named 'compatible' be defined whose default value is 7.2 (i.e new functionality). But you could set compatible=7.1 to revert back to the old functionality. (This is how Oracle deals with similar issues in its code base). This parameter could then be set either in the JDBC URL (i.e. jdbc:postgresql://localhost:5432:template1?compatible=7.1) or passed explicily in the connect() method. thanks, --Barry Tom Lane wrote: > Barry Lind <barry@xythos.com> writes: > >>This is what I think needs to be done wrt large objects and binary data >>support ... >>[ much snipped ] >>As you can probably guess I don't like the current implementation of >>large objects in postgresql >> > > Yup, I got that ;-). > > While these seem like good changes in the long run, I'm concerned about > breaking existing client apps wholesale. Is it feasible to have a > backwards-compatibility mode? I wouldn't even insist that it be the > default behavior --- but adding a one-line "set backwards-compatible > mode" kind of call seems better than major rewrites, for apps that > depend on the old behavior. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
GUC is how this type of stuff is controlled on the server, but I don't know of any examples where it controlls client only functionality. Why would you want parameters on the server that the server doesn't use? thanks, --Barry Bruce Momjian wrote: >>If people feel that backwards compatibiliy is important I would suggest >>it be done in the following way: >> >>A new connection parameter named 'compatible' be defined whose default >>value is 7.2 (i.e new functionality). But you could set compatible=7.1 >>to revert back to the old functionality. (This is how Oracle deals with >>similar issues in its code base). This parameter could then be set >>either in the JDBC URL (i.e. >>jdbc:postgresql://localhost:5432:template1?compatible=7.1) or passed >>explicily in the connect() method. >> > > GUC seems to be the way to control these things. It can be set in > postgresql.conf and via a SET command. > >
Index: Connection.java
===================================================================
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Co
nnection.java,v
retrieving revision 1.21
diff -f -r1.21 Connection.java
c1039 1040
info.put("user", PG_USER);
info.put("password", PG_PASSWORD);
> GUC is how this type of stuff is controlled on the server, but I don't > know of any examples where it controlls client only functionality. Why > would you want parameters on the server that the server doesn't use? Oh, I didn't realize this was client side too. -- 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
> If people feel that backwards compatibiliy is important I would suggest > it be done in the following way: > > A new connection parameter named 'compatible' be defined whose default > value is 7.2 (i.e new functionality). But you could set compatible=7.1 > to revert back to the old functionality. (This is how Oracle deals with > similar issues in its code base). This parameter could then be set > either in the JDBC URL (i.e. > jdbc:postgresql://localhost:5432:template1?compatible=7.1) or passed > explicily in the connect() method. GUC seems to be the way to control these things. It can be set in postgresql.conf and via a SET command. -- 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
On Wed, 1 Aug 2001, Bruce Momjian wrote: > > This appeared on the JDBC list. Do we need to address this? Where's Peter Mount? Isn't he the maintainer? Vince. > > > I actually consider the biggest problem the fact the the 'official' > > postgres jdbc website is very much out of date > > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > > I feel that either someone needs to maintain this page; or someone needs > > to create a new website and get the jdbc.postgresql.org DNS entry to > > point to the new site, or the page should just be decommisioned. At > > this point I think it is doing more harm than good. > > -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Thu, 2 Aug 2001, Rene Pijlman wrote: > Bruce Momjian wrote: > >Peter has replied to a few people stating he is very > >busy and wants someone to take over the jdbc.postgresql.org website. > >Marc, Vince, and others are working on it now. > > Do you need help? We will very soon. I'll hang onto your address and get back to you. If for some reason you don't hear from me in the next couple of weeks, drop me a note in case I forgot about you. You can either mail to this address or to webmaster@postgresql.org. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Barry, On Thu, 02 Aug 2001 22:59:11 -0700, you wrote: >Now in the long run, I would even like to change >getBlob()/setBlob()/getClob()/setClob() methods to no longer support the >old large object functionality of postgresql but to move these to >support a 'toast' version of large objects (once the corresponding >access methods to toasted columns exist so that toasted columns can >really be treated as large objects). Could you elaborate on that please? What new access methods are needed on toasted columns? Does this require backend support? FE/BE protocol changes? Would it be conceivable to implement the Lob JDBC interface on the current implementation of toasted columns (in both the backend and the protocol), e.g. using a OID/column name pair as the "logical pointer" needed by JDBC? Also, I'm wondering if it would be wise to re-architect Lob support in the JDBC interface only? Someone creating a Lob through JDBC may have a hard time accessing his data using another interface that not yet supports efficient access methods on huge toasted data. I definitely agree Blob->toast is the most desirable mapping from a JDBC point of view, but I'm not sure if this should be changed only in JDBC. Regards, René Pijlman
Vince, has this been addressed? > On Wed, 1 Aug 2001, Bruce Momjian wrote: > > > > > This appeared on the JDBC list. Do we need to address this? > > Where's Peter Mount? Isn't he the maintainer? > > Vince. > > > > > > I actually consider the biggest problem the fact the the 'official' > > > postgres jdbc website is very much out of date > > > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > > > I feel that either someone needs to maintain this page; or someone needs > > > to create a new website and get the jdbc.postgresql.org DNS entry to > > > point to the new site, or the page should just be decommisioned. At > > > this point I think it is doing more harm than good. > > > > > > -- > ========================================================================== > Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directory http://www.camping-usa.com > Online Giftshop Superstore http://www.cloudninegifts.com > ========================================================================== > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- 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
Added to TODO: * -Make binary interface for TOAST columns (base64) * Make file in/out interface for TOAST columns, similar to large object interface (force out-of-line storage and no compression) > This is what I think needs to be done wrt large objects and binary data > support (and hopefully what I plan to do sometime before 7.2 beta, but > if anyone else feels up to it, feel free to do any of these things > yourself): > > Add support for the postgresql binary datatype 'bytea'. This means > adding the logic to encode/decode binary data into the ascii escape > sequences used by postgresql. This also means that the > getBytes()/setBytes() methods will be changed to interact with the bytea > datatype instead of the current mapping to large objects. This is a non > backwardly compatable change in functionality that makes the driver more > compliant with the spec. > > Second I plan to change the getBinaryStream()/setBinaryStream() methods > to likewise work on the bytea datatype instead of large objects. Given > that toast allows bytea values to be upto 1G in size a stream interface > makes sense. This change also breaks backward compatibilty, but is more > spec compliant. The spec implies that these methods are for accessing > regular binary data (i.e. bytea), and that the > getBlob().getBinaryStream() is for binary large object access. > > Third, I plan to change the getCharacterStream()/setCharacterStream() > methods to work against text datatypes (text, char, varchar) instead of > large objects. Same reason and same consequences as for the binary > stream methods. > > That will leave getBlob()/setBlob() and getClob()/setClob() as the > supported way of accessing large objects (along with the LargeObject > class itself). Which my reading of the spec says is correct. > > Now in the long run, I would even like to change > getBlob()/setBlob()/getClob()/setClob() methods to no longer support the > old large object functionality of postgresql but to move these to > support a 'toast' version of large objects (once the corresponding > access methods to toasted columns exist so that toasted columns can > really be treated as large objects). This would solve the problem with > deletes not deleting the large objects. At that time the only way to > access the old large object functionality would be through the > functionality provided by the LargeObject class. > > As you can probably guess I don't like the current implementation of > large objects in postgresql (and I haven't even gotten into the security > issues they have). I believe that 'toast' will provide the > functionality of large objects in the future in a way that is compatable > with other databases and the JDBC Blob/Clob interface. Until the time > that toast is ready, I believe we need to make the above changes and > document very clearly the issues with the current large object > functionality. > > thanks, > --Barry > > > > Gunnar R?nning wrote: > > > [Answering as Anders Norwegian brother :-] > > > > * Barry Lind <barry@xythos.com> wrote: > > | > > | Anders, > > | > > | What aspects of BLOB support do you consider broken? Are these > > | aspects that are broken in the JDBC layer or are 'broken' at the > > | server layer? > > > > We should have support for the bytea datatype, so applications are not > > required to wrap blob operations into a transaction. This has been > > a showstopper for using PostgreSQL with the Turbine framework at Apache > > for a long time. If we get that to work with PostgreSQL we will attract > > more users and be a step closer to world domination ;-) > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- 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
On Thu, 6 Sep 2001, Bruce Momjian wrote: > > Vince, has this been addressed? Yes, Barry Lind is handling the website. I expect a few days to a week for him to be ready to go live. Sorry Bruce, I meant to CC you on it. Vince. > > > > On Wed, 1 Aug 2001, Bruce Momjian wrote: > > > > > > > > This appeared on the JDBC list. Do we need to address this? > > > > Where's Peter Mount? Isn't he the maintainer? > > > > Vince. > > > > > > > > > I actually consider the biggest problem the fact the the 'official' > > > > postgres jdbc website is very much out of date > > > > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > > > > I feel that either someone needs to maintain this page; or someone needs > > > > to create a new website and get the jdbc.postgresql.org DNS entry to > > > > point to the new site, or the page should just be decommisioned. At > > > > this point I think it is doing more harm than good. > > > > > > > > > > -- > > ========================================================================== > > Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net > > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > > Online Campground Directory http://www.camping-usa.com > > Online Giftshop Superstore http://www.cloudninegifts.com > > ========================================================================== > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Barry just got some info back to me to create his account, so he should also be online later tonight ... On Thu, 6 Sep 2001, Vince Vielhaber wrote: > On Thu, 6 Sep 2001, Bruce Momjian wrote: > > > > > Vince, has this been addressed? > > Yes, Barry Lind is handling the website. I expect a few days to > a week for him to be ready to go live. Sorry Bruce, I meant to > CC you on it. > > Vince. > > > > > > > > On Wed, 1 Aug 2001, Bruce Momjian wrote: > > > > > > > > > > > This appeared on the JDBC list. Do we need to address this? > > > > > > Where's Peter Mount? Isn't he the maintainer? > > > > > > Vince. > > > > > > > > > > > > I actually consider the biggest problem the fact the the 'official' > > > > > postgres jdbc website is very much out of date > > > > > (http://jdbc.postgresql.org). (it doesn't even have the 7.1 drivers). > > > > > I feel that either someone needs to maintain this page; or someone needs > > > > > to create a new website and get the jdbc.postgresql.org DNS entry to > > > > > point to the new site, or the page should just be decommisioned. At > > > > > this point I think it is doing more harm than good. > > > > > > > > > > > > > > -- > > > ========================================================================== > > > Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net > > > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > > > Online Campground Directory http://www.camping-usa.com > > > Online Giftshop Superstore http://www.cloudninegifts.com > > > ========================================================================== > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://www.postgresql.org/search.mpl > > > > > > > > > -- > ========================================================================== > Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directory http://www.camping-usa.com > Online Giftshop Superstore http://www.cloudninegifts.com > ========================================================================== > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >