Use UTF-8 as default encoding instead of ASCII

[gvenzl]

All other major databases use UTF-8 as the default characterset encoding for connections while cx_Oracle is using ASCII. Given how limited and rarely useful ASCII is these days, it makes more sense to follow "common convention" and Oracle's default since 11g: UTF-8

MySQL: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html
Postgres: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS (seems to determine the default from the server)
SQL Server: https://pymssql.readthedocs.io/en/latest/ref/pymssql.html#connection-class

[gitpickle]

+1

…

On Fri, Mar 20, 2020, 1:46 AM Gerald Venzl ***@***.***> wrote: All other major databases use UTF-8 as the default characterset encoding for connections while cx_Oracle is using ASCII. Given how limited and rarely useful ASCII is these days, it makes more sense to follow "common convention" and Oracle's default since 11g: UTF-8 MySQL: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html Postgres: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS (seems to determine the default from the server) SQL Server: https://pymssql.readthedocs.io/en/latest/ref/pymssql.html#connection-class — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#409>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANYVZZJHFP4RMXZFCHXX7DDRIL7KXANCNFSM4LQBH5BA> .

[anthony-tuininga]

The default is currently based on the Oracle Client library's default -- which is indeed ASCII if the environment variable NLS_LANG is not set. If I change the default to be UTF-8 that implies that the environment variable NLS_LANG will be ignored. I personally don't have a problem with that, though, as the encoding is now only relevant for performance (since if the database encoding itself is not UTF-8 there will be conversion required between the two encodings). As far as the Python user is concerned, the encoding isn't relevant as they simply get back strings (which don't have an encoding). I'll see if @cjbj has an objection to making this change.

[gvenzl]

I think the NLS_LANG environment variable should still be honored to continue to provide the same behavior that users are used to for many years. So ideally the order would be:

1. use what's provided via the encoding, nencoding parameter
2. use the value provided by NLS_LANG
3. default to UTF-8

I believe that would mean that rather than just setting encoding from None to UTF-8 some more changes are required in the underlying code.

An interesting remark about relevance for performance only. When loading a UTF-8 file with no enconding set and a database running in AL32UTF8, the Python driver will throw a:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 7: ordinal not in range(128)

This is with cx-Oracle 7.2.2.

[cjbj]

That order seems straightforward but Anthony has some issues, particularly on Windows. We'll review and make a decision for cx_Oracle 8.

What do you mean "loading a ... file"?

[cjbj]

For the record, NLS_NCHAR is the variable that determines the client's national character set (e.g. for NCHAR, NVARCHAR, and NCLOB columns). This needs to be considered in the plans.

[anthony-tuininga]

Agreed. I would set its default to "UTF-8" as well -- unless of course there is a good reason to use "UTF-16" in light of the fact that NCHAR, NVARCHAR2 and NCLOB data is stored in UCS-2 code points regardless of the database character set.

I also wonder what you mean about loading a file. We are talking about the way that cx_Oracle sends data to the database and how it fetches data from the database. It has nothing to do with loading a file!

[gvenzl]

Sorry, I shouldn't have said file but value. I also forgot to add that the error is thrown only when a non-ascii character is present in the value, of course.

[anthony-tuininga]

Well, when you pass data to the database, it is encoded into the encoding specified when you created the connection (or via NLS_LANG and if that isn't set ASCII). By changing the default to UTF-8 this problem would be avoided -- well, always assuming that the database is capable of storing that data of course!

[gvenzl]

Yes, indeed, I was referring to your sentence above:

I personally don't have a problem with that, though, as the encoding is now only relevant for performance (since if the database encoding itself is not UTF-8 there will be conversion required between the two encodings)

Perhaps I misunderstood what you meant by the sentence but there is not only a performance relevance with encoding but also a functional one.

But yes, precisely, the issue wouldn't be there if the default is UTF-8.

[anthony-tuininga]

My statement may not have been clear. Hopefully this one is!

It is definitely not a functional issue. The encoding can be set to anything that supports the data and the application code will continue to work -- no matter what that encoding is. In order to cover all possible data, one of the universal encodings is recommended -- either UTF-8, UTF-16, etc. but the most commonly used one is indeed UTF-8. Setting the default to UTF-8 should not have any impact on the functionality of the applications using cx_Oracle. If you think that there is such a case, please say on!

[gvenzl]

Thanks for clarifying, @anthony-tuininga, I get it now!
Agreed, changing the default to UTF-8 should not have any functional impact.

[anthony-tuininga]

Looking at the Python/C API there are a number of optimizations that are in place when exclusively using UTF-8. As such, I think it might be better to not just make the default UTF-8 but to make it exclusively UTF-8. This is already done by most of the other drivers that are available and it makes usage much simpler. If this were to be done, as noted earlier there should be no functional impact. If a different encoding was specified an exception could be raised or a warning could be generated indicating that the value is simply ignored (and in version 9 the argument would be entirely removed, thereby causing an exception). Any thoughts on that?

[anthony-tuininga]

@zzzeek, your ccomments would be welcome, especially in light of the documentation in SQLAlchemy found in issue #439.