Re^4: Global symbol "$prepare" requires explicit package name

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]

Comment on Re^4: Global symbol "$prepare" requires explicit package name Select or Download Code

Replies are listed 'Best First'.
Re^5: Global symbol "$prepare" requires explicit package name by hippo (Archbishop) on Sep 19, 2019 at 14:29 UTC
Placeholders cannot be used for table names or schema names or anything within the schema. They may only be used for data.	[reply]
Re^6: Global symbol "$prepare" requires explicit package name by tukusejssirs (Beadle) on Sep 19, 2019 at 14:52 UTC
The problem was that the statement handle was not finished. Solution is to add `$sth_test->finish();` before disconnecting the database (src).<\p> However, I never used this command before and never encountered this error. Should I use it always before disconnecting the database?	[reply] [d/l]
Re^7: Global symbol "$prepare" requires explicit package name by hippo (Archbishop) on Sep 19, 2019 at 15:17 UTC
The documentation for DBI is really good. I do recommend that you read it. In this case the documentation for the finish method goes into some detail about when you should call it and when you should not and the advantages and disadvantages of both. Also, what you have encountered on disconnection is not an error. It is a warning. You might think these 2 terms mean the same thing but they do not. See Re: Warnings vs. Errors. Warnings are good because they alert you to situations (such as disconnecting while there are active statement handles) which could be indicative of other problems.	[reply]