You’re right. I missed that.

But still it does not work; it throws the following error on $sth2->execute(); line (it was output doubled like this with the same line number):

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

Try

$sth2 = $dbh->$prepare("update $schema.$table 
                        set $date_col = ?
                        where $where_col = ?;") or die;
$sth2->execute($date_new,1);
[download]

poj

And could someone tell why the following code does not work? It does not work either with or without placeholders, but with different errors.

In this case, I want to select all unique dates from timestamps. It works as expected with select distinct cast(date as date) from table; issued directly in psql.

# With placeholders
my $sth_test = $dbh->prepare("select distinct cast($date_col as date) 
+from ?;") or die;
$sth_test->execute($schema.$table);

# Errors (on execute())
INFO: The database has been opened successfully.
DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: select distinct cast(date as date) from $1;
                                                       ^ at ./refresh_
+dates.pl line n.
DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: select distinct cast(date as date) from $1;
                                                       ^ at ./refresh_
+dates.pl line n.


# Without placeholders
my $sth_test = $dbh->prepare("select distinct cast($date_col as date) 
+from $schema.$table;") or die;
$sth_test->execute();
$dbh->disconnect();

# Error (on disconnect)
DBI::db=HASH(0x32cec98)->disconnect invalidates 1 active statement han
+dle (either destroy statement handles or call finish on them before d
+isconnecting) at ./refresh_dates.pl line n.
[download]

In addition to what poj said, instead of

$sth2 = $dbh->prepare("update $what$ever");
[download]

to see what's going on, write

my $sqltext = "update $what$ever";
print qq(SQL query is: "$sqltext");
$sth2 = $dbh->prepare($sqltext);
[download]

Perhaps there's a stray question mark in your $date_new

Indeed poj, it works now.

Could you tell me please, why does it need to have ? (I believe these question marks are those placeholders from the error message) in the prepare part? And why I could not run $dbh->execute("update $schema.$table set $date_col = $date_new where $where_col = 1;") or die; directly? In this particular code I don’t need neither $sth handle for reuse nor the placeholders to re-execute the code (as the variables will be all the same in all the loop cycles).<\p>

Thank you very much!