python-sybase-misc

[python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Valerie R. C. <vr...@co...>]

Hi,

I'm getting Memory Errors with very simple queries using Python-Sybase 
0.35 and the latest FreeTDS to connect to a MS SQL server.  Is this a 
problem with FreeTDS?  Will Kevin's patch (from an earlier thread) fix 
my problem?  Or is there a problem with my code?  

Here is the code:

import Sybase
db = Sybase.connect(SERVER, USER, PASSWORD, DATABASE)
db.execute("SELECT * FROM FEMMD_Parameters WHERE MDregionSize = 10")

The table isn't that huge, maybe 100 rows or less.  Anyway, I get the 
same error whether or not I include a condition that would select only a 
few lines.  It might also help if I mention that I'm telling FreeTDS to 
use version 7.0 in the freetds.conf file.

Here is the Traceback:

Traceback (most recent call last):
  File "DBTest.py", line 10, in ?
    db.execute("SELECT * FROM FEMMD_Parameters WHERE MDregionSize = 10")
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 727, in execute
    result_list = self._fetch_results()
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 745, in _fetch_results
    bufs = _row_bind(cmd, self.arraysize)
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 159, in _row_bind
    status, buf = cmd.ct_bind(i + 1, fmt)
MemoryError

Thanks a lot,
Valerie

[python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Valerie R. C. <vr...@co...>]

Hi,

