Informix

 View Only
  • 1.  User defined data type

    Posted Thu April 07, 2022 10:47 AM
    Hy

    We want to use user-defined types, but there are many different errors.

    1) After we altered the tables from decimal(16,0) to pieceround_t datatype we have issue with a case/decode expressions.  (-800 Corresponding data types must be compatible in CASE expression or DECODE function.)

    2) The strangest error is that at the return values ​​of some selects, functions, the system reports an error. SQL Error (-661) : Number of variables does not match number of values returned. The functions worked fine before we defiened the data types.

    3) Arithmetic Operators has a problem when we want to use the new datatypes. Can we override the arithmetic function with the new datatypes?



    Does anyone have experience using user defined types? Any suggestions on what to look for?

    create distinct type pieceround_t as decimal(16,0); 

    create distinct type piece_t as decimal(26,10); 
    ------------------------------------------------------------------------------------------------------------------------
    -- pieceround_t -> piece_t cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'pieceround_t_to_piece_t' );
    create procedure pieceround_t_to_piece_t( quantity pieceround_t ) returning piece_t with (not variant);

    return quantity::decimal(26,10);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- pieceround_t -> float cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'pieceround_t_to_float' );
    create procedure pieceround_t_to_float( quantity pieceround_t ) returning float with (not variant);

    return quantity::decimal(16,0);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- pieceround_t -> int cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'pieceround_t_to_int' );
    create procedure pieceround_t_to_int( quantity pieceround_t ) returning integer with (not variant);

    return quantity::decimal(16,0);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- pieceround_t -> smallint cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'pieceround_t_to_smallint' );
    create procedure pieceround_t_to_smallint( quantity pieceround_t ) returning smallint with (not variant);

    return quantity::decimal(16,0);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- pieceround_t -> varchar cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'pieceround_t_to_varchar' );
    create procedure pieceround_t_to_varchar( quantity pieceround_t ) returning varchar(255) with (not variant);

    return quantity::decimal(16,0);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- piece_t -> pieceround_t cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'piece_t_to_pieceround_t' );
    create procedure piece_t_to_pieceround_t( quantity piece_t ) returning pieceround_t with (not variant);

    if round(quantity) != quantity then
    raise exception -746, 0, 'Invalid data conversion';end if;

    return quantity::decimal(16,0);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- piece_t -> float cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'piece_t_to_float' );
    create procedure piece_t_to_float( quantity piece_t ) returning float with (not variant);

    return quantity::decimal(26,10);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- piece_t -> int cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'piece_t_to_int' );
    create procedure piece_t_to_int( quantity piece_t ) returning integer with (not variant);

    if round(quantity) != quantity then
    raise exception -746, 0, 'Invalid data conversion';
    end if;

    return quantity::decimal(26,10);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- piece_t -> smallint cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'piece_t_to_smallint' );
    create procedure piece_t_to_smallint( quantity piece_t ) returning smallint with (not variant);

    if round(quantity) != quantity then
    raise exception -746, 0, 'Invalid data conversion';end if;

    return quantity::decimal(26,10);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- piece_t -> varchar cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'piece_t_to_varchar' );
    create procedure piece_t_to_varchar( quantity piece_t ) returning varchar(255) with (not variant);

    return quantity::decimal(26,10);

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- int, smallint, float -> pieceround_t cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'to_pieceround_t' );
    create procedure to_pieceround_t( quantity decimal(26,10) ) returning pieceround_t with (not variant);

    if round(quantity) != quantity then
    raise exception -746, 0, 'Invalid data conversion';end if;

    return quantity::pieceround_t;

    end procedure;

    ------------------------------------------------------------------------------------------------------------------------
    -- int, smallint, float -> piece_t cast function
    ------------------------------------------------------------------------------------------------------------------------
    execute procedure drop_routine( 'to_piece_t' );
    create procedure to_piece_t( quantity decimal(26,10) ) returning piece_t with (not variant);

    return quantity::piece_t;

    end procedure;


    -- IMPLICIT CAST definition

    CREATE IMPLICIT CAST (pieceround_t AS piece_t WITH pieceround_t_to_piece_t);
    CREATE IMPLICIT CAST (pieceround_t AS float WITH pieceround_t_to_float);
    CREATE IMPLICIT CAST (pieceround_t AS int WITH pieceround_t_to_int);
    CREATE IMPLICIT CAST (pieceround_t AS smallint WITH pieceround_t_to_smallint);
    CREATE IMPLICIT CAST (pieceround_t AS varchar WITH pieceround_t_to_varchar);


    CREATE IMPLICIT CAST (piece_t AS pieceround_t WITH piece_t_to_pieceround_t);
    CREATE IMPLICIT CAST (piece_t AS float WITH piece_t_to_float);
    CREATE IMPLICIT CAST (piece_t AS int WITH piece_t_to_int);
    CREATE IMPLICIT CAST (piece_t AS smallint WITH piece_t_to_smallint);
    CREATE IMPLICIT CAST (piece_t AS varchar WITH piece_t_to_varchar);

    CREATE IMPLICIT CAST (integer AS pieceround_t WITH to_pieceround_t);
    CREATE IMPLICIT CAST (float AS pieceround_t WITH to_pieceround_t);
    CREATE IMPLICIT CAST (smallint AS pieceround_t WITH to_pieceround_t);


    drop cast ( pieceround_tas decimal(16,0));
    drop cast ( decimal(16,0) as pieceround_t);

    create implicit cast ( pieceround_t as decimal(16,0));
    create implicit cast ( decimal(16,0) as pieceround_t);



    ------------------------------
    Gábor Fekete
    Software Engineer
    Capital Systems
    Budapest
    ------------------------------


  • 2.  RE: User defined data type

    IBM Champion
    Posted Thu April 07, 2022 12:35 PM
    Gabor:

    First, when you create a DISTINCT type it comes with an implicit conversion back to its base type, and that's working for me. Not sure what you need to accomplish with all the other casts.

    If you need to operate on the new types with full arithmetic operations, string concatenation, etc. then you may have to define them as UDT's and write support functions for the operations. See the "IBM Informix User-Defined Routines and Data Types Developer's Guide" for details.

    The usual operations work fine. Witness:

    > create distinct type pieceround_t as decimal(16,0);  

    Distinct type created.

    > create implicit cast (pieceround_t as decimal(16,0));

    9919: Cast already exists in database.

    > create table pieces( one serial, two pieceround_t );

    Table created.

    > insert into pieces values (0, 123::decimal(16,0)::pieceround_t);

    1 row(s) inserted.

    > insert into pieces values (0, 456::decimal::pieceround_t);

    1 row(s) inserted.

    > select * from pieces;

    one  1
    two  123  

    one  2
    two  456

    2 row(s) retrieved.

    > select two from pieces;

    two  123  

    two  456  

    2 row(s) retrieved.

    > select two::decimal from pieces;

       (expression)  

    123.000000000000
    456.000000000000

    2 row(s) retrieved.

    > select two::decimal(16,0) from pieces;

       (expression)  

                123
                456

    2 row(s) retrieved.
    > select two::decimal(26,10) from pieces;

       (expression)  

     123.0000000000
     456.0000000000

    2 row(s) retrieved.

    > select two::decimal * 100 from pieces;

       (expression)  

    12300.0000000000
    45600.0000000000

    2 row(s) retrieved.




    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: User defined data type

    Posted Fri April 08, 2022 03:41 AM
    Art 

    If we didn't define the other casts, then we get the following error.

    select * from pieces where two > 123;

    -- SQL Error (-674) : Routine (greaterthan) can not be resolved.

    select decode(one, 2, 0, two) from pieces;
    -- SQL Error (-800) Corresponding data types must be compatible in CASE expression or DECODE function.


    We have a similar database for other customer, and there it's work fine more then 15 years. ( Informix versions: 9.4, 10.0, 11.5, 14.10 FC3)

    This database running with 12.10 FC7 . I think it's not a version issue.

    ------------------------------
    Gábor Fekete
    Software Engineer
    Capital Systems
    Budapest
    ------------------------------



  • 4.  RE: User defined data type

    IBM Champion
    Posted Fri April 08, 2022 06:29 AM
    Hmm. Interesting. I don't have any version as old as 14.10.FC3, but I did test in .FC6 and the results are the same. I would suggest opening a PMR with IBM. If it is a version specific bug, that's the way to get it fixed.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------