[DB-SIG] Help With Cursors

Robert Theiss rtheiss@yahoo.com
Tue, 13 Aug 2002 13:25:28 -0700 (PDT)


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