Insert Row

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: IBM CLI 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]

Comment on Insert Row Download Code

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]
Re^2: Insert Row by Rhandom (Curate) on Dec 07, 2006 at 16:20 UTC
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]
Re^3: Insert Row by reneeb (Chaplain) on Dec 08, 2006 at 08:07 UTC
You should use `quote_identifier` instead of tr///. `$table = $dbh->quote_identifier($table);`	[reply] [d/l] [select]
Re^4: Insert Row by Rhandom (Curate) on Dec 10, 2006 at 07:12 UTC
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]


Keep It Simple, Stupid
	PerlMonks