Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Database access problem

by Win (Novice)
on May 19, 2006 at 15:26 UTC ( #550511=perlquestion: print w/replies, xml ) Need Help??

Win has asked for the wisdom of the Perl Monks concerning the following question:

This node falls below the community's threshold of quality. You may see it by logging in.

Replies are listed 'Best First'.
Re: Database access problem
by davorg (Chancellor) on May 19, 2006 at 15:47 UTC

    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;

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

Re: Database access problem
by gellyfish (Monsignor) on May 19, 2006 at 15:44 UTC

    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.


    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Database access problem
by SamCG (Hermit) on May 19, 2006 at 16:01 UTC
    I'll give you the best explanation I can of the Perl part:
    Assuming $dbh is a database handle and you're using DBI, it attempts to prepare a sql statement
    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
    and gives an error if it can't prepare it.

    It does so by 1) setting the sql statement to a scalar variable, 2) declaring another scalar, and 3) using a previously prepared database handle to prepare the sql statement.

    Of course, you don't actually need to know much Perl to know that; I'm sure anyone who worked in any language could tell you that. I'd suspect you yourself knew that already.

    That's why people keep telling you you're not asking Perl questions. If my answer above was actually helpful, maybe you really are having Perl problems. If not, you should go to some other forum with this stuff. Believe it or not, generally Sql Server experts know more about T-SQL than Perl experts (not that I'm claiming particular expertise), and you'd get far better answers to your questions.

    s''limp';@p=split '!','n!h!p!';s,m,s,;$s=y;$c=slice @p1;so brutally;d;$n=reverse;$c=$s**$#p;print(''.$c^chop($n))while($c/=$#p)>=1;

      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>

Re: Database access problem
by jdporter (Chancellor) on May 19, 2006 at 15:33 UTC

    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.

Re: Database access problem
by rhesa (Vicar) on May 19, 2006 at 15:48 UTC
    You may want to actually execute that statement with $sth_Drop_quick_fix->execute.

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

Re: Database access problem
by bart (Canon) on May 19, 2006 at 16:16 UTC
    You could just try to drop the table and ignore any errors. eval is good for that kind of stuff.

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

      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"); }

      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.

Re: Database access problem
by ptum (Priest) on May 19, 2006 at 15:44 UTC

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

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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://550511]
Approved by marto
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (2)
As of 2023-03-25 06:57 GMT
Find Nodes?
    Voting Booth?
    Which type of climate do you prefer to live in?

    Results (62 votes). Check out past polls.