Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

built-in function fetch_tuple returned a result with an error set #359

Closed
ramstein74 opened this issue Nov 27, 2018 · 20 comments
Closed

built-in function fetch_tuple returned a result with an error set #359

ramstein74 opened this issue Nov 27, 2018 · 20 comments

Comments

@ramstein74
Copy link

Hi, im from Portugal so we have special chars like you can see below...

when i have a query that gets a row with this text
"PORCELANA PARA MAÇARICO DE IGNIÇ."
in a table column i get the error

4.4:zeta@~/pyWork/Tasks/allTasks> python3 test1.py
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x8e in position 38: invalid start byte

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_dbi.py", line 1472, in _fetch_helper
row = ibm_db.fetch_tuple(self.stmt_handler)
SystemError: returned a result with an error set

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "test1.py", line 15, in
for row in c1:
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_dbi.py", line 1128, in next
row = self.fetchone()
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_dbi.py", line 1492, in fetchone
row_list = self._fetch_helper(1)
File "/QOpenSys/pkgs/lib/python3.6/site-packages/ibm_db_dbi.py", line 1476, in _fetch_helper
raise self.messages[-1]
ibm_db_dbi.Error: ibm_db_dbi::Error: SystemError(' returned a result with an error set',)
4.4:zeta@~/pyWork/Tasks/allTasks>

All other records are parsed correctly.

any help?
regards

my code
import ibm_db_dbi as db2
conn = db2.connect()
c1 = conn.cursor() sql="select * ..... " -> querying only the record that crashed the code...
c1.execute(sql)
for row in c1:
print(row)

there is something strange because if i change "PORCELANA PARA MAÇARICO DE IGNIÇ." to "ÇÇÇÇÇ" it works but keep adding Ç at the end of the string eventually leads to the error.

any help ?
@kadler
Copy link
Contributor

kadler commented Nov 27, 2018

What's the CCSID of the column and the job CCSID (and default job CCSID)?

@ramstein74
Copy link
Author

I may be wrong but ccsid of ibm i is 1140 and i tought it could be something related to ccsid and i created a table with ccsid 1140 and still the error, then tried 37 and the result is a litle better but eventually it gets the error again.

@kadler
Copy link
Contributor

kadler commented Nov 27, 2018 •
edited

You can query the column CCSID like so:

select ordinal_position, data_type, length, ccsid from qsys2.syscolumns where system_table_schema = '<SCHEMA>' and column_name = '<COLUMN>' and table_name = '<TABLE>'

@kadler
Copy link
Contributor

kadler commented Nov 27, 2018

I think I'm able to recreate it, though the result is slightly different.

create or replace table kadler.portuguese(c char(10) ccsid 1140);
delete from kadler.portuguese where 1=1;
insert into kadler.portuguese values('ÇÇÇÇÇÇÇÇ');

create or replace table kadler.english(c char(10) ccsid 37);
delete from kadler.english where 1=1;
insert into kadler.english values('ÇÇÇÇÇÇÇÇ');
import ibm_db_dbi as db2

conn = db2.connect()
cur = conn.cursor()

cur.execute('select * from kadler.portuguese')
print(cur.fetchone())

cur.execute('select * from kadler.english')
print(cur.fetchone())

cur.callproc('qsys2.qcmdexc', ('CHGJOB CCSID(1140)', ))

cur.execute('select * from kadler.portuguese')
print(cur.fetchone())

cur.execute('select * from kadler.english')
print(cur.fetchone())
('ÇÇÇÇÇ\x00\x00\x00\x00\x00\x00\x07\x00',)
('ÇÇÇÇÇ\x00\x00\x00\x00\x00\x00\x07\x00',)
('ÇÇÇÇÇ\x00\x00\x00\x00\x00\x00\x07\x00',)
('ÇÇÇÇÇ\x00\x00\x00\x00\x00\x00\x07\x00',)

Above, you can see that there's extra garbage in the fetched data. I think in your case, the garbage just happens to contain invalid UTF-8 sequence and throws the exception.

