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

I had previously asked a question regarding testing for a numeric or string value. That thread ended up discussing placeholders to eliminate the need for the test. So I tried using placeholder after reading through the links provided and could not find a solution.

Here's what I currently have (that works) but I would like to better understand how placeholders could simplify the code. This is used to create a backup for a mySQL database (Part of a mySQL table editor) 

# 1.  Download text-only data. Comma delimited and quoted strings for 
+?easy? upload.
my $table = $q->param('dbtable'); # Can be any table in the database
my @field_type = ();
my $n = 0;  
my $sth = $dbh->prepare("DESCRIBE $table");  $sth->execute();
while (my @field_descr = $sth->fetchrow_array) {
    @field_type[$n] = ($field_descr[1] =~ /char/i) ? 1 : 0;  # Set to 
+1 if string
    $n++;
}
$sth->finish();
$sth = $dbh->prepare("SELECT * FROM $table");  $sth->execute();
print "Content-Type: text/plain\n\n";
while (my @record = $sth->fetchrow_array()) {
    # Build a string of each record, comma delimited, and inserting qu
+otes as needed
    my ($recStr,$dl,$n) = ("","",0);
    foreach my $field (@record) {
        $field =~ s/(\n|\r)//g; # Get rid of any return chars that MAY
+ have been erroneously uploaded.
        $recStr .= ($field_type[$n]) ? "$dl'$field'" : "$dl$field"; # 
+Insert the quotes.
        $dl = ",";  $n++;
    }
    print "$recStr\n";
}
$sth->finish();

# 2. Upload the data.
my $dbdata = $q->param('dbdata');
my @filedata = <$dbdata>;
foreach my $record (@filedata) {
    my $sql = "INSERT INTO $table VALUES ($record)";
    my $rows = $dbh->do($sql);
}

[download]
If I could eliminate the need for the quotes, it would greatly simplify the 'download' code. I understand that, if I knew the number of fields in the table, I could use placeholders to add the quotes; but I don't because it can be any table in the database. 
my $sql = "INSERT INTO tablename VALUES(?,?,?,?)";
my $sth = $dbh->prepare($sql);
foreach my $record (@filedata) {
    $sth->execute($fa,$fb,$fc,$fd);
}

[download]
Any ideas, thoughts, or comments would be appreciated and will, hopefully :), expand my limited Perl knowledge

Replies are listed 'Best First'.
Re: Understanding placeholders
by perlplexer (Hermit) on May 03, 2003 at 21:40 UTC
    You should really consider removing "select * from" and replacing it with a "select" that specifically states what fields it's selecting and in which order. The same applies to "insert into table". This way your code will continue working even if table layout is altered. Since you're doing a "describe" already, it's easy enough to pull field names from it. Once you have the names, do a proper "select" and also store field names in the file that you're exporting, perhaps on the very first line, so that the application on the other end will know how to construct the "insert". Getting the right number of "?" for placeholders is also trivial in this case.

    --perlplexer
[reply]
      This way your code will continue working even if table layout is altered
      That's a good idea, Thanks.
[reply]
Re: Understanding placeholders
by thraxil (Prior) on May 03, 2003 at 21:50 UTC

    instead of 

    foreach my $record (@filedata) {
    my $sql = "INSERT INTO $table VALUES ($record)";
    my $rows = $dbh->do($sql);
}

    [download]

    you could do something like:

    foreach my $record (@filedata) {
    my @fields = split /,/, $record;
    my $placeholders = "?," x scalar (@fields);
    chop $placeholders; # remove the end ','
    my $sql = "INSERT INTO $table VALUES ($placeholders)";
    my $sth = $dbh->prepare($sql);
    my $rows = $sth->execute(@fields);
    $sth->finish();
}

    [download]

    and, of course, if all the lines of @filedata are for the same table, you could move some of that code outside the loop.

    anders pearson

[reply]
[d/l]
[select]
      I would discourage the usage of "split /,/, $record" as the method for determining the number of fields and, therefore, placeholders. The problem here is that his data fields may contain commas in them; e.g.,

      'foo','bar',1,23,'foobar,bar,foo'

      in which case "split /,/, $record" won't return proper results.

      Text::CSV and DBD::CSV are probably more appropriate.

      --perlplexer
[reply]
      thraxil: Still have to test, but this looks good. I've recoded, as suggested, to move most of the code outside the loop.

      perlplexer: There are indeed commas in the data so I plan to change the 'seperator' to '::'. But I'll certainly take a look at the 2 links you provided

      Thanks to both!
[reply]
      foreach my $record (@filedata)
{
    my @fields = split /,/, $record;
    my $placeholders = join ',', (('?') x @fields);

    my $sql = <<"END_SQL";
INSERT INTO $table
VALUES ($placeholders)
END_SQL

    my $sth = $dbh->prepare_cached($sql)
        || die "Could not prepare '$sql'\n";

    $sth->execute(@fields)
        || die "Could not execute '$sql'\n";

    $sth->finish;
}

      [download]
      That's the first bit - use join and check the return values.

      However, $record should be an arrayref, not a string. Which would mean that the split disappears. But, that's further back up the chain.

      ------
      We are the carpenters and bricklayers of the Information Age.

      Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

      Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
[d/l]
[select]
Re: Understanding placeholders
by BUU (Prior) on May 04, 2003 at 00:05 UTC
[reply]
      I read though that FAQ before posting but could not figure out how to add placeholders 'on-the-fly'. thraxil's post showed me how to do that.
[reply]
Re: Understanding placeholders
by markjugg (Curate) on May 04, 2003 at 14:42 UTC

    I highly recommend using SQL::Abstract to help you generate your place holders. You give it some perlish syntax describing your "where" statement, it gives you back a SQL snippet and some bind variables. Very clean and easy to use.

    I used to use DBIx::Abstract, which is similar, but I found it does a lot more than I need, it's slower, and it's gets tied to your database handle. (Although I think the author may currently be working on making it faster and able to work without a DB handle).

    Also, look at using the RaiseError and PrintError attributes in DBI to help with your error handling. It's not clear from the code above that you are doing any. My favorite technique is to turn on RaiseError, which will cause DBI to die if there is an error. Then I wrap a whole block of DBI statements with an eval statement, and check the errors in one place. Of course, this works better if your database supports transactions and you can rollback after an error. :)

    You may also be interested in looking into the prepare_cached method for additional performance gains.

    -mark

[reply]
Re: Understanding placeholders
by crouchingpenguin (Priest) on May 04, 2003 at 19:49 UTC

    One quick suggestion, you don't have to use DESCRIBE to retrieve the column names, you can pull the column names from your already executed statement handle like so:

    my $column_names_aref = $sth->{NAME}; #or NAME_lc or NAME_uc

    This way you are not dependant upon mysql specific syntax.


    cp
    ----
    "Never be afraid to try something new. Remember, amateurs built the ark. Professionals built the Titanic."
[reply]
[d/l]
Re: Understanding placeholders
by nedals (Deacon) on May 03, 2003 at 21:10 UTC
    Forgot to login!
[reply]

Back to Seekers of Perl Wisdom