export from systables

[Thomas Hill <th...@t-online.de>]

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

Re: export from systables

[Rick Hillegas <ri...@oracle.com>]

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