DBI Conundrum Using CGI::App

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

I can verify the data is returned and delivered to my routine with the edits intact
I can verify that SQLite dutifully throws an error if I mal-form the SQL>
I can stub out the routine to print the SQL statement, post interpolation -- it is correct
I can verify that SQL statement operates as intended when applied to the SQLite command line
Single table referenced, Media:
CREATE TABLE Media (media_code TEXT, media_desc TEXT, PRIMARY KEY (media_code));

Question

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,

Walt

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;
[download]

Comment on DBI Conundrum Using CGI::App Select or Download Code

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?	[reply] [d/l]
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. Appreciatively, Walt	[reply]
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	[reply]
Re^2: DBI Conundrum Using CGI::App by Tanktalus (Canon) on Jul 06, 2006 at 19:50 UTC
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 ?.	[reply]
Re^3: DBI Conundrum Using CGI::App by eric256 (Parson) on Jul 06, 2006 at 20:46 UTC
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	[reply] [d/l] [select]
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	[reply]
Re^2: DBI Conundrum Using CGI::App by waltw (Acolyte) on Jul 06, 2006 at 20:02 UTC
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! Appreciatively, Walt	[reply]
Re^2: DBI Conundrum Using CGI::App by shmem (Chancellor) on Jul 06, 2006 at 19:48 UTC
`$dbh->commit`? Oh, yes. But before I'd do a `$sth->finish`. --shmem _($_=" "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}	[reply] [d/l] [select]
Re^3: DBI Conundrum Using CGI::App by Hue-Bond (Priest) on Jul 06, 2006 at 20:05 UTC
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	[reply]
Re^3: DBI Conundrum Using CGI::App by jZed (Prior) on Jul 06, 2006 at 20:03 UTC
$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.	[reply]