Re: Problem with getImportedKeys / getExportedKeys

Jeroen,

Thanks for the great test case, I will have a look as soon as possible.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Vianen, Jeroen van
Sent: Monday, January 14, 2002 8:06 AM
To: pgsql-jdbc
Subject: [JDBC] Problem with getImportedKeys / getExportedKeys


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