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);
}
Re: Dynamic SQL Bind values
created: 2006-04-22 11:47:06
Check out [cpan://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);
Re^2: Dynamic SQL Bind values
created: 2006-04-22 17:11:30
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
created: 2006-04-22 21:48:00
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. )
If God had meant us to fly, he would *never* have given us the railroads.
--Michael Flanders
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
Re^2: Dynamic SQL Bind values
created: 2006-04-23 14:09:12
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
Re^3: Dynamic SQL Bind values
created: 2006-04-23 19:25:10
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