Agregate functions results being truncated

I've encountered a problem using VB5 SP3, ADO 2.0, Intersolv 3.01 driver, Informix 7.30.UC5 The problem is sometime values are returned incorrectly, seemingly only returning the most significant digit. For example, select count(*) from ... when run from DB-Access will say 50, and retrieving the same value in VB will say 5. This also seems to be dependent on what the value is that gets returned, or which rows are being selected, as some queries always return the correct value, and some always return the same incorrect value. I've been hit by it with both the count(*) and sum(field) aggregate functions, and the problem seems to be a problem with the type. prepending the functions with ''|| (as in select ''||count(*) from ... ) returns the correct values, except as their string representations, and that is the work-around I've had to use to continue working. I was wondering if anyone who has encountered this problem before is able to tell me which component is at fault. I suspect either ADO or the 3.01 driver, but I haven't found this behaviour mentioned anywhere, or mentioned as fixed in ADO 2.1 or Merant's 3.50 driver. Any help would be appreciated, Stephen.

Stephen Denne wrote: > > I've encountered a problem using VB5 SP3, ADO 2.0, Intersolv 3.01 driver, > Informix 7.30.UC5 > > The problem is sometime values are returned incorrectly, seemingly only > returning the most significant digit. > > For example, select count(*) from ... > when run from DB-Access will say 50, and retrieving the same value in VB > will say 5. > > This also seems to be dependent on what the value is that gets returned, or > which rows are being selected, as some queries always return the correct > value, and some always return the same incorrect value. > > I've been hit by it with both the count(*) and sum(field) aggregate > functions, and the problem seems to be a problem with the type. prepending > the functions with ''|| (as in select ''||count(*) from ... ) returns the > correct values, except as their string representations, and that is the > work-around I've had to use to continue working. > > I was wondering if anyone who has encountered this problem before is able to > tell me which component is at fault. I suspect either ADO or the 3.01 > driver, but I haven't found this behaviour mentioned anywhere, or mentioned > as fixed in ADO 2.1 or Merant's 3.50 driver. IDS 7.xx returns the results of aggregate functions like COUNT(*) as an Informix DECIMAL(32) data type. This is stored as a string of BASE 100 digits (or two base 10 digits) each as a binary integer stored in a char. Sounds like VB5 is not describing the query to declare data conversion to integer. The reason for the DECIMAL returns is that, especially with a COUNT(*), an IDS database can easily be too large for a 32bit integer to store the results of the function. Seems like VB is assuming an integer has been returned in Little-Endian (ie Intel) format and the DECIMAL, which is ALWAYS Big-Endian on all platforms, looks like the integer 5 because the scaling information for the DECIMAL is stored in a byte beyond the 4bytes that VB is looking at. Art S. Kagel

A few more tests leads me to think that either VB or ADO are ignoring the scale of the decimal. I've ruled out the Intersolv driver, as MSAccess functions normally using it. Example results instead of sequential numbers: ... 98 99 1 (1 * 10^2) 101 102 ... 108 109 11 (11 * 10^1) 111 112 Art S. Kagel <kagel@bloomberg.net> wrote in message news:371227FA.19BE@bloomberg.net... > Stephen Denne wrote: > > > > I've encountered a problem using VB5 SP3, ADO 2.0, Intersolv 3.01 driver, > > Informix 7.30.UC5 > > > > The problem is sometime values are returned incorrectly, seemingly only > > returning the most significant digit. > > > > For example, select count(*) from ... > > when run from DB-Access will say 50, and retrieving the same value in VB > > will say 5. > > > > This also seems to be dependent on what the value is that gets returned, or > > which rows are being selected, as some queries always return the correct > > value, and some always return the same incorrect value. > > > > I've been hit by it with both the count(*) and sum(field) aggregate > > functions, and the problem seems to be a problem with the type. prepending > > the functions with ''|| (as in select ''||count(*) from ... ) returns the > > correct values, except as their string representations, and that is the > > work-around I've had to use to continue working. > > > > I was wondering if anyone who has encountered this problem before is able to > > tell me which component is at fault. I suspect either ADO or the 3.01 > > driver, but I haven't found this behaviour mentioned anywhere, or mentioned > > as fixed in ADO 2.1 or Merant's 3.50 driver. > > IDS 7.xx returns the results of aggregate functions like COUNT(*) as > an Informix DECIMAL(32) data type. This is stored as a string of BASE > 100 digits (or two base 10 digits) each as a binary integer stored in a > char. Sounds like VB5 is not describing the query to declare data > conversion to integer. The reason for the DECIMAL returns is that, > especially with a COUNT(*), an IDS database can easily be too large for > a 32bit integer to store the results of the function. > > Seems like VB is assuming an integer has been returned in Little-Endian > (ie Intel) format and the DECIMAL, which is ALWAYS Big-Endian on all > platforms, looks like the integer 5 because the scaling information for > the DECIMAL is stored in a byte beyond the 4bytes that VB is looking > at. > > Art S. Kagel