It doesn't do anything because you aren't executing the SQL statement. You prepare it, but you don't execute it. DBI has an "execute" method that needs to be called in order to run SQL against a database.

$sth_Drop_quick_fix->execute
  or die $sth_Drop_quick_fix->errstr;
[download]

Er, so please explain what it is supposed to do and what it is doing that it is not supposed to do. And does the SQL that you have behave in the same way in query analyzer, if it does that is a sure sign that you do not have a Perl question but should ask somewhere else.

/J\

if exists(select 1 from INFORMATION_SCHEMA
+.tables where table_name = 'DSR_SMR_Result_storage_keep') begin DROP 
+TABLE DSR_SMR_Result_storage_keep  end
[download]

Thanks for this gentle and insightful response. Posts like this are one of the things that set PerlMonks apart as a site where people (even lazy people) are patiently taught to fish rather than being given a quick meal. Not that responses like Where's my pony? aren't funny, but I suspect some readers don't understand the humor and chalk them up as monks being spitefully mean.

As someone who has occasionally been unsure about whether a question had 'enough Perl' in it to justify a SoPW post, I appreciate your analysis.


No good deed goes unpunished. -- (attributed to) Oscar Wilde

I'm actually surprised. Aside from the complete lack of context, any explanation of what's expected, any error messages, or useful direction besides the Neanderthalic "Ugh code no work" I could see where this might actually be the first recent posting have a vague marginal amount of Perl relevance out of the gate which wasn't stealth edited in after people have already downvoted it into the bowels of Worst Nodes.

Granted he's still a bozo who won't bother to learn from How (Not) To Ask A Question no matter how many people point him at it.

And I still want my pony.

and gives an error if it can't prepare it.

Actually it will give an error but not the one from DBI, since if the prepare fails the statement handle scalar will be undef and Perl will gripe about attempting to call a method on it.</pedant>

Is this a quiz?

Seriously — Inasmuch as PerlMonks isn't a free debugging service (contrary to popular opinion), you'd undoubtedly get better responses if you'd read, and heed, the FAQlet How do I post a question effectively?.

I'll take SQL problems for $500 Alex!

Remember, all answers must be in the form of a question :)

Update:On a serious note Win, many times you have been pointed towards How do I post a question effectively?. Please read and learn from this. Please take the advice that gellyfish has given you. Please put more effort into your posts.

Martin

Or simply bypass the statement handle creation, and call $dbh->do( $Drop_table_quick_fix ).

Looks to me like you're trying to pass some Perl code into a statement handle prepare. I don't recognize the 'if exists' as being valid SQL ... but then I'm more of an Oracle guy and I'm not very good with PL/SQL.

Update: I'm told this is T-SQL. This illustrates why asking this kind of question here may not get you the results you seek, as other monks have commented. :)

eval {
    $dbh->do("drop table DSR_SMR_Result_storage_keep");
};
[download]

eval{} is a general solition but is not needed in this case becaue DBI handles have two modes for reporting errors depending on the state of the RaiseError attribute.

If $dbh->{RaiseError} is false (the default) then you can ignore SQL errors simply by not checking to see if there's been one.

Personsally I always open DBI handles with RaiseError true and switch it off locally in blocks where I want to ignore or recover from SQL errors.

To drop an SQL object that may or may not exist I do:

{
    local $dbh->{RaiseError} = 0;
    $dbh->do("drop table DSR_SMR_Result_storage_keep");
}
[download]

Obviously if the drop fails for some reason other than the object not existing then I miss the reason but since the next SQL statment is typically about to create the object again I do at least get an error there.

There used to be a memory leak when you used local on an element of a tied hash, and a DBI handle is a tied hash. See Memory Leak Bug. I don't know what Perl version it got fixed, if it even got fixed, but in case you're forced to use an old perl version like 5.005_03 or 5.6.0, then it's safer to use eval.

We're talking about a quick fix for a short piece of code that shouldn't be run too often. In such a case, I'm willing to settle for "easy" and "good enough" code. local is easy, emulating local to avoid the meory leak, isn't.