Re: DBD::Oracle, Support binding of integers so they are returned as IVs

Just to let you guys know I am following this very closely

Right now I as still pinned down with regular work but hopefully this 
week I will get some DBD::Oracle time.

So far I have to agree with Tim about the 'magic numbers'

I think as well as we do a describe before the execute we can get the 
size automatically and sizer them up there a little better than I do now.

we do get the

OCI_ATTR_DATA_TYPE and  OCI_ATTR_DATA_SIZE

and I handle the number stuff like this


case    ORA_NUMBER:                /* NUMBER    */
            case    21:                /* BINARY FLOAT os-endian    */
            case    22:                /* BINARY DOUBLE os-endian    */
            case    100:                /* BINARY FLOAT oracle-endian    */
            case    101:                /* BINARY DOUBLE oracle-endian    */
                fbh->disize = 130+38+3;        /* worst case    */
                avg_width = 4;     /* NUMBER approx +/- 1_000_000 */
                break;

maybe we can just diddle with this code and fix it for you long ints

Like you I am just taking a quick look at it

chees
John Scoles

Martin Evans wrote:
> Thanks Tim for the help on this.
>
> Tim Bunce wrote:
>   
>> On Mon, Oct 26, 2009 at 05:29:21PM +0000, Martin Evans wrote:
>>     
>>> What follows is a very rough patch (definitely not finished) which
>>> proves you can do what I wanted to do. However, there on no checks on
>>> the column being bound existing and I'm not sure how to save the TYPE
>>> attribute when bind_col is called before execute (that is when the
>>> result-set is not described yet). Basically, I think more is required in
>>> dbd_st_bind_col but I've not sure as yet what that is and it is possible
>>> returning 1 is a total hack. I'd appreciate any advice to complete this.
>>>
>>> Index: oci8.c
>>> ===================================================================
>>> --- oci8.c	(revision 13427)
>>> +++ oci8.c	(working copy)
>>> @@ -3279,10 +3279,31 @@
>>> +
>>> +                    if ((fbh->req_type == 3) &&
>>> +                        ((fbh->dbtype == 2) || (fbh->dbtype == 3))){
>>>       
>> Best to avoid 'magic numbers'.
>>     
>
> As I said = very rough. I'd already changed those to SQLT_NUM and
> SQLT_INT as ORA types but I guessed they would need to be SQL_INTEGER,
> SQL_NUMERIC, SQL_DOUBLE when finished i.e. you use the DBI types not the
> oracle types here since the data is coming back into perl.
>
>   
>>> +                        char *e;
>>> +                        char zval[32];
>>> +                        long val;
>>> +
>>> +                        memcpy(zval, p, datalen);
>>> +                        zval[datalen] = '\0';
>>> +                        val = strtol(zval, &e, 10);
>>> +
>>> +                        if ((val == LONG_MAX) || (val == LONG_MIN) ||
>>> +                            (e && (*e != '\0'))) {
>>> +                            oci_error(sth, imp_sth->errhp, OCI_ERROR,
>>> +                                      "invalid number or over/under flow");
>>> +                            return Nullav;
>>> +                        }
>>> +                        sv_setiv(sv, val);
>>> +                    } else {
>>> +                        sv_setpvn(sv, p, (STRLEN)datalen);
>>> +                        if (CSFORM_IMPLIES_UTF8(fbh->csform) ){
>>> +                            SvUTF8_on(sv);
>>> +                        }
>>> +                    }
>>>       
>
> Tried your suggestion of the grok_number but it does not work well for
> negative numbers since it returns the abs then and puts the result in a
> UV which may not fit signed into an IV. Anyway, you seem to have had
> other ideas so I'll not worry about that too much.
>
>   
>> A simpler safer and more portable approach may be to just let the
>> existing code store the value in an sv and then add these lines:
>>
>>     if (fbh->req_type == 3)
>>         sv_2iv(sv);
>>
>> If the number is too large for an IV (or UV) you'll get an NV (float).
>> The original string of digits is preserved in all cases. That's all very
>> natural and predictable perlish behaviour.
>>     
>
> Ok, I get that except you keep saying "(or UV)". Are you suggesting
> there is some other logic to decide whether you create an IV or a UV?
>
> I tried out various values and sv_2iv(sv) and what was returned looked
> ok - I get a string when the number has decimal places or is too big and
> an IV when it is an integer and fits.
>
>   
>> The next question is whether overflowing to an NV should be an error.
>> I'm thinking we could adopt these semantics for bind_col types:
>>
>>   SQL_INTEGER  IV or UV via sv_2iv(sv) with error on overflow
>>     
>
> this would be ideal.
>
>   
>>   SQL_DOUBLE   NV via sv_2nv(sv)
>>   SQL_NUMERIC  IV else UV else NV via grok_number() with no error
>>
>> I could sketch out the logic for those cases if you'd be happy to polish
>> up and test.
>>     
>
> I would be happy to do that.
>
> BTW, did you look over the possible hackery I did in dbd_st_bind_col - I
> wasn't sure if simply storing the requested type and returning 1 was
> acceptable. My current dbd_st_bind_col is:
>
> int dbd_st_bind_col(SV *sth, imp_sth_t *imp_sth, SV *col, SV *ref, IV
> type, SV *attribs) {
>     dTHX;
>
>     int field = SvIV(col);
>
>     if (field <= DBIc_NUM_FIELDS(imp_sth)) {
>         imp_sth->fbh[field-1].req_type = type;
>     }
>
>     return 1;
> }
>
> This means if someone attempts to bind a non-existent column it falls
> back into DBI's bind_col and signals the error but it also means
> dbd_st_bind_col in DBD::Oracle is only there to capture the requested
> bind type.
>
> Thanks
>
> Martin
>