Re: [python-sybase] Pulling my hair out about a query...

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

Skip Montanaro wrote:
>     >> I have a fairly straightforward query:
>     ...
>     >> that fails (returns no result rows) from Python. 
> 
>     stan> a.  Is it possible to get an error code from the
>     stan> cursor?  It should be an attribute you can print.
> 
> I don't see anything like an error attribute and cursor objects done have
> __getattr__ methods so it can't be a dynamically retrieved thing.  I also
> checked the cursor's _fetcher attribute and the connection for error-like
> attributes but didn't see anything.
> 
>     stan> b.  Can it be that the date routine is not being
>     stan> called until after the main query has run?  You could
>     stan> check this by declaring variables and setting them
>     stan> with the date call before you run the actual query. 
>     stan> If the dates are unassigned while the query runs, you
>     stan> would get no results.
> 
> I'm not sure what you mean here.  When run, this code
> 
>     import Sybase
> 
>     q = """\
>     SELECT trade_time, price
>       FROM underlying_daily_fp
>       WHERE symbol = @sym
>         AND instrument = @inst
>         AND trade_time BETWEEN @start AND @end
>         AND (price_type='H' or price_type='L')
>       ORDER BY trade_time DESC
>     """
> 
>     args = {'@sym': 'ES',
>             '@start': Sybase.Date(2004, 4, 19),
>             '@end': Sybase.Date(2004, 5, 17),
>             '@inst': 'F'
>             }
> 
>     print [(k,type(v),v) for (k,v) in args.items()]
> 
>     conn = Sybase.Connection(...)
>     c = conn.cursor()
>     c.execute(q, args)
>     print c.fetchall()
> 
> prints this output:
> 
>     [('@sym', <type 'str'>, 'ES'), ('@start', <type 'DateTimeType'>, 'Apr 19
>     2004 12:00AM'), ('@end', <type 'DateTimeType'>, 'May 17 2004 12:00AM'),
>     ('@inst', <type 'str'>, 'F')] 
>     []
> 
> The DateTimeType objects are clearly created before the query executes.  In
> any case, I'm not sure what you mean by "declaring variables and setting
> them with the date call".  Is this something I embed in my query string?
> 
> If I twiddle the trade_time clause to be any of
> 
>         AND trade_time >= @start
>         AND trade_time <= @end
> 
> or
> 
>         AND trade_time >= @start
> 
> or
> 
>         AND trade_time <= @end
> 
> the query always returns empty results.  If I remove the trade_time clause
> altogether the query succeeds, but of course the date range of the returned
> results is incorrect.

Can you try doing something like the following:

import Sybase

Sybase.set_debug(open('debug.log', 'w'))
    :
conn = Sybase.Connection(..., debug=1)
    :

Then the module will produce quite a bit of debugging output in the log 
file.  I hope that some of the output in the log file will shed some 
light on what is going on.

- Dave

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