Thread: getExportedKeys/getImportedKeys
I'm having real problems with getting the foreign keys from my db. In fact I'm not getting it to work at all. I'm running postgresql 7.2 and I noticed that the code for getting the keys is different for 7.2 and 7.3 in AbstractJdbc1DatabaseMetaData.java. I've tested all different parameters I can think of but still not getting any results. I've made sure that the table parameter is correct and passed null for catalog and schema. Is the method not working or what can I be doing wrong?
Desperate... Thanks in advance for any help! Btw I'm making a database admin tool for multiple databases so I really need this to work or my diagrams won't look so good.
Regards,
Patrik Lundgren
Hi.
PostGreSql is CASE SESITIVE - for creating foreign keys etc. This seems to hold true for meta data retrieval etc (I admit to having somne trouble with this).
So, if you are passing the table pattern through - ensure that it is LOWER CASE ( *I think* ).
One other thing that I found funny with the JDBC implementation is this.
Data Type TEXT - when you get the meta data back for a column of type TEXT, the data type int (as specified in java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - this si incorrect according to java.sql.Types - it should be returning -1, as 12 is the VARCHAR java.sql.Types datatype id.
I am using version 7.3, not 7.2 so not sure if this was a bug back and has been fixed...
Best regards,
Carl
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Patrik Lundgren
Sent: 12 March 2003 01:41 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] getExportedKeys/getImportedKeysI'm having real problems with getting the foreign keys from my db. In fact I'm not getting it to work at all. I'm running postgresql 7.2 and I noticed that the code for getting the keys is different for 7.2 and 7.3 in AbstractJdbc1DatabaseMetaData.java. I've tested all different parameters I can think of but still not getting any results. I've made sure that the table parameter is correct and passed null for catalog and schema. Is the method not working or what can I be doing wrong?Desperate... Thanks in advance for any help! Btw I'm making a database admin tool for multiple databases so I really need this to work or my diagrams won't look so good.Regards,Patrik Lundgren
Well, that is one ting we can rule out. I've made sure that the table name is correct. I've tested lower case, upper case and everything in between. Does anyone have any experience with using this function with 7.2? Again my problem is that getExportedKeys/getImportedKeys returns an empty ResultSet.
Regards,
Patrik Lundgren
-----Ursprungligt meddelande-----Från: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] För Carl Olivier
Skickat: Wednesday, March 12, 2003 1:13 PM
Till: Patrik Lundgren; pgsql-jdbc@postgresql.org
Ämne: Re: [JDBC] getExportedKeys/getImportedKeys
Hi.PostGreSql is CASE SESITIVE - for creating foreign keys etc. This seems to hold true for meta data retrieval etc (I admit to having somne trouble with this).So, if you are passing the table pattern through - ensure that it is LOWER CASE ( *I think* ).One other thing that I found funny with the JDBC implementation is this.Data Type TEXT - when you get the meta data back for a column of type TEXT, the data type int (as specified in java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - this si incorrect according to java.sql.Types - it should be returning -1, as 12 is the VARCHAR java.sql.Types datatype id.I am using version 7.3, not 7.2 so not sure if this was a bug back and has been fixed...Best regards,Carl-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Patrik Lundgren
Sent: 12 March 2003 01:41 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] getExportedKeys/getImportedKeysI'm having real problems with getting the foreign keys from my db. In fact I'm not getting it to work at all. I'm running postgresql 7.2 and I noticed that the code for getting the keys is different for 7.2 and 7.3 in AbstractJdbc1DatabaseMetaData.java. I've tested all different parameters I can think of but still not getting any results. I've made sure that the table parameter is correct and passed null for catalog and schema. Is the method not working or what can I be doing wrong?Desperate... Thanks in advance for any help! Btw I'm making a database admin tool for multiple databases so I really need this to work or my diagrams won't look so good.Regards,Patrik Lundgren
Patrik Can you send me a small sql file with some exported keys to test this. I have a 7.2 version somewhere. Dave On Wed, 2003-03-12 at 09:22, Patrik Lundgren wrote: > Well, that is one ting we can rule out. I've made sure that the table > name is correct. I've tested lower case, upper case and everything in > between. Does anyone have any experience with using this function with > 7.2? Again my problem is that getExportedKeys/getImportedKeys returns > an empty ResultSet. > > Regards, > Patrik Lundgren > -----Ursprungligt meddelande----- > Från: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] För Carl Olivier > Skickat: Wednesday, March 12, 2003 1:13 PM > Till: Patrik Lundgren; pgsql-jdbc@postgresql.org > Ämne: Re: [JDBC] getExportedKeys/getImportedKeys > > > Hi. > PostGreSql is CASE SESITIVE - for creating foreign keys etc. > This seems to hold true for meta data retrieval etc (I admit > to having somne trouble with this). > > So, if you are passing the table pattern through - ensure that > it is LOWER CASE ( *I think* ). > > One other thing that I found funny with the JDBC > implementation is this. > > Data Type TEXT - when you get the meta data back for a column > of type TEXT, the data type int (as specified in > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 > - this si incorrect according to java.sql.Types - it should be > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > I am using version 7.3, not 7.2 so not sure if this was a bug > back and has been fixed... > > Best regards, > > Carl > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of > Patrik Lundgren > Sent: 12 March 2003 01:41 PM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] getExportedKeys/getImportedKeys > > > I'm having real problems with getting the foreign keys > from my db. In fact I'm not getting it to work at all. > I'm running postgresql 7.2 and I noticed that the code > for getting the keys is different for 7.2 and 7.3 in > AbstractJdbc1DatabaseMetaData.java. I've tested all > different parameters I can think of but still not > getting any results. I've made sure that the table > parameter is correct and passed null for catalog and > schema. Is the method not working or what can I be > doing wrong? > > Desperate... Thanks in advance for any help! Btw I'm > making a database admin tool for multiple databases so > I really need this to work or my diagrams won't look > so good. > > Regards, > Patrik Lundgren -- Dave Cramer <Dave@micro-automation.net>
Patrik, If you provide a test case we can look into the problem. thanks, --Barry Patrik Lundgren wrote: > I'm having real problems with getting the foreign keys from my db. In > fact I'm not getting it to work at all. I'm running postgresql 7.2 and I > noticed that the code for getting the keys is different for 7.2 and 7.3 > in AbstractJdbc1DatabaseMetaData.java. I've tested all different > parameters I can think of but still not getting any results. I've made > sure that the table parameter is correct and passed null for catalog and > schema. Is the method not working or what can I be doing wrong? > > Desperate... Thanks in advance for any help! Btw I'm making a database > admin tool for multiple databases so I really need this to work or my > diagrams won't look so good. > > Regards, > Patrik Lundgren
Patrik, Another thing you mention you are using 7.2. Are you using 7.2 for both the driver and the server? If so have you tried using the 7.3 driver against the 7.2 server? This is a supported setup and the 7.3 driver has a significant number of fixes in this area. thanks, --Barry Patrik Lundgren wrote: > Well, that is one ting we can rule out. I've made sure that the table > name is correct. I've tested lower case, upper case and everything in > between. Does anyone have any experience with using this function with > 7.2? Again my problem is that getExportedKeys/getImportedKeys returns an > empty ResultSet. > > Regards, > Patrik Lundgren > -----Ursprungligt meddelande----- > *Från:* pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > *Skickat:* Wednesday, March 12, 2003 1:13 PM > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > Hi. > PostGreSql is CASE SESITIVE - for creating foreign keys etc. This > seems to hold true for meta data retrieval etc (I admit to having > somne trouble with this). > > So, if you are passing the table pattern through - ensure that it is > LOWER CASE ( *I think* ). > > One other thing that I found funny with the JDBC implementation is this. > > Data Type TEXT - when you get the meta data back for a column of > type TEXT, the data type int (as specified in > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - > this si incorrect according to java.sql.Types - it should be > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > I am using version 7.3, not 7.2 so not sure if this was a bug back > and has been fixed... > > Best regards, > > Carl > > -----Original Message----- > *From:* pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of *Patrik > Lundgren > *Sent:* 12 March 2003 01:41 PM > *To:* pgsql-jdbc@postgresql.org > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > I'm having real problems with getting the foreign keys from my > db. In fact I'm not getting it to work at all. I'm running > postgresql 7.2 and I noticed that the code for getting the keys > is different for 7.2 and 7.3 in > AbstractJdbc1DatabaseMetaData.java. I've tested all different > parameters I can think of but still not getting any results. > I've made sure that the table parameter is correct and passed > null for catalog and schema. Is the method not working or what > can I be doing wrong? > > Desperate... Thanks in advance for any help! Btw I'm making a > database admin tool for multiple databases so I really need this > to work or my diagrams won't look so good. > > Regards, > Patrik Lundgren
I'm using the latest stable driver from jdbc.postgresql.org. By popular demand I've attached an sql-file, but since I've had this problem with all databases I've tested, there's really no need. I've tested some java code from other people and I still get the same problem. I'm attaching some java code I found in the archives. The url needs to be changed before compilation. The problem, so everyone understans correctly, is that I get an empty ResultSet when asking for foreign keys. I've also tested the jdbxConn driver and it returns the keys perfectly. The problem with that driver is that it doesn't return the correct field lengths. Regards, Patrik -----Ursprungligt meddelande----- Fran: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] For Barry Lind Skickat: Wednesday, March 12, 2003 4:12 PM Till: Patrik Lundgren Kopia: pgsql-jdbc@postgresql.org Amne: Re: [JDBC] getExportedKeys/getImportedKeys Patrik, If you provide a test case we can look into the problem. thanks, --Barry Patrik Lundgren wrote: > I'm having real problems with getting the foreign keys from my db. In > fact I'm not getting it to work at all. I'm running postgresql 7.2 and I > noticed that the code for getting the keys is different for 7.2 and 7.3 > in AbstractJdbc1DatabaseMetaData.java. I've tested all different > parameters I can think of but still not getting any results. I've made > sure that the table parameter is correct and passed null for catalog and > schema. Is the method not working or what can I be doing wrong? > > Desperate... Thanks in advance for any help! Btw I'm making a database > admin tool for multiple databases so I really need this to work or my > diagrams won't look so good. > > Regards, > Patrik Lundgren ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Attachment
Patrik, Can you try Barry's suggestion before I dig into it. Dave On Wed, 2003-03-12 at 10:16, Barry Lind wrote: > Patrik, > > Another thing you mention you are using 7.2. Are you using 7.2 for both > the driver and the server? If so have you tried using the 7.3 driver > against the 7.2 server? This is a supported setup and the 7.3 driver > has a significant number of fixes in this area. > > thanks, > --Barry > > > Patrik Lundgren wrote: > > Well, that is one ting we can rule out. I've made sure that the table > > name is correct. I've tested lower case, upper case and everything in > > between. Does anyone have any experience with using this function with > > 7.2? Again my problem is that getExportedKeys/getImportedKeys returns an > > empty ResultSet. > > > > Regards, > > Patrik Lundgren > > -----Ursprungligt meddelande----- > > *Från:* pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > > *Skickat:* Wednesday, March 12, 2003 1:13 PM > > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > > > Hi. > > PostGreSql is CASE SESITIVE - for creating foreign keys etc. This > > seems to hold true for meta data retrieval etc (I admit to having > > somne trouble with this). > > > > So, if you are passing the table pattern through - ensure that it is > > LOWER CASE ( *I think* ). > > > > One other thing that I found funny with the JDBC implementation is this. > > > > Data Type TEXT - when you get the meta data back for a column of > > type TEXT, the data type int (as specified in > > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - > > this si incorrect according to java.sql.Types - it should be > > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > > > I am using version 7.3, not 7.2 so not sure if this was a bug back > > and has been fixed... > > > > Best regards, > > > > Carl > > > > -----Original Message----- > > *From:* pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of *Patrik > > Lundgren > > *Sent:* 12 March 2003 01:41 PM > > *To:* pgsql-jdbc@postgresql.org > > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > > > I'm having real problems with getting the foreign keys from my > > db. In fact I'm not getting it to work at all. I'm running > > postgresql 7.2 and I noticed that the code for getting the keys > > is different for 7.2 and 7.3 in > > AbstractJdbc1DatabaseMetaData.java. I've tested all different > > parameters I can think of but still not getting any results. > > I've made sure that the table parameter is correct and passed > > null for catalog and schema. Is the method not working or what > > can I be doing wrong? > > > > Desperate... Thanks in advance for any help! Btw I'm making a > > database admin tool for multiple databases so I really need this > > to work or my diagrams won't look so good. > > > > Regards, > > Patrik Lundgren > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
I've tried using the latest stable JDBC3 driver (the one for 7.3), and I also just tested the latest development build. Sadly no difference. The thing is that when looking at the CVS I see that this is handled differently in 7.3 since the system tables have changed but it's not like itäs not supported in earlier versions. I haven't had time to upgrade postgresql to 7.3 yet so I don't know if I'll get the same problem there. Most likely I will not since there are not so many different parameters to call getExportedKeys with. If catalog and schema is set to null and the table name is correct then there should be no problem. Unfortunately I don't have access to any 7.3 servers so I can't test this at the moment. This is going to be a smaller and smaller problem if it works with 7.3 since people are starting to upgrade. It would be nice though if it worked with 7.2. Thanks everyone who answered so far. Regards, Patrik ------------------------------- Patrik, Can you try Barry's suggestion before I dig into it. Dave On Wed, 2003-03-12 at 10:16, Barry Lind wrote: > Patrik, > > Another thing you mention you are using 7.2. Are you using 7.2 for > both > the driver and the server? If so have you tried using the 7.3 driver > against the 7.2 server? This is a supported setup and the 7.3 driver > has a significant number of fixes in this area. > > thanks, > --Barry > > > Patrik Lundgren wrote: > > Well, that is one ting we can rule out. I've made sure that the > > table > > name is correct. I've tested lower case, upper case and everything in > > between. Does anyone have any experience with using this function with > > 7.2? Again my problem is that getExportedKeys/getImportedKeys returns an > > empty ResultSet. > > > > Regards, > > Patrik Lundgren > > -----Ursprungligt meddelande----- > > *Från:* pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > > *Skickat:* Wednesday, March 12, 2003 1:13 PM > > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > > > Hi. > > PostGreSql is CASE SESITIVE - for creating foreign keys etc. This > > seems to hold true for meta data retrieval etc (I admit to having > > somne trouble with this). > > > > So, if you are passing the table pattern through - ensure that it is > > LOWER CASE ( *I think* ). > > > > One other thing that I found funny with the JDBC implementation > > is this. > > > > Data Type TEXT - when you get the meta data back for a column of > > type TEXT, the data type int (as specified in > > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - > > this si incorrect according to java.sql.Types - it should be > > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > > > I am using version 7.3, not 7.2 so not sure if this was a bug back > > and has been fixed... > > > > Best regards, > > > > Carl > > > > -----Original Message----- > > *From:* pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of *Patrik > > Lundgren > > *Sent:* 12 March 2003 01:41 PM > > *To:* pgsql-jdbc@postgresql.org > > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > > > I'm having real problems with getting the foreign keys from my > > db. In fact I'm not getting it to work at all. I'm running > > postgresql 7.2 and I noticed that the code for getting the keys > > is different for 7.2 and 7.3 in > > AbstractJdbc1DatabaseMetaData.java. I've tested all different > > parameters I can think of but still not getting any results. > > I've made sure that the table parameter is correct and passed > > null for catalog and schema. Is the method not working or what > > can I be doing wrong? > > > > Desperate... Thanks in advance for any help! Btw I'm making a > > database admin tool for multiple databases so I really need this > > to work or my diagrams won't look so good. > > > > Regards, > > Patrik Lundgren > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Sounds great, thanks a lot. /Patrik -----Ursprungligt meddelande----- Från: Dave Cramer [mailto:davec@fastcrypt.com] Skickat: Wednesday, March 12, 2003 7:48 PM Till: Patrik Lundgren Kopia: pgsql-jdbc@postgresql.org Ämne: Re: [JDBC] getExportedKeys/getImportedKeys Ok, I have confirmed that it isn't working with a 7.3 driver and a 7.2 db so now I'll have a crack at fixing it. Dave On Wed, 2003-03-12 at 10:54, Patrik Lundgren wrote: > I've tried using the latest stable JDBC3 driver (the one for 7.3), and > I also just tested the latest development build. Sadly no difference. > The thing is that when looking at the CVS I see that this is handled > differently in 7.3 since the system tables have changed but it's not > like itäs not supported in earlier versions. > > I haven't had time to upgrade postgresql to 7.3 yet so I don't know if > I'll get the same problem there. Most likely I will not since there > are not so many different parameters to call getExportedKeys with. If > catalog and schema is set to null and the table name is correct then > there should be no problem. Unfortunately I don't have access to any > 7.3 servers so I can't test this at the moment. This is going to be a > smaller and smaller problem if it works with 7.3 since people are > starting to upgrade. It would be nice though if it worked with 7.2. > > Thanks everyone who answered so far. > > Regards, > Patrik > > ------------------------------- > > > Patrik, > > Can you try Barry's suggestion before I dig into it. > > Dave > On Wed, 2003-03-12 at 10:16, Barry Lind wrote: > > Patrik, > > > > Another thing you mention you are using 7.2. Are you using 7.2 for > > both > > the driver and the server? If so have you tried using the 7.3 driver > > against the 7.2 server? This is a supported setup and the 7.3 driver > > has a significant number of fixes in this area. > > > > thanks, > > --Barry > > > > > > Patrik Lundgren wrote: > > > Well, that is one ting we can rule out. I've made sure that the > > > table > > > name is correct. I've tested lower case, upper case and everything > in > > > between. Does anyone have any experience with using this function > with > > > 7.2? Again my problem is that getExportedKeys/getImportedKeys > returns an > > > empty ResultSet. > > > > > > Regards, > > > Patrik Lundgren > > > -----Ursprungligt meddelande----- > > > *Från:* pgsql-jdbc-owner@postgresql.org > > > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > > > *Skickat:* Wednesday, March 12, 2003 1:13 PM > > > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > > > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > > > > > Hi. > > > PostGreSql is CASE SESITIVE - for creating foreign keys etc. > This > > > seems to hold true for meta data retrieval etc (I admit to > having > > > somne trouble with this). > > > > > > So, if you are passing the table pattern through - ensure that > it is > > > LOWER CASE ( *I think* ). > > > > > > One other thing that I found funny with the JDBC > > > implementation > > > is this. > > > > > > Data Type TEXT - when you get the meta data back for a column of > > > type TEXT, the data type int (as specified in > > > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 - > > > this si incorrect according to java.sql.Types - it should be > > > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > > > > > I am using version 7.3, not 7.2 so not sure if this was a bug > back > > > and has been fixed... > > > > > > Best regards, > > > > > > Carl > > > > > > -----Original Message----- > > > *From:* pgsql-jdbc-owner@postgresql.org > > > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of > *Patrik > > > Lundgren > > > *Sent:* 12 March 2003 01:41 PM > > > *To:* pgsql-jdbc@postgresql.org > > > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > > > > > I'm having real problems with getting the foreign keys > > > from > my > > > db. In fact I'm not getting it to work at all. I'm running > > > postgresql 7.2 and I noticed that the code for getting the > keys > > > is different for 7.2 and 7.3 in > > > AbstractJdbc1DatabaseMetaData.java. I've tested all > different > > > parameters I can think of but still not getting any results. > > > I've made sure that the table parameter is correct and > passed > > > null for catalog and schema. Is the method not working or > what > > > can I be doing wrong? > > > > > > Desperate... Thanks in advance for any help! Btw I'm > > > making > a > > > database admin tool for multiple databases so I really > > > need > this > > > to work or my diagrams won't look so good. > > > > > > Regards, > > > Patrik Lundgren > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org -- Dave Cramer <davec@fastcrypt.com> Cramer Consulting
Ok, This doesn't work on a 7.3 db either :( The think is that serial does not create a primary key ( news to me too ) so if you just change your sql to be create table foo ( id serial primary key .... ) it will work but obviously this is not desirable I will look some more when I have more time, this should get you going for now though Dave On Wed, 2003-03-12 at 15:07, Patrik Lundgren wrote: > Sounds great, thanks a lot. > > /Patrik > > -----Ursprungligt meddelande----- > Från: Dave Cramer [mailto:davec@fastcrypt.com] > Skickat: Wednesday, March 12, 2003 7:48 PM > Till: Patrik Lundgren > Kopia: pgsql-jdbc@postgresql.org > Ämne: Re: [JDBC] getExportedKeys/getImportedKeys > > > Ok, I have confirmed that it isn't working with a 7.3 driver and a 7.2 > db so now I'll have a crack at fixing it. > > Dave > On Wed, 2003-03-12 at 10:54, Patrik Lundgren wrote: > > I've tried using the latest stable JDBC3 driver (the one for 7.3), and > > > I also just tested the latest development build. Sadly no difference. > > The thing is that when looking at the CVS I see that this is handled > > differently in 7.3 since the system tables have changed but it's not > > like itäs not supported in earlier versions. > > > > I haven't had time to upgrade postgresql to 7.3 yet so I don't know if > > > I'll get the same problem there. Most likely I will not since there > > are not so many different parameters to call getExportedKeys with. If > > catalog and schema is set to null and the table name is correct then > > there should be no problem. Unfortunately I don't have access to any > > 7.3 servers so I can't test this at the moment. This is going to be a > > smaller and smaller problem if it works with 7.3 since people are > > starting to upgrade. It would be nice though if it worked with 7.2. > > > > Thanks everyone who answered so far. > > > > Regards, > > Patrik > > > > ------------------------------- > > > > > > Patrik, > > > > Can you try Barry's suggestion before I dig into it. > > > > Dave > > On Wed, 2003-03-12 at 10:16, Barry Lind wrote: > > > Patrik, > > > > > > Another thing you mention you are using 7.2. Are you using 7.2 for > > > both > > > the driver and the server? If so have you tried using the 7.3 > driver > > > against the 7.2 server? This is a supported setup and the 7.3 > driver > > > has a significant number of fixes in this area. > > > > > > thanks, > > > --Barry > > > > > > > > > Patrik Lundgren wrote: > > > > Well, that is one ting we can rule out. I've made sure that the > > > > table > > > > name is correct. I've tested lower case, upper case and everything > > in > > > > between. Does anyone have any experience with using this function > > with > > > > 7.2? Again my problem is that getExportedKeys/getImportedKeys > > returns an > > > > empty ResultSet. > > > > > > > > Regards, > > > > Patrik Lundgren > > > > -----Ursprungligt meddelande----- > > > > *Från:* pgsql-jdbc-owner@postgresql.org > > > > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > > > > *Skickat:* Wednesday, March 12, 2003 1:13 PM > > > > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > > > > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > > > > > > > Hi. > > > > PostGreSql is CASE SESITIVE - for creating foreign keys etc. > > This > > > > seems to hold true for meta data retrieval etc (I admit to > > having > > > > somne trouble with this). > > > > > > > > So, if you are passing the table pattern through - ensure that > > it is > > > > LOWER CASE ( *I think* ). > > > > > > > > One other thing that I found funny with the JDBC > > > > implementation > > > > is this. > > > > > > > > Data Type TEXT - when you get the meta data back for a column > of > > > > type TEXT, the data type int (as specified in > > > > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 > - > > > > this si incorrect according to java.sql.Types - it should be > > > > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > > > > > > > I am using version 7.3, not 7.2 so not sure if this was a bug > > back > > > > and has been fixed... > > > > > > > > Best regards, > > > > > > > > Carl > > > > > > > > -----Original Message----- > > > > *From:* pgsql-jdbc-owner@postgresql.org > > > > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of > > *Patrik > > > > Lundgren > > > > *Sent:* 12 March 2003 01:41 PM > > > > *To:* pgsql-jdbc@postgresql.org > > > > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > > > > > > > I'm having real problems with getting the foreign keys > > > > from > > my > > > > db. In fact I'm not getting it to work at all. I'm running > > > > postgresql 7.2 and I noticed that the code for getting the > > keys > > > > is different for 7.2 and 7.3 in > > > > AbstractJdbc1DatabaseMetaData.java. I've tested all > > different > > > > parameters I can think of but still not getting any > results. > > > > I've made sure that the table parameter is correct and > > passed > > > > null for catalog and schema. Is the method not working or > > what > > > > can I be doing wrong? > > > > > > > > Desperate... Thanks in advance for any help! Btw I'm > > > > making > > a > > > > database admin tool for multiple databases so I really > > > > need > > this > > > > to work or my diagrams won't look so good. > > > > > > > > Regards, > > > > Patrik Lundgren > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
Ok, read the spec, a primary key is required here, so this is why this doesn't work. On Wed, 2003-03-12 at 20:03, Dave Cramer wrote: > Ok, > > This doesn't work on a 7.3 db either :( > > The think is that serial does not create a primary key ( news to me too > ) > > so if you just change your sql to be > > create table foo ( id serial primary key .... ) it will work > > but obviously this is not desirable > > I will look some more when I have more time, this should get you going > for now though > > Dave > On Wed, 2003-03-12 at 15:07, Patrik Lundgren wrote: > > Sounds great, thanks a lot. > > > > /Patrik > > > > -----Ursprungligt meddelande----- > > Från: Dave Cramer [mailto:davec@fastcrypt.com] > > Skickat: Wednesday, March 12, 2003 7:48 PM > > Till: Patrik Lundgren > > Kopia: pgsql-jdbc@postgresql.org > > Ämne: Re: [JDBC] getExportedKeys/getImportedKeys > > > > > > Ok, I have confirmed that it isn't working with a 7.3 driver and a 7.2 > > db so now I'll have a crack at fixing it. > > > > Dave > > On Wed, 2003-03-12 at 10:54, Patrik Lundgren wrote: > > > I've tried using the latest stable JDBC3 driver (the one for 7.3), and > > > > > I also just tested the latest development build. Sadly no difference. > > > The thing is that when looking at the CVS I see that this is handled > > > differently in 7.3 since the system tables have changed but it's not > > > like itäs not supported in earlier versions. > > > > > > I haven't had time to upgrade postgresql to 7.3 yet so I don't know if > > > > > I'll get the same problem there. Most likely I will not since there > > > are not so many different parameters to call getExportedKeys with. If > > > catalog and schema is set to null and the table name is correct then > > > there should be no problem. Unfortunately I don't have access to any > > > 7.3 servers so I can't test this at the moment. This is going to be a > > > smaller and smaller problem if it works with 7.3 since people are > > > starting to upgrade. It would be nice though if it worked with 7.2. > > > > > > Thanks everyone who answered so far. > > > > > > Regards, > > > Patrik > > > > > > ------------------------------- > > > > > > > > > Patrik, > > > > > > Can you try Barry's suggestion before I dig into it. > > > > > > Dave > > > On Wed, 2003-03-12 at 10:16, Barry Lind wrote: > > > > Patrik, > > > > > > > > Another thing you mention you are using 7.2. Are you using 7.2 for > > > > both > > > > the driver and the server? If so have you tried using the 7.3 > > driver > > > > against the 7.2 server? This is a supported setup and the 7.3 > > driver > > > > has a significant number of fixes in this area. > > > > > > > > thanks, > > > > --Barry > > > > > > > > > > > > Patrik Lundgren wrote: > > > > > Well, that is one ting we can rule out. I've made sure that the > > > > > table > > > > > name is correct. I've tested lower case, upper case and everything > > > in > > > > > between. Does anyone have any experience with using this function > > > with > > > > > 7.2? Again my problem is that getExportedKeys/getImportedKeys > > > returns an > > > > > empty ResultSet. > > > > > > > > > > Regards, > > > > > Patrik Lundgren > > > > > -----Ursprungligt meddelande----- > > > > > *Från:* pgsql-jdbc-owner@postgresql.org > > > > > [mailto:pgsql-jdbc-owner@postgresql.org] *För *Carl Olivier > > > > > *Skickat:* Wednesday, March 12, 2003 1:13 PM > > > > > *Till:* Patrik Lundgren; pgsql-jdbc@postgresql.org > > > > > *Ämne:* Re: [JDBC] getExportedKeys/getImportedKeys > > > > > > > > > > Hi. > > > > > PostGreSql is CASE SESITIVE - for creating foreign keys etc. > > > This > > > > > seems to hold true for meta data retrieval etc (I admit to > > > having > > > > > somne trouble with this). > > > > > > > > > > So, if you are passing the table pattern through - ensure that > > > it is > > > > > LOWER CASE ( *I think* ). > > > > > > > > > > One other thing that I found funny with the JDBC > > > > > implementation > > > > > is this. > > > > > > > > > > Data Type TEXT - when you get the meta data back for a column > > of > > > > > type TEXT, the data type int (as specified in > > > > > java.sql.Types.LONGVARCHAR) - wich is what TEXT is - return 12 > > - > > > > > this si incorrect according to java.sql.Types - it should be > > > > > returning -1, as 12 is the VARCHAR java.sql.Types datatype id. > > > > > > > > > > I am using version 7.3, not 7.2 so not sure if this was a bug > > > back > > > > > and has been fixed... > > > > > > > > > > Best regards, > > > > > > > > > > Carl > > > > > > > > > > -----Original Message----- > > > > > *From:* pgsql-jdbc-owner@postgresql.org > > > > > [mailto:pgsql-jdbc-owner@postgresql.org]*On Behalf Of > > > *Patrik > > > > > Lundgren > > > > > *Sent:* 12 March 2003 01:41 PM > > > > > *To:* pgsql-jdbc@postgresql.org > > > > > *Subject:* [JDBC] getExportedKeys/getImportedKeys > > > > > > > > > > I'm having real problems with getting the foreign keys > > > > > from > > > my > > > > > db. In fact I'm not getting it to work at all. I'm running > > > > > postgresql 7.2 and I noticed that the code for getting the > > > keys > > > > > is different for 7.2 and 7.3 in > > > > > AbstractJdbc1DatabaseMetaData.java. I've tested all > > > different > > > > > parameters I can think of but still not getting any > > results. > > > > > I've made sure that the table parameter is correct and > > > passed > > > > > null for catalog and schema. Is the method not working or > > > what > > > > > can I be doing wrong? > > > > > > > > > > Desperate... Thanks in advance for any help! Btw I'm > > > > > making > > > a > > > > > database admin tool for multiple databases so I really > > > > > need > > > this > > > > > to work or my diagrams won't look so good. > > > > > > > > > > Regards, > > > > > Patrik Lundgren > > > > > > > > > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > TIP 6: Have you searched our list archives? > > > > > > > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>