search

smaudet / pypyodbc

Automatically exported from code.google.com/p/pypyodbc
0 stars 0 forks source link

pypyodbc duplicates previous value for column instead of NULL #29

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago 
Steps to reproduce the problem.

1. Create a database and populate it with test data

CREATE DATABASE test;
USE test;
CREATE TABLE test (host NVARCHAR(256), ip NVARCHAR(15));
INSERT INTO test VALUES ('srv1', '192.168.1.1');
INSERT INTO test VALUES ('srv2', '192.168.1.2');
INSERT INTO test VALUES ('srv3', '192.168.1.3');
INSERT INTO test VALUES ('srv4', NULL);
INSERT INTO test VALUES ('srv5', NULL);
INSERT INTO test VALUES ('srv6', '192.168.1.6');
INSERT INTO test VALUES ('srv7', NULL);
INSERT INTO test VALUES ('srv8', NULL);
INSERT INTO test VALUES ('srv9', NULL);

2. Check database content

SELECT * FROM test;

Database must contain the following data

srv1    192.168.1.1
srv2    192.168.1.2
srv3    192.168.1.3
srv4    NULL
srv5    NULL
srv6    192.168.1.6
srv7    NULL
srv8    NULL
srv9    NULL

3. Now execute sample Python script

import pypyodbc as odbc
sql = 
odbc.connect("Driver=FreeTDS;Server=127.0.0.1;Port=1433;Database=test;UID=test;P
WD=secret")
cursor = sql.cursor()
cursor.execute("SELECT * FROM test;")

while True:
    row = cursor.fetchone()
    if row is None:
        break
    print row

Expected output

(u'srv1', u'192.168.1.1')
(u'srv2', u'192.168.1.2')
(u'srv3', u'192.168.1.3')
(u'srv4', None)
(u'srv5', None)
(u'srv6', u'192.168.1.6')
(u'srv7', None)
(u'srv8', None)
(u'srv9', None)

But script duplicates previous value for column instead of NULL

('srv1', '192.168.1.1')
('srv2', '192.168.1.2')
('srv3', '192.168.1.3')
('srv4', '192.168.1.3')
('srv5', '192.168.1.3')
('srv6', '192.168.1.6')
('srv7', '192.168.1.6')
('srv8', '192.168.1.6')
('srv9', '192.168.1.6')

The same code with pyodbc returns correct output!

Version of the product and operating system

Linux, Python 2.7.3, pypyodbc-1.2.1, libfreetds-0.91, libtdsodbc0-0.91
Mycrosoft SQL Server 2005

Original issue reported on code.google.com by aleksey....@gmail.com on 27 Nov 2013 at 4:53

GoogleCodeExporter commented 8 years ago 
What is your Linux environment. ?

On my Ubuntu Linux, I checked the same, but worked as expected.

Original comment by jiangwen...@gmail.com on 30 Nov 2013 at 4:00

Attachments:

GoogleCodeExporter commented 8 years ago 
Thank you for the answer!

I can reproduce the problem on my old OpenSuSE 10.2 server with original Python 
2.5 from repository and custom build Python 2.7.3. So looks like the problem is 
in some of the additional libraries 

unixODBC 2.2.12
libtdsodbc0 0.91
freetds-tools 0.91
libfreetds 0.91

Tested with pypyodbc 1.1.5 and 1.2.1. As mentioned earlier pyodbc on the same 
server returns correct output.

Original comment by aleksey....@gmail.com on 2 Dec 2013 at 9:38

GoogleCodeExporter commented 8 years ago 
Hi Attached is the next 1.3.0 version of pypyodbc, which mainly enhanced 
performance.

I added some statement to print some key run-time variables in the file, if you 
can reproduce the problem again with this 1.3.0 file, please send out the 
output to me.

Original comment by jiangwen...@gmail.com on 5 Dec 2013 at 1:43

Attachments:

GoogleCodeExporter commented 8 years ago 
Hi,

aleksey@srv:~>cat test1.py 
#!/usr/bin/python

import pypyodbc as odbc

print odbc.version

sql = 
odbc.connect("Driver=FreeTDS;Server=192.168.0.2;Port=1433;Database=test;UID=sa;P
WD=secret")
cursor = sql.cursor()
cursor.execute("SELECT * FROM test;")

while True:
    row = cursor.fetchone()
    if row is None:
        break
    print row

aleksey@srv:~>./test1.py 
1.3.0
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(8) 8
bind_data: True
used_buf_len.value c_long(2) 2
bind_data: True
used_buf_len.value c_long(2) 2
bind_data: True
used_buf_len.value c_long(1) 1
bind_data: True
used_buf_len.value c_long(1) 1
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
bind_data: True
used_buf_len.value c_long(1) 1
bind_data: True
used_buf_len.value c_long(1) 1
bind_data: True
used_buf_len.value c_long(1) 1
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
SQLFetch ret 100
SQLFetch ret 100
SQLFetch ret 100
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(11) 11
('srv1', '192.168.1.1')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(11) 11
('srv2', '192.168.1.2')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(11) 11
('srv3', '192.168.1.3')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
('srv4', '192.168.1.3')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
('srv5', '192.168.1.3')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(11) 11
('srv6', '192.168.1.6')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
('srv7', '192.168.1.6')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
('srv8', '192.168.1.6')
SQLFetch ret 0
bind_data: True
used_buf_len.value c_long(4) 4
bind_data: True
used_buf_len.value c_long(4294967295) 4294967295
('srv9', '192.168.1.6')
SQLFetch ret 100

aleksey@srv:~>cat test2.py  
#!/usr/bin/python

import pyodbc as odbc

print odbc.version

sql = 
odbc.connect("Driver=FreeTDS;Server=192.168.0.2;Port=1433;Database=test;UID=sa;P
WD=secret")
cursor = sql.cursor()
cursor.execute("SELECT * FROM test;")

while True:
    row = cursor.fetchone()
    if row is None:
        break
    print row

aleksey@srv:~>./test2.py
3.0.7
('srv1', '192.168.1.1')
('srv2', '192.168.1.2')
('srv3', '192.168.1.3')
('srv4', None)
('srv5', None)
('srv6', '192.168.1.6')
('srv7', None)
('srv8', None)
('srv9', None)

Original comment by aleksey....@gmail.com on 5 Dec 2013 at 9:28

Attachments:

GoogleCodeExporter commented 8 years ago 
[deleted comment]
GoogleCodeExporter commented 8 years ago 
I believe the attached pypyodbc.py can be normally used by you now. Currently 
it's a quick fix but I think it would solve your problem and no side effects.

The reason why you did not get None, is somewhere some code sending the None 
mark -1 to a unsigned variable that would treat -1 as 4294967295. Might be the 
unixodbc is not the latest 2.3 version?

Also is your computer 32bit or 64bit? Can you help to run pypyodbc_debug.py and 
send back the outputs?

Original comment by jiangwen...@gmail.com on 5 Dec 2013 at 11:41

Attachments:

GoogleCodeExporter commented 8 years ago 
Computer running OpenSuSE 10.2 64-bit and unixODBC 2.2.12 from repository.

Problem is fixed with attached pypyodbc.py. Output of pypyodbc_debug.py almost 
the same as pypyodbc.py.

aleksey@srv:~> cat test1.py 
#!/usr/bin/python

import pypyodbc as odbc

print odbc.version

sql = 
odbc.connect("Driver=FreeTDS;Server=192.168.0.2;Port=1433;Database=test;UID=sa;P
WD=secret")
cursor = sql.cursor()
cursor.execute("SELECT * FROM test;")

while True:
    row = cursor.fetchone()
    if row is None:
        break
    print row

aleksey@srv:~> ./test1.py 
1.3.0
used_buf_len.value c_long(8) 8
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(8) 8
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv1', '192.168.1.1')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv2', '192.168.1.2')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv3', '192.168.1.3')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv4', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv5', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv6', '192.168.1.6')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv7', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv8', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv9', None)

aleksey@srv:~> cat test1.py   
#!/usr/bin/python

import pypyodbc_debug as odbc

print odbc.version

sql = 
odbc.connect("Driver=FreeTDS;Server=192.168.0.2;Port=1433;Database=test;UID=sa;P
WD=secret")
cursor = sql.cursor()
cursor.execute("SELECT * FROM test;")

while True:
    row = cursor.fetchone()
    if row is None:
        break
    print row

aleksey@srv:~> ./test1.py 
ctypes.c_ssize_t = ctypes.c_long
1.3.0
used_buf_len.value c_long(8) 8
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(8) 8
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(1) 1
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(4294967295) 4294967295
used_buf_len.value c_long(2) 2
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv1', '192.168.1.1')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv2', '192.168.1.2')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv3', '192.168.1.3')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv4', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv5', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(11) 11
('srv6', '192.168.1.6')
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv7', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv8', None)
used_buf_len.value c_long(4) 4
used_buf_len.value c_long(4294967295) 4294967295
('srv9', None)

Original comment by aleksey....@gmail.com on 5 Dec 2013 at 12:34

GoogleCodeExporter commented 8 years ago 
looks like the ctypes.c_long is 64bit c int, while unixodbc provided -1 in 
32bit c int.

will looking for a more correct way to fix that.

Original comment by jiangwen...@gmail.com on 6 Dec 2013 at 2:17

GoogleCodeExporter commented 8 years ago 
Do you tried to figure out why pyodbc does not affected by this issue?

Original comment by aleksey....@gmail.com on 6 Dec 2013 at 8:09

GoogleCodeExporter commented 8 years ago 
still finding ways to solve that. 

pyodbc uses c++ header files compile binaries on every platform, and during the 
compilation of binary, from the definition in the header files, the compiler 
knows the definition of data types on that platform.

while pypyodbc doesn't use header files, and doesn't need complation, so 
pypyodbc has to find ways to detect how data types are defined on the platform.

so can not leverage pyodbc's method.

Original comment by jiangwen...@gmail.com on 9 Dec 2013 at 7:59