Thread: Re: [Firebird-devel] XML Load/Dump Utility (Page 2)

A powerful, cross platform, SQL database system

Brought to you by: alexpeshkoff, asfernandes, awharrison, cincura_net, and 5 others

firebird-devel

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

From: Jim S. <ja...@ne...> - 2004-09-07 19:16:02


Leyne, Sean wrote:

>  
>
> <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?


-- 

Jim Starkey
Netfrastructure, Inc.
978 526-1376

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

From: Daniel R. <da...@ac...> - 2004-09-07 19:37:23

Hi,

At September 7, 2004, 16:19, Jim Starkey wrote:

> 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?

The difference is in the sub-type. They are both INTEGER(8) types, but
NUMERIC has a subtype of 1 and DECIMAL has a sub-type of 2.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)

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

From: Arno B. <fir...@ab...> - 2004-09-07 19:39:53

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

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

From: Martijn T. <m.t...@up...> - 2004-09-08 06:29:03


<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.

<JS> 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?


Har har har! Good joke Jim...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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

From: Lester C. <le...@ls...> - 2004-09-08 08:18:06

Jim Starkey wrote:

>> <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?

OK does it matter, but any chance we could follow
http://www.w3.org/TR/xmlschema-2/
when doing this

I know it is a bit heavy, but if everything is already in XML datatype 
format we are getting ahead of the game - even if we only keep to the 
same 'element' names at this stage. It does handle base64Binary amongst 
other things.

For example
CHAR = string with
<length value='8' fixed='true'/>
<whiteSpace value='preserve'/>

VARCHAR = string with
<length value='8' fixed='false'/>
<whiteSpace value='collapse'/>

At the very least can we avoid anything that precludes a switch to the 
standard at some point?

-- 
Lester Caine
-----------------------------
L.S.Caine Electronic Services

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

From: Leyne, S. <Se...@br...> - 2004-09-07 20:12:11

Arno,

	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?

	=20

rdb$field_sub_type

=20

something like this :-)

=20

SELECT
  rf.rdb$relation_name AS "Relationname",
  rf.rdb$field_name AS "Fieldname",
  CASE f.rdb$field_type
...

=20

<SL> Could you be MORE specific ;-)))

=20

=20

Sean

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

From: Nando D. <na...@de...> - 2004-09-08 05:53:30

Sean,

S> <SL> Could you be MORE specific;-)))

launching yet another quoting style? ;-)

Ciao
-- 
Nando Dessena
mailto:na...@de...

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

From: Jim S. <ja...@ne...> - 2004-08-31 14:33:05

Alexander Klenin wrote:

>> The utility is built on the JDBC C++ binding defined in IscDbc. 
>
> How specific to Firebird is this tool then? Can it be used to 
> dump/load data from any JBDC-compatible database?
>
Funny you should ask.  It's completely general.  It should work against 
any database system that exports an JDBC/IscDbc compliant C++ API.  At 
the moment, there are two: Firebird and Netfrastructure.  If you'd like 
to use it against Netfrastructure, contact me off list and we'll talk!

> How do you determine the encoding? I think it sould at least be 
> controllable by the user.

By copying it out of a book.  I really haven't given it any thought.  If 
you have some ideas on what is required and how to handle various 
encodings, I'd like to hear about them.

>
>
>> The key questions, I think, are how data is presented. My starting 
>> point is:
>>
>>    * A table row is represented as a single xml element
>>    * Each non-null column is presented by an xml attribute
>
> I think that such a scheme is impossible to validate, at least with 
> DTD. This  is very unfortunate, but the only "correct" way IMO is
>   <row>
>     <column name="col1">data 1</column>
>     <column name="col2">more data</column>
>   </row>

Then don't validate the schema.  The purpose is to a) dump data from one 
database and b) restore it into another c) in a CVS friendly way.  As 
long as the grammatical rules of XML are observed, I don't see the 
theological problem as anything to get excited about.

> Yes, XML _is_ bloated :(
>
>> (I'm sending this as both text and html for readability. For those 
>> morally opposed to html mail, get a life.)
>
> I receive this list in a digest form, so I have to scroll through both 
> text and *undecoded* html versions of your e-mails. So while I think I 
> do have a life, it is not made any easier by this ;)

I'm sorry about that.  More intelligent list handling software would 
have prevented it.  I try to stay with straight text, but when 
formatting significantly clarifies the message, I want to be able to 
take advantage of it.  The SMTP mail format is designed for tools that 
can pick a rendering that it likes and ignore the rest.  It's a pity 
that the list server hasn't mastered MIME.

-- 

Jim Starkey
Netfrastructure, Inc.
978 526-1376

<< < 1 2 (Page 2 of 2)