Character encoding problem

[joitronix]

Environment:

Software: Python
Interbase driver: 1.0.1
Client: Debian linux 12
Server: Interbase 2020 899 Debian 12

DATABASE CHARSET: NONE
COLUMN CHARSET: NONE

Error:

Traceback (most recent call last):
File "/root/test_interbase.py", line 20, in
querie = cursor.execute(f"select * from tincidencias where codrep = {number}").fetchall()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3543, in fetchall
return [row for row in self]
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3543, in
return [row for row in self]
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3309, in next
row = self.fetchone()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3501, in fetchone
return self._ps._fetchone()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3186, in _fetchone
return self.__XSQLDA2Tuple(self._out_sqlda)
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 2473, in __XSQLDA2Tuple
value = b2u(value,self.__python_charset)
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 365, in b2u
return st.decode(charset)
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf1 in position 22: invalid continuation byte

TEXT IN THE COLUMN: mal olor en todo el baño, cliente cambia hab.

CODE:

import interbase

con = interbase.connect(
host="192.168.1.37",
database="D://.IB",
# database="D:\\\DB\****.IB",
user="SYSDBA",
password="masterkey",
sql_dialect=3,
ib_library_name="/opt/interbase/lib/libgds.so",
charset="NONE"
)

cursor = con.cursor()

number = 300000
while True:
number += 1
print(number)
querie = cursor.execute(f"select * from tincidencias where codrep = {number}").fetchall()
for a in querie:
print(a)

If i change the charset to valid spanish charset ISO8859_1:

Traceback (most recent call last):
File "/root/test_interbase.py", line 20, in
querie = cursor.execute(f"select * from tincidencias where codrep = {number}").fetchall()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3543, in fetchall
return [row for row in self]
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3543, in
return [row for row in self]
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3309, in next
row = self.fetchone()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3501, in fetchone
return self._ps._fetchone()
File "/usr/local/lib/python3.9/dist-packages/interbase/ibcore.py", line 3192, in _fetchone
raise exception_from_status(DatabaseError,
interbase.ibcore.DatabaseError: ("Cursor.fetchone:\n- SQLCODE: -802\n- b'arithmetic exception, numeric overflow, or string truncation'\n- b'Cannot transliterate character between character sets'", -802, 335544321)

In ibexpert, all rows works ok, and i can see all data perfectly, configured with connetion with none.

[joitronix]

#7

Is really the same issue like this guy so.....

[joitronix]

Tested the solution, from the #7 and worked, but manage by myself encoding is kynda.... when ibexpert a program that have more than 14 years, handles ok..., and in pyodbc works good as well and i not need to define if is none or if its iso8859 etc.

select * from tincidencias where descripcion like '%ñ%'

But not solves the example upper me, when try to query like that, not return nothing, i supose that send to the database and encode that not match the actually, and no fails but no return nothing, while in ibexpert return all rows perfectly, pyodbc too.

remember datasbe encoding none and databse column encoding none

[joitronix]

Solved changing here:

The problem occurs when interacting with databse with charset NONE, if the databse is for example win1250 and put in the connection that charset works as expected without change this.

[joitronix]

hi?

[lmbelo]

@joitronix I'm working on it right now.

[lmbelo]

When encoding is not provided, strings are encoded using your system locale settings. If you save and load data from different locales, this error might happen.
Try to create a record with special characters from the same Python code you read it, it may not fail.

charset_map = {
    # DB CHAR SET NAME    :   PYTHON CODEC NAME (CANONICAL)
    # -------------------------------------------------------------------------
    None                  :   locale.getpreferredencoding(),
    'NONE'                :   locale.getpreferredencoding(),
    ...

• Always define character set for your databases and specify connection charset. It will make your life much easier.

Basically, all you need to do is set the encoding for your connection:

con = interbase.connect(
    host="HOST",
    database="DB",
    user="USER",
    password="PASSWORD",
    sql_dialect=3,
    charset="ISO8859_1"
)

This will convert your strings from Unicode to ISO8859_1 (Latin-1) when writing, and decode bytes using the same encoding when reading.

Again, you should always set a common encoding for your connection; otherwise, you may end up with inconsistent string encodings in your database.