Re: [Firebird-devel] XML Load/Dump Utility

[Arno B. <fir...@ab...>]

Hi,




    <SL> The JDBC conventions are more than appropriate for Java and (as =
you suggest) internal uses, there are plenty of languages where a =
NUMERIC( 10, 2) and NUMERIC( 15, 2) would have significantly different =
meanings, due to the data access components.  Therefore, the load/dump =
utility could produce a database which is incompatible with the target =
application.

  OK, I've got a problem to solve.  Anybody: How do I tell from the =
Firebird system tables the difference between numeric(10,2) and =
decimal(10,2)?  Anybody?  Anybody?

rdb$field_sub_type

something like this :-)

SELECT
  rf.rdb$relation_name AS "Relationname",
  rf.rdb$field_name AS "Fieldname",
  CASE f.rdb$field_type
    WHEN 7 THEN
      CASE
        WHEN (COALESCE(f.rdb$field_sub_type, 0) =3D 0) THEN 'SMALLINT'
        WHEN (f.rdb$field_sub_type =3D 1) THEN 'NUMERIC' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
        WHEN (f.rdb$field_sub_type =3D 2) THEN 'DECIMAL' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
      ELSE
        'Unknown [7,' || f.rdb$field_sub_type ||']'
      END
    WHEN 8 THEN
      CASE
        WHEN (COALESCE(f.rdb$field_sub_type, 0) =3D 0) THEN 'INTEGER'
        WHEN (f.rdb$field_sub_type =3D 1) THEN 'NUMERIC' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
        WHEN (f.rdb$field_sub_type =3D 2) THEN 'DECIMAL' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
      ELSE
        'Unknown [8,' || f.rdb$field_type ||']'
      END
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || f.rdb$field_length || ')' || COALESCE(' =
CHARACTER SET ' || rdb$character_set_name, '')
    WHEN 16 THEN
      CASE
        WHEN (COALESCE(f.rdb$field_sub_type, 0) =3D 0) THEN 'BIGINT'
        WHEN (f.rdb$field_sub_type =3D 1) THEN 'NUMERIC' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
        WHEN (f.rdb$field_sub_type =3D 2) THEN 'DECIMAL' || '(' || =
f.rdb$field_precision || ',' || CAST( -f.rdb$field_scale AS varChar(2)) =
|| ')'
      ELSE
        'Unknown [16,' || f.rdb$field_type ||']'
      END
    WHEN 27 THEN 'DOUBLE PRECISION'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN 'VARCHAR(' || f.rdb$field_length || ')' || COALESCE(' =
CHARACTER SET ' || rdb$character_set_name, '')
    WHEN 261 THEN 'BLOB SUB_TYPE ' || f.rdb$field_sub_type || ' SEGMENT =
SIZE ' || rdb$segment_length || COALESCE(' CHARACTER SET ' || =
rdb$character_set_name, '')
    ELSE 'Unknown [' || f.rdb$field_type ||']'
  END ||
  CASE WHEN (rf.rdb$null_flag IS NULL) THEN '' ELSE ' NOT NULL' END ||
  COALESCE(' COLLATE ' || c.rdb$collation_name,'') AS "Column =
definition"
FROM
  rdb$relation_fields rf
  JOIN rdb$fields f ON (f.rdb$field_name =3D rf.rdb$field_source)
  LEFT JOIN rdb$field_dimensions fd ON (fd.rdb$field_name =3D =
f.rdb$field_name)
  LEFT JOIN RDB$CHARACTER_SETS cs ON (cs.rdb$character_set_id =3D =
f.rdb$character_set_id)
  LEFT JOIN RDB$COLLATIONS c ON (c.rdb$collation_id =3D =
rf.rdb$collation_id and c.rdb$character_set_id =3D =
f.rdb$character_set_id)
WHERE
  rf.rdb$relation_name =3D :RELATION_NAME
ORDER BY
  rf.rdb$relation_name, rf.rdb$field_position, fd.rdb$dimension

Regards,
Arno Brinkman
ABVisie

-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-
Firebird open source database (based on IB-OE) with many SQL-99 features =
:
  http://www.firebirdsql.org
  http://www.firebirdsql.info
  http://www.fingerbird.de/
  http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
fir...@ya...

Nederlandse firebird nieuwsgroep :
news://80.126.130.81