culture settings in text to decimal casting

[pavel-wbr]

I would like ask about culture settings and how it influence casting from text value to decimal number.

We have an automated import from external system using delimited files and pre-stage table in application database. From the file the data is loaded as a string and from the string it is translated to the decimal value in connector. We use the query here (see that there is explicitlely specified a culture to be used for a casting):

select
IFRS_account
, ledger_id
, currency
, pairing_code
, ICS_code
, Partner_description
, PARSE (local_ccy_amount as decimal(26,5) using 'en-US') as local_ccy_amount
, PARSE (transaction_ccy_amount as decimal(26,5) using 'en-US') as transaction_ccy_amount
from [dbo].[MyExternalTable]
where importGUID = '{import_GUID}'
and _tbValid = 'Y'
and coalesce(ICS_code, '') = ''

During the testing we found that culture settings for user have an influence on the results. How is that possible? I thought this is a job for a server side and there should be no effect of client application culture settings. How to avoid that?

[mod edit: removed personal info]

Thank you and best regards,

[JackLacava]

The way in which Culture settings affect OneStream can be surprising. In terms of workarounds, they will depend on what you're doing; in this case, you could explicitly insert the necessary localization details in your connection string when you open the connection.

BTW: in future, you probably don't want to post explicit names of your environments here, this forum is public. Only send that sort of thing to Support.

[pavel-wbr]

Thank you for censoring it Jack, it was a copy-paste from support ticket, I forgot to delete that.

I was searching how to change the connection string to add there a localization but was not successful. Could you please provide a link where this parameter of connection string is described? 

[JackLacava]

So, if you're executing that SQL in a Connector, you must be opening a database connection somewhere; I assume you are using the standard Onestream facilities for this, rather than custom stuff. In order to connect with that, you have to specify the connection details, which is done with a string of key=value parameters:

• If you are using CreateCustomExternalDbConnInfo, you specify the connection string as a parameter in that call
• If you are using CreateExternalDbConnInfo or CreateDbConnInfo, the connection string has been specified in the OneStream Application Server Settings using the related utility (described in the Installation guide, search for "Defining Database Connections Manually"). To build the initial string, you can leverage the OneStream Database Configuration utility, which has a wizard to connect to databases and allows you to generate a connection string.

The actual parameters you can pass in that string should be the ones described at this link , which include a Language option and a Regional switch that I think could influence things.

To be honest, I wouldn't mess with all this. If the explicit conversion in sql works, just keep using that; if you are pulling those numbers from a DataTable object to convert to strings, just call .ToString on them with culture-specific parameters to get what you want, e.g. myDecimal.ToString("N3", CultureInfo.InvariantCulture) will always give you "1,234.123" (because InvariantCulture is based on US-English - if you want something else you can use CultureInfo.getCultureInfo("some culture") instead of CultureInfo.InvariantCulture). See Decimal.ToString for more details.

This is all generic advice, to be more precise we would have to see the actual connector code that executes that statement, and maybe what happens when you change things.

[pavel-wbr]

Hi Jack, do you have any news to my problem?
Thanks, 

Pavel

[pavel-wbr]

Hi Jack,

Current connection string 

Data Source=censored.database.windows.net;Initial Catalog=censored;User ID=censored;Password=censored;Max Pool Size=3000;Connect Timeout=60;Encrypt=True;TrustServerCertificate=False

Tried adding Regional=no 

ended with an error: Invalid Connection String. Keyword not supported: 'regional'.

Tried adding Language=English

even thow the descrioption of this parameter was not that promissing. Did not raised an error but returned the numbers with wrong conversion.