in reply to Insert Row

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)];

Replies are listed 'Best First'.
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]
      You should use quote_identifier instead of tr///.

      $table = $dbh->quote_identifier($table);
[reply]
[d/l]
[select]
        I most certainly agree. I never program in such a way that I am passing in arbitrary table names though and so I've never needed to look up the method that would quote it for me. Thankyou for pointing out the correct method.

        In all cases the name of the table could be validated before trying to use it in actual SQL - either by using "show tables" in MySQL or by "SELECT table_name FROM user_tables" in Oracle. Either way I would not be using the user supplied data in that portion of the SQL.

        my @a=qw(random brilliant braindead); print $a[rand(@a)];
[reply]

In Section Seekers of Perl Wisdom