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 is that your program converts table names to uppercase. I've written the code so it is expecting case-sensitive names. Should this be changed - I'm not up to date on whether things should all be converted to lowercase or not- ? Once this is resolved 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(at)netspade(dot)com