Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
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
Replies are listed 'Best First'.
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 meditating upon the Monastery: (18)
As of 2015-07-31 17:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (279 votes), past polls