I've been trying to figure out this problem myself.  I don't think the 
error is in freetds, as far as I can tell.  Can someone just tell me how 
to turn on the debugging? (Yes, I'm a newbie.)

Thanks,
Valerie

Re: [python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Dave C. <dj...@ob...>]

>>>>> "Valerie" == Valerie R Coffman <vr...@co...> writes:

Valerie> Hi, I've been trying to figure out this problem myself.  I
Valerie> don't think the error is in freetds, as far as I can tell.
Valerie> Can someone just tell me how to turn on the debugging? (Yes,
Valerie> I'm a newbie.)

Debugging can be turned on by doing this:

import Sybase
Sybase._ctx.debug = 1

This should give enough clues to get to the bottom of the problem.

I just realised that I have some minor unreleased changes which *may*
have an impact on what you are seeing.  I will see if I can get a new
0.36pre release out tonight.

- Dave

-- 
http://www.object-craft.com.au

Re: [python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Valerie R. C. <vr...@co...>]

Hi,

Dave, thanks for your response.  We have a big interest in this module 
because without it, we have to use a combination of Perl and Python and 
it's pretty ugly.

I've dowloaded the version 0.36pre3 and turned on the debugging.  I'm 
still seeing the same error and it appears that the problem is with 
retrieving a text type column.  Here is part of the output.  The full 
output is attatched in the file "output".

ct_describe(cmd1, 9, &fmt) -> CS_SUCCEED, datafmt8=[name:"lengthScale" 
type:CS_FLOAT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:8]
ct_bind(cmd1, 9, &datafmt8->fmt=[name:"lengthScale" type:CS_FLOAT_TYPE 
status:CS_FALSE format:CS_FMT_UNUSED count:32 maxlength:8], 
databuf8->buff, databuf8->copied, databuf8->indicator) -> CS_SUCCEED, 
databuf8
ct_describe(cmd1, 10, &fmt) -> CS_SUCCEED, datafmt9=[name:"material" 
type:CS_TEXT_TYPE status:CS_CANBENULL format:CS_FMT_UNUSED count:1 
maxlength:2147483647]
Traceback (most recent call last):
  File "DBTest.py", line 10, in ?
    db.execute("SELECT * FROM FEMMD_Parameters")
  File "/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", line 
727, in execute
    result_list = self._fetch_results()
  File "/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", line 
745, in _fetch_results
    bufs = _row_bind(cmd, self.arraysize)
  File "/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", line 
159, in _row_bind
    status, buf = cmd.ct_bind(i + 1, fmt)
MemoryError
ct_cmd_drop(cmd1) -> CS_SUCCEED
ct_con_props(conn0, CS_GET, CS_CON_STATUS, &value, CS_UNUSED, NULL) -> 
CS_SUCCEED, CS_CONSTAT_CONNECTED
ct_close(conn0, CS_OPT_STATS_IO) -> CS_SUCCEED
ct_con_drop(conn0) -> CS_SUCCEED

There should be two more columns after the "material" column, so it 
seems that it does not finish retrieving one row, but stops at the first 
text type row.

Knowing this, I've tried a similar query on a table that does not have a 
text column and I get a different error.  Here is part of the output, 
the full output is attached in "output2".

ct_describe(cmd1, 8, &fmt) -> CS_SUCCEED, datafmt7=[name:"energy" 
type:CS_FLOAT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1 
maxlength:8 scale:0 precision:0]
ct_bind(cmd1, 8, &datafmt7->fmt=[name:"energy" type:CS_FLOAT_TYPE 
status:CS_FALSE format:CS_FMT_UNUSED count:32 maxlength:8 scale:0 
precision:0], databuf7->buff, databuf7->copied, databuf7->indicator) -> 
CS_SUCCEED, databuf7
ct_fetch(cmd1, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> 
CS_ROW_FAIL, 0
Traceback (most recent call last):
  File "DBTest.py", line 10, in ?
    db.execute("SELECT * FROM FEMMD_AtomicInfo")
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 732, in execute
    result_list = self._fetch_results()
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 751, in _fetch_results
    logical_result = self._fetch_logical_result(bufs)
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 760, in _fetch_logical_result
    rows = _fetch_rows(cmd, bufs)
  File 
"/afs/.msc.cornell.edu/i386_linux22/usr/local/pub/dm/lib/python2.1/site-packages/Sybase.py", 
line 196, in _fetch_rows
    raise Error('ct_fetch')
Sybase.Error: ct_fetch
ct_cmd_drop(cmd1) -> CS_SUCCEED
ct_con_props(conn0, CS_GET, CS_CON_STATUS, &value, CS_UNUSED, NULL) -> 
CS_SUCCEED, CS_CONSTAT_CONNECTED
ct_close(conn0, CS_OPT_STATS_IO) -> CS_SUCCEED
ct_con_drop(conn0) -> CS_SUCCEED

Thanks,
Valerie

Re: [python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Dave C. <dj...@ob...>]

> Hi, Dave, thanks for your response.  We have a big interest in this
> module because without it, we have to use a combination of Perl and
> Python and it's pretty ugly.

Funny, that is why I wrote the module - I was looking at either Perl
or ISQL...

[snip]

> ct_describe(cmd1, 10, &fmt) -> CS_SUCCEED, datafmt9=[name:"material"
> type:CS_TEXT_TYPE status:CS_CANBENULL format:CS_FMT_UNUSED count:1
> maxlength:2147483647]

I think that maxlength is the problem here.  Can you try a little
experiment for me...  Try this patch:

--- Sybase.py	23 Jan 2003 10:11:02 -0000	1.56
+++ Sybase.py	24 Jan 2003 01:01:13 -0000
@@ -158,6 +158,8 @@
         fmt.count = count
         if fmt.datatype == CS_VARBINARY_TYPE:
             fmt.datatype = CS_BINARY_TYPE
+        if fmt.maxlngth > 65536:
+            fmx.maxlength = 65536
         status, buf = cmd.ct_bind(i + 1, fmt)
         if status != CS_SUCCEED:
             raise Error('ct_bind')

I am not sure if will solve the problem or not, but it is worth a try.

> There should be two more columns after the "material" column, so it
> seems that it does not finish retrieving one row, but stops at the
> first text type row.
> 
> Knowing this, I've tried a similar query on a table that does not
> have a text column and I get a different error.  Here is part of the
> output, the full output is attached in "output2".
> 
> ct_describe(cmd1, 8, &fmt) -> CS_SUCCEED, datafmt7=[name:"energy"
> type:CS_FLOAT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1
> maxlength:8 scale:0 precision:0]
> ct_bind(cmd1, 8, &datafmt7->fmt=[name:"energy" type:CS_FLOAT_TYPE
> status:CS_FALSE format:CS_FMT_UNUSED count:32 maxlength:8 scale:0
> precision:0], databuf7->buff, databuf7->copied, databuf7->indicator)
> ->
> CS_SUCCEED, databuf7
> ct_fetch(cmd1, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) ->
> CS_ROW_FAIL, 0

This looks like another column binding problem.  Can you send a more
complete debug trace?

- Dave

-- 
http://www.object-craft.com.au

[python-sybase] MemoryError fixed!

[Valerie R. C. <vr...@co...>]

>
>
>I think that maxlength is the problem here.  Can you try a little
>experiment for me...  Try this patch:
>
>--- Sybase.py	23 Jan 2003 10:11:02 -0000	1.56
>+++ Sybase.py	24 Jan 2003 01:01:13 -0000
>@@ -158,6 +158,8 @@
>         fmt.count = count
>         if fmt.datatype == CS_VARBINARY_TYPE:
>             fmt.datatype = CS_BINARY_TYPE
>+        if fmt.maxlength > 65536:
>+            fmt.maxlength = 65536
>         status, buf = cmd.ct_bind(i + 1, fmt)
>         if status != CS_SUCCEED:
>             raise Error('ct_bind')
>
>  
>
Dave,

This patch works!  (I have edited for typos).  Furthermore, it works 
whether or not the text fields are all at the end.  I don't get the 
other error I was talking about as long as I don't select a field with a 
unique type.

Thanks!
Valerie

Re: [python-sybase] MemoryError using Sybase, FreeTDS, MS SQL

[Dave C. <dj...@ob...>]

> Knowing this, I've tried a similar query on a table that does not
> have a text column and I get a different error.  Here is part of the
> output, the full output is attached in "output2".

[snip]

> ct_command(cmd1, CS_LANG_CMD, "SELECT * FROM FEMMD_AtomicInfo", CS_NULLTERM, CS_OPT_AUTHOFF) -> CS_SUCCEED

[snip]

> ct_describe(cmd1, 1, &fmt) -> CS_SUCCEED, datafmt0=[name:"guid" type:CS_FORCE_CLOSE status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:16 scale:0 precision:0]

Hmm...  My debug code tries to convert the reported type of the column
back into a text string and comes up with CS_FORCE_CLOSE!?!

The debug code groups binary values into categories.  When doing a
lookup, it tries to resolve the value in the VAL_TYPE category and
fails.  It then just returns anything which matches the value.

Looking at the FreeTDS header, this value corresponds to the type
CS_UNIQUE_TYPE (which I don't have in my module).  Maybe I should
download the latest Sybase packages.

Try building statements which select one column at a time to see which
column types are causing the module to trip up.

- Dave

-- 
http://www.object-craft.com.au