• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

real number returned with comma as decimal delimiter

Explorer ,
Apr 17, 2012 Apr 17, 2012

Copy link to clipboard

Copied

Hi,
I have CF9 installed on W7 using an Access database.
In a table I have a currency value of
-488.7

The windows is French, but I have set the delimiter for both the decimal values AND currency values to dot instead of comma.

Though I get this error in CF:

The value -488,7 cannot be converted to a number.

CF is receiving the value with a comma instead of a dot.

- If I browse the table under Access, the value is correctly displayed with a dot.

- If I modify the column type to teTEXT instead of real or currency, the value is converted to text with a dot, and there is no error in CF.

Obviously a conversion from dot to comma is made in some other layer, but where?

I've checked in the ODBC connection and found nothing.

Any idea?

TOPICS
Database access

Views

1.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Explorer , Apr 18, 2012 Apr 18, 2012

>>If you do a simple query and cfdump the result, do you get a period or comma?

Yes I do.

But I finaly found the problem.

The query is produced by a UNION, and in one of the sub SELECTs, the column was set to '0' instead of 0. This tiggered a converstion to text for the same column in the next SELECT, probably in the ODBC driver, and this conversion implied the use of a comma instead of a dot. For some unknown reason, it follows the rule for French because the version of Windows is French, but byp

...

Votes

Translate

Translate
LEGEND ,
Apr 18, 2012 Apr 18, 2012

Copy link to clipboard

Copied

If you do a simple query and cfdump the result, do you get a period or comma?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 18, 2012 Apr 18, 2012

Copy link to clipboard

Copied

>>If you do a simple query and cfdump the result, do you get a period or comma?

Yes I do.

But I finaly found the problem.

The query is produced by a UNION, and in one of the sub SELECTs, the column was set to '0' instead of 0. This tiggered a converstion to text for the same column in the next SELECT, probably in the ODBC driver, and this conversion implied the use of a comma instead of a dot. For some unknown reason, it follows the rule for French because the version of Windows is French, but bypasses the settings in Windows.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 18, 2012 Apr 18, 2012

Copy link to clipboard

Copied

You mean you had a query like this:

select some_text_field, etc

union

select some_numeric_field, etc

that didn't error out?

I just tried that in redbrick and sql server and in both cases, they crashed.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 18, 2012 Apr 18, 2012

Copy link to clipboard

Copied

>>You mean you had a query like this:

It was rather:

select '0' AS some_numeric_field, etc

union

select some_numeric_field, etc

It doesn't error out, but it converts the numeric field as text.

This is with an ODBC Access driver.

Kind of weird indeed, you're right, it should cause an error.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 19, 2012 Apr 19, 2012

Copy link to clipboard

Copied

LATEST

Kind of weird indeed, you're right, it should cause an error.

Databases frequently do implicit conversion on string values.  So I would not expect an error unless the string could not be converted to the numeric data type its being merged with. That is how it works in sql server. For example, this automatically converts the string '0' to numeric (or whatever data type is used)

         SELECT '0' AS Test

         UNION

         SELECT cast(10.50 as numeric(10,2))

Message was edited by: -==cfSearching==-

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation