cx-oracle-users

[cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Himanshu <him...@gm...>]

Hi,

    The following code doesn't return any rows but the same query returns
300+ rows when run from sql developer. What could I be missing? How to debug
this?

import cx_Oracle
# snipped setting up connection_string
connection        = cx_Oracle.connect(**connection_string)
cursor            = connection.cursor()
cursor.execute('''
    SELECT s.hasc, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
       JOIN statoids s
         ON s.capital = g.city
      WHERE log_date >= '10-Feb-11'
        AND log_date <= '10-Feb-11'
        AND g.country  =  'IND'
   GROUP BY s.hasc, status
''')
print cursor.fetchall()

Thank You,
Himanshu

Re: [cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Amaury F. d'A. <ama...@gm...>]

Hi,

2011/3/1 Himanshu <him...@gm...>:
>       WHERE log_date >= '10-Feb-11'
>         AND log_date <= '10-Feb-11'

You should avoid date literals in SQL statements. The exact format may
depend on environment
variables (language...) and other Oracle client settings.
Use something like to_date('10-02-2011', 'DD-MM-YYYY')
Also, this WHERE clause only selects lines where "log_date" has no
time (or midnight).
Is it really the case in your data?

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Himanshu <him...@gm...>]

On 1 March 2011 15:29, Amaury Forgeot d'Arc <ama...@gm...> wrote:

> Hi,
>
> 2011/3/1 Himanshu <him...@gm...>:
> >       WHERE log_date >= '10-Feb-11'
> >         AND log_date <= '10-Feb-11'
>
> You should avoid date literals in SQL statements. The exact format may
> depend on environment
> variables (language...) and other Oracle client settings.
> Use something like to_date('10-02-2011', 'DD-MM-YYYY')
> Also, this WHERE clause only selects lines where "log_date" has no
> time (or midnight).
> Is it really the case in your data?
>
Thanks for the reply. The actual code has a variable that populates with the
date time value oracle needs. This was the smallest test case I could create
for my post. Sorry for the confusion though.

What puzzles me is that the following code returns rows from python  :-

-- begin working code with one join less --

import cx_Oracle
connection_string = {snipped}
connection        = cx_Oracle.connect(**connection_string)
cursor            = connection.cursor()
cursor.execute('''
    SELECT g.city, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
      WHERE log_date >= to_date('10-02-2011', 'DD-MM-YYYY')
        AND log_date <= to_date('10-02-2011', 'DD-MM-YYYY')
        AND g.country  =  'IND'
   GROUP BY g.city, status
''')
print cursor.fetchall()

 -- end working code with one join less --

and the following works from sql developer

-- begin identical working query --

SELECT s.hasc, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
       JOIN statoids s
         ON s.capital = g.city
      WHERE log_date >= '10-Feb-11'
        AND log_date <= '10-Feb-11'
        AND g.country  =  'IND'
   GROUP BY s.hasc, status;

-- end identical working query --

but the following doesn't :-

-- begin not working code --

import cx_Oracle
connection_string = {snipped}
connection        = cx_Oracle.connect(**connection_string)
cursor            = connection.cursor()
cursor.execute('''
    SELECT s.hasc, status, sum(count)
       FROM a_counts m
       JOIN geo g
         ON m.geo_id = g.id
       JOIN cities c
         ON c.accent_city = g.city
       JOIN statoids s
         ON s.capital = g.city
      WHERE log_date >= to_date('10-02-2011', 'DD-MM-YYYY')
        AND log_date <= to_date('10-02-2011', 'DD-MM-YYYY')
        AND g.country  =  'IND'
   GROUP BY s.hasc, status
''')
print cursor.fetchall()

-- end not working code --

Re: [cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Doug H. <djh...@te...>]

On 2011-03-01 02:59, Amaury Forgeot d'Arc wrote:
> Hi,
>
> 2011/3/1 Himanshu <him...@gm...>:
>>       WHERE log_date >= '10-Feb-11'
>>         AND log_date <= '10-Feb-11'
> You should avoid date literals in SQL statements. The exact format may
> depend on environment
> variables (language...) and other Oracle client settings.
> Use something like to_date('10-02-2011', 'DD-MM-YYYY')
> Also, this WHERE clause only selects lines where "log_date" has no
> time (or midnight).
> Is it really the case in your data?
>
SQL Developer silently issues ALTER SESSION commands based on the
settings in the Tools-Preferences dialog, NLS panel. It can also import
login scripts, which might contain alter session commands. All this in
addition to the impact of environment variables, and even windows
registry settings.

Your date literals may not be interpreted in the way you expect.

Try using the DATE'yyyy-mm-dd' format, or provide an explicit conversion
for your date literals.

-- 
Doug Henderson, Calgary, Alberta, Canada

Re: [cx-oracle-users] Query returns zero rows via cx_oracle but some from sql developer

[Himanshu <him...@gm...>]

On 3 March 2011 06:49, Doug Henderson <djh...@te...> wrote:

>  On 2011-03-01 02:59, Amaury Forgeot d'Arc wrote:
> > Hi,
> >
> > 2011/3/1 Himanshu <him...@gm...>:
> >>       WHERE log_date >= '10-Feb-11'
> >>         AND log_date <= '10-Feb-11'
> > You should avoid date literals in SQL statements. The exact format may
> > depend on environment
> > variables (language...) and other Oracle client settings.
> > Use something like to_date('10-02-2011', 'DD-MM-YYYY')
> > Also, this WHERE clause only selects lines where "log_date" has no
> > time (or midnight).
> > Is it really the case in your data?
> >
> SQL Developer silently issues ALTER SESSION commands based on the
> settings in the Tools-Preferences dialog, NLS panel. It can also import
> login scripts, which might contain alter session commands. All this in
> addition to the impact of environment variables, and even windows
> registry settings.
>
> Your date literals may not be interpreted in the way you expect.
>
> Try using the DATE'yyyy-mm-dd' format, or provide an explicit conversion
> for your date literals.
>
Thanks a lot for the reply. The problem was just this behavior of sql
developer. It had some permissions which my python code didn't have for the
same user. It worked on doing a grant select on one of the tables. Further
investigation needed but I am done for now.

Thanks,
Himanshu