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

Thanks for the reply John.

John Scoles wrote:
> Sounds like an easy patch to DBD::Oracle (off the top of my head) I am
> not sure how it would fix into the DBI spec though.
> 
> If I am reading the question right you want to be able to tell
> DBI/DBD::Oracle that col X of a return is an int?

Yes

> something like
> 
> $SQL='select my_id,my_name from my_table'
> 
> my $C=$DBH->prepare($SQL,{row1=int})
> 
> for lack of a better example

example from DBI docs below.

> cheers
> John Scoles

DBI for bind_col says:

=====
The \%attr parameter can be used to hint at the data type formatting the
column should have. For example, you can use:

$sth->bind_col(1, undef, { TYPE => SQL_DATETIME });

to specify that you'd like the column (which presumably is some kind of
datetime type) to be returned in the standard format for SQL_DATETIME,
which is 'YYYY-MM-DD HH:MM:SS', rather than the native formatting the
database would normally use.

There's no $var_to_bind in that example to emphasize the point that
bind_col() works on the underlying column and not just a particular
bound variable.
=====

so I think what I want to do is already in the spec but not implemented
by DBD::Oracle. I want to call bind_col saying SQL_INTEGER then call
fetchall_arrayref and get IVs back for those columns instead of SVs.

So DBD::Oracle would need to know bind_col was called with a type and
save the type then at fetch time create an IV instead of an SV.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> Martin J. Evans wrote:
>> Hi,
>>
>> With reference to the rt I created "Support binding of integers so they
>> are returned as IVs" at
>> http://rt.cpan.org/Public/Bug/Display.html?id=49818
>>
>> I am now at the point where being unable to bind columns to results-sets
>> in DBD::Oracle with a bind type of SQL_INTEGER (or whatever) so they
>> look like integers in Perl is slowing some code of ours down
>> dramatically. We convert a fetchall_arrayref returned structure into
>> JSON with JSON::XS and JSON::XS converts strings to "string" and numbers
>> to a plain number. Our select returns a number of columns which are
>> really integer columns and as the result-set is very large the extra
>> space we use encoding integers as "number" is more than just annoying.
>> JSON::XS appears to know a perl scalar has been used in the context of a
>> number as if we add 0 to the integer columns returned in
>> fetchall_arrayref it encodes them as plain numbers instead of strings
>> like "number" (see the rt for the snippet from JSON::XS which does
>> this). As a result, a workaround we are using now is to loop through the
>> rows adding 0 to all integer columns. I believe DBI allows bind_col to
>> be called without a destination scalar so you can use it to specify the
>> type of the bind e.g., SQL_INTEGER but still call fetchall_arrayref.
>>
>> Does anyone know if it is feasible to make this work with DBD::Oracle
>> and if so do you have some pointers as to how it may be achieved. I am
>> not looking for anyone else to do the work but would like to sound
>> people out about the possibility before I launch into it.
>>
>> Thanks
>>
>> Martin
>>   
> 
>