How to write (and read) bit vector to (and from) MySQL?

[Siegfried Heintze]

I'm using the vec feature to create a bit vector and I want to write it to a
mysql database. When this did not work using a string field, I tried tiny
blob. It is writing some bits. When I get results back, they are not what I
started with.

 

Here is my code. Can someone tell me what I am doing wrong? Here is what the
code below prints:

 

573 They are not equal:
$bvKeyWord2=0,1,2,5,10,11,12,14,20,21,26,27,32,33,34,37 $bvKeyWords=10,11

 

Thanks,

Siegfried

 

  my @nRoleID; # array of integers

  my $bvKeyWords = "";

  vec($bvKeyWords,$_,1)=1 foreach @nRoleID;

 

# write to database

  my $uUpdate = "UPDATE  jobposting SET bvKeyWords = ? WHERE id = ?";

  {my $sth = $dbh->prepare ( $uUpdate );

  $sth->bind_param(1, $dbh->quote($bvKeyWords));

  $sth->bind_param(2, $fkJobPosting[0]);

  die "No sth: " . $dbh->errstr unless $sth; 

  $sth->execute || die __LINE__." Failed: '$uUpdate' ". $sth->errstr; }

 

# read from database

  my $sSelect = "SELECT bvKeyWords FROM  jobposting  WHERE id = ?";

  { my $sth = $dbh->prepare ( $sSelect );

  die "No sth: " . $dbh->errstr unless $sth; 

  $sth->execute($fkJobPosting[0]) || die __LINE__." Failed: '$sSelect' ".
$sth->errstr;

  my $row;

  while ( $row = $sth->fetch ) {

    my ($bvKeyWord2) = @$row;

    if ($bvKeyWord2 eq $bvKeyWords){

      print __LINE__." Hurray, they are equal: \$bvKeyWords\n";

    } else{

      print __LINE__." They are not equal: \$bvKeyWord2=";

      my @ids;

      for(0..300){

        push @ids, $_ if vec($bvKeyWord2,$_,1);

      }

      print join(",",@ids)." \$bvKeyWords=";

      @ids=();

      for(0..300){

        push @ids, $_ if vec($bvKeyWords,$_,1);

      }

      print join(",",@ids)."\n";

 

    }