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

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
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com