Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Dynamic SQL Bind values

by blogical (Pilgrim)
on Apr 22, 2006 at 07:58 UTC ( #545030=snippet: print w/replies, xml ) Need Help??
Description: Generating dynamic SQL statements and need to plug in bind values? Maintain a single list of columns and let this function handle stringifications and preping your bind valuees array.
sub setup_bindings {
   # Produces strings useful for generating dynamic
   # SQL statements as well as an array of matching
   # binding values (because you use bind values, right?)

   #Args:
   #  $_[0]: Ref to hash of values, keyed by column
   #  $_[1]: Ref to array of columns used

   #Usage:
   # my ( $column_string, $bindings_string, $bound_values )
   #    =  setup_bindings( \%value_for_column, \@columns );
   # my $statement = " INSERT into YOUR_TABLE ( $column_string )
   #                   VALUES ( $bindings_string )";
   # $dbhandle->do( $statement, undef, @$bound_values ); #Or whatever 
+use you have- this is `do` from the DBI

   my $values        = shift; 
   my @columns       = @{ +shift };
   my @set_columns   = ();
   my @set_bindings  = ();
   my @bound_values  = ();

   for my $col_2_bind ( @columns ) {
       push @set_columns, $col_2_bind;
       push @set_bindings, '?';
       push @bound_values, $values->{$col_2_bind};
   };

   my $column_string   = join ', ', @set_columns;
   my $bindings_string = join ', ', @set_bindings;
    
  return ($column_string, $bindings_string, \@bound_values);
}
Replies are listed 'Best First'.
Re: Dynamic SQL Bind values
by davidrw (Prior) on Apr 22, 2006 at 15:47 UTC
    Check out SQL::Abstract -- it goes further and eliminates the need for the "INSERT ..." sql snippet (and has SELECT, UPDATE, DELETE and WHERE clause support).
    use SQL::Abstract; my $sa = SQL::Abstract->new; my($sql, @bind) = $sa->insert('YOUR_TABLE', \%value_for_column); $dbhandle->do($sql, {}, @bind);
      Thanks, that looks like a handy dandy module. This is just a snippet that I've ended up finding use for a few times now and thought I'd share. Less featureful than SQL::Abstract, but when the shoe fits (and doesn't require buying a matching outfit to boot...)

      "One is enough. If you are acquainted with the principle, what do you care for the myriad instances and applications?"
      - Henry David Thoreau, Walden

Re: Dynamic SQL Bind values
by blogical (Pilgrim) on Apr 23, 2006 at 01:48 UTC
    my @columns       = @{ shift };
    doesn't work, use
    my @columns       = @{ $_[0] };
    (or don't bother with the temps, they were thrown in for clarity. My bad. )

    "One is enough. If you are acquainted with the principle, what do you care for the myriad instances and applications?"
    - Henry David Thoreau, Walden

      Actually, it can be made to work by adding a + to the beginning: @{shift} treats "shift" as a string, and looks for a variable named @shift (which would—obligatory plug—be caught by use strict 'vars'), but @{+shift} forces it to use the keyword shift, which is what you were expecting it to do.



      If God had meant us to fly, he would *never* have given us the railroads.
          --Michael Flanders

        Danke chemboy.

        "One is enough. If you are acquainted with the principle, what do you care for the myriad instances and applications?"
        - Henry David Thoreau, Walden

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: snippet [id://545030]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (2)
As of 2022-10-02 22:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (13 votes). Check out past polls.

    Notices?