edimusrex has asked for the wisdom of the Perl Monks concerning the following question:

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]

Replies are listed 'Best First'.
Re: MySQL DBI dealing with hex blob field
by graff (Chancellor) on Apr 18, 2015 at 02:25 UTC
    The OP code snippet contains too many unknown variables (what does "$hex" actually refer to?), and it looks like you have some mistakes, too.

    In the "retrieveData" sub, the "else" condition (which isn't being used in this case) is misusing the "fetchrow_array()" function, because the function is supposed to return an array, and you're assigning that to a scalar. On top of that, it looks like the caller is treating the return value as a hash ref, when it's actually an array ref.

[reply]
      Yea, that code is wrong, looks like I pasted it from the wrong script I was working on. I am testing a few things and if I have no luck I will repost the current code. Thanks
[reply]
        unpack was the key to this. I figured it out. The following code is working as expected. Thank you. Here's a snippet 
        #!/usr/bin/perl
use warnings;
use strict;
use IO::Async::Loop;
use Net::Async::CassandraCQL;
use Protocol::CassandraCQL qw( CONSISTENCY_QUORUM );
use DBI();
use DateTime qw();
use Getopt::Long;
use feature qw(say);
use MIME::Lite;
use File::Basename;
use Term::ANSIColor;
use utf8;
use Text::Unidecode;
use bigint;

my $database = "xxxx";
my $host = "xxxx";
my $port = xxxx;
my $user = "xxxx";
my $password = "xxxx";
my $mysqlTable = "xxxx";

my $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
my $dbh = DBI->connect( $dsn, $user, $password ) || warn "Failed to co
+nnect to the database: " . DBI->errstr;

my $table = "accounts";
my $keyspace = "loop_non_hadoop_test";
my $loop = IO::Async::Loop->new;

my $cass = Net::Async::CassandraCQL->new(
   host => $host,
   keyspace => $keyspace,
   default_consistency => CONSISTENCY_QUORUM,
);

$loop->add( $cass );
$cass->connect->get;

my $sql = "SELECT `key` FROM `users`";
my $keys = &retrieveData($sql,1);
foreach my $get (@{$keys}) {
    my $hex =  unpack('H*', $get->{key});
    my $get_stmt = $cass->prepare( 'SELECT "accessedDt" FROM accounts 
+WHERE key =0x'.$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]
[reply]
[d/l]

Back to Seekers of Perl Wisdom