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

maillists lists at gmnet.net
Tue Aug 23 15:16:05 EDT 2005


On Mon, 2005-08-22 at 21:09 -0500, Mike Heins wrote:
> Quoting jeff at hisgirlfridays.com (jeff at hisgirlfridays.com):
> > On Mon, Aug 22, 2005 at 03:56:43PM -0500, Mike Heins wrote:
> > 
> 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]
> 

Thanks Mike for your sample code! For some reason, I still cannot get
this to work. Can anyone please help? Sorry if this is long, but I am
getting into alot of trouble with this project and I need a solution
soon.

I am trying to insert a new record from a form and return back the code
from that new record.  Does this look right? Am I even close?

This is in my catalog.cfg: ##########
###################################################
Database tablename AUTO_SEQUENCE code_seq <<-- I'm not sure what this
does?

This is part of my tablename.mysql and i rebuilt the database with
Interchange restart. ############
###################################################
Database  tablename  tablename.txt __SQLDSN__
Database  tablename  AUTO_NUMBER  00101
Database  tablename  KEY          code
Database  tablename  COLUMN_DEF   "code=INT(20) NOT NULL PRIMARY KEY"
...

Here is a piece of my new form file test_form: #####
###################################################

[if scratch message_sent]
  Done! Here is the value of the new code [cgi key]
  [set message_sent][/set]
[value name=message set='']
[else]
<form action="[process]" method=post>
<INPUT TYPE=hidden NAME=mv_session_id VALUE="[data session id]">
<INPUT TYPE=hidden NAME=mv_form_profile VALUE=check_profile>
<INPUT TYPE=hidden NAME=mv_nextpage VALUE="@@MV_PAGE@@">
<INPUT TYPE=hidden NAME=mv_todo  VALUE=set>
<INPUT TYPE=text NAME=var1 VALUE="[value var1]">
<INPUT TYPE=text NAME=var2 VALUE="[value var2]">
<INPUT TYPE=text NAME=var3 VALUE="[value var3]">
<INPUT TYPE=SUBMIT VALUE="[L]Send Request[/L]">
[/else]
[/if]

Here is my profile: ##########
###################################################
__NAME__                check_profile
&success=../special_pages/test_success
&fail=test_form
__END__

Here is my test_success page from Mikes code abouve: ##### 
###################################################
[flag type=write table="tablename"]
[perl tables=tablename]
        my $db = $Db{tablename};
        my @wanted = qw/ var1 var2 var3 /;
        my %record;
        for(@wanted) {
            my $thing = $Values->{$_};
            my $keyname = $_;
            $record{$keyname} = $thing;
        }
        my $key = $db->set_slice(undef, \%record);
        return;
[/perl]
[set message_sent]1[/set]
[bounce page=test_form]


This is what I get in my error log: ################
#####################################################
set_slice error as called by Safe::Hole: DBD::mysql::st execute failed:
Column 'code' cannot be null
at /usr/local/interchange-5.2/lib/Vend/Table/DBI.pm line 1251.
query was:insert into tablename (var1,var2,var3) VALUES (?,?,?)
values were:'','123', 'foo'

Thanks again for all your help
Chris




More information about the interchange-users mailing list