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

I'm writing an insert_row function using DBI. I get the following error:
DBD::DB2::st execute failed: IBMCLI Driver...blah.......The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty. SQLSTATE=42617. Can't call method execute on an undefined value.

I think it is a problem with my binding parameters. Here is the code:

sub insert_row{
  if( $#_ < 2 ){
    print 'USAGE: $rv = insert_row($DB_TYPE, $DB_HANDLE, $TABLE, $COL1
+, $COL2, $VAL2, $VAL2)';
    return 0;
  }
  my $db_type = shift;
  my $dbh = shift;
  my $table = shift;
  if( ($#_+1) % 2 != 0 ){
    print "COLUMN/VALUE mismatch @ insert_row($db_type,$dbh,$table...)
+";
    return 0;
  }
  my $num_cols = ($#_+1)/2;
  my $val_index = ($#_+1)/2;
  my $col_index = 0;
  my %columns = ();
  my $x = 0;
  for($x = 0, $x < $num_cols, $x++){
    $columns{$_[$col_index]} = $_[$val_index];
    $col_index++;
    $val_index++;
  }
  my $sql = "INSERT INTO " . $table . " (";
  while ( my ($key, $value) = each(%columns) ) {
        $sql .= "$key,";
  }
  chop $sql;
  $sql .=") VALUES (";
  for( $x = 0; $x < $num_cols; $x ++ ){
    $sql .= '?,';
  }
  chop $sql;
  $sql .= ')';
  my $sth = $dbh->prepare($sql);
  my @bind_values = ();
  while ( my ($key, $value) = each(%columns) ) {
        push @bind_values, $value;
  }
  return $sth->execute(@bind_values);
}

[download]

Replies are listed 'Best First'.
Re: Insert Row
by Rhandom (Curate) on Dec 07, 2006 at 16:11 UTC
    A quick comment. Try modifying one line to this: 

    my $sth = $dbh->prepare($sql) || die "DBI Err: $DBI::errstr for SQL \"
+$sql\"";

    [download]


    That should give you a little better feedback. You should always check the condition of your db method calls. Alternately I would strongly suggest setting $dbh->{'RaiseError'} = 1.

    my @a=qw(random brilliant braindead); print $a[rand(@a)];
[reply]
[d/l]
      And now a comment about simplifying things. I don't advocate Class::DBI or any of the other SQL abstraction modules - but you can cleanup your SQL generation.

      Try this:
        $table =~ tr/A-Za-z0-9_//cd;
  my @fields = keys %columns;
  my @values = values %columns; # or @columns{@fields}
  my $sql = "INSERT INTO $table ("
      .join(", ", @fields)
      .") VALUES ("
      .join(", ", ("?") x @fields)
      .")";
  my $sth = $dbh->prepare($sql) || die $DBI::Errstr;
  $sth->execute(@values) || die $DBI::Errstr;

      [download]


      my @a=qw(random brilliant braindead); print $a[rand(@a)];
[reply]
[d/l]
        You should use quote_identifier instead of tr///.

        $table = $dbh->quote_identifier($table);
[reply]
[d/l]
[select]
Re: Insert Row
by talexb (Chancellor) on Dec 08, 2006 at 04:19 UTC

    Rather than comment on your code, let me first argue against building an SQL command by concatenating strings together -- it makes things really clumsy,

    Instead, the approach that I follow is to write the final command that I'm going to submit to the prepare method ..

    my $cmd = "INSERT INTO $table ($fields) VALUES ($placeholderList)";

    [download]

    .. and then work backwards, filling bits in as I go. The sub preamble will be something like this ..

    #  Accepts a database handle, a table name, and a hashref
#  of field names and values. Returns on success, otherwise
#  dies.

sub addData
{
  my ( $dbh, $table, $args ) = @_;

    [download]

    Now it's time to build the pieces leading up to your command ..

      #  Get a comma separated list of the field names ..
  my $fields = join(',', keys %$args);

  #  Get the values into an array ..
  my @values = values %$args;

  #  Get a comma separated list of '?', one for each value
  #  ..
  my $placeholderList = join(',',('?') x @values);

    [download]

    Then, once the command is built, you do the rest of the stuff with the database ..

      #  Prepare the command and execute it with the array of
  #  values.
  my $sth = $dbh->prepare($cmd);
  $sth->execute(@values) or die $sth->error;

  return 0;     # Success!

    [download]

    So in the end you have a piece of code that's clean and fairly well documented. Once more, here's the entire thing: 

    #  Accepts a database handle, a table name, and a hashref
#  of field names and values. Returns on success, otherwise
#  dies.

sub addData
{
  my ( $dbh, $table, $args ) = @_;

  #  Get a comma separated list of the field names ..
  my $fields = join(',', keys %$args);

  #  Get the values into an array ..
  my @values = values %$args;

  #  Get a comma separated list of '?', one for each value ..
  my $placeholderList = join(',',('?') x @values);

  #  Build the entire command ..
  my $cmd = "INSERT INTO $table ($fields) VALUES ($placeholderList)";

  #  Prepare the command and execute it with the array of values.
  my $sth = $dbh->prepare($cmd);
  $sth->execute(@values) or die $sth->error;

  return 0;     # Success!
}

    [download]

    Way cleaner, no?

    Alex / talexb / Toronto

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom