Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

PostgreSQL UPDATE

by nlafferty (Scribe)
on Jul 18, 2001 at 21:23 UTC ( #97780=perlquestion: print w/ replies, xml ) Need Help??
nlafferty has asked for the wisdom of the Perl Monks concerning the following question:

For some reason I can't get this to update my database and i see no problems with the script. And i get no errors in my logs.
#!/usr/bin/perl ### modify2.cgi use CGI qw/:standard/; require "common.sub"; $old_email = $ENV{QUERY_STRING}; $emp_id = &filter(param(emp_id)); $f_name = &filter(param(f_name)); $l_name = &filter(param(l_name)); $extension = &filter(param(extension)); $phone = &filter(param(phone)); $email = &filter(param(email)); $notes = &filter(param(notes)); print header; &Create_DB_Connection; &write_data; &print_output; # Disconnect from the database $dbh->disconnect; ################ BEGIN WRITE DATA SUBROUTINE sub write_data{ $SQL="UPDATE contact SET emp_id = '$emp_id', f_name = '$f_name', l_name = '$l_name', extension - '$extension', phone = '$phone', email = '$email', notes = '$notes' WHERE email = '$old_email' "; &Do_SQL; } # End of write_data ################ END WRITE DATA SUBROUTINE ################ BEGIN PRINT OUTPUT SUBROUTINE sub print_output{ print<<HTML; <HTML><BODY BGCOLOR="#F1EDD3"> <CENTER><FONT SIZE=5 FACE=ARIAL> Record modified in database <P> <A HREF="http://somewhere.com/nick/dev/index.html">Return to Main +Page</A> </P> </FONT></CENTER> </BODY></HTML> HTML } # End of subroutine ################ END PRINT OUTPUT SUBROUTINE
Thanks

Comment on PostgreSQL UPDATE
Download Code
Re: PostgreSQL UPDATE
by Cubes (Pilgrim) on Jul 18, 2001 at 21:29 UTC
    I had similar problems with PostgreSQL (silently failing inserts/updates). Mine ended up being permissions in the database -- be sure you do the appropriate grants to whatever user you're connecting as.
(ichimunki) Re: PostgreSQL UPDATE
by ichimunki (Priest) on Jul 18, 2001 at 21:50 UTC
    Can you show us the Do_SQL() subroutine? This is a key to your problem..

    Other things I would suggest working on: not using $ENV{QUERY_STRING} at all if you can help it, putting a content-type header on your HTML output, using strict, and avoiding globals.
Re: PostgreSQL UPDATE
by TheoPetersen (Priest) on Jul 18, 2001 at 22:05 UTC
    Do you have AutoCommit set to 0? If so, you must explicitly call commit to save your writes to the database before you disconnect.

    The setting for AutoCommit would be in your DBI connect call.

      What would that look like?
Re: PostgreSQL UPDATE
by thraxil (Prior) on Jul 18, 2001 at 22:08 UTC

    have you tried running the SQL by hand in pgsql?

    i've seen postgresql have problems with silently dropping inserts after i had inadvertantly shut it down improperly. the problem went away after i shut it down nicely and restarted it.

    i'd also strongly recommend using DBI's prepared statements with placeholders instead of trying to escape the input yourself. keeping track of nested quotes, backslashes, and misc. characters is trickier than it would seem at first and is best left to DBI. if your '&filter' function isn't handling something correctly, it may be munging $old_email into something that doesn't match any rows in the database. that would certainly cause it to not update anything and not print an error message either.

    anders pearson

Re: PostgreSQL UPDATE
by lestrrat (Deacon) on Jul 18, 2001 at 22:17 UTC

    I think we need to see Create_DB_Connection() and Do_SQL()...

    From my experience, these problems tend to occur due to some implicit behavior that the user is unaware of -- so we need to know how you created the $dbh variable, and how you talk to the DB

Re: PostgreSQL UPDATE
by nlafferty (Scribe) on Jul 18, 2001 at 22:40 UTC
    I can't believe the response time in this place. I love it. BTW, My insert statment works fine so i did not include that script. I tried to compare the two but nothing seemed wrong there.
    ########################################## ## Create a connection to the database. ## ########################################## sub Create_DB_Connection{ use DBI; use DBD::Pg; $DSN = "DBI:Pg:dbname=blah"; $user = "blah"; $pw = "blah"; $dbh = DBI->connect($DSN,$user,$pw) || die "Cannot connect: $DBI::errstr\n" unless $dbh; return; } # End of Create_DB_Connection subroutine. ########################################## ########################################## ## Executes the SQL command and then ## ## returns to the calling area of the ## ## program. ## ########################################## sub Do_SQL{ eval{ $sth = $dbh->prepare($SQL); }; # End of eval # Check for errors. if($@){ $dbh->disconnect; print "Content-type: text/html\n\n"; print "An ERROR occurred! $@\n<P>"; exit; } else { $sth->execute; } # End of if..else return ($sth); } # End of Do_SQL subroutine #################################################################
      Forgot the filter code. All three of these are contained in common.sub
      #################################### ### Filter - Gets rid of ### ### characters that screw up the ### ### program. ### #################################### sub filter{ $_[0]=~s/\'/\\\'/g; return $_[0]; } # End of filter subroutine ################################################################# 1; # Required or won't work!
      Your eval won't catch anything unless you set the RaiseError attribute on your database handle:
      $dbh = DBI->connect($DSN,$user,$pw, { RaiseError => 1 }) || die "Cannot connect: $DBI::errstr\n" unless $dbh;
      Otherwise, check the results of all of your $dbh calls for undef and do something with $DBI::errstr yourself.
        This is also where you can set AutoCommit, by the way.

        adamsj

        They laughed at Joan of Arc, but she went right ahead and built it. --Gracie Allen

Re: PostgreSQL UPDATE
by TheoPetersen (Priest) on Jul 18, 2001 at 23:10 UTC
    l_name = '$l_name', extension - '$extension', phone = '$phone',
    Shouldn't that be
    l_name = '$l_name', extension = '$extension', phone = '$phone',
    I would think you'd get an error back from the prepare call, but then, I've been wrong about databases doing obvious things before :)
      Phew. Now that is definatly a problem. Thank you
