[DB-SIG] Help With Cursors

Robert Theiss rtheiss@yahoo.com
Wed, 14 Aug 2002 05:46:13 -0700 (PDT)


I don't believe the problem is in the SQL layer.  when
I run the query (as written) via ISQL, it works.  I
can also run the same query through MS-Access using an
ODBC connection to informix.  Sorry I didn't make that
clear in the original message.  That's one of the
reasons I think the error is in the fetchall
statement.  In addition, I know that the select
statement runs successfully in my Python script form
looking at the return code.  The fetchall is where it
is failing.

I'll check the reference you sent as well.  Thanks for
the help.

Begards,
Bob

--- Marcos Sánchez Provencio <msanchez@grupoburke.com>
wrote:
> The problem lies in the SQL layer. Your query seems
> to be unorthodox
> when asking for operations on NULL values. You would
> get the same answer
> from a isql/sqlplus/psql/whatever the minimal
> interface for informix is.
> 
> This might do as reference:
>
http://www.devx.com/upload/free/features/vbpj/2002/04apr02/jf0402/jf0402-3.asp
> 
> El mar, 13-08-2002 a las 22:25, Robert Theiss
> escribió:
> > I am trying to fetch a small number of rows into a
> > cursor, using informixdb.py.  I get the following
> > error on the fetchall statement when executing the
> > code:
> > 
> > Exception type: InformixdbError
> > Exception value: Error 0 performing FETCH: Null
> value
> > eliminated in set function
> > 
> > The relevant code looks like this:
> > 
> >    SqlSelectStatement = """
> >     select   
> >       loc_nbr,
> >       inv_loc_nbr,
> >       txn_type, 
> >       txn_code,
> >       txn_reason,
> >       NVL(txn_from_disp, 'NONE'),
> >       NVL(txn_to_disp, 'NONE'),
> >       sum(qty_txn),
> >       sum((qty_txn*avg_cost))
> >     from
> >       inv_audit
> >     where
> >       inv_loc_nbr =  %i and
> >       txn_datet >= '2002-07-01 00:00' and
> >       txn_datet <  '2002-08-01 00:00' 
> >     group by 
> >      loc_nbr,
> >      inv_loc_nbr,
> >      txn_type,
> >      txn_code,
> >      txn_reason,
> >      txn_from_disp,
> >      txn_to_disp;
> >       """ % EomInvLocNbr 
> >    try:
> >     db.execute('set isolation dirty read;')
> >    except db.error:
> >     exc_type, exc_value = sys.exc_info()[:2]
> >     ExceptionHandler(exc_type, exc_value,
> DataFile,
> > LogFile, db)
> >    print "dirty read set " 
> > 
> >    try:
> >     db.cursor()   
> >     db.execute(SqlSelectStatement)
> >    except db.error:
> >     exc_type, exc_value = sys.exc_info()[:2]
> >     ExceptionHandler(exc_type, exc_value,
> DataFile,
> > LogFile, db)
> >    print "select done "  
> > 
> >    try:
> >     TransactionList = []	   
> >     TransactionList = db.fetchall()
> >    except db.error:
> >     exc_type, exc_value = sys.exc_info()[:2]
> >     ExceptionHandler(exc_type, exc_value,
> DataFile,
> > LogFile, db)
> > 
> >    for record in TransactionList:
> >      InvAudLocNbr = record[0]
> >      InvAudInvLocNbr = record[1]
> >      InvAudTxnType = record[2]
> >      InvAudTxnCode = record[3]
> >      InvAudTxnReason = record[4]
> >      InvAudTxnFrom = record[5] 
> >      InvAudTxnTo = record[6]
> >      InvAudQty = record[7] 
> >      InvAudAvgCost = record[8] 
> > 
> > In the above code, the problem seems to be in the
> > fields txn_from_disp and txn_to_disp.  Either of
> these
> > fields could be null.  When I take them out of the
> > select statement, the program works like a charm.
> > 
> > >From researching other message threads, it
> appears
> > that fetchall() should handle NULL values, but my
> code
> > obviously does not.
> > 
> > Any help is appreciated.
> > 
> > Regards,
> > 
> > Bob Theiss    
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > HotJobs - Search Thousands of New Jobs
> > http://www.hotjobs.com
> > 
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG@python.org
> > http://mail.python.org/mailman/listinfo/db-sig
> 
> 


__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com