CODEWITHSUNDEEP
pythondb2
Jacobian
Jacobian

Reputation: 10812

Python and IBM DB2: UnicodeDecodeError

I'm getting this error message

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc8 in position 38: ordinal not in range(128)

when I try to execute any sql query in Python, like this one:

>>> import ibm_db
>>> conn = ibm_db.connect("sample","root","root")
>>> ibm_db.exec_immediate(conn, "select * from act")

I checked default encoding and it seems to be 'utf8':

>>> import sys
>>> sys.getdefaultencoding()
'utf-8'

I also know about this thread, where people are discussing quite a similar problem. One of the advices is:

Have you applied the required database PTFs (SI57014 and SI57015 for 7.1 and SI57146 and SI57147 for 7.2)? They are included as a distreq, so they should have been in the order with your PTFs, but won't be automatically applied.

However, I do not know what is database PTF and how to apply it. Need help.

PS. I'm using Windows 10.

EDIT

This is how I get my error message:

>>> print(ibm_db.stmt_errormsg())
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc8 in position 38:    
ordinal not in range(128)

But when I run the same query "select * from act" in DB2 CLP, then it's ok. And this is driver information, whcih I got running this code in Python:

if client:
    print("DRIVER_NAME: string(%d) \"%s\"" % (len(client.DRIVER_NAME), client.DRIVER_NAME))
    print("DRIVER_VER: string(%d) \"%s\"" % (len(client.DRIVER_VER), client.DRIVER_VER))
    print("DATA_SOURCE_NAME: string(%d) \"%s\"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME))
    print("DRIVER_ODBC_VER: string(%d) \"%s\"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER))
    print("ODBC_VER: string(%d) \"%s\"" % (len(client.ODBC_VER), client.ODBC_VER))
    print("ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE))
    print("APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE)
    print("CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE)
    ibm_db.close(conn)
else:
    print("Error.")

it prints:

DRIVER_NAME: string(10) "DB2CLI.DLL"
DRIVER_VER: string(10) "10.05.0007"
DATA_SOURCE_NAME: string(6) "SAMPLE"
DRIVER_ODBC_VER: string(5) "03.51"
ODBC_VER: string(10) "03.01.0000"
ODBC_SQL_CONFORMANCE: string(8) "EXTENDED"
APPL_CODEPAGE: int(1251)
CONN_CODEPAGE: int(1208)
True

EDIT

I also tried this:

>>> cnx = ibm_db.connect("sample","root","root")
>>> query = "select * from act"
>>> query.encode('ascii')
b'select * from act'
>>> ibm_db.exec_immediate(cnx, query)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
Exception
>>> print(ibm_db.stmt_errormsg())
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc8 in position 38: 
ordinal not in range(128)

As you can see, in this case I also get the very same error message.

SUMMARY

Below are all my attemts:

C:\Windows\system32>chcp
Active code page: 65001

C:\Windows\system32>python
Python 3.4.4 (v3.4.4:737efcadf5a6, Dec 20 2015, 20:20:57) [MSC v.1600 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibm_db
>>> cnx = ibm_db.connect("sample","root","root")
>>> ibm_db.exec_immediate(cnx, "select * from act")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception
>>> print(ibm_db.stmt_errormsg())
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc8 in position 38: ordinal not in range(128)
>>> ibm_db.exec_immediate(cnx, b"select * from act")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception: statement must be a string or unicode
>>> query = "select * from act"
>>> query = query.encode()
>>> ibm_db.exec_immediate(cnx, query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception: statement must be a string or unicode
>>> ibm_db.exec_immediate(cnx, "select * from act").decode('cp-1251')
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
Exception

Upvotes: 5

Views: 3012

Answers (3)

Peter Brittain
Peter Brittain

Reputation: 13619

What you have here is an incompatibility between your client code (ibm_db) and the DB2 server. As you can see in the client code the logic for your query is basically:

  1. Extract and check the parameters passed in (lines 4873 to 4918).
  2. Allocate native objects for the query (up to 4954).
  3. Do the query and decode the results (the rest of the function).

Based on our investigations so far, you know that the data you're passing in for the query is well-formed (and so it is not step 1). Looking at the error paths in step 2, you'd see simple error messages explaining these failures. You're therefore failing in step 3.

You are getting an empty Exception raised on the query and when you try to get the details of the error you get another Unicode decoding Exception. This looks like either a bug in ibm_db or a configuration error that means your DB2 installation is not compatible. So how can we find out which...?

As flagged elsewhere, the issue is fundamentally to do with codepages. All the ibm_db code basically interprets strings as ASCII (by converting them with StringOBJ_FromASCII which maps down to calls into Python APIs that insist on receiving ASCII chars - and will throw unicode exceptions if not).

Based on your diags, you could try to prove/disprove this problem, by installing/configuring both your systems (client and DB2 server) to use US English. This should get you past the codepage incompatibility to find the real error here.

If the query is really going out over the network, you might just get a network trace that shows the response coming back from the server. However, based on the fact that you saw nothing in the logs, I'm not convinced this will bear any fruit.

Failing that you need to patch the ibm_db code to handle non-ASCII content - either by raising a bug report with the maintainer or trying it yourself (if you know how to build and debug C extensions).

Upvotes: 2

user2508324
user2508324

Reputation:

The problem is that the DB2 server is returned CP-1251 (also known as Windows-1251) text (as evidenced by APPL_CODEPAGE: int(1251)) in your config output. Python (specifically, the interactive Python REPL) is expecting either UTF-8 or ASCII output, so this causes issues.

The solution is to do:

ibm_db.exec_immediate(conn, "select * from act").decode('cp-1251')

Additionally, you need to make sure that your terminal's text encoding is set to UTF-8. Details on changing that setting will depend on the specific terminal that you are using. Since you have said you are using cmd, the appropriate command is chcp 65001.

Upvotes: 1

mquantin
mquantin

Reputation: 1158

In this kind of case, using an utf8 environment, with a stuff that requires a ascii one; i use the decode method. 

'ascii' codec can't decode byte 0xc8

Allright, it's normal, this is not ascii but utf8 string: you should decode it with utf8 encoding.

...  
query.decode('utf8')  
ibm_db.exec_immediate(cnx, query)

After that you may need to re-encode the results to write or print them.

Upvotes: 0

Related Questions