Re: PostgreSQL UPDATE
by merlyn (Sage) on Jul 18, 2001 at 23:32 UTC
    I don't like that you are looking at both $ENV{QUERY_STRING} and param. If this is a GET, then QUERY_STRING is gonna be ugly, not just the old_email. If this is a POST, then QUERY_STRING will be empty on compliant browsers, and it all shows up in params.

    Time to redesign your calling interface.

    -- Randal L. Schwartz, Perl hacker

      hmm. What suggestions do you have for Replacing $ENV{QUERY_STRING} and param? What are the problems with using these?
        I need to understand your problem better. I need to know what question you were answering when you answered it "I have to use both a query string and a param", because that question is either ineffective, or the question that triggered an answer that triggered that question needs to be questioned. And there's your answer. {grin}

        -- Randal L. Schwartz, Perl hacker

Re: PostgreSQL UPDATE
by lestrrat (Deacon) on Jul 18, 2001 at 23:40 UTC

    I'd strongly suggest using RaiseError, since Error handling is so much easier that way. Plus, you're not making sure that $sth->execute actually worked or not

    I have a feeling that if you set RaiseError, then you would start getting fatal errors... then you can debug

    As a side note, I personally like to use AutoCommit => 0 as well, just so that I know exactly what I'm doing...

      I also like trace(2) function of DBI. It will show you what is going on with your SQL statements.

      pmas
      To make errors is human. But to make million errors per second, you need a computer.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://97780]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2014-07-13 08:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (248 votes), past polls