[Chicago-talk] Automatic handling of insert ... returning querys.

Steven Lembark lembark at wrkhors.com
Sun Aug 22 14:48:00 CDT 2004

After noticing the syntax I've written a module that handles
the process. Before putting it on CPAN I'd like to get opinons
on the interface. The point is to avoid all of the
bind_param_inout setup by creating some per-handle metadata
with the bound array and fields to copy in from @_, out to
the caller. After that the caller only sees a prep step and
the execute calls.

One approach uses classes derived from DBI and DBI::st to
add "prepare_bound" to DBI and "execute" to DBI::st. The
resulting code looks somethingn like:

	my $dbh = blah;

	my $sql =
		insert into
			table ( ... )
			values ( ?, ?, ?, ?)
			returning idfield into ?

	my $sth = $dbh->prepare_bound( $sql );


	my $id = $sth->execute( @insert_values );

	my @id = $sth->execute( @insert_values );

This seems nice in that the syntax for prepare (and
prepare_bound_cached) look rather DBI-ish.

Catch is that this makes deriving other classes and
using statement handles from oddly-derived classes
somewhat tricky.

Another approach is simply adding a post-processing
step to the statement handle:

	my $dbh = blah;

	my $sth = $dbh->prepare( $sql );

	$sth->binderize( $sql );

	my $id = $sth->execute_bound( @insert_values );

The downside here is extra steps to binderize the
handle and a separate execute command -- which
might interfere with the statement handle if
accidentally mixed with $sth->execute.

Personally, I'm leaning towards the first technique
even if the internals are a bit messier: NEXT makes
re-dispatching the execute and prepare steps
manageable and the syntax seems cleaner.

Any suggestions?

Steven Lembark                                         85-09 90th St.
Workhorse Computing                               Woodhaven, NY 11421
lembark at wrkhors.com                                    1 888 359 3508

More information about the Chicago-talk mailing list