[Dbix-class] Getting numeric columns as numeric values

[Martin J. Evans]

On 26/02/15 19:05, Peter Rabbitson wrote:
> On 02/26/2015 07:04 PM, Augustus Saunders wrote:
>>
>> The problem originates in DBI drivers
>
> Not true for most DBDs
>
>> In the interim, numifying when the user asks for the value seems simple.
>
> And architecturally incorrect. Just like another 40-odd features I am holding because they are not good enough for general consumption of the *extremely wide* userbase of DBIC.
>
>> As for performance, my understanding is that extracting the numeric part of a scalar is essentially free.
>
> Nothing is "essentially free".
>
> If checking to see whether a column is numeric is expensive, then that should be cached when the column is created/configured, and not recalculated when extracting values.
>
> This has already been the case for years.
>
>> In all fairness, anybody who cares about this probably isn't going to use a heavy ORM tool in the first place. Hell, witness the fact that most of the database drivers haven't fixed this problem; nobody seems to care about performance *that much*.
>
> Let's start with "I care" and... leave it at that? :)
>
> TL;DR: Your proposed fix is not going to be merged, besides you were already given a non-invasive and reliable solution.
>
> Cheers

Just as a reminder but I'm sure Peter knows this already.

When I came across this problem (not using DBIC) we started adding 0 to every numeric column when it was fetched but that became a pain. Then, with Tim's help I added

http://search.cpan.org/~timb/DBI-1.633/DBI.pm#sql_type_cast

http://search.cpan.org/~timb/DBI-1.633/DBI.pm#bind_col
StrictlyTyped and DiscardString

So when I fetch data with DBD::Oracle or DBD::ODBC which I know are numbers and want to encode as JSON I do something like:

my $s = prepare(q/select a_number from table/);
$s->bind_col(1, \my $dest, {DiscardString => 1, TYPE => SQL_INTEGER});
$s->fetch;
encode_json($dest);

and $dest is a number JSON::XS recognises as such and encodes as NN instead of "NN".

Not that I'm suggesting this is relevant to DBIC, just adding the info of a solution in another arena.

Martin