4 ms·
> Oh. Locales. The remembered pain. More like the ongoing pain. I had to write the following just this year because SQL Server still defaults to using CP 1252
by hermitdev 7y ago
> Oh. Locales. The remembered pain.
More like the ongoing pain.
I had to write the following just this year because SQL Server still defaults to using CP 1252 for text. The culprit? One of those damned stylized quotes that Office loves to insert for you. The code:
def _wrap_str(value: str):
try:
return SqlVarChar(
value.encode("cp1252")
)
except UnicodeEncodeError:
logging.getLogger("bulk copy").exception(f"value causing error: {value}")
raise
- btilly 7y agoI would call that Windows pain at this point, not locales. In the Linux/Postgres world, everything is UTF-8. Which is the default for all internet protocols. Do that and the pain is gone. Of course Windows doesn't do that...
- batat 7y agoSQL Server prior to 2019 stores unicode data in UCS-2 (UTF-16 analog, rougly). SQL Server 2019 supports UTF-8.
- hermitdev 7y agoLinux is not entirely UTF-8, though plenty of people treate it as if is so. Even if on Linux, you might need to consume files from other OS's or other Linux systems with different locales. Once had issues with systems configured with "C" locale vs "en-US" should have been near identical, but enough slight differences to cause failures. Been +10 years, so I dont remember the details. Windows, the OS, is UTF-16 (or UCS-2 - I forget the details between the two), SQL Server has, for historical reasons, defaulted to CP1252, probably for compatibility with Office components. But, it's not really a Windows problem, per se, because you have to deal with this issue if you deal with data originated from numerous Windows apps, even if on Linux. Yeah, you can insert a byte order mark (BOM) to indicate UTF-8, but most tools expecting UTF-8 actually dont check for the BOM and blow up in interesting ways if present. Ive seen this far too many times. Enough that anytime I see an encoding error from the likes of Python or Ruby, its an instant recognition (I do a lot ETL work from a number of vendors, so I see a lot of different files "types").