Pro*C generated code truncating TO_DATE results incorrectly
Date: 15 Aug 2001 12:05:00 -0700
Message-ID: <5dac3ab3.0108151105.eddb658_at_posting.google.com>
We are moving from Informix to Oracle, and one of the many things I liked about Informix was the ease of manipulating date/time values in C. I looked around and found the trick of subtracting a date from 1-1-1970 to get number of minutes or seconds as a base reference value. However, I am getting very strange results when SQL statements involving TO_DATE (xxx) * 1440 into long host variables. We opened a case (#1757282.999) and was told that this was a C-issue and not an oracle issue. Please review the following data that was sent to Oracle and show me where this is a C issue versus a Pro*C or server issue.
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. The float values have no
residual fractional parts, however selecting into a long returns one
less than the integer equivalents of the doubles. If you change this
to seconds, you still get an error of 1.
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 - 21:05:00 CEST