[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    Re: Type Names in DBD drivers
From:       johnl () informix ! com (Jonathan Leffler)
Date:       1996-11-17 16:47:44
[Download RAW message or body]

    *** From dbi-dev -- To unsubscribe, see the end of this message. ***

Hi,

Thanks for the prompt answer -- what are you doing up so late in the
morning?  And I think your system clock is an hour off, since I received
your response at about Sunday 17th November 1996 16:30 +0800 (PST)...:-)

>From: descarte@hermetica.com
>Date: Mon, 18 Nov 1996 01:19:00 +0000 (GMT)
>
>> I've got DBD::Informix v0.25 working reasonably well, and I wanted to
>> axe some Oracle definitions that don't translate into Informix idioms,
>> but I came up with an encoding problem.  When the user requests:
>>
>> @type = @{$statement->{TYPE}};
>>
>> What should the values in the @type array be?  Numbers, or strings?
>> Upper or lower case strings?  Similarly, what should be in arrays:
>>
>> @prec = @{$statement->{PRECISION}};
>> @scal = @{$statement->{SCALE}};
>>
>> What information do these array convey for each and every type?
>
>PRECISION is the "decimal precision for NUMBER datatype; BINARY precision for
>FLOAT datatype; NULL for all other datatypes" to quote the good book.
>SCALE is "digits to right of decimal point in a number"

OK, that is understandable.  What about SMALLFLOAT, REAL, NUMERIC, DECIMAL,
etc.  DOUBLE PRECISION, etc.

How do you find out how big a CHAR type is?  VARCHAR?  NCHAR?  NVARCHAR?

How do you handle DATE, DATETIME and INTERVAL types?  What about types
which don't have any analogue on other systems?

>> With Informix, it seems to me that there are several possible answers to
>> these questions, and different people will find different answers most
>> useful.
>>
>> In fact, I suggest that the @type array should contain the full type names
>> for each column in any format that would be equivalent to what is
>> used in the CREATE TABLE statement, with the names in lower case.
>>
>> So, this might produce, with an Informix database:
>>
>> @type[0] == "integer"
>> @type[1] == "decimal"
>> @type[2] == "money(12,2)"
>> @type[3] == "datetime year to fraction(5)"
>> @type[4] == "interval day(6) to minute"
>>
>> I suggest that the @prec and @scal arrays have no real use under this
>> scheme, but since I don't know what should be in them anyway, that
>> judegement could be premature.
>>
>> For Informix, it makes sense to make two other arrays available
>> (probably as an Informix-only extension):
>>
>> @coltyp = @{$statement->{COLTYPE}};
>> @collen = @{$statement->{COLLENGTH}};
>>
>> These would return the basic integer values found in the ColType and
>> ColLength columns of the SysColumns table in the Informix system
>> catalogue.
>
>Why not just use SCALE and PRECISION? Or perhaps a generic solution like
>"LENGTH" and "SCALE", where "LENGTH" is the type-specific length of the
>object ( also database-specific ), and "SCALE" is the number of decimal
>places? That'd be scalable across most databases, I reckon.

So, if I have a DECIMAL(16) type, what goes in the TYPE array?  DECIMAL or
DECIMAL(16)?  What goes into the LENGTH?  Trivial answer: 16.  What goes in
the SCALE?  Dunno -- it's a floating point number, not a fixed, so
empty/undefined?  Not 0; that's a 16-digit integer.

And if I have an INTERVAL DAY(6) TO FRACTION(2), what goes in each item?

>> Anybody got any ideas about this?  Yea, Nay, Maybe?
>
>I'm kinda tempted to standardise for TYPE as the SQL types, with mappings.
>That would make cross-database tools ( like dbmv ) be possibly more reliable.

Well, yes, but...  But it isn't always possible.

That's why I suggested 'a type acceptable to CREATE TABLE'.  That works
nicely for each database, and the types should be as close as possible to
the standard (ie, an SQL standard type name should be used when it will
work, but not if there isn't one or it won't work).

Also, moving data from one database system to another will always be tricky
when the starting database is not using the SQL types.  Real Informix
databases use lots of none-standard data types.  These will always need to
be handled with care with any program (such as your dbmv).

Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>


------------------------------------------------------------------------------
To unsubscribe from this list, please visit http://www.fugue.com/dbi.
If you are without web access, or if you are having trouble with the web page,
please send mail to dbi-request@fugue.com.   Please try to use the web
page first - it will take a long time for your request to be processed by hand.
------------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]