Schema of informix tables

[Maurizio Scarpis]

Hi Thomas.

Using last version of SQL-Workbench (not sure that the problem was present in previous ones) I have some troubles with the schema of informix tables, on "datetime" and "decimal" columns (versions of Informix 11 and 12)

Here an example:

dbschema from informix (extract):

    dataora_ins datetime year to fraction(5),

    totale_fatt decimal(24,6),

wbtableschema from wb (extract):

   dataora_ins     datetime  to,

   totale_fatt     decimal(5,6),

content of syscolumns:

  colname coltype collength

  dataora_ins 10 4879

  totale_fatt 5 6150

Thanks in advance

Maurizio 

[Thomas Kellerer]

Hi Maurizio,

hmm, not sure what's going on there.

With the driver version 4.5 the driver already returns correctly "datetime year to fraction(5)" as the datatype. So I don't have do adjust anything there.

For older drivers (e.g. 3.7) that only return "datetime", I have implemented a workaround which extracts the "from" and "to" values based on the "collength".

But that works correctly for a length of 4878 for me.

So even with driver version 3.7 the following table:

create table dt_test
(
year_to_minute datetime year to minute,
year_to_fraction datetime year to fraction,
year_to_fraction5 datetime year to fraction(5),
decimal_24_6 decimal(24,6),
decimal_10_2 decimal(10,2)
);


The above then is shown like this in SQL Workbench:

COLUMN_NAME | DATA_TYPE
------------------+-----------------------------
year_to_minute | datetime year to minute
year_to_fraction | datetime year to fraction(3)
year_to_fraction5 | datetime year to fraction(5)
decimal_24_6 | decimal(24,6)
decimal_10_2 | decimal(10,2)

What does the following query return on your system?

select c.colname, c.collength, c.coltype
from informix.systables t
join informix.syscolumns c on t.tabid = c.tabid
where t.tabname = '<your table name here>'

I don't have any special handling for the decimal type, so any strange results are caused by the JDBC driver

Can you please switch the loglevel to debug, turn on "Log internal SQL statements as INFO messages", display the table definition and then send me the logfile?

Regards
Thomas

Maurizio Scarpis schrieb am 29.07.2021 um 11:30:
> Hi Thomas.
> Using last version of SQL-Workbench (not sure that the problem was present in previous ones) I have some troubles with the schema of informix tables, on "datetime" and "decimal" columns (versions of Informix 11 and 12)
> Here an example:
>

> _dbschema from informix (extract):_

>     dataora_ins datetime year to fraction(5),
>     totale_fatt decimal(24,6),
>

> _wbtableschema from wb (extract):_

>    dataora_ins     datetime  to,
>    totale_fatt     decimal(5,6),
>

> _content of syscolumns:_
>   colnamecoltypecollength
>   dataora_ins104879
>   totale_fatt56150
>
> Thanks in advance
> Maurizio

[Maurizio Scarpis]

It seems the opposite that is:

> with 4.50.2 version I have troubles

 > with elder one I have no troubles

Regards

Maurizio 

[Thomas Kellerer]

Hmm,

really strange.

as I wrote, I would need the logfile (with DEBUG level turned on) to further investigate what is going on in yor environment.

Regards
Thomas

Maurizio Scarpis schrieb am 02.08.2021 um 09:43:
> It seems the opposite that is:
> > with 4.50.2 version I have troubles
>  > with elder one I have no troubles
> Regards
> Maurizio
>
> Il giorno giovedì 29 luglio 2021 alle 11:30:48 UTC+2 Maurizio Scarpis ha scritto:
>
> Hi Thomas.
> Using last version of SQL-Workbench (not sure that the problem was present in previous ones) I have some troubles with the schema of informix tables, on "datetime" and "decimal" columns (versions of Informix 11 and 12)
> Here an example:
>

> _dbschema from informix (extract):_

>     dataora_ins datetime year to fraction(5),
>     totale_fatt decimal(24,6),
>

> _wbtableschema from wb (extract):_

>    dataora_ins     datetime  to,
>    totale_fatt     decimal(5,6),
>

[Maurizio Scarpis]

Hi Thomas.

You will find the log in the attachment.

Regards.

Maurizio