Thread: ResultSetMetaData getColumnTypeName()
Hi, I would like to discuss an issue ResultSetMetaData.getColumnTypeName(). Within current implementation of PostgreSQL JDBC getColumnTypeName() returns only locale name of a type, even if it is a user-defined type. The JDBC docs says: "...returns type name used by the database. If the column type is a user-defined type, then a fully-qualified type name is returned." It means, that any other type that is not defined within pg_catalog should be returned with its schema name, so e.g. type my_price in schema blabla should be returned as "blabla.my_price". It's quite simple to fix it and I'm ready to do it, but before that I would like to ask about your opinion about changing this behavior. I found this problem when I needed to translate user-defined types to Java and without schema I cannot be sure, that a type is the one that I want to translate (cause there can be many types with the same local name). I would be appreciated for your comments. Ps. Kris - if we will accept, that getColumnTypeName() should return qualified name, then that will cause small changes within org.postgresql.jdbc2.TypeInfoCache class and I would like to know what about my previous changes within this class that are related to array types. Best wishes, Marek
On Wed, 21 Nov 2007, Marek Lewczuk wrote: > I would like to discuss an issue ResultSetMetaData.getColumnTypeName(). > Within current implementation of PostgreSQL JDBC getColumnTypeName() > returns only locale name of a type, even if it is a user-defined type. > The JDBC docs says: "...returns type name used by the database. If the > column type is a user-defined type, then a fully-qualified type name is > returned." > > It means, that any other type that is not defined within pg_catalog > should be returned with its schema name, so e.g. type my_price in schema > blabla should be returned as "blabla.my_price". It's quite simple to fix > it and I'm ready to do it, but before that I would like to ask about > your opinion about changing this behavior. I found this problem when I > needed to translate user-defined types to Java and without schema I > cannot be sure, that a type is the one that I want to translate (cause > there can be many types with the same local name). > Currently the driver implements DatabaseMetaData.getUDTs by considering only complex types and domains as UDTs and the javadoc for that method seems to imply that's correct. Perhaps the JDBC spec isn't prepared for the extensibility that pg offers, but you'll need to reconcile these two definitions if you want to qualify all types outside pg_catalog. Could explain in more detail how you're mapping these UDTs to Java? Using PGobject? I'm not sure how you'd map without an implemention of SQLData/Input/Output. My recollection was that making all type operations in the driver schema qualified was more work that a simple change to TypeInfoCache, but I don't recall the details, so I'd like to see what you come up with. While you wouldn't want to report the pg_catalog parts of base types you would need a way to ensure that if you had myschema.int4 it wouldn't conflict. Kris Jurka
Kris Jurka pisze: > > Currently the driver implements DatabaseMetaData.getUDTs by considering > only complex types and domains as UDTs and the javadoc for that method > seems to imply that's correct. Perhaps the JDBC spec isn't prepared for > the extensibility that pg offers, but you'll need to reconcile these two > definitions if you want to qualify all types outside pg_catalog. From my point of view getUDTs is working just fine. > My recollection was that making all type operations in the driver schema > qualified was more work that a simple change to TypeInfoCache, but I > don't recall the details, so I'd like to see what you come up with. > While you wouldn't want to report the pg_catalog parts of base types you > would need a way to ensure that if you had myschema.int4 it wouldn't > conflict. Maybe I was to optimistic that is so easy to implement this, but it is not so hard too. The main change is to use qualified names within TypeCacheInfo everywhere it is possible, that means that even basic types should be presented with its schema (pg_catalog). Of course that change will force other changes especially in TypeCacheInfo internal sql queries and other places where type name is taken/provided. With this changes ResultSetMetaData.getColumnTypeName() will return local name for basic types (that is a type that qualified name's startsWith("pg_catalog")) and qualified name for all other types. > Could explain in more detail how you're mapping these UDTs to Java? > Using PGobject? I'm not sure how you'd map without an implemention of > SQLData/Input/Output. I have made a wrapper for JDBC classes, that override default implementation of JDBC driver and when needed (ResultSet.getObject()) it translates sql types to Java types and vice-versa (PreparedStatement.setObject()). My implementation works only for composite types, cause composite type have same structure of string representation. Best wishes, ML