Re: [cx-oracle-users] A probably stupid question...

[Anthony T. <ant...@gm...>]

On Tue, Aug 28, 2012 at 1:44 PM, Kim <kim...@ya...> wrote:
> whatstr="SELECT field111 from MSG_ARCHIVE where "+searchField+" =:record_number
> and field4 between :s_time and :e_time"
> curs.prepare(whatstr)
> curs.execute(None, {'record_number':mrn,'s_time':start_time, 'e_time':end_time})
>
> searchField is actually field111, mrn is a set number (9 digits),
> the start_time and end_time are oracle timestamps.
>
> This simply hangs -- no error, no return. If I hit ctrl-c, it simply says
> keyboard interrupt (no kidding...).
>
> I am having difficulties with a python script, that was working with our 10g
> database and 11g client, but now does not work...
>
> whatstr=('select '+out_fields+' from MSG_ARCHIVE where '+searchField+' =
> :record_number and field4 between :s_time and :e_time')
> print whatstr
> curs.execute(whatstr,{'record_number':mrn, 's_time':start_time,
> 'e_time':end_time})
>
> Can anyone think of why this would have changed? I can run either a
> record_number search or a "between timestamp" search and have it work fine.
>
> Thank you very much for your time!

The problem is likely an issue with bind variable values or type
mismatches but you can only really find out by testing and debugging.
Nothing obvious to my mind from what you have written. You can make
things a lot simpler, though.

cursor.execute(sql, record_number = mrn, s_time = start_time, e_time = end_time)

Questions to consider:

If you replace the bind variables with constants does the query return
as expected?
What are the actual types of the column values that are being queried?
What are the actual types of the bind variables being passed in?
Can you issue the same query in SQL*Plus and have it return as expected?

Hopefully some of those will help you diagnose the problem. I've had a
few of these and they can be very painful to figure out. They're
"obvious" once you figure them out, of course, but getting there can
be very time consuming. Good luck!

Anthony