From: | "Dave Cramer" <Dave(at)micro-automation(dot)net> |
---|---|
To: | "'Vianen, Jeroen van'" <jeroen(dot)van(dot)vianen(at)satama(dot)com>, "'pgsql-jdbc'" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: Problem with getImportedKeys / getExportedKeys |
Date: | 2002-01-14 14:50:39 |
Message-ID: | 01a501c19d0a$d52b40e0$c201a8c0@inspiron |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Jeroen,
Thanks for the great test case, I will have a look as soon as possible.
Dave
-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Benjamin.Feinstein | 2002-01-14 15:12:31 | Re: LISTEN/NOTIFY support in JDBC driver? |
Previous Message | Vianen, Jeroen van | 2002-01-14 13:05:52 | Problem with getImportedKeys / getExportedKeys |