I am having a bit of an issue with MySQL DBI. My script currently grabs data from a Cassandra database and populates a MySQL database with the returned values. One of the fields retrieved from Cassandra is a key field which is a hexadecimal value in the following format 0xD3BAA1BC343E492F9C7A2C310B8A5C32.
That key is then inserted into a blob column in MySQL so that I may retain the hex value without it being converted (the key value is important for querying our Cassandra database).

I have various option switches I can use with my script and one of them grabs the key value from MySQL and uses it to query the Cassandra database. The issue is that when the DBI returns the value it interprets or converts the value instead of leaving it in it's exact format.

If this is making any sense and if some one could help me, that would be hugely helpful.
Here is a sample of the code. 

if ($access) {
    my $sql = "SELECT `key` FROM `users`";
    my $keys = &retrieveData($sql,1);
    foreach my $get (@{$keys}) {
        say $get->{key};
        my $get_stmt = $cass->prepare( 'SELECT "accessedDt" FROM accou
+nts WHERE key = '.$hex->as_hex)->get;
        my ( undef, $result ) = $get_stmt->execute( [] )->get;
        foreach my $row ($result->rows_hash) {
            my $key_find = ($row->{"accessedDt"});
            if (defined $key_find) {
                say "I found this date --- $key_find";
            }
        }
    }
}

sub retrieveData {
    my $value;
    my $sth = $dbh->prepare($_[0]);
    $sth->execute();
    if ($_[1]) { $value = $sth->fetchall_arrayref({}); }
    else { $value = $sth->fetchrow_array(); }
    return $value;
}

[download]

In reply to MySQL DBI dealing with hex blob field by edimusrex

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.