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

[stan lysiak]

--- Skip Montanaro <skip@pobox.com> wrote:
> I have a fairly straightforward query:
> 
>     SELECT trade_time, price
>       FROM underlying_daily_fp d, underlying_months
> m
>       WHERE d.instrument = @inst
>         AND d.symbol = @sym
>         AND m.instrument=d.instrument
>         AND m.symbol=d.symbol
>         AND (price_type='H' or price_type='L')
>         AND m.exp_year=d.exp_year
>         AND m.exp_month=d.exp_month
>         AND trade_time BETWEEN @start AND @end
>         AND d.trade_time=m.date
>         AND m.exp_num=1
>       ORDER BY trade_time  DESC
> 
> with args:
> 
>     {'@sym': 'ES',
>      '@start': 'Apr 19 2004 12:00AM',
>      '@end': 'May 17 2004 12:00AM',
>      '@inst': 'F'}
> 
> that fails (returns no result rows) from Python. 
> (The @start and @end
> params are actually Sybase.Date() objects.) 
> However, the equivalent code
> pasted into isql (using strings for dates) works as
> expected:
> 
>     1>  select trade_time, price from
> underlying_daily_fp d, underlying_months m
>        where d.instrument='F'
>        and d.symbol='ES'
>        and m.instrument=d.instrument
>      and m.symbol=d.symbol
>      and (price_type='H' or price_type='L')
>      AND m.exp_year=d.exp_year
>      AND m.exp_month=d.exp_month
>      AND trade_time BETWEEN 'Apr 19 2004 12:00AM'
> and 'May 17 2004 12:00AM'
>      AND d.trade_time=m.date
>      AND m.exp_num=1
>      ORDER BY trade_time  DESC
>     2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
>     14> go
>      trade_time                 price               
> 
>      -------------------------- --------------------
> 
>             May 17 2004 12:00AM          1078.250000
> 
>             May 17 2004 12:00AM          1089.250000
> 
>             ...
>             Apr 19 2004 12:00AM          1135.250000
> 
> 
>     (42 rows affected)
>     1> 
> 
> Furthermore, if I manually expand the arguments and
> call the connection's or
> cursor's execute() methods:
> 
>     >>> q
>     "SELECT trade_time, price\n  FROM
> underlying_daily_fp d, underlying_months m\n  WHERE
> d.instrument = 'F'\n    AND d.symbol = 'ES'\n    AND
> m.instrument=d.instrument\n    AND
> m.symbol=d.symbol\n    AND (price_type='H' or
> price_type='L')\n    AND m.exp_year=d.exp_year\n   
> AND m.exp_month=d.exp_month\n    AND trade_time
> BETWEEN 'Apr 19 2004 12:00AM' AND 'May 17 2004
> 12:00AM'\n    AND d.trade_time=m.date\n    AND
> m.exp_num=1\n  ORDER BY trade_time  DESC"
>     >>> len(db.execute(q)[0])
>     42
>     >>> c = db.cursor()
>     >>> c.execute(q)
>     >>> len(c.fetchall())
>     42
> 
> it also works.
> 
> Is it date handling?  Is it possible to get the
> actual SQL statement
> executed by the server so I can see if and where
> I've gone wrong?  Do other
> people experience this problem (works from isql but
> not Python)?  The
> platform is OCS 12.5 and the latest version of the
> Sybase module running on
> Mandrake 10.
> 
> Thanks,
> 
> Skip Montanaro
> skip@pobox.com
> _______________________________________________
> Python-sybase mailing list
> Python-sybase@www.object-craft.com.au
>
https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
> 


Hi Skip,

I don't have a solution to your problem, just a couple
of suggestions.  

a.  Is it possible to get an error code from the
cursor?  It should be an attribute you can print.
b.  Can it be that the date routine is not being
called until after the main query has run?  You could
check this by declaring variables and setting them
with the date call before you run the actual query. 
If the dates are unassigned while the query runs, you
would get no results.

Just some thoughts.

Stan



		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail