12-07-2022 06:17 AM - last edited on 12-07-2022 07:06 AM by JackLacava
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,
12-07-2022 07:05 AM - edited 12-07-2022 07:08 AM
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.
12-07-2022 08:25 AM
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?
12-07-2022 07:23 PM - edited 12-07-2022 07:25 PM
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:
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.
12-20-2022 12:12 PM
Hi Jack, do you have any news to my problem?
Thanks,
Pavel
12-08-2022 05:35 PM - edited 12-08-2022 06:11 PM
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.