Re: [ic] Perl/SQL - UPDATE Queries

[Mark Johnson <markj@redhat.com>]

Russ Mann wrote:
> 
> How would you do this in a [perl] block?


Russ,

I responded to this question that you wrote last Thursday. I'm
re-sending my reply to you here.


--------------------

Russ Mann wrote:
> 
> Hello List,
> 
> I have a form taht sends the variable login_email to another page, with this
> code on it.  The purpose is to update a persons username, since we encourage
> people to use their email addresses as their username.
> 
> The problem is my SQL query.  I've loaded $sql_req with all the proper query
> lines, but can't find a way to execute the query without breaking something.
> 
> Any ideas would be great.
> 
> Thanks,
> 
> Russ
> 

I believe the data tag called from embedded perl will fail if there is
no matching record. Thus, the !($Tag->data('userdb', 'username',
$login_email )) is doomed. Second, to allow writing, you should mark
your tables with [flag type=write table="my table list"]. Also, when
using embedded perl, you have access to many nice functions via the %Db
object. Try this:

[flag type=write tables="userdb ship_addresses transactions orderline"]
[perl tables="userdb ship_addresses transactions orderline"]

 my @tables = (
                 "userdb",
                 "ship_addresses",
                 "transactions",
                 "orderline"
                 );

 my $login_email = $Values->{login_email};
 my $old_username = $Session->{username};
 
 return "<b>$login_email</b> is the same as <b>$old_username</b>.  No
update performed."
        if $login_email eq $old_username;

 unless ( $Db{userdb}->record_exists($login_email) ) {

   # This may not be necessary since it's an email, but you should get
in the
   # habit of always escaping your data!

   my $f_login_email = $Tag->filter('sql',$login_email);
   my $f_old_username = $Tag->filter('sql',$old_username);

   foreach my $table(@tables) {
     my $sql_req = <<EOP;
UPDATE $table
SET username = '$f_login_email'
WHERE username = '$f_old_username'
EOP
     $Db{$table}->query($sql_req);

   }

   return <<EOP;
Your login email address has been changed to <b>$login_email</b>.  You
will
need to <a href="[area login]">Login</a> again with your new address.
EOP

 }

 return <<EOP;
<b>$login_email</b> is already in use.  If you have more than one
account with us, please email <a href="[area help/contact]">Customer
Service</a>
to get this resolved.
EOP
[/perl]

You do not need interpolate=1 in this or the vast majority of cases with
a perl tag.
 
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

-- 
Mark Johnson
Senior Systems Architect - Professional Services
Red Hat, Inc.
E-Business Solutions
markj@redhat.com
703-456-2912