Informix

nested-group-icon.png

DB2

Expand all | Collapse all

JDBC: Can´t store big Intervals

  • 1.  JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 04:03 AM

    Hi!

    I have a problem with an interval field.
    Currently I have a field "INTERVAL HOUR(3) TO MINUTE".

    Our customer wants to store 2600 hours in this field (estimated time for a project).

     

    I changed the field to "INTERVAL HOUR(5) TO MINUTE" and thought, it´s done.

    But,when I try to store 2600 hours over JDBC, I get the Error "java.sql.SQLException: Intervals or datetimes are incompatible for the operation.".

     

    So I started a loop to check, where the limitations are:

    • From 0 to 8639999 seconds (2399 hours, 59 minutes, 59 seconds), everything works as expected
    • From 8640000 to 22118399 seconds (6143 hours, 59 minutes, 59 seconds), I get the SQLException: "Intervals or datetimes are incompatible for the operation."
    • From 22118400: I can store the value, but I get the wrong value returned.
      • Insert with 22118400 is ok, But I get 0 as Select-Result.
    Tested with 14.10.FC5, JDBC-VERSION=4.50.JC3 (I also tried 4.50.JC4W1).

    My Test-Code (Delete all rows; Insert value; select and check the row).
            this.connection = C_glob.getSQLContext().getConnection();
            
            //create table tjs_intervalhmsX1 (col1 INTERVAL HOUR(5) TO MINUTE)
            PreparedStatement insertStmt = connection.prepareStatement("INSERT INTO " + table + "(col1) VALUES (?) ");
            PreparedStatement selStmt = connection.prepareStatement(" SELECT col1 FROM " + table + "");
            PreparedStatement delStmt = connection.prepareStatement(" DELETE FROM " + table + "");
    
            int startValue = 0;
            for (int totalSeconds = startValue; totalSeconds < Integer.MAX_VALUE; totalSeconds++) {
                delStmt.execute(); //Delete all Rows
                
                //Insert the value
                IntervalDF valueInsert = new IntervalDF(totalSeconds, 0);
                insertStmt.setObject(1, valueInsert);
                try {
                    insertStmt.execute();
                } catch (SQLException x) {
                    System.out.println("Error: " + totalSeconds + ": " + x); //8640000;2400:00:00;java.sql.SQLException: Intervals or datetimes are incompatible for the operation.
                }
    
                //Check the result:
                try (ResultSet res = selStmt.executeQuery()) {
                    if (res.next()) {
                        IntervalDF valueSelect = (IntervalDF) res.getObject(1);
                        
                        if (valueSelect.equals(valueInsert)) {
                            System.out.println("Diff: " + valueSelect + " <> " + valueInsert);
                        } else {
                            System.out.println("OK: " + totalSeconds);
                        }
                    }
                }
            }

    Should I define the field in an other way?
    Or is this really a JDBC-Bug (I´m wondering, that this bug doesn´t occur more often).

    My ugly workaround is currently, to execute the Insert with 0 and than I execute an update over JDBC:
    "UPDATE tablexy set co1='2600:00' WHERE id=?"​


    ------------------------------
    Kind Regards
    Stefan
    ------------------------------


  • 2.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 08:12 AM
    Time to open a PMR with IBM!

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



  • 3.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 08:15 AM

    Hi,

    I think the problem is cause by the method used to create the Interval parameter.

    IntervalDF valueInsert = new IntervalDF(totalSeconds, 0);

    Without any info about the Interval, the JDBC driver will create a DAY to FRACTION(5) so for a long value like '863999' the parameter will be sent to the server as

    99 23:59:58:00000

    and the server will cast that to a hour to fraction value.

    For a long '8640000' the value the client sent is:

    100 00:00:00:00000

    which is not valid for the default DAY TO FRACTION(5) (which is the same as a DAY(2) TO FRACTION(5)) as the default is 2 digits.
    You can see the problem with dbaccess:

    > select "99 00:00:00.00000"::interval day to fraction(5) from table(set{1});

    (constant)

    99 00:00:00.00000

    1 row(s) retrieved.

    > select "100 00:00:00.00000"::interval day to fraction(5) from table(set{1});

    1261: Too many digits in the first field of datetime or interval.
    Error in line 1
    Near character position 74
    >

    > select "100 00:00:00.00000"::interval day(3) to fraction(5) from table(set{1});

    (constant)

    100 00:00:00.00000

    1 row(s) retrieved.

    >

    To be able to insert those long values, you need to use a different constructor and specify a qualifier,
    so the driver will bind/send the values as the server expects. Something like:

    //valueInsert = new IntervalDF(totalSeconds, 0);
    valueInsert = new IntervalDF(totalSeconds, 0,
       com.informix.lang.Interval.getQualifier(
          (byte) 8,
          com.informix.lang.Interval.TU_HOUR,
          com.informix.lang.Interval.TU_MINUTE
        )
     );


    The 4 parameter (8) is the size.

    With that I can insert all values.

    OK: 8639990
    OK: 8639991
    OK: 8639992
    OK: 8639993
    OK: 8639994
    OK: 8639995
    OK: 8639996
    OK: 8639997
    OK: 8639998
    OK: 8639999
    OK: 8640000
    OK: 8640001
    OK: 8640002
    OK: 8640003
    OK: 8640004
    ....

    > select * from tjs_intervalhmsX1;


    col1

    2400:05

    1 row(s) retrieved.

    > select * from tjs_intervalhmsX1;


    col1

    2402:42

    1 row(s) retrieved.

    >

    You can find more information about the IntervalDF constructors and Intervals in general here:

    https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_145.htm#ids_jdbc_145
    https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.sqlr.doc/ids_sqr_123.htm

    Give it a try ;)

    Javier



    ------------------------------
    Javier Sagrera
    ------------------------------



  • 4.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 09:03 AM
    Hi!

    Thanks for that.

    But this means for the general solution:
    1. Read the exact datatype-definition from database (before insert/update)
    2. When the value in some situations is greater than value XY, then I should use an other constructor.
      (There are many unknown combinations to check).

    I´ll open an PMR and then lets see, what the support-team will tell us.



    ------------------------------
    Kind Regards
    Stefan
    ------------------------------



  • 5.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 09:21 AM

    You don't need to use a different constructor based on the value, just use the proper one all the time.

    My take is that is always better to use the correct data types from the application side and not assume the driver (or the server) will be able to convert the data.

    If your column is defined as INTERVAL HOUR(3) TO MINUTE, you must write the application to use that specific type. The driver can't always know which Interval flavor it needs to use, that's one of the reasons why there are several constructors for the Interval classes.



    ------------------------------
    Javier Sagrera
    ------------------------------



  • 6.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 08:27 AM
    Not a Java person but are you sure you are defining the variable correctly ?  With datetime and timeseries I know there are additional steps to get the data to be 'seen' correctly or that is why the Java person I was working with told me

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 7.  RE: JDBC: Can´t store big Intervals

    Posted Fri January 15, 2021 09:05 AM
    Hi!

    I hope so (this part of the code is about 20 years old).
    It worked good (but this was the first column with more than 3 digits).

    ------------------------------
    Kind Regards
    Stefan
    ------------------------------