Subject | Re: [Firebird-Java] Re: Only real types in FBDatabaseMetaData.getTypeInfo |
---|---|

Author | Blas Rodriguez Somoza |

Post date | 2002-11-19T00:34:10Z |

Hello David

There are two problems in the mapping.

The first problem is that the typeInfo serves to map SQL types to java.sql.Types, and can be many to one but not one to many.

Each SQL type must have a row in the table and only one. In the case of BLOB subtype, the solution I choose was to specify the

subtype as part of the SQL name, thus there are only one line for each type because there are not generic BLOB in typeInfo.

But there is another clearer argument . There is a condition that must be meet to map a SQL type to a java type. The SQL type

must be able to store values from the min to the max of the java type, and if the type is an exact one, integers and

decimal/numeric, then the values must not loose precision.

For instance, for Integer the values must be between 2^31-1(2.147.483.647) and -2^31 (2.147.483.648) which are exactly the

limits is interbase, so the integer type in interbase can be mapped to java.sql.Types.INTEGER.

For BIGINT(equivalent to Long) this means that the field must allow values from 2^63-1 to -2^63 without loosing of precision,

what is not true for NUMERIC(18,0). So NUMERIC(18,0) can't be mapped to BIGINT because it can't containt values between the max and

min of a BIGINT without loosing precision, and the tests with setLong on this field will fail because the value get will be

different from the one set.

So the mapping NUMERIC(18,0) to BIGINT is an illegal one and must be removed until firebird have a INT64 field.

Reviewing this change, I see you remove the type "BLOB SUBTYPE > 1" I don't know exactly why, but I suppose is a mistake.

Regards

Blas Rodriguez Somoza

There are two problems in the mapping.

The first problem is that the typeInfo serves to map SQL types to java.sql.Types, and can be many to one but not one to many.

Each SQL type must have a row in the table and only one. In the case of BLOB subtype, the solution I choose was to specify the

subtype as part of the SQL name, thus there are only one line for each type because there are not generic BLOB in typeInfo.

But there is another clearer argument . There is a condition that must be meet to map a SQL type to a java type. The SQL type

must be able to store values from the min to the max of the java type, and if the type is an exact one, integers and

decimal/numeric, then the values must not loose precision.

For instance, for Integer the values must be between 2^31-1(2.147.483.647) and -2^31 (2.147.483.648) which are exactly the

limits is interbase, so the integer type in interbase can be mapped to java.sql.Types.INTEGER.

For BIGINT(equivalent to Long) this means that the field must allow values from 2^63-1 to -2^63 without loosing of precision,

what is not true for NUMERIC(18,0). So NUMERIC(18,0) can't be mapped to BIGINT because it can't containt values between the max and

min of a BIGINT without loosing precision, and the tests with setLong on this field will fail because the value get will be

different from the one set.

So the mapping NUMERIC(18,0) to BIGINT is an illegal one and must be removed until firebird have a INT64 field.

Reviewing this change, I see you remove the type "BLOB SUBTYPE > 1" I don't know exactly why, but I suppose is a mistake.

Regards

Blas Rodriguez Somoza

----- Original Message -----

From: "David Jencks" <davidjencks@...>

To: <Firebird-Java@yahoogroups.com>

Sent: Monday, November 18, 2002 8:07 PM

Subject: Re: [Firebird-Java] Re: Only real types in FBDatabaseMetaData.getTypeInfo

> When I looked at this and implemented the BIGINT support, I didnt see

> anything in the spec or javadocs that indicated that the Type returned from

> a column should be the same as that used to get the sql to create the

> column. In other words, I think the Type to db-specific sql types can be

> many to one. Do you have some evidence that the spec authors intend

> otherwise? I don't remember too clearly but I thought there were some

> hints that the relation between Type and db-specific sql type could be

> many-many.

>

> I would think a more generally plausible algorithm for you would be to

> compare the db specific sql for the Type from the column and the Type from

> typeinfo and decide if they are compatible.

>

> does your algorithm work for any other databases?

>

> thanks

> david jencks

>

> On 2002.11.18 12:05:17 -0500 sp64_asaon wrote:

> > --- In Firebird-Java@y..., David Jencks <davidjencks@d...> wrote:

> > > Can you please explain the problem you are having in considerably

> > more

> > > detail?

> >

> > I would like to explain it on the hand of a pseudo code extraction:

> >

> > // Returns database specific info about the given data type.

> > MyTypeInfo MyMetaData.getTypeInfo(int type);

> >

> > // Returns info about the most matching data type

> > // supported by the underlying database

> > MyTypeInfo MyMetaData.getSupportedType(int type) {

> > MyTypeInfo ti = null;

> > switch (type) {

> > ...

> > case Types.BIGINT:

> > if ((ti = getTypeInfo(Types.BIGINT) == null)

> > if ((ti = getTypeInfo(Types.DOUBLE) == null)

> > ti = getTypeInfo(Types.INTEGER);

> > break;

> > ...

> > }

> > return ti;

> > }

> >

> >

> > // Update-Strategy:

> >

> > columnType = X; // retrieved from getColumnns

> > desiredType = Types.BIGINT;

> > supportedType = myMetaData.getSupportedType(desiredType);

> >

> > boolean haveToUpdate = columnType != supportedType

> > // and other criteria depending on the data type:

> > // || columnSize < neededSize

> > // || ...

> >

> >

> > Example with your changes:

> > columnType = Types.NUMERIC;

> > desiredType = Types.BIGINT;

> > supportedType = Types.BIGINT;

> > haveToUpdate = true;

> > // columnType will never match the 'erroneosly'

> > // reported supported type ==> repeated updates

> >

> > Example without your type entry for BIGINT:

> > columnType = Types.DOUBLE;

> > desiredType = Types.BIGINT;

> > supportedType = Types.DOUBLE;

> > haveToUpdate = false;

> > // columnType matches the mapped supported type

> >

> >

> > Best regards

> > Stephan

> >

> >

> > To unsubscribe from this group, send an email to:

> > Firebird-Java-unsubscribe@yahoogroups.com

> >

> >

> >

> > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

> >

> >

> >

> >

> >

>

>

> To unsubscribe from this group, send an email to:

> Firebird-Java-unsubscribe@yahoogroups.com

>

>

>

> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

>

>

>