Can not insert Date

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

I have a perl script to update my SQL 2003 database with a bit of information, including a user inputed date. The problem is that the wrong date is being put into the database. Everytime I check it says the date is "1900/01/01 0:00:00" Even though I put "10/24/06" into the date field. Here is the script code:

#!/user/bin/perl -w
use CGI q~:standard~;
use CGI::Carp qw(fatalsToBrowser);
use strict;
use DBI;
print "content-type: text/html\n\n";
my ($dbh, $sth, $sitepath, @file, $Update, $Name, $Region, $Region_sec
+tion, $POS, $NPC, $Requierments, $Requiredquests, $Fame, $Reward,
$Rewardtype, $Title, $isrepeatable, $walkthough, $Results, $date);
$sitepath = 'e:/web/public_html/finalfantasyinfo';
$Update = param('Update');
$Name = param('Name');
$Region = param('Region');
$Region_section = param('Region_Section');
$POS = param('POS');
$NPC = param('NPC');
$Requierments = param('Requierments');
$Requiredquests = param('Requiredquests');
$Fame = param('Fame');
$Reward = param('Reward');
$Rewardtype = param('Rewardtype');
$Title = param('Title');
$isrepeatable = param('isrepeatable');
$walkthough = param('walkthough');
$date = param('date');
$dbh = DBI -> connect ('dbi:ODBC:', '', '') or die "$DBI::errstr;";
if ($Update eq 'on') {
    if ($Region != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set region = '$Region
+' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Region_section != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set region_region = '
+$Region_section' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($POS != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set pos = '$POS' wher
+e quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($NPC != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set npc = '$NPC' wher
+e quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Requierments != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set requierments = '$
+Requierments' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Requiredquests != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set requiredquests = 
+'$Requiredquests' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Fame != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set fame = '$Fame' wh
+ere quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Reward != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set reward = '$Reward
+' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Rewardtype != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set rewardtype = '$Re
+wardtype' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($Title != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set title = '$Title' 
+where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($isrepeatable != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set isrepeatable = '$
+isrepeatable' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($walkthough != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set walkthough = '$wa
+lkthough' where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
    if ($date != 'No Update') {
        $sth = $dbh -> prepare (qq~update quests set updated = '$date'
+ where quest_name = '$Name'~) or die "$DBI::errstr";
        $sth -> execute or die "$DBI::errstr";
    }
} else {
    $sth = $dbh -> prepare (qq~insert into quests (quest_name, region,
+ region_section, pos, npc, requierments, requiredquests, fame, reward
+, rewardtype, title, isrepeatable, walkthough, updated) values ('$Nam
+e', '$Region', '$Region_section', '$POS', '$NPC', '$Requierments', '$
+Requiredquests', '$Fame', '$Reward', '$Rewardtype', '$Title', '$isrep
+eatable', '$walkthough', '$date')~);
    $sth -> execute or die "$DBI::errstr;";
}
$dbh -> disconnect();
[download]

Comment on Can not insert Date Download Code

Replies are listed 'Best First'.
Re: Can not insert Date by andyford (Curate) on Oct 24, 2006 at 23:02 UTC
This is basic guesswork, but I have to ask: did you verify that the right thing is getting into your cgi program? `$date = param('date'); print "got date $date in cgi";` [download] I guess the other basic thing to check would be to use your database's command line tool to make sure that it recognized the date format of what your feeding it as a date. Your database might have very specific rules on how to format dates. andyford or non-Perl: Andy Ford	[reply] [d/l]
Re: Can not insert Date by graff (Chancellor) on Oct 25, 2006 at 05:13 UTC
Since andyford has given the advice that is directly relevant, let me give you some slightly off-topic advice: Don't use "!=" to test inequality of string values; use "ne" for that. Use "my" to declare each variable close to (just within the scope of) the place where you actual use it; declaring them all at the top is crude and not helpful. You might want to check all your parameters for valid, sensible values (not just the date field). Your SQL statements should be prepared with "?" placeholders where the parameter values go, and the parameters should be passed as args in the "execute" calls. If/when you turn off the "fatals to browser" feature, you should make your error messages more informative -- include $0 (name of the script) and the full sql statement or whatever made it die -- so that referring to the web server's error log will be more helpful. If you use a hash for parameters (keys are param names, values are param values) instead of a distinct scalar variable for every distinct param, the code will be easier to maintain. When you find yourself copy/pasting the same code block lots of times and just changing a variable name each time, you really should consider turning it into a subroutine and/or using a loop over the variables (this is where a hash can be really handy), so that the code block only needs to occur once in your script. This will also make your code a lot easier to maintain (and easier for others to read and understand). Here's a version of your code that applies some of those suggestions. It should be equivalent to what you posted (but I only tested to make sure that it compiles correctly). #!/user/bin/perl -w use CGI qw(:standard); use CGI::Carp qw(fatalsToBrowser); use strict; use DBI; print "content-type: text/html\n\n"; my @parnames = qw/Update Name Region Region_Section POS NPC Requirements Requiredquests Fame Reward Rewardtype Title isrepeatable walkthrough date/; my @to_update = @parnames[2..$#parnames]; my @to_insert = @parnames[1..$#parnames]; my %col_name; my %par_value; for my $par ( @parnames ) { $col_name{$par} = lc( $par ); $par_value{$par} = param( $par ) \|\| ''; } # handle some exceptional column names: $col_name{Region_Section} = 'region_region'; $col_name{Name} = 'quest_name'; $col_name{date} = 'updated'; my $sitepath = 'e:/web/public_html/finalfantasyinfo'; my $dbh = DBI -> connect ('dbi:ODBC:', '', '') or die "$DBI::errstr;"; if ($par_value{Update} eq 'on') { for my $par ( @to_update ) { next if ( $par_value{$par} eq 'No Update' ); my $sql = "update quests set $col_name{$par} = ? where quest_n +ame = ?"; my $sth = $dbh->prepare( $sql ); $sth->execute( $par_value{$par}, $par_value{Name} ) or die "$sql:\n $DBI::errstr"; } } else { my $cols = join( ",", @to_insert ); my $vals = join( ",", ("?") x scalar @to_insert ); my $sql = "insert into quests ($cols) values ($vals)"; my $sth = $dbh->prepare( $sql ); $sth->execute( @par_value{@to_insert} ) or die "$sql:\n $DBI::errstr"; } $dbh -> disconnect(); print "Looks like that worked."; [download]	[reply] [d/l]
Re: Can not insert Date by davorg (Chancellor) on Oct 25, 2006 at 07:55 UTC
How is the 'updated' column in your table defined? Is it a datetime column or a text column? If it's a datetime column then you need to be sure that the date that you are giving it is in the right format. This whole program would be a lot simpler if you used an ORM layer like DBIx::Class or a complete MVC framework like Catalyst. But even if you don't switch to something like that, you should really look at using placeholders in your SQL statements as that will handle all of the quoting for you and remove some potential problems from your code. -- <http://dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]