If I change the data to a bunch of "C" characters instead, I get the expected data:

('CCCCCCCC  ',)
('CCCCCCCC  ',)
('CCCCCCCC  ',)
('CCCCCCCC  ',)

I think we're getting back bad info from the underlying CLI APIs, so I'll have to investigate further.

@ramstein74
Copy link
Author

ramstein74 commented Nov 27, 2018 via email

@ramstein74
Copy link
Author

ramstein74 commented Nov 27, 2018 via email

@kadler
Copy link
Contributor

kadler commented Nov 27, 2018

The problem is that ibm_db uses the returned column size information to determine the size of the buffer to allocate on the SQLBindCol. In this case a column size of 10 will result in a 10-byte buffer being allocated. However, converting to UTF-8 will see the byte length of the column increase to greater than the size of the buffer. A 'Ç' is 1 byte in single byte EBCDIC code pages, but 2 bytes in UTF-8. If the data expands beyond the buffer size, the result will be truncated and the total size of the data will be returned in the indicator.

So far, the only problem is that we have truncated data, but later the fetch code uses the returned value in the indicator as the total length of the data in the buffer, assuming that it hasn't been truncated. This leads to a buffer over-read and garbage data is returned.

@ramstein74
Copy link
Author

ramstein74 commented Nov 27, 2018 via email

@kadler
Copy link
Contributor

kadler commented Nov 27, 2018

I'm working on a fix

@ramstein74
Copy link
Author

ramstein74 commented Nov 27, 2018 via email

@ramstein74
Copy link
Author

@kadler using your code above and running the python script i get this output

('▒▒▒▒▒\x00\x00\x00\x00\x00\x00\x07\x00',)
('▒▒▒▒▒\x00\x00\x00\x00\x00\x00\x07\x00',)
('▒▒▒▒▒\x00\x00\x00\x00\x00\x00\x07\x00',)
('▒▒▒▒▒\x00\x00\x00\x00\x00\x00\x07\x00',)

@kadler
Copy link
Contributor

kadler commented Nov 28, 2018 •
edited

Should be fixed with kadler@2c4cfc3. I'll work on rolling out a new RPM shortly.

@ramstein74
Copy link
Author

ramstein74 commented Nov 28, 2018 via email

@kadler
Copy link
Contributor

kadler commented Nov 28, 2018

RPMs are now available. You should be able to do a yum upgrade python3-ibm_db and it should work (may have to clear cache with yum clean metadata first).

@ramstein74
Copy link
Author

ramstein74 commented Nov 28, 2018 via email

@ramstein74
Copy link
Author

finally it works. but i get this
{'CCOD': '2250512001', 'CDSCR': 'PORCELANA P/MA▒ARICO DE IGNI▒.', 'CUNID': 'UN', 'AMEXI': 3.0}
instead of this
{'CCOD': '2250512001', 'CDSCR': 'PORCELANA P/MAÇARICO DE IGNIÇ.', 'CUNID': 'UN', 'AMEXI': 3.0}
any last tip ?

many thanks

@ramstein74
Copy link
Author

Kevin, forget it. i think its an issue with my bash shell. I then send this to another app and there it appears correctly.
I already have an issue in stackoverflow about it
https://stackoverflow.com/questions/53325665/bash-pressing-%C3%A7-or-%C3%87-emits-beep-instead-of-writing-the-key/53325862?noredirect=1#comment93883417_53325862

if you have any idea feel free to save me again :)

@kadler
Copy link
Contributor

kadler commented Nov 29, 2018

You need to set your locale to a UTF-8 locale (eg. on AIX this would be all caps: PT_PT). By default it's a single-byte locale, which cause Python to convert the character to 0xC7 instead of 0xC387, which is not valid UTF-8.

I did answer your stack overflow question similarly.

@ramstein74
Copy link
Author

ramstein74 commented Nov 29, 2018 via email

@kadler
Copy link
Contributor

kadler commented Nov 29, 2018

@ramstein74 care to close the issue?

@abmusse abmusse mentioned this issue Jan 24, 2019
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@ramstein74 @kadler