[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