Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 lurking in the Monastery: (1)
As of 2022-10-02 02:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (7 votes). Check out past polls.

    Notices?