Hi,

Situation: We use "iAnywhere for Oracle driver" via ODBC in order to perform mobilink synchronizations. When performing inserts in the database there are triggers that convert decimals in to characters via a TO_CHAR() command (This is just something we need for our business)

The culture/currency this session is using is American => so the decimals that are converted to text look like "2.5" Though, when we manually insert data via a personal IDE and these trigger fire => The tekst looks like "2,5" because in the local culture comma's and dots are used the other way around....Also the Database settings have a local culture in the NLS_Settings.

We currently cannot pinpoint why Mobilink is using the American culture.

Does anyone know where "iAnywhere driver for oracle" gets it's culture from? Is this from the OS or from somewhere else?

Thanks in advance !

asked 21 May '15, 10:28

vdcey's gravatar image

vdcey
654222839
accept rate: 33%

It is not clear what is really going on here but since you are scripting the upload for this you can always consider adding your TO_CHAR() conversion logic to your upload_insert script yourself.

Otherwise . . . Regional/NLS setting may not apply at all but to know for sure one would need to know more about your schemas (both remote and consolidated), trigger design, and upload_insert scripts for this table.

(21 May '15, 13:49) Nick Elson S...

Because the upload_insert script is called by a user/session with american culture, it would give the same result. We don't know why the mobilink session is using an american currency while the nls_settings currency of the Oracle DB is Netherlands. Please correct me if I misunderstood your comment. Regards,

(22 May '15, 01:58) vdcey

This is intentional behaviour - see CR 480434. MobiLink cannot handle data that uses a comma for values - attempting to synchronize values with such a format to a SQL Anywhere or UltraLite database results in an error.

In order to avoid this problem, the "SQL Anywhere - Oracle" ODBC driver will always executes the following option upon connection to Oracle to set the format back to using a period as a decimal separator for its own connections:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,"

In addition to this, the SQL:2011 standard formally defines an exact numeric literal as:

<exact numeric literal> ::= <unsigned integer> [ <period> [ <unsigned integer> ] ] | <period> <unsigned integer>

This is because other SQL statements (e.g. INSERT) use a comma as a value separator, so it can't be used to express decimal points in general.

permanent link

answered 22 May '15, 15:12

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

1

Wow - thanks. I didn't think anywone would be able to answer this question. Good to know ! Thanks for looking this up. This gives us something to rely on. This way we know that this option will Always be checked when using this driver.

(26 May '15, 09:08) vdcey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×295
×125
×27
×10
×8

question asked: 21 May '15, 10:28

question was seen: 968 times

last updated: 26 May '15, 09:37