db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: export from systables
Date Mon, 29 Oct 2012 13:52:11 GMT
On 10/26/12 2:31 PM, Thomas Hill wrote:
> Hi,
>
> I am trying to export a comma separated list from the syscatalog tables to a
> file which holds the following information:
> schemaname, tablename, columnname, columdatatype and javadatatype.
> e.g.
> appl, mytable, column2, char(1), java.lang.String
> appl, mytable, column2, integer, java.lang.Integer
>
> Is this possible using IJ and export query system function?
>
> Thanks
>
>
>
Hi Thomas,

You should be able to do what you want by going directly against the 
system catalogs. You will have to write some functions to map the Derby 
type descriptor objects into human-readable strings. However, I do not 
recommend this solution. That is because is relies on APIs which aren't 
part of Derby's public interface and which may change over time.

Instead, I recommend that you use the DBMDWrapper functions attached to 
DERBY-3973 and the TypeMapper functions attached to DERBY-5967. This 
solution uses stable public APIs. It can also be used to introspect the 
metadata of any JDBC database.

Hope this helps,
-Rick

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';

call syscs_util.syscs_export_query
(
   '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',
   'z.dat', null, null, null
);


Mime
View raw message