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;

[download]

In reply to DBI Conundrum Using CGI::App by waltw

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.