Can DBI output or retrieve numeric column types when retrieving rows?

Question

Suppose I query a table with the following:

$dbh->selectrow_hashref('SELECT id, name FROM foos WHERE name = "bar"'); 

Naturally, id will be an integer, but the resulting hashref will have a value internally stored as Perl PV, not IV. This, in turn, will lead to an undesirable result when serializing data, for example, JSON.

Of course, you can manually call 0+ on a value, but is there a way to have DBI automatically save it as an actual integer, and not just as a string that looks like a number? It was also said that DBIx::Class and friends have a solution to this problem, but what about DBI from its loneliness?

Answer 1

Depending on your database driver, you can ^* use type hints in bind_col :

 use DBI qw(:sql_types); ... my $sth = $dbh->prepare('SELECT id, name FROM foos WHERE name = "bar"'); $sth->execute; $sth->bind_col(1, undef, { TYPE => SQL_INTEGER, StrictlyTyped => 1, DiscardString => 1 }); while (my $hr = $sth->fetchrow_hashref) { say to_json $hr; } 

This tries to associate the first column (with an index from one) with the SQL_INTEGER type and throws an error if the listing is not performed for any value. As bohica notes, the DiscardString attribute DiscardString required because it "discards the line portion of your data (pv)."

---

* According to DBI documentation:

A bit of support for drivers specifying a data type by calling bind_col (most just ignore the data type).

DBD::Oracle and DBD::ODBC support it, and DBD::Pg can support it, according to this thread (although I can’t check it), but DBD::mysql not. I am not sure about other drivers.

Answer 2

Although ThisSuitlsBlackNot is almost correct, there are some important omissions from his answer. As the author of most of the StrictlyTyped and DiscardString attributes, I can tell you that the DiscardString attribute is actually much more important in this case.

DBI will try to transfer your data to the specified TYPE, and if this fails, your data will be left alone and no error will be generated. If the failure is completed and StrictlyTyped is specified, an error is generated.

The DiscardString attribute discards the line portion of your data (pv) is discarded. This is especially important when using JSON :: XS and possibly other JSON modules, since JSON :: XS specifically looks at pv.

So you should do at least:

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

NOTE. Several DBDs actually pay attention to the type of binding in bind_col. DBD :: ODBC does because I support it. DBD :: Oracle does not pay attention to the TYPE usually, except when used with StrictlyTyped and / or DiscardString (because I added this functionality exactly for the same problem as yours).

If the driver supports these attributes or not, this can really be confirmed (if not specified in the documentation) by looking for the code to use sql_type_cast and their equivalents xs sql_type_cast_svpv, etc. I do not believe that DBD :: Pg supports StrictlyTyped or DiscardString; in fact, I believe that at this time only DBD :: ODBC and DBD :: Oracle do (as they are added).

You can also find this interesting.