[Zope-DB] DCOracle2 query return null value column

Matthew T. Kromer matt at zope.com
Mon Sep 29 12:07:59 EDT 2003

If you haven't pulled down the latest CVS code, that's what I suggest 
using.  I think the problems you're seeing will go away with respect to 
NULLs occasionally showing up in the output.

The tracker on zope.org is still busted; last time I change the 
permissions on it, it worked briefly then failed again.

erry at longyouth.com wrote:

>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
>      ) {     
>    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  
> 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. 
> The selected value is null, and the value of the output variable is unchanged. 
>                                       ~~~~~
>                                       or??
> 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...

Matt Kromer
Zope Corporation  http://www.zope.com/ 

More information about the Zope-DB mailing list