ozzie has asked for the wisdom of the Perl Monks concerning the following question:

hello all. I have had some problems with some code i have been working on! I cant seem to get the SQL update to work for this particular script! i have tried everything i can think of, even parts of script found here in this site by others. im new to perl so any help would be great. thanks NOTE: html tags have been removed. 
$db->Sql ("SELECT * FROM Staff");
$hit=0;
while ($db->FetchRow())
{    
  my(%record) = $db->DataHash();    
  if ($FORM{'name'} eq $record{'login'} && $FORM    {'password'} eq $r
+ecord{'password'}) 

{            
$hit=1;
            
if ($FORM{'newpassword'} eq $FORM{'confirmpassword'})
{

-------------------------------                
PROBLEM 

#$sql = qq{INSERT INTO Dreamcpu(FirstName, LastName, Address, Email, P
+h +one, Username, Password) VALUES ('$first', '$last', '$adress', '$e
+mai +l','$phone', '$user', '$pass');}; 

#$sqlupdate = qq {UPDATE staff SET password=$FORM WHERE $FORM{'user'} 
+LIKE $record{'login'}";

my $query = "UPDATE staff SET password=$FORM{'newpassword'} WHERE $FOR
+M={'name'} eq $record{'login'}";

#$dbh->do (qq{ UPDATE `staff` SET `password` = $FORM{'newpassword'}})"
+;

$dbh->do(sqlupdate);
$db->close();
----------------------------
#OK PASSWORD CHANGED

}
else
{
#Please Use Your Browsers Back Button To Re-Enter    
$db->Close();
                
}            
    
}               
}
if ($hit ne 1)
{
#logs error logouts.
           
        
#COULD NOT FIND LOGIN NAME!;
Please Use Your Browsers Back Button & Re-Enter;
}
    
#close the connection to the database end the html page.
$db->Close();

[download]

Replies are listed 'Best First'.
Re: Perl MSAccess SQL UPDATE
by glivings (Scribe) on Mar 25, 2003 at 15:29 UTC
    my $query = "UPDATE staff SET password=$FORM{'newpassword'} WHERE $FORM={'name'} eq $ +record{'login'}";

    Are you really sure you want "WHERE $FORM={'name'}" in your query? You use $FORM as a hash, and then use it in scalar context - so your query would get passed to your DB as: "... WHERE HASH(0x013221)={'name'}", or some other garbled mess.

    I think you probably want "... WHERE name=$record{'login'}", or, making things simpler (and safer), using placeholders (if your dbh supports it) such as '... WHERE name=?'

    Update: just one other quick thought - if your input is coming from a CGI form, as your $FORM variable indicates, you should be using the CGI.pm module. It'll save you a lot of headaches. :)

    Update (again): just looking things over now, $FORM wouldn't print HASH(blahblah) - it wouldn't print anything. Once again, I'd forgotten that you can have $FORM and @FORM and %FORM and sub FORM{}, all at the same time. 'use strict' is good for finding your type of error, btw.

[reply]
Re: Perl MSAccess SQL UPDATE
by benn (Vicar) on Mar 25, 2003 at 14:36 UTC
    Have a look around for error messages in your logs etc. (and consider using the DBI 'RaiseError' etc.), but I suspect this might be to do with quoting in your SQL statements. Try using $dbh->quote() something like this... 
    my $query = "UPDATE staff 
            SET password=".$dbh->quote($FORM{'newpassword'}.
            " WHERE ".$dbh->quote($FORM{'name'}).
            " =". $dbh->quote($record{'login'});

    [download]
    ....or in a less clumsy way than this, if you like :)
[reply]
[d/l]
Re: Perl MSAccess SQL UPDATE
by CountZero (Bishop) on Mar 25, 2003 at 20:13 UTC

    What kind of module are you using to access MSAccess? It doesn't look like a DBI/DBD-combo to me.

    We really need to know that before we can give some useful help.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
      He is using a combination of Win32::ODBC and DBI. $db->FetchRow and $db->Sql are Win32::ODBC. $dbh->do is DBI. I am sure using both together results in some weird interactions.
[reply]
[d/l]
[select]

        I knew there was something mixed-up in that code, only I could not directly trace what it was.

        Thanks for pointing that out.

        CountZero

        "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

Back to Seekers of Perl Wisdom