Set a PC environment based on a Country name..

[Ian Branch 127]

Hi Team,

I don't even know if this is practical or possible but it probably is given the capability of Delphi.

I have a Multi-User Customer who is more than a little negligent in setting up his workstations with the correct Language/Region settings. :-(

In their Customer Table is a field called Country.  In it is the Company's Country name.  e.g. Australia, India, Fiji, Angola, etc.

What I would like to do is to read that name and based on it retrieve from somewhere the relevant Language/Regional settings and set Delphi's Settings accordingly, such that no matter what the Workstation settings are, the Delphi App is working with and according to the Country parameters.

Thoughts/suggestions appreciated.

Regards,

Ian

[Fr0sT.Brutal 900]

You mean, you want a relation between Country name and app's locale? Why not use the system's locale? If your customer is Chinese living in Australia (I heard there are many) he probably wants Chinese labels not English. I'd let user decide what language he prefers.

[Sherlock 663]

@Fr0sT.Brutal

7 hours ago, Ian Branch said:

I have a Multi-User Customer who is more than a little negligent in setting up his workstations with the correct Language/Region settings. 😞

[Ian Branch 127]

Actually Language I can live with, it is the date and currency differences between the database and their PC that is causing the grief.

e.g. The Database is expecting dd/mm/yyyy and their PC is set for dd-MMM-yy.

They are English in any case.

Ian

[Fr0sT.Brutal 900]

10 minutes ago, Ian Branch said:

Actually Language I can live with, it is the date and currency differences between the database and their PC that is causing the grief.

e.g. The Database is expecting dd/mm/yyyy and their PC is set for dd-MMM-yy.

They are English in any case.

Ian

Aha! That's the very issue! And I'd say you trying to cure a consequence instead of a cause. One of developer rules I discovered is to NEVER ever rely on any regional settings other than for displaying purposes. Especially where any kind of communication between two PCs involved. In your case, DB should never accept or send a timestamp as a string. Just never. If there's no such ability, just carve timestamp format in stone and use it everywhere for internal communication. Of course, for display purposes use local settings (f.ex., American mm/dd/yy format makes other people crazy).

 

@Sherlock it wasn't clear enough

Edited March 17, 2020 by Fr0sT.Brutal

[Ian Branch 127]

3 minutes ago, Fr0sT.Brutal said:

DB should never accept or send a timestamp as a string

Database Dates are always as datetime.  Where it falls down is when creating a filter.

e.g.

'13-Mar-20' is not a valid date.  This is from a Calendar component picking up the PCs short date format.

'13/03/2020' is a valid date.

So, my objective is to force the, dates in this case, to the same format as required.

I get the same issue with Currency symbols, etc.

[Cristian Peța 103]

Don't use date as string in SQL filters. Pass dates as parameters. That calendar component should give you the date as TDate or something, not string.

[A.M. Hoornweg 144]

20 minutes ago, Cristian Peța said:

Don't use date as string in SQL filters. Pass dates as parameters. That calendar component should give you the date as TDate or something, not string.

Alternatively, if you already have a lot of code executing SQL statements as strings, just create a new tFormatsettings with the correct formats for date, time and decimalseparator for your database.  

 

Many Delphi string routines such as Format(), DateTimeToStr() etcetera have an optional Parameter of type tFormatsettings which will then apply this format.

 

 

[Fr0sT.Brutal 900]

2 hours ago, Ian Branch said:

13-Mar-20' is not a valid date.  This is from a Calendar component picking up the PCs short date format.

'13/03/2020' is a valid date. 

Yes. So just get the date from Calendar locally as Tdatetime or TIMESTAMP or whatever and send it to DB using parameters.

Otherwise

3 hours ago, Fr0sT.Brutal said:

just carve timestamp format in stone and use it everywhere for internal communication

 

[aehimself 396]

This is why I use UNIX timestamps adjusted to GMT without DST (where applicable) as DateTime values in databases lately. Store them as numbers, read them as numbers, handle them as numbers.

Edited March 19, 2020 by aehimself

[Vandrovnik 214]

17 minutes ago, aehimself said:

This is why I use UNIX timestamps adjusted to GMT without DST (where applicable) as DateTime values in databases lately. Store them as numbers, read them as numbers, handle them as numbers.

And what data type do you use? https://en.wikipedia.org/wiki/Year_2038_problem

[aehimself 396]

58 minutes ago, Vandrovnik said:

And what data type do you use? https://en.wikipedia.org/wiki/Year_2038_problem

I'm paranoid. Even for IDs expected to be in the million range I use UInt64 🙂

[Fr0sT.Brutal 900]

12 hours ago, aehimself said:

This is why I use UNIX timestamps adjusted to GMT without DST (where applicable) as DateTime values in databases lately. Store them as numbers, read them as numbers, handle them as numbers.

*scratching my head* any database internally uses its own timestamp format that converts without problems to Delphi TDateTime by DB libs. I see no sense in replacing this mechanism by custom one that only your apps know how to deal with, so f.ex. examining table data with an admin tool becomes a real pain.

[aehimself 396]

20 minutes ago, Fr0sT.Brutal said:

*scratching my head* any database internally uses its own timestamp format that converts without problems to Delphi TDateTime by DB libs. I see no sense in replacing this mechanism by custom one that only your apps know how to deal with, so f.ex. examining table data with an admin tool becomes a real pain.

True, from this perspective it's useless. My problem with TDateTime values is that they are local; once you decide to go international you'll have to change the codebase. I mean for example a record was created at 21:00:00 in China. If someone from the US checks it, it will only tell that the record was created at 21:00:00 but that will be incorrect as 21:00:00 in China is 03:00:00 in the US. As I'm thinking in UNIX timestamps only lately I have a library to take care of these conversions for me in all of my applications.

As for the database storage, the only important factor is that your app should know how to handle this data; but most DB engines have UNIX encoding functions (e.g. FROM_UNIXTIME in MySQL). I also wrote my own database explorer with these built-in:

 

 

 

But, I don't want to hijack the topic. @Ian Branch If you can not rely on the OS, the easiest solution is if you add a drop-down menu in your settings to choose the appropriate location. If you want an automatic solution, my idea would be to use an IP to location API to detect the country the PC is connecting to the Internet from. This method won't work if they are using VPN, though; so maybe you can just add "Automatic" in the drop-down. Normally they are being detected, but they can override it in case.

[Fr0sT.Brutal 900]

42 minutes ago, aehimself said:

My problem with TDateTime values is that they are local; once you decide to go international you'll have to change the codebase.

First, nothing in TDateTime could be "local". It's just the same shift from some constant point as any other timestamp. It's up to client app to generate this shift either in local timezone or in UTC (which is what I always recommend, just like UTF8 in string encoding).

Second, there are TIMESTAMP_WITH_TIMEZONE type in most of DB engines. It requires some additional handling though and not mapped as-is to TDateTime for obvious reasons.

48 minutes ago, aehimself said:

maybe you can just add "Automatic" in the drop-down. Normally they are being detected, but they can override it in case.

Just like most of web-sites behave today. "We determined your city as %CityName, is that correct?" and leave ability to set whatever other city user wants

[aehimself 396]

11 hours ago, Fr0sT.Brutal said:

First, nothing in TDateTime could be "local". It's just the same shift from some constant point as any other timestamp. It's up to client app to generate this shift either in local timezone or in UTC (which is what I always recommend, just like UTF8 in string encoding).

Second, there are TIMESTAMP_WITH_TIMEZONE type in most of DB engines. It requires some additional handling though and not mapped as-is to TDateTime for obvious reasons.

I completely agree with you on this one. this is just how I started, and later continued to think. We still have to agree that in a Client-Server architecture, where you have to create the filter on the Client (and have no access to dataset parameters) working with a number is easier. I already had enough of TO_DATE and CONVERT for a lifetime.

11 hours ago, Fr0sT.Brutal said:

Just like most of web-sites behave today. "We determined your city as %CityName, is that correct?" and leave ability to set whatever other city user wants

Well, yes - that is my idea. Convince, but still controllable. And as an end user I like to have control