Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

SQL INSERT creator

by boo_radley (Parson)
on Dec 27, 2000 at 22:46 UTC ( #48489=CUFP: print w/ replies, xml ) Need Help??

For Ovid -- prepares an insert from an array or hash.
#################### # # sub create_SQLpreparedinsert # # in : a database handle, the name of the table to update, and an arr +ay # of fieldnames. # out : a statement handle. # # this sub will create a prepared statement, or die with a verbose err +or. # note that under sybase, prepare statements may not include TEXT or I +MAGE types. # for those cases, use create_SQLinsert. # Typically, however, prepared statements will run faster, so this sho +uld be the sub of choice. # #################### sub create_SQLpreparedinsert{ my ($l_database, $l_tablename,@l_fields) = @_; my $l_fieldnames; my $l_placeholder; my $l_prepare; my $l_fieldnames = join ",", (@l_fields); $l_placeholder = (join ",", ('?')x(@l_fields)); $l_prepare = $l_database->prepare("INSERT $l_tablename (" . $l_fie +ldnames . ")\n VALUES (" . $l_placeholder . ")") or die ("Could not c +reate statement handle in sub create_SQLpreparedinsert! Database obj +ect says : ", $DBI::errstr); } #################### # # sub create_SQLinsert # # in : a database handle, the name of the table to update, and an arr +ay # of fieldnames. # out : a statement handle. # #################### sub create_SQLinsert{ my ($database, $l_tablename,%l_fields) = @_; my $l_sth; #foreach (values %l_fields) {$_=$database->quote($_)} my $l_prepare; my $l_fieldnames = join ",", (keys %l_fields); my $l_fieldvalues = join ",", (values %l_fields); $l_prepare = "INSERT $l_tablename (" . $l_fieldnames . ")\n VALUES + (" . $l_fieldvalues . ")"; $sth= $database->prepare($l_prepare) or die ("Could not create sta +tement handler in create_SQLinsert. database object says : ", $DBI::e +rstr); }

Comment on SQL INSERT creator
Download Code
Re: SQL INSERT creator
by Kanji (Parson) on Dec 28, 2000 at 11:08 UTC

    Ever thought about (ab)using interpolation and the list-seperator variable to simplify things visually? Or perhaps some rudimentary checking of expected arguments?

      sub insert {      # Untested, but you get the idea ...
    
          my $dbh     = shift or warn("No database handle"), return;
          my $table   = shift or warn("No table given"),     return;
          my @columns = @_    or warn("No columns given"),   return;
    
          my $sql;
          {
              local $"    = ","; # interpolation voodoo :-)
    
              my @values  = ("?") x @columns;
              $sql        = "INSERT INTO $table (@columns) VALUES (@values)"; # s/my //
          }
    
          $dbh->prepare($sql) or die("Couldn't prepare $sql " . $dbh->errstr);
    
      }

        --k.

    Update: per extremely below, because they've got a point. :-)


      Ouch, did you just change $" and pass that change on to DBI? What say you localize that a bit more local. =)
      my $sql; { my @values = ('?') x @columns; # I love that, BTW... local $" = ','; $sql = "INSERT INTO $table (@columns) VALUES (@values)"; } $dbh->prepare($sql) or die("Couldn't prepare $sql " . $dbh->errstr);

      Sure, the likelyhood of any of the DBI/DBD stuff using a string-interpolated array is pretty low but it is still bad practice.

      --
      $you = new YOU;
      honk() if $you->love(perl)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://48489]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2014-07-13 23:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (252 votes), past polls