[DERBY-5967] Create some type mapping functions for use in turning JDBC type information into human-readable strings.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.1.1
• Fix Version/s: 10.10.1.1
• Component/s: Tools
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 29/Oct/12 13:26
• Updated: 14/Jun/13 18:29
• Resolved: 14/Jun/13 18:29

Description

I will attach a set of type mapping functions which turn JDBC type information into human-readable type names. The two functions do the following:

mapType() - This function turns the JDBC type info into an SQL type name suitable for use in a Derby CREATE TABLE statement.

mapToJavaType() - This turns the JDBC type info into the corresponding Java type name given in table B-3 of the JDBC spec.

I am posting these functions in response to the following discussion on derby-user: http://old.nabble.com/export-from-systables-to34607734.html#a34607734

Here is an example of how to use these functions in conjunction with the DatabaseMetaData wrapper functions attached to DERBY-3973:

connect 'jdbc:derby:memory:db;create=true';

create procedure registerPublicStaticMethods( in connectionURL varchar( 200 ), in printSQL boolean )
language java parameter style java modifies sql data
external name 'DBMDWrapper.registerPublicStaticMethods';

call registerPublicStaticMethods( 'jdbc:default:connection', false );

create function mapType( jdbcType int, precision int, scale int, foreignTypeName varchar( 100 ) )
returns varchar( 100 )
language java parameter style java deterministic no sql
external name 'TypeMapper.mapType';

create function mapToJavaType( jdbcType int, foreignTypeName varchar( 100 ) )
returns varchar( 100 )
language java parameter style java deterministic no sql
external name 'TypeMapper.mapToJavaType';

select c.table_schem, c.table_name, c.column_name,
mapType( c.data_type, c.column_size, c.decimal_digits, c.type_name ),
mapToJavaType( c.data_type, c.type_name )
from table( getColumns( null, null, '%', '%' ) ) c;

Issue Links

• is related to: DERBY-6022 Add a system procedure for (un)registering optional packages of Derby tools.
• relates to: DERBY-3973 Create function wrappers for DatabaseMetaData so that you can join metadata results in queries.