I have a table filled with some data; the only important for this question is date.
Now I need to update (modify) the date column (which contains timestamp formatted like this: YYYY-MM-DD HH:MM:SS+ZZ so that the date portion will become today (or possibly any other date) while the time portion remains intact. It should take into account the daylight saving.
But I ran into some problems. While I was trying to deal with it, there where different errors:
DBD::Pg::st execute failed: execute called with an unbound placeholder
+ at ./refresh_dates.pl line nn.
DBD::Pg::st execute failed: execute called with an unbound placeholder
+ at ./refresh_dates.pl line nn.
# Or:
Can't locate object method "execute" via package "DBI::db" at ./refres
+h_dates.pl line nn.
Current error looks like this:
Global symbol "$prepare" requires explicit package name at ./refresh_d
+ates.pl line nn.
Execution of ./refresh_dates.pl aborted due to compilation errors.
All of these errors were about the same line(s), these:
my $sth2 = $dbh->$prepare("update $schema.$table set $date_col = $date
+_new where $where_col = 1;") or die;
$sth2->execute();
And here is the current code in full. Note that I plan to implement a
for, but for now I just wanted to test the code with one (first) row.
#!/bin/perl
use strict;
use warnings;
use lib ('./');
use mylib;
use DateTime;
use DateTime::Duration;
use Time::Piece;
use POSIX qw(strftime);
# User-dependent variables
my $db_driver = 'Pg'; # `Pg` for psql, `mysql` for
+mysql
my $username = 'postgres'; # Database user name
my $password = 'password'; # Database user password
my $database = 'mydb'; # Database name
my $schema_table = 'myschema.mytable'; # Schema.table
# Other variables
my $schema = $schema_table;
my $table = $schema_table;
$schema =~ s/^([^.]*).*$/$1/;
$table =~ s/^[^.]*.([^.]*)$/$1/;
if ($schema_table =~ /\./){
$schema =~ s/^([^.]*).*$/$1/;
$table =~ s/^[^.]*\.([^.]*)$/$1/;
} else {
$schema = 'public';
$table = $schema_table;
$schema_table = '$schema.$table';
}
my $date_col = "date";
my $where_col = "id";
my ($hours, $minutes, $seconds);
my (@date_time, $temp, $date_new);
my ($year, $month, $day);
my ($date_tbl, time_tbl);
my ($dsn, $dbh, $sth, $sth2);
# Open the database
$dsn = "DBI:$db_driver:dbname = $database";
$dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1 }) or di
+e RED, "ERROR: The database could not be opened.\n $DBI::errstr
+\n Stopped$!";
print "INFO: The database has been opened successfully.\n";
# Get time from DB
$sth = $dbh->prepare("select $date_col from $schema.$table where $wher
+e_col = 1;") or die;
$sth->execute();
$sth->bind_col(1, \$temp);
while ($sth->fetch()) {
$date_time[0] = $temp;
}
# Get date
$date_tbl = $temp;
$temp =~ /([^-]+)-([^-]+)-([^-]+) /;
($year, $month, $day) = ($1, $2, $3);
# Get hours, minutes, seconds from time
$time_tbl = $temp;
$time_tbl =~ / ([^:]+):([^:]+):([^:]+)[+]/;
($hours, $minutes, $seconds) = ($1, $2, $3);
# Set new date
$date_new = DateTime->today->set_time_zone('Europe/London')->set_hour(
+$hours)->set_minute($minutes)->set_second($seconds)->strftime("%F %T%
+z");
# Update the dates
$sth2 = $dbh->$prepare("update $schema.$table set $date_col = $date_ne
+w where $where_col = 1;") or die;
$sth2->execute();
# Close the database
$dbh->disconnect() or die RED, "ERROR: The database could not be disco
+nnected.\n $DBI::errstr\n Stopped$!";
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.