Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Dana P. <da...@gm...>]

> Second, why do you want to use different encodings? Oracle will
> automatically translate between whatever encoding is used in the
> database and whatever encoding you are using on the client anyway. So
> you can feel quite free to use utf-8 for both databases and everything
> will work just fine since utf-8 is capable of displaying all of the
> characters in the latin-1 character set.

Ok, this is ideal, so I tried using utf-8 for both connections. This
works for my trivial example. That is, binding a variable and
selecting it back out. But not if I actually insert data into a table
and select it back out.

The table below is defined as (NLS_LENGTH_SEMANTICS IS 'BYTE' for both
databases):

CREATE TABLE test (str VARCHAR2(30 BYTE));


databases = ('utf8db', 'latin1db')
username = 'dana'
password = 'whatever'

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
for tns in databases:
   conn = cx_Oracle.connect(username, password, tns)
   print "connection encoding: %s" % conn.encoding

   conn.cursor().execute('INSERT INTO test (str) VALUES (:s)',
s=u'd\u00e4t\u00e4')
   s = conn.cursor().execute('SELECT str FROM
test').fetchone()[0].decode('utf8')
   print s, type(s), len(s), len(s.encode('utf8'))

   conn.close()

Which outputs:

connection encoding: UTF-8
dåta <type 'unicode'> 4 5
connection encoding: UTF-8
d¿ta <type 'unicode'> 4 5

Perhaps I'm missing something?


On Mon, May 9, 2011 at 12:14 PM, Anthony Tuininga
<ant...@gm...> wrote:
> Hi,
>
> First, Oracle does not allow redefining the default encoding with
> multiple connections. So the behavior you are seeing is expected.
>
> Second, why do you want to use different encodings? Oracle will
> automatically translate between whatever encoding is used in the
> database and whatever encoding you are using on the client anyway. So
> you can feel quite free to use utf-8 for both databases and everything
> will work just fine since utf-8 is capable of displaying all of the
> characters in the latin-1 character set.
>
> Hope that helps.
>
> Anthony
>
> On Mon, May 9, 2011 at 7:00 AM, Dana Pieluszczak <da...@gm...> wrote:
>> I have a python process which needs to connect to multiple oracle
>> databases with different NLS_CHARACTERSET values.
>>
>> I can set the appropriate character set by setting the NLS_LANG
>> environment variable, either in my shell or using os.environ before
>> connecting. For utf-8 databases, I set it to "AMERICAN_AMERICA.UTF8",
>> for Latin-1 (ISO-8859-1) I set it to "AMERICAN_AMERICA.WE8ISO8859P1".
>>
>> Here's some code that illustrates the problem:
>>
>> import os
>> import cx_Oracle
>>
>> os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
>> utf8_conn = cx_Oracle.connect('dana', 'pw', 'utf8db')
>> print utf8_conn.encoding
>> s = utf8_conn.cursor().execute('SELECT :s AS str FROM dual',
>> s=u'd\u00e4t\u00e4').fetchone()[0]
>> print s, type(s), len(s), len(s.encode('utf8'))
>>
>> utf8_conn.close()
>>
>> os.environ['NLS_LANG'] ='AMERICAN_AMERICA.WE8ISO8859P1'
>> latin1_conn = cx_Oracle.connect('dana', 'pw', 'latin1db')
>> print latin1_conn.encoding
>> s = latin1_conn.cursor().execute('SELECT :s AS str FROM dual',
>> s='d\xe4t\xe4').fetchone()[0].decode('latin1')
>> print s, type(s), len(s), len(s.encode('latin1'))
>>
>> This outputs:
>> UTF-8
>> dätä <type 'unicode'> 4 6
>> ISO-8859-1
>> dätä <type 'unicode'> 4 4
>>
>> The problem is, if I don't close the utf8 connection before opening
>> the latin1 connection, the latin1 connection's encoding will be utf8.
>> Here's the output with the utf8_conn.close() call removed:
>>
>> UTF-8
>> dätä <type 'unicode'> 4 6
>> UTF-8
>> d¿ <type 'unicode'> 3 3
>>
>> Is there any way around this? I'd like to be able to open all my
>> database connections at startup time instead of constantly
>> opening/closing them.
>>
>> I'm using cx_Oracle 5.1 with oracle client version 10.2.0.1 on SLES
>> 10. Both databases that I'm connecting to are 10.2.0.5.
>>
>> Thanks,
>>
>> Dana P
>>
>> ------------------------------------------------------------------------------
>> WhatsUp Gold - Download Free Network Management Software
>> The most intuitive, comprehensive, and cost-effective network
>> management toolset available today.  Delivers lowest initial
>> acquisition cost and overall TCO of any competing solution.
>> http://p.sf.net/sfu/whatsupgold-sd
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> ------------------------------------------------------------------------------
> WhatsUp Gold - Download Free Network Management Software
> The most intuitive, comprehensive, and cost-effective network
> management toolset available today.  Delivers lowest initial
> acquisition cost and overall TCO of any competing solution.
> http://p.sf.net/sfu/whatsupgold-sd
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>