endless loop when SQL_NULL_DATA is returned in ctrl_error()

[GoogleCodeExporter]

I'm having problems connecting on OSX with FreeTDS, and while the DSN works 
fine with cPython + pyodbc, pypyodbc isn't managing it.

However, at least to prevent an endless loop, I'm observing an unhandled case 
in ctrl_err() which leads to a hang.    My random guess would be because OSX 
uses iODBC and not unixODBC (or win32 ODBC), things are different than what 
pypyodbc is expecting.    I'm not sure how this value should be dealt with but 
the patch below at least causes the program to exit, rather than looping 
endlessly because it does not accommodate a return value of -1:

--- pypyodbc.py 2013-08-05 14:04:52.000000000 -0400
+++ pypyodbc.py.new 2013-08-05 14:05:00.000000000 -0400
@@ -939,7 +939,8 @@
             else:
                 err_list.append((from_buffer_u(state), from_buffer_u(Message), NativeError.value))
             number_errors += 1
-
+        elif ret == SQL_NULL_DATA:
+             raise ProgrammingError('', 'SQL_NULL_DATA')

 def check_success(ODBC_obj, ret):

Original issue reported on code.google.com by zzz...@gmail.com on 5 Aug 2013 at 6:05

[GoogleCodeExporter]

note this issue is with pypy only - with cPython I seem to be connecting 
successfully.

Original comment by zzz...@gmail.com on 5 Aug 2013 at 6:10

[GoogleCodeExporter]

oh and it happens on cPython3.3 also.  So only cPython2.7 is working.

Original comment by zzz...@gmail.com on 5 Aug 2013 at 6:14

[GoogleCodeExporter]

Hi Mike, 
Good to see you in my project's comment areas. I like your MAKO template's 
speed :)

Because I don't have an OSX environment, it's hard for me to reproduce the 
problem. However, I did research the doc about the problem.

The truth is, the return value is -1, but it's SQL_ERROR instead of 
SQL_NULL_DATA.

From MSDN, the reason why SQLGetDiagRec returns a SQL_ERROR can be caused by 3 
possibilities:

RecNumber was negative or 0.
（which is number_errors in my script)

BufferLength was less than zero.
 (which is len(Message) in my script)

When using asynchronous notification, the asynchronous operation on the handle 
was not complete. 
 (Which I'm not sure if you were using asynchronous operation?)

So can you do some value tracking about the 3 possible reasons if you can 
reproduce the bug?

Original comment by jiangwen...@gmail.com on 11 Aug 2013 at 5:02

• Changed state: Accepted

[GoogleCodeExporter]

I will first apply your patch to the development branch in GitHub

Original comment by jiangwen...@gmail.com on 11 Aug 2013 at 5:04

[GoogleCodeExporter]

doesn't seem like any of those things.  I doubt the MS docs are enough here as 
we are using non-MS ODBC client libraries on OSX (iODBC):

Python 3.3.2 (default, Jun 30 2013, 23:23:11) 
[GCC 4.2.1 Compatible Apple LLVM 4.2 (clang-425.0.24)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> c = pypyodbc.connect("DSN=ms2005;UID=scott;PWD=tiger")
> 
/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/py
pyodbc.py(945)ctrl_err()
-> raise ProgrammingError('', 'SQL_NULL_DATA')
(Pdb) locals()
{'pdb': <module 'pdb' from 
'/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/pdb.py'>, 
'ansi': False, 'Buffer_len': c_short(109), 'ret': -1, 'ht': 2, 'err_list': 
[('I', '[', 0)], 'h': c_void_p(140644749023424), 'state': 
<ctypes.c_char_Array_22 object at 0x1102cfd40>, 'val_ret': -1, 'raw_s': <class 
'str'>, 'number_errors': 2, 'ODBC_func': <_FuncPtr object at 0x110496c80>, 
'Message': <ctypes.c_char_Array_10240 object at 0x1102cfe60>, 'NativeError': 
c_int(0)}
(Pdb) len(Message)
10240
(Pdb) state.value
b'I'
(Pdb) Message.value
b'['
(Pdb) number_errors
2
(Pdb) 

Original comment by zzz...@gmail.com on 11 Aug 2013 at 6:06

[GoogleCodeExporter]

Hi, I found one API error usage, thought it had no impact on Windows and Linux, 
but I'm not sure if it's causing this problem. Can you test it?

Line 912, change the below:

            NativeError, Message, len(Message), ADDR(Buffer_len))

to

            ADDR(NativeError), Message, len(Message), ADDR(Buffer_len))

