Dynamic SQL Bind values

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);
}
[download]

Comment on Dynamic SQL Bind values Download Code

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);` [download]	[reply] [d/l]
Re^2: Dynamic SQL Bind values by blogical (Pilgrim) on Apr 22, 2006 at 21:11 UTC
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	[reply]
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	[reply] [d/l] [select]
Re^2: Dynamic SQL Bind values by ChemBoy (Priest) on Apr 23, 2006 at 18:09 UTC
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	[reply] [d/l] [select]
Re^3: Dynamic SQL Bind values by blogical (Pilgrim) on Apr 23, 2006 at 23:25 UTC
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	[reply]