Test SQL statement facility - wrong on CS3 OS X

[172256]

I'm viewing the same Access database data using DW CS3 on Mac OS X 10.4.11 and using DW on a PC running XP (IIS5/asp) - as part of a tutorial http://www.sawmac.com/missing/dwmx2004/tutorials/index.html

I've noticed during the recordset creation process in DW the Test button displays SQL data that simply isn't in the database - but it only does this in the Mac version of DW. The PC version shows correct data.

Example, a column of Access price data when viewed through Test on the PC shows $29.99, the correct value as stated in the database; but when viewed during Test on the Mac is shown as $29.990000000000002. This error only applies to some prices in the column - the rest are fine! Changing the data to $29.95 in the db yields $29.950000000000003. Nuts!

Even when viewed embedded in a page on any browser - even on a Mac - the data is displayed correctly, so this appears to be a fault in DW for Mac's SQL Test facility. Same problem occurs in DW 8 on Mac.

I'm new to integrating DW with databases - is this a common problem? I've searched the net and couldn't find any record of the issue.

[Newsgroup_User]

172256 wrote:
> I'm viewing the same Access database data using DW CS3 on Mac OS X 10.4.11 and
> using DW on a PC running XP (IIS5/asp) - as part of a tutorial
> http://www.sawmac.com/missing/dwmx2004/tutorials/index.html
>
> I've noticed during the recordset creation process in DW the Test button
> displays SQL data that simply isn't in the database - but it only does this in
> the Mac version of DW. The PC version shows correct data.
>
> Example, a column of Access price data when viewed through Test on the PC
> shows $29.99, the correct value as stated in the database; but when viewed
> during Test on the Mac is shown as $29.990000000000002. This error only applies
> to some prices in the column - the rest are fine! Changing the data to $29.95
> in the db yields $29.950000000000003. Nuts!
>
> Even when viewed embedded in a page on any browser - even on a Mac - the data
> is displayed correctly, so this appears to be a fault in DW for Mac's SQL Test
> facility. Same problem occurs in DW 8 on Mac.
>
> I'm new to integrating DW with databases - is this a common problem? I've
> searched the net and couldn't find any record of the issue.

Never heard of this one before. What happens when you display the field
on your page? Does it give the correct data?

Steve

[Newsgroup_User]

172256 wrote:
> I'm viewing the same Access database data using DW CS3 on Mac OS X 10.4.11 and
> using DW on a PC running XP (IIS5/asp) - as part of a tutorial
> http://www.sawmac.com/missing/dwmx2004/tutorials/index.html
>
> I've noticed during the recordset creation process in DW the Test button
> displays SQL data that simply isn't in the database - but it only does this in
> the Mac version of DW. The PC version shows correct data.
>
> Example, a column of Access price data when viewed through Test on the PC
> shows $29.99, the correct value as stated in the database; but when viewed
> during Test on the Mac is shown as $29.990000000000002. This error only applies
> to some prices in the column - the rest are fine! Changing the data to $29.95
> in the db yields $29.950000000000003. Nuts!
>
> Even when viewed embedded in a page on any browser - even on a Mac - the data
> is displayed correctly, so this appears to be a fault in DW for Mac's SQL Test
> facility. Same problem occurs in DW 8 on Mac.
>
> I'm new to integrating DW with databases - is this a common problem? I've
> searched the net and couldn't find any record of the issue.
>
>
What is the data type of the column? It's good practice to use INT for
currency.
(2995 for 29.55), that will eliminate floating point errors.
Mick

[Newsgroup_User]

.oO(172256)

>I'm viewing the same Access database data using DW CS3 on Mac OS X 10.4.11 and
>using DW on a PC running XP (IIS5/asp) - as part of a tutorial
> http://www.sawmac.com/missing/dwmx2004/tutorials/index.html
>
> I've noticed during the recordset creation process in DW the Test button
>displays SQL data that simply isn't in the database - but it only does this in
>the Mac version of DW. The PC version shows correct data.
>
> Example, a column of Access price data when viewed through Test on the PC
>shows $29.99, the correct value as stated in the database; but when viewed
>during Test on the Mac is shown as $29.990000000000002. This error only applies
>to some prices in the column - the rest are fine! Changing the data to $29.95
>in the db yields $29.950000000000003. Nuts!

Not nuts, but expected. Most fractions cannot be exactly represented in
binary form. Their precision is always limited and depends not only on
the software, but also on the hardware. At some point there has to be a
cut or a rounding.

There's a nice example in the PHP manual:

floor((0.1+0.7)*10) // floor() rounds down to the next integer

The expected result here would be 8 without any question, but in many
(if not most) cases you would get 7, because the internal representation
of the result will be something like 7.99999999999...

I guess that something similar happens in your case. When working with
floats, there are some rules you have to follow. But in your case there
is a better solution - avoid the floats. Store your prices as cents in
an INT field or use the DECIMAL type, if your database supports it. Then
do the floating point formatting just for the browser output.

Micha

[172256]

Thanks for all your help - the problem turned out to be an error in the database supplied with the Tutorial (O'Reilly).