This type of trick is good for when you are just writing out sql files for
some other process to apply. I'd like something that would take a number of
columns that you are going to insert, then return insert statements nicely
formatted and properly quoted for printing. Maybe it could even check for
non-nullable fields in your insert statement, and things like that. But
maybe that's beyond the scope of DBI. For example:
my $table = 'people';
my $printer = $dbh->sql_printer($table);
# so now $printer knows about the columns and types of the target table
# it will quote things properly, complain about missing non-nullable
columns,
# complain about addition columns, etc. Maybe even some type checking?
# you can do the sql directly:
$dbh->do($printer->insert({
age => 11,
first => 'mitch',
last => undef
}));
# or print it out for later:
print $printer->insert({
age => 11,
first => 'mitch',
last => undef
});
# or do placeholders
my $sth = $dbh->do($printer->insert_placeholder({
qw(first last age)
});
# prints something like:
insert people (
first,
last,
age
)
values (
'mitch', -- first
NULL, -- last
11, -- age
)
print $printer->delete({
age => 11,
first => 'mitch',
last => undef
});
# prints something like:
delete people
where first = 'mitch'
and last is null
and age = 11
Or maybe an interface like this would be better:
$printer->sql('insert', {
age => 11,
first => 'mitch',
last => undef
});
# prints this:
#insert people (age, first,last) values(11, 'mitch', NULL)
$printer->placeholder_sql('insert', {
age => 1,
first => 1,
last => 1
});
# prints this:
# insert people (age, first, last) values (?, ?, ?)
> -----Original Message-----
> From: Keith Jackson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 11, 2003 8:40 AM
> To: Tim Bunce
> Cc: [EMAIL PROTECTED]
> Subject: Re: shortcuts for common placeholder idioms...
>
>
> my 2 cents... You can't just blindly take form names from the query
> object and try to stuff them in a table. I have function that I call
> first that determines the common fields between the form and my table.
> Then I create the insert/update statement. Thanks Tim for the "x @cols"
> clause. Going to change my code to use it.
>
>
>
> sub commonFields{
> my $table = shift;
> my $query = shift;
> my $dbh = shift;
> my %mark;
>
> my $sth=$dbh->prepare("select * from $table where 1=0")|| die
> $dbh->errstr;
> $sth->execute || die $dbh->errstr;
>
> grep($mark{$_}++,@{$sth->{NAME_lc}});
>
> return grep($mark{lc $_},$query->param);
> }
>
>
> On Mon, 2003-03-10 at 17:15, Tim Bunce wrote:
> > > foreach ( $cgi->param() ) {
> > > push @cols, $_;
> > > push @vals, $cgi -> param( $_ );
> > > }
> > > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > > " VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
> >
> > Seeing all that 'line noise' makes me think we need a neater way.
> >
> > Personally I'd have written it more like
> >
> > my $sql = qq{
> > INSERT INTO table
> > ( } . join( ", ", @cols ) . qq{ )
> > VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
> > };
> >
> > but that's not really that much clearer and possibly more
> > 'magical' to the novice who may not be familiar with 'x' as a
> > list replication operator.
> >
> >
> > But imagine the DBI provided these two functions:
> >
> > sub comma_separated_values { join ", ", @_ }
> > sub comma_separated_placeholders { join ", ", ("?") x @_ }
> >
> > you could then write the statement like this:
> > separated
> > my $sql = qq{
> > INSERT INTO table
> > ( ${\comma_separated_values(@col)} )
> > VALUES ( ${\comma_separated_placeholders(@col)} )
> > };
> >
> > The "... ${\...} ..." is a little magical but not hard to master.
> > It's just a cute way to evaluate expressions inside a double quoted
> > string. The ${...} part expects a reference to a scalar so that's
> > what the backslash before the function name is for. The backslash
> > takes a reference to the scalar value returned by the function and
> > the ${...} then interpolates that into the string.
> >
> > Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
> >
> > my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES (
> ${\dbi_csp(@col)} )";
> >
> > which seems rather neater than where we started:
> >
> > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > " VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
> >
> >
> > Comments?
> >
> > Tim.
> --
> Keith Jackson <[EMAIL PROTECTED]>
>
>
