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

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----                            
> Hash: RIPEMD160                                               
>
>
>   
>> I'm mostly settled on DBIstcf_STRICT _not_ being the default.
>> So 'loosely typed' will be the default and people who want an error
>> if the type can't be cast safely will need to use StrictlyTyped => 1.
>>                                                                      
>> I'm (slightly) open to persuasion, but that seems to fit the general 
>> philosophy of perl and the DBI better.  It also makes transition easier
>> for anyone who happens to have code that calls bind_column with an SQL type.
>>     
>
> I'd opine that since we're dealing with databases holding Important Things, 
> the default should be to throw an error and make people flip a switch       
> to get lax mode. A large warning in the upgreade notes should suffice for   
> anyone transitioning.                                                       
>
>   
>> I'm undecided about the name of the attribute to indicate use of
>> DBIstcf_DISCARD_PV. MinMemory or DiscardString or ...?          
>> I'm leaning more towards DiscardString => 1 as it directly describes
>> what it does rather than a side-effect.                             
>>     
>
> +1 to DiscardString
>
>   
>> o I wouldn't mind someone else looking over the changes as although I
>> currently maintain DBD::ODBC I do not consider myself a DBI internals of
>> XS expert.
>>     
>
>   
>>> Post a diff and I'll review it for you. The code you appended previously
>>> looks ok.
>>>       
>
> Ditto.
>
>   
>> o Although my primary impetus for doing this is I need it in DBD::Oracle
>> I would plan to do something similar in DBD::ODBC once this stuff is
>> committed.
>>     
>
> Does anyone know if there are other databases that have mapping issues with
> integers and Perlish integers other than Oracle? Just curious, as I only
> personally know how a handful deal with ints internally.
>
>
>   
Greg, the issue I started by trying to address is rather specific to our 
use of JSON::XS and roughly described at  
http://rt.cpan.org/Public/Bug/Display.html?id=49818. The problem we 
faced is not specific to DBD::Oracle and applies to many DBDs (and 
certainly DBD::ODBC) although I believe it was addressed in Postgres a 
while ago. I don't think there is anything fundamentally wrong with what 
most DBDs do right now - Perl has always worked on the basis that you 
shouldn't need to know the way the underlaying data is stored, Perl will 
just do the right thing for the context you use the data. On the other 
hand we have the TYPE attribute to bind_col and no DBDs seems to do 
anything with it. While some may (and do) argue JSON::XS shouldn't do 
what it does I think the DBI side of this has moved on a bit now with 
these changes. Few, if any, DBDs pay any attention to the TYPE attribute 
on the bind_col method but /could/ benefit from knowing the type before 
the data is retrieved. For instance, DBD::ODBC (like may other DBDs) 
binds all the data from the database as strings but in many cases if you 
know the data is really an integer (or will be used as an integer) it is 
more efficient to retrieve it as such from the database. The TYPE 
argument to bind_col provides that information and these changes (if 
implemented in a DBD) simply arrange for the retrieved data to be "cast" 
to a Perl scalar with only the IV etc set and not the pv (assuming 
DiscardString is used).

A big (pardon the pun) gain (other than the above) would be to support 
SQL_BIGINT. The database we are using is Oracle and it supports very 
large integers and as we know these will get (for primary keys etc) too 
big to fit in an integer or long integer we have to treat them as 
strings but they are not strings. Also, Oracle (and perhaps other 
databases) don't always like accepting strings for columns which are 
integers (they have to convert the strings and then sometimes decide 
that because of that they won't use an index on that column). I'd like 
to follow these changes up with some investigation for big integers and 
I'll hopefully find some time to do just that but one thing at a time.

Martin