Re: Problem with getImportedKeys / getExportedKeys

Hi,

I noticed a couple of things. First of all, there is a small bug in the code which can be easily fixed. The other thing
isthat your program converts table names to uppercase. I've written the code so it is expecting case-sensitive names.
Shouldthis be changed - I'm not up to date on whether things should all be converted to lowercase or not- ? Once this
isresolved I'll issue a patch :-) 

Thanks,

Jason Davies

On Mon, Jan 14, 2002 at 02:05:52PM +0100, Vianen, Jeroen van wrote:
> Hi,
>
> I have a problem with the getImportedKeys() / getExportedKeys() pair in
> current JDBC sources.
>
> Given the following data model:
>
> create table users (
>     userid integer not null,
>     username varchar(50) not null,
>     password varchar(50) not null,
>     name1 varchar(20) not null,
>     name2 varchar(10),
>     name3 varchar(40) not null,
>     email varchar(50) not null,
> --
>     constraint pk_users primary key (userid)
> );
>
>
> create table roles (
>     rolename varchar(5) not null,
>     description varchar(50) not null,
> --
>     constraint pk_roles primary key (rolename)
> );
>
>
> create table users_roles (
>     userid      integer not null,
>     rolename    varchar(5) not null,
> --
>     constraint pk_users_roles primary key (userid, rolename),
>     constraint fk_users_roles__userid foreign key (userid) references
> users(userid),
>     constraint fk_users_roles__rolename foreign key (rolename) references
> roles(rolename)
> );
>
>
> I.e. the following datamodel
>
> +-------+      +-------------+      +-------+
> | users |-----<| users_roles |>-----| roles |
> +-------+      +-------------+      +-------+
>
> and the following Java application (TestKeys.java):
>
> import java.sql.*;
>
> public class TestKeys {
>     static public void main(String[] args) {
>         if (args.length > 0) {
>             String table = args[0].toUpperCase();
>             Connection conn = null;
>             try {
>                 Class.forName("org.postgresql.Driver");
>                 conn =
> DriverManager.getConnection("<YOUR_URL_HERE>", "<USERNAME>", "<PASSWORD>");
>                 DatabaseMetaData dbmd = conn.getMetaData();
>
>                 System.out.println("Imported keys:");
>                 ResultSet rs = dbmd.getImportedKeys(null,
> null, table);
>                 while (rs.next()) {
>                     System.out.println("PKTABLE_NAME = "
> + rs.getString("PKTABLE_NAME"));
>                     System.out.println("PKCOLUMN_NAME =
> " + rs.getString("PKCOLUMN_NAME"));
>                     System.out.println("FKTABLE_NAME = "
> + rs.getString("FKTABLE_NAME"));
>                     System.out.println("FKCOLUMN_NAME =
> " + rs.getString("FKCOLUMN_NAME"));
>                 }
>                 rs.close();
>
>                 System.out.println("Exported keys:");
>                 rs = dbmd.getExportedKeys(null, null,
> table);
>                 while (rs.next()) {
>                     System.out.println("PKTABLE_NAME = "
> + rs.getString("PKTABLE_NAME"));
>                     System.out.println("PKCOLUMN_NAME =
> " + rs.getString("PKCOLUMN_NAME"));
>                     System.out.println("FKTABLE_NAME = "
> + rs.getString("FKTABLE_NAME"));
>                     System.out.println("FKCOLUMN_NAME =
> " + rs.getString("FKCOLUMN_NAME"));
>                 }
>                 rs.close();
>                 conn.close();
>             } catch (Exception ex) {
>                 ex.printStackTrace(System.err);
>             }
>             System.exit(0);
>         } else {
>             System.err.println("Please specify a tablename");
>         }
>     }
> }
>
>
>
> In Oracle I get:
>
> # java -classpath classes12.zip:. TestKeys users
> Imported keys:
> Exported keys:
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
>
> # java -classpath classes12.zip:. TestKeys users_roles
> Imported keys:
> PKTABLE_NAME = ROLES
> PKCOLUMN_NAME = ROLENAME
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = ROLENAME
> PKTABLE_NAME = USERS
> PKCOLUMN_NAME = USERID
> FKTABLE_NAME = USERS_ROLES
> FKCOLUMN_NAME = USERID
> Exported keys:
>
>
> In PostgreSQL 7.2 (current sources) I get:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users
> Imported keys:
> Exported keys:
>
> # java -classpath jdbc7.2dev-1.2.jar:. TestKeys users_roles
> Imported keys:
> Exported keys:
>
> As might be guessed, Oracle is right here, and PostgreSQL is not. I do
> recall that this once worked in a previous version of the 7.2 development
> sources.
>
> Any clue?
>
>
> Jeroen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Jason Davies

jason@netspade.com