Handling text fields in Sybase

[prev] [thread] [next] [Date index for 2004/07/23]

Hello,

We've been using Class::DBI for three days now and are having a few
issues when using some more advanced features of Sybase. We are using
Class::DBI::Sybase, which helped use across the first hurdle of column
names. 

Next we wanted to use identity columns in Sybase, which are generated in
the database. Trouble is that Class::DBI wants to write to primary key
fields, so we need to prohibit that. Easily done:

MSDW::MDP::Db::Release->flag_column(IDENTITY => qw/release_id/);

sub _auto_increment_value {
  my $self = shift;
  my $dbh  = $self->db_Main;
  my @primary_columns = $self->primary_columns;

  #The sybase way
  my $id;
  eval {
    #Can't use placeholders (not conditions)
    my $sth = $dbh->prepare("select MAX( $primary_columns[0] ) from " .
                            $self->table);
    $sth->execute();
    $id = ($sth->fetchrow_array)[0];
  };
  $self->_croak("Can't get last insert id") unless defined $id;
  return $id;
}

#Flag columns as either IDENTITY or TEXT for special treatment
sub flag_column {
  my $proto = shift;
  my $class = ref $proto || $proto;
  my $tag = shift || 'NONE';

  #Make ID column virtual
  if($tag eq 'IDENTITY' && @_) {
    $class->columns(TEMP => @_);
  }

  #Set up text columns
  if($tag eq 'TEXT' && @_) {
    $class->_text_col(\@_);
  }
}

So this now works. Next issue is text fields. They cannot be used in
placeholders ('?') with DBD::Sybase. So we override _insert_row and
update :

#Overwritten from Class::DBI to not use placeholders for text columns
sub _insert_row { 
  my $self = shift;
  my $data = shift;
  eval {
    my @columns = keys %$data;
    my %place_holders;

    #Ok - Don't use placeholders for text fields
    foreach my $col (@columns) {
      if($self->_is_text($col)) {
        $place_holders{$col} = "'" . $data->{$col} . "'";
        delete $data->{$col};
      }
      else {
        $place_holders{$col} = $self->_column_placeholder($col);
      }
    }

    my $sth = $self->sql_MakeNewObj(
                                    join(', ', @columns),
                                    join(', ', map $place_holders{$_},
@columns),
                                   );

    $self->_bind_param($sth, \@columns);
    $sth->execute(values %$data);
    my @primary_columns = $self->primary_columns;
    $data->{ $primary_columns[0] } = $self->_auto_increment_value
      if @primary_columns == 1
        && !defined $data->{ $primary_columns[0] };
  };
  if ($@) {
    my $class = ref $self;
    return $self->_croak(
                         "Can't insert new $class: $@",
                         err    => $@,
                         method => 'create'
                        );
  }
  return 1;
}

Oh we need to change the "'" . $data->{$col} . "'" to use DBI quote
ofcourse. So what do you guys think about this approach?

Also, if I declare something a primary key it seems you cannot update it
to another value, as it gets internally changed before the update
happens, so you end up with a where clause looking for the new value
instead of the old one. Is this by design? We now have to delete the row
first, then stick in a new one, which isn't really updating ofcourse.

Regards,
        -- 
        Merijn Broeren | 'I want to understand everything. I want to know every-
Software Geek  |  thing and put it all together to see what it means.'
               | 'Excellent project, it will look very good on your resume.'