[ic] return value of autonumber or code at time of INSERT

[Mike Heins]

Quoting jeff at hisgirlfridays.com (jeff at hisgirlfridays.com):
> On Mon, Aug 22, 2005 at 03:56:43PM -0500, Mike Heins wrote:
> 
> > If you are using [query ...], which was not intended by me to do database
> > updates, then all bets are off. I don't know how to get the key off the top
> > of my head and I ain't looking.
> 
> Ut oh - I use the heck out of the query tag to do updates via SQL.
> What would be the more appropriate way to do it?

If it works for you, so be it. I didn't intend it to be used that way
and I haven't/won't put any effort into supporting it.

I use the embedded Perl approach, which allows me to vet and 
structure the data quite easily before putting it in the database.
It also allows 

The row_hash() method is nice for getting records from the database to
operate on them, and the set_slice() method is convenient because it will
do an insert or update as required.

This is a skeleton routine like what I typically do with data:

[perl tables=foo]

	# get the db object
	my $db = $Db{foo};

	## If my variables don't match my database table
	my %alias = ( zip => 'postcode' );

	# Subroutines to transform/filter the data
	my %transform = (
		zip => sub {
			 my $value = shift;
			 $value =~ s/\D+//g;
			 return $value;
			},
	     );

	my @wanted = qw/ fname lname address1 city state country zip /;

	my %record;

	for(@wanted) {
	    my $thing = $Values->{$_};

	    # get rid of inconvenient things like tabs and returns
	    $thing =~ s/^\s+//g;
	    $thing =~ s/\s+$//g;
	    $thing =~ s/\s+/ /g;

	    # put in record

	    if(my $sub = $transform{$_}) {
		$thing = $sub->($thing);
	    }

	    my $keyname = $alias{$_} || $_;

	    $record{$keyname} = $thing;
	}

	## Assumes autonumbering is configured in
	my $key = $db->set_slice(undef, \%record);

	if($key) {
	    $Tag->warning("Created new database record $key");
	}
	else {
	    my $msg = $db->errstr();
	    $Tag->error({ name => "set table foo", set => $msg });
	}

	return;
[/perl]

Of course it can be simpler or more complex.

I like this approach because it makes it easy to add fields, subtract
them, or change their data just by adding members to arrays. Some
would find this too complex, and just hardcode it all. Whatever floats
your boat. 8-)

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

If you think nobody cares if you're alive, try missing a couple of
car payments.  -- Earl Wilson