pyodbc HY000 error if commit() after complex SELECT

Moderator: NorbertKrupa

Post Reply
matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

pyodbc HY000 error if commit() after complex SELECT

Post by matthewcornell » Mon Feb 03, 2014 6:55 pm

Hi folks. I've been chasing down this error for a week:

> pyodbc.Error: ('HY000', '[HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution: (20) (SQLFetch)')

After lots of trial and error, the key is a combination of this particular SELECT query (simpler ones don't error) plus a call to commit() after curs.execute(). I'm really curious to hear what you think is going on. Below (followed by configuration information) is the trimmed-down example.

Thanks in advance!

* versions

Python: 3.3.2 [GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux
pyodbc: 3.0.7 (pyodbc.SQL_DBMS_VER: 18, pyodbc.SQL_DM_VER: 171, pyodbc.SQL_DRIVER_ODBC_VER: 77, pyodbc.SQL_DRIVER_VER: 7, pyodbc.SQL_ODBC_VER: 10)
unixODBC: 2.2.14
vertica: Vertica Analytic Database v7.0.0-0
OS: Linux version 2.6.32-279.14.1.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.6 20120305 (Red Hat 4.4.6-4) (GCC) )

Code: Select all

import pyodbc

query = '''
SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val, relVarTable3.val, relVarTable4.val
FROM (SELECT id FROM B) relVarTable0
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.X) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.X IS NOT NULL
GROUP BY B1.id) relVarTable1
    ON relVarTable0.id = relVarTable1.id
LEFT JOIN
(SELECT B1.id AS id, B1.Y AS val
FROM B B1
WHERE B1.Y IS NOT NULL ) relVarTable2
    ON relVarTable0.id = relVarTable2.id
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.W) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.W IS NOT NULL
GROUP BY B1.id) relVarTable3
    ON relVarTable0.id = relVarTable3.id
LEFT JOIN
(SELECT B1.id AS id, AVG(C1.Z) AS val
FROM B B1, BC BC1, C C1
WHERE B1.id = BC1.B_id AND C1.id = BC1.C_id AND C1.Z IS NOT NULL
GROUP BY B1.id) relVarTable4
    ON relVarTable0.id = relVarTable4.id;
'''

conn = pyodbc.connect('DSN=vertica_kdl_dsn;UserName=__;Password=__)
curs = conn.cursor()
curs.execute('SET search_path TO "$user", rcd_test, public, v_catalog, v_monitor, v_internal;')
curs.execute(query)
curs.commit()           # comment out to get no error
rows = curs.fetchall()  # if above commit called: pyodbc.Error: ('HY000', '[HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution:  (20) (SQLFetch)')
curs.close()
Attachments
pyodbc-HY000-files.zip
(5.25 KiB) Downloaded 84 times

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: pyodbc HY000 error if commit() after complex SELECT

Post by matthewcornell » Mon Feb 10, 2014 7:33 pm

FYI the solution was to add ResultBufferSize=0 in odbc.ini under the DSN

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: pyodbc HY000 error if commit() after complex SELECT

Post by id10t » Mon Feb 10, 2014 7:48 pm

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 4:47 pm, edited 1 time in total.

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: pyodbc HY000 error if commit() after complex SELECT

Post by matthewcornell » Mon Feb 10, 2014 10:31 pm

I think it's a Vertica-specific setting, but I'm not 100% certain.
sKwa wrote:Hi!

Interesting... if it same problem in Perl:
viewtopic.php?f=34&t=1300
viewtopic.php?f=34&t=1295

Do you know meaning of 0 ? Is it standard:
* 0 means unbuffered
* 1 means line buffered
* X > 0 means use a buffer of that size.
* X < 0 use the system default
???

Post Reply

Return to “Python”