Thanks.

Original comment by jiangwen...@gmail.com on 14 Aug 2013 at 2:07

[GoogleCodeExporter]

pretty much the same

Python 3.3.2 (default, Jun 30 2013, 23:23:11) 
[GCC 4.2.1 Compatible Apple LLVM 4.2 (clang-425.0.24)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> try:
...     pypyodbc.connect("DSN=ms2005;PWD=tiger;UID=scott")
... except:
...     import pdb
...     pdb.post_mortem()
... 
> 
/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/py
pyodbc.py(945)ctrl_err()
-> raise ProgrammingError('', 'SQL_NULL_DATA')
(Pdb) locals()
{'Buffer_len': c_short(109), 'Message': <ctypes.c_char_Array_10240 object at 
0x10171ae60>, 'ht': 2, 'val_ret': -1, 'ansi': False, 'err_list': [('I', '[', 
0)], 'state': <ctypes.c_char_Array_22 object at 0x10171ad40>, 'number_errors': 
2, 'ODBC_func': <_FuncPtr object at 0x101821120>, 'NativeError': c_int(0), 'h': 
c_void_p(140189483713840), 'ret': -1, 'raw_s': <class 'str'>}
(Pdb) number_errors
2
(Pdb) state.value
b'I'
(Pdb) Message.value
b'['
(Pdb) 

Original comment by zzz...@gmail.com on 14 Aug 2013 at 4:13

[GoogleCodeExporter]

From the incomplete err_list, your OS's internal encoding might use UCS-4 
instead of UCS-2. And this might related to why SQL_ERROR (-1) was returned.

I'm attaching a modified pypyodbc.py, which contains debug information and will 
print out the raw encoded string of variable state, so we could see what is the 
encoding used.

Can you try it and post the pdb info? 

Original comment by jiangwen...@gmail.com on 17 Aug 2013 at 9:14

Attachments:

• pypyodbc.py

[GoogleCodeExporter]

Python 3.3.2 (default, Jun 30 2013, 23:23:11) 
[GCC 4.2.1 Compatible Apple LLVM 4.2 (clang-425.0.24)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> try:
...     pypyodbc.connect("DSN=ms2005;PWD=tiger;UID=scott")
... except:
...     import pdb
...     pdb.post_mortem()
... 
b'I\x00\x00\x00M\x00\x00\x000\x00\x00\x000\x00\x00\x002\x00\x00\x00\x00\x00'
b'I\x00\x00\x00M\x00\x00\x000\x00\x00\x000\x00\x00\x002\x00\x00\x00\x00\x00'
> 
/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/py
pyodbc.py(945)ctrl_err()
-> raise ProgrammingError('', 'SQL_ERROR')
(Pdb) locals()
{'ODBC_func': <_FuncPtr object at 0x109753120>, 'err_list': [('I', '[', 0)], 
'h': c_void_p(140387085764016), 'val_ret': -1, 'ret': -1, 'Message': 
<ctypes.c_char_Array_4096 object at 0x10964de60>, 'raw_s': <class 'str'>, 
'ansi': False, 'NativeError': c_int(0), 'Buffer_len': c_short(109), 'ht': 2, 
'debug': 
b'I\x00\x00\x00M\x00\x00\x000\x00\x00\x000\x00\x00\x002\x00\x00\x00\x00\x00', 
'number_errors': 2, 'state': <ctypes.c_char_Array_22 object at 0x10964dd40>}
(Pdb) state.value
b'I'
(Pdb) Message.value
b'['
(Pdb) 

Original comment by zzz...@gmail.com on 17 Aug 2013 at 2:58

[GoogleCodeExporter]

OK, confirmed your OS's internal encoding use UCS-4. So I'm attaching a 
pypyodbc.py for UCS4, can you run it and see if it makes a difference.

However, even it works, problems remains how to detect iODBC returns string in 
UCS2 or UCS4.

But let's first see if the attached works or not. 

Thanks!

Original comment by jiangwen...@gmail.com on 18 Aug 2013 at 2:00

Attachments:

• pypyodbc.py

[GoogleCodeExporter]

Python 3.3.2 (default, Jun 30 2013, 23:23:11) 
[GCC 4.2.1 Compatible Apple LLVM 4.2 (clang-425.0.24)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> try:
...     pypyodbc.connect("DSN=ms2005;PWD=tiger;UID=scott")
... except:
...     import pdb
...     pdb.post_mortem()
... 
> 
/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/py
pyodbc.py(462)ucs2_dec()
-> uchar = buffer.raw[i:i + 4].decode('utf_32')
(Pdb) locals()
{'i': 20, 'uchar': '2', 'buffer': <ctypes.c_char_Array_22 object at 
0x104fc9d40>, 'uchars': ['I', 'M', '0', '0', '2']}
(Pdb) where
  <stdin>(2)<module>()
  /Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py(2287)__init__()
-> self.connect(connectString, autocommit, ansi, timeout, unicode_results, 
readonly)
  /Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py(2335)connect()
-> check_success(self, ret)
  /Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py(953)check_success()
-> ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  /Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py(940)ctrl_err()
-> err_list.append((from_buffer_u(state), from_buffer_u(Message), 
NativeError.value))
> 
/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/py
pyodbc.py(462)ucs2_dec()
-> uchar = buffer.raw[i:i + 4].decode('utf_32')
(Pdb) 

Original comment by zzz...@gmail.com on 20 Aug 2013 at 6:21

[GoogleCodeExporter]

here's the full traceback:

(Pdb) cont
>>> pypyodbc.connect("DSN=ms2005;PWD=tiger;UID=scott")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py", line 2287, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py", line 2335, in connect
    check_success(self, ret)
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py", line 953, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py", line 940, in ctrl_err
    err_list.append((from_buffer_u(state), from_buffer_u(Message), NativeError.value))
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/pypyodbc.py", line 462, in ucs2_dec
    uchar = buffer.raw[i:i + 4].decode('utf_32')
UnicodeDecodeError: 'utf32' codec can't decode bytes in position 0-1: truncated 
data

Original comment by zzz...@gmail.com on 20 Aug 2013 at 6:21

[GoogleCodeExporter]

Ah, truncated data, not enough buffer. Can you try this one?

Original comment by jiangwen...@gmail.com on 22 Aug 2013 at 2:33

Attachments:

• pypyodbc.py

[GoogleCodeExporter]

that fixed it, nice work

Original comment by zzz...@gmail.com on 22 Aug 2013 at 3:28

[GoogleCodeExporter]

The reason why pypyodbc got broken on OSX, is that OSX's iODBC provide data to 
applications in UCS4(UTF32), which is special compared with MS ODBC and 
unixODBC. 

The common practice for MS ODBC, and unixODBC is providing data to applications 
in UCS2(UTF16).

So I would revise pypyodbc to switch the buffer interface for UCS4 when 
detecting that iODBC is the underlying ODBC driver manager. The way to detect 
that is by the name of the dll library loaded. iODBC's dll should be 
"libiodbc.dylib"

Did you change anything about DLL loading part of pypyodbc so pypyodbc can load 
iODBC's library on OSX, or you just used the version I provided without any 
changes? 

I will add a detection logic, then pypyodbc would be able to run under 
iODBC/unixODBC without any changes needed.

Original comment by jiangwen...@gmail.com on 23 Aug 2013 at 5:00

[GoogleCodeExporter]

Version 1.2.0 has been uploaded and contains the fix. The new version judges if 
iODBC is been used and switch to use UCS4/UTF-32 to decode and encoding data 
from the ODBC library.

Original comment by jiangwen...@gmail.com on 21 Sep 2013 at 11:17

• Changed state: Fixed