Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

DBI Conundrum Using CGI::App

by waltw (Acolyte)
on Jul 06, 2006 at 19:29 UTC ( #559653=perlquestion: print w/replies, xml ) Need Help??

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

Dear Perl Monks:

I am using CGI::App and have as one of my run-modes the following short and simple update routine against a data schema implemented using SQLite.

The objective is to take the edited media type code description returned from an HTML form and update the existing record in the database.

The Problem

The database does not update with the new data, and continues to show the old data. SQLite does not throw an error. I can't figure out my sin of commission or omission and hope for a quick head slapping experience.

Other notes

  1. I can verify the data is returned and delivered to my routine with the edits intact
  2. I can verify that SQLite dutifully throws an error if I mal-form the SQL>
  3. I can stub out the routine to print the SQL statement, post interpolation -- it is correct
  4. I can verify that SQL statement operates as intended when applied to the SQLite command line
  5. Single table referenced, Media:

    CREATE TABLE Media (media_code TEXT, media_desc TEXT, PRIMARY KEY (media_code));


After $sth->execute(); the database remains non-UPDATEd nor does it throw any errors -- why??!!

I'm pulling my hair and beard over this one -- read through Programming the Perl DBI, searched on this site, read through CPAN's docs on CGI and CGI:App.

Thank you,


Short Routine Follows

sub updateMedia { my $self=shift; my $q = $self->query(); my $dbh = $self->param('mydbh'); my $scripturl = $q->url(); my $scripthome = $q->script_name(); # From HTML form my $media_desc = $q->param('txtMediaDesc'); my $media_code = $q->param('media_code'); # SQL Update my $media_desc_quoted = $dbh->quote( $media_desc ); my $media_code_quoted = $dbh->quote( $media_code ); my $sql = qq{UPDATE Media SET media_desc=$media_desc_quoted WHERE media_code=$media_code_quoted}; my $sth = $dbh->prepare($sql); $sth->execute(); # Redirect back to start $self->header_type('redirect'); $self->header_props(-url=>$scripthome."?rm=editMedia&media_cod +e=".$media_code); return;

Replies are listed 'Best First'.
Re: DBI Conundrum Using CGI::App
by rhesa (Vicar) on Jul 06, 2006 at 19:59 UTC
    I'd like to see the way you connect to the database as well. How does your DBI->connect call look?
Re: DBI Conundrum Using CGI::App
by waltw (Acolyte) on Jul 06, 2006 at 20:13 UTC

    Dear Monks:

    Thank you one and all for the rapid responses -- and interesting directions of inquiry - these have been a valuable read.

    I am fixed and all is working as intended / expected -- I have now to confess to mis-setting the directory permission, leaving the Apache server with read-only access to the SQLite data file.

    (:-o This is me, slapping my head. Please pardon me for posting a non-problem.



Re: DBI Conundrum Using CGI::App
by Hue-Bond (Priest) on Jul 06, 2006 at 19:32 UTC
    I can't figure out my sin of commission

    Maybe you're missing a $dbh->commit.

    David Serrano

      Thank you, David, but with SQLite I have auto-commit.

      However, your response kicked several synapses into gear and I took a look at permissions..... I'm so red-faced! My directory was 755 and the webserver had read-only permission.

      I'm fixed and all is working as it should be.

      Pardon me for posting a non-problem!



      $dbh->commit? Oh, yes. But before I'd do a $sth->finish.


      _($_=" "x(1<<5)."?\n".q/)Oo.  G\        /
                                    /\_/(q    /
      ----------------------------  \__(m.====.(_("always off the crowd"))."
      ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}

        Quoting the DBI documentation:

        • rarely needed
        • frequently overused
        • you should not normally need to call it explicitly
        • internal "housekeeping" method that is rarely needed

        And I don't see why it should be used here.

        David Serrano

        $sth->finish() is relevant only to SELECT and other data-returning calls and only then when the data has not been fully fetched. It has no relevance to an UPDATE statement and should *not* be used in this case.
Re: DBI Conundrum Using CGI::App
by eric256 (Parson) on Jul 06, 2006 at 19:45 UTC

    If your media code is a number could it be failing because the number is quoted? I tend to use ? instead of quote because i think it does the right thing more often but that might just be my ignorance ;) Either way you should verify that the SQL getting generated is what you expect and that the prepare isn't failing, i've had prepare fail pretty silently in the past. Check your error logs and print out the SQL it generates to make sure that is all working as expected.

    Eric Hodges

      Your ignorance? I think that the rule is that you only quote when you have to, and bind your parameters at all other times. Which pretty much means a borked driver, I think, where it doesn't handle quoting for you, and neither does the database backend (if any). So, if anything, your only ignorance might be saying you're ignorant about using ? instead of quote ;-)

      Update: ok, that might have been confusing. You're absolutely right - use ? over quote(). Everytime. Unless, of course, the DBD doesn't support ?.

        Jsut to clafiy, because now you've confused me: I use ? and it seems to quote exactly when i would expect it to. Which means treating numbers correctly. I avoid ->quote() as much as possible becuase it didnt' seem to DWIM as often as ? did and ? looks better to me. ;) So I think that the OP might need to say column="$quoted_string" or column  = ? and pass it an unquoted string.

        Eric Hodges

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://559653]
Approved by willyyam
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (2)
As of 2023-10-01 12:42 GMT
Find Nodes?
    Voting Booth?

    No recent polls found