DBI::sql_type_cast: DBIstcf_DISCARD_STRING - question

My hope was, that DBI::sql_type_cast with the DBIstcf_DISCARD_STRING-flag w开发者_运维百科ould modify $sv from '4.8g' to 4.8.

(DBIstcf_DISCARD_STRING: "If this flag is specified then when the driver successfully casts the bound perl scalar to a non-string type then the string portion of the scalar will be discarded.")

What does the return-value sv could not be case and DBIstcf_STRICT was not used mean?

#!/usr/bin/env perl
use warnings;
use 5.012;
use DBI qw(:sql_types);

my $dsn = "DBI:Proxy:hostname=horst;port=2000;dsn=DBI:ODBC:db1.mdb";
my $dbh = DBI->connect( $dsn, undef, undef, { RaiseError => 1, PrintError => 0 } )
or die $DBI::errstr;

my $sv = '4.8g';
my $sql_type = SQL_DOUBLE;
my $flags = DBIstcf_DISCARD_STRING;

my $sts = DBI::sql_type_cast( $sv, $sql_type, $flags );

say $sts; # 1 (sv could not be case and DBIstcf_STRICT was not used)

say $sv;


# Argument "4.8b" isn't numeric in subroutine entry at ./perl6.pl line 14.
# 1
# 4.8b

---

1. The documentation contains a typo -- the description for $sts == 1 should be "sv could not be cast" -- i.e. a cast to SQL_DOUBLE wasn't possible for the value you provided and so nothing was done.

2. DBIstcf_DISCARD_STRING means something different from what you want. In Perl internal terms it means that if you pass an SV with POK and NOK and PV part "1.23" and NV part 1.23, you will get back an SV with !POK and NOK and NV part 1.23 -- that is, the stored string part of the scalar will be invalidated, leaving the numeric part intact, so any future attempt to use the scalar as a string will force it to be re-converted from a number to a string. But note that it says that this will only happen if the cast is successful, and a cast to SQL_DOUBLE isn't successful if the value isn't a valid number to begin with. "4.8g" doesn't pass the test.

3. You can clean up the string part of the value almost as effectively as DBI on your own just by doing $sv = 0 + $sv; which will clear POK and force a reconversion to string in the same way. The difference between this and what DBI does is that it's not actually clearing the PV in the way that DBI would, only marking it invalid. To force the value to be cleared immediately in the same way as DBI, you need to do something like

   $sv = do { my $tmp = 0 + $sv; undef $sv; $tmp };

   but unless you have some really good explanation for why you need that, you don't -- so don't use it. :)

---

After reading through the documentation and the code in DBI.xs (the implementation is in sql_type_cast_svpv), the return value of 1 means 'the value could not be cast cleanly and DBIstcf_STRICT was not used'.

Taking the key part of that function, in your case:

case SQL_DOUBLE:
    sv_2nv(sv);
    /* SvNOK should be set but won't if sv is not numeric (in which
     * case perl would have warn'd already if -w or warnings are in effect)
     */
    cast_ok = SvNOK(sv);
    break;

....

if (cast_ok) {

    if (flags & DBIstcf_DISCARD_STRING
    && SvNIOK(sv)  /* we set a numeric value */
    && SvPVX(sv)   /* we have a buffer to discard */
    ) {
        SvOOK_off(sv);
        if (SvLEN(sv))
            Safefree(SvPVX(sv));
        SvPOK_off(sv);
        SvPV_set(sv, NULL);
        SvLEN_set(sv, 0);
        SvCUR_set(sv, 0);
    }
}

if (cast_ok)
    return 2;

SvNOK should be set for you. Without digging in further into sv_2nv, the core of the problem is that "4.8g" is not a numeric type, as the numeric flag in the scalar value is not set (this is what SvNOK checks for).

My suggestion, use a regular expression to strip that input before calling sql_type_cast.

---

The typo in the documentation is now fixed in the subversion trunk.

Here is a brief explanation of why sql_type_cast was added.

Although there is nothing to stop you using sql_type_cast it was specifically added for drivers (DBDs) to cast data returned from the database. The original issue it solved was that integers are mostly bound as strings so when the data is returned from the database the scalar's pv is set. Some modules like JSON::XS are clever and look at the pv to help decide if the scalar is a number of not. Without the sql_type_cast JSON::XS was converting a scalar containing a 1 but with the pv set to "1" instead of the shorter 1 in JSON conversions.

To my knowledge only DBD::Oracle does this right now although it is in the TODO for DBD::ODBC.