Pro*C and TO_DATE queries into long variables

From: Cary Gerber <carygerber_at_yahoo.com>
Date: 15 Aug 2001 13:05:27 -0700
Message-ID: <5dac3ab3.0108151205.7c74efc@posting.google.com>

I searched on how to store dates in minutes or seconds since 1970, and found how to do this from discussion groups like these. However, when I implement C code to do certain date conversion queries into long variables, I get incorrect results. We opened a case number, but the response was that this was not a bug, development has known of this behavior for years, but they say it is not a bug, therefore there is no issue.

Please review the following. If I do not round the intermediate results in the query, I get an incorrect answer into a long host variable, but any double/float variable gets the right answer. Oracle is truncating incorrectly in regards to long storage.

Please help me understand why I have to jump thru hoops to get the correct answer, when all 3rd party line editors (SQL processors, like PL/SQL, etc) return the correct answers.

It is not a problem with C-conversion to integer (long). It is a problem with whatever PRO*C is generating. I have changed the query to return the same value 3 times, once into different data types of C. I further do a C-conversion of a float to integer and do not get the same integer value that the query returns into my int host variable.

Connect [rcWfh]XZ%h\knc_b^cnnbgr] -> ediuser/ediuser Seconds without Round -> 16612342.000000, 16612342.000000, 16612341 (returned from query into long host variable) C conversion to int -> 16612342 (same variable, just assigned the int. truncated value from the double float above) Seconds with Round -> 16612342.000000, 16612342.000000, 16612342 C conversion to int -> 16612342

/***** INCLUDES HERE ******/
int main(int argc, char *argv[])
{

    int      count = 0;
    int      ret = 0;
         
    EXEC SQL begin declare section;
        char        connect[16];
        EVENT_DS    one_evt;

        /**** TEST ****/
        double dsecs_since_70;
        float  fsecs_since_70;
        long   isecs_since_70;

    EXEC SQL end declare section;

        /****** CONNECT HERE *****/     EXEC SQL set transaction isolation level read committed;     if(!SQLOK()) exit(1);

    /***** TEST *****/
    exec sql select

        (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440,

        (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440,

        (TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss') - TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440

        into :dsecs_since_70, :fsecs_since_70, :isecs_since_70
        from DUAL;

    if (!SQLOK()) exit(1);

    printf("Seconds without Round -> %lf, %f, %ld\n", dsecs_since_70, fsecs_since_70, isecs_since_70);

    isecs_since_70 = (long)dsecs_since_70;     printf("C conversion to int -> %ld\n", isecs_since_70);

    exec sql select

        ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440),

        ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440),

        ROUND((TO_DATE('02-08-2001 08:22:00','DD-MM-YYYY hh24:mi:ss')
- TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY hh24:mi:ss')) * 1440)

        into :dsecs_since_70, :fsecs_since_70, :isecs_since_70
        from DUAL;

    if (!SQLOK()) exit(1);

    printf("Seconds with Round -> %lf, %f, %ld\n", dsecs_since_70, fsecs_since_70, isecs_since_70);

    isecs_since_70 = (long)dsecs_since_70;     printf("C conversion to int -> %ld\n", isecs_since_70); } Received on Wed Aug 15 2001 - 15:05:27 CDT