DBI Conundrum Using CGI::App

[waltw]

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));

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;

[rhesa]

I'd like to see the way you connect to the database as well. How does your DBI->connect call look?

[waltw]

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

[Hue-Bond]

I can't figure out my sin of commission

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

[waltw]

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

[shmem]

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

--shmem

[Hue-Bond]

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.

[jZed]

$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.

[eric256]

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.

[Tanktalus]

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 ?.

[eric256]

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.