[Zope-DB] DCOracle2 query return null value column

[jerry at longyouth.com]

Hello every one,

        I am having trouble using DCOracle2(compile with oci8) to query the oracle database(8.1.7/9.2.1).I am sending a sql like "select column_name from table_name" throught DCOracle2 to oracle,all row are return,but some row return with field value None which actually do have value.
        
        I try many many times with following conclusion:
        1 If I change that column from Nullable to mandatory,all value return correctly
        2 changing cursor.arraysize to higher than 20 will disappear this problem
        3 using setPrefetch to some value(like None/1000/100) will solve this problem,but set to some other value like 21/22 may not work(this really confuse me)
        4 setting value some specific null field sometimes will make the null value go away(but not for sure)
        5 if two result row with same result,the next row may be turn into null value
	
	with more test,this seems to be oci8's problem:
	6 with a watch of ResultSet->indp,I found that oci return indp=-1 for those non-null field,dco2.c's code list below:

  data = self->rs->valuep;
  data += self->item * self->rs->width;

  rlenp = (ub2 *) self->rs->rlenp;
  rlenp += self->item;

  indp = self->rs->indp;
  indp += self->item;

  //check if fetch call return status was OCI_SUCCESS_WITH_INFO

++++++++oci return those non-null field with indp=-1,so DCOracle2 just skip it with None -++++++++return,this seems to be an oci8's bug

  if (*indp == -1
#ifndef ORACLE8i 
      && self->rs->fetchResultCode==OCI_SUCCESS_WITH_INFO
#endif
      ) {     
    Py_INCREF(Py_None);
    TRACE(T_EXIT,("sAs", "ResultSetItem_value", Py_None,
      "NULL"));
    return Py_None;
  }

	7. as I read the oracle doc,I found following descript for indicator variable(indp):

Table 2-8 Output Indicator Values 
Output Indicator Value  Meaning  
-2 
 The length of the item is greater than the length of the output variable; the item has been truncated. Additionally, the original length is longer than the maximum data length that can be returned in the sb2 indicator variable. 
 
-1 
 The selected value is null, and the value of the output variable is unchanged. 
                                       ~~~~~
                                       or??
 
0 
 Oracle assigned an intact value to the host variable. 

	I am really confused,as I describe in point 5,all null value is actually with same value as last row(same fields).

	8. I try to use setPrefetch from 0-100,and check each time if it has this problem,I got following number that will cause this problem(9-35,60-73),this can be reproduced stablly,code list below:

import DCOracle2

conn = DCOracle2.Connect("jerry/jerry at jdata")
for i in xrange(0, 100):
  cur = conn.cursor()
  cur.setPrefetch(i)
  cur.execute("select prod_property_1 from a_month_fee_type")
  rec = cur.fetchall()
  if rec[-3][0] is None:   #this field should has value,if null return,this number failed this test
    print "%d failed"%i
  else:
    print "%d passed"%i
	
	9.the current table do test above has 90 rows of record,as I increase the record,those problem-cause number will changed and decreased,as I increase the table to about 600 rows,all number(0-100) can return correct value.
 


        I also try to search the google,and find someone posted somekind of same behavior of DCOracle2,see:http://mail.python.org/pipermail/db-sig/2003-March/003303.html,it point me to 
http://www.zope.org/Members/matt/dco2/Tracker/61 for help,but I can't get there.even I register myself, and following the DCOracle2 issue tracker link(http://www.zope.org/Members/matt/dco2/Tracker),I just get the following pages

 Insufficient Privileges
You do not have sufficient privileges to view this page.

If you are getting this message when trying to edit your own zope.org content, remember that you must retract published items before you may edit them. To retract an item, click on the State link to view the publishing status form and select "retract" from the publishing options. 

If you believe you are receiving this message in error, please send an e-mail to webmaster at zope.org. 

        can anyone help me about this problem or help me reach the issue tracker?

        btw:DCOracle2 compile with oracle 9i works without this problem.

        thanks in advance...
-- 
Best regards,
 jerry                          mailto:jerry at longyouth.com

-- 
Best regards,
 jerry                          mailto:jerry at longyouth.com