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

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

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

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

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.