Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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

In reply to Global symbol "$prepare" requires explicit package name by tukusejssirs

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



  • 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.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2024-03-28 23:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found