[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

SQL query within fetchrow

Hello,

Im writing a perl script and need to select some rows from a table, then run a sql query based on the information received for each row.

ie.

$SQL = "SELECT * FROM table";
&Do_SQL;

while($pointer = $sth->fetchrow_hashref)
{

$gross = $pointer->{'gross'};
$name += $pointer->{'name'};
   if($gross eq "A")
   {
    $SQL = "SELECT title FROM table WHERE bla bla";
    &Do_SQL;
       while($pointer = $sth->fetchrow_hashref)
       {
        $title = $pointer->{'title'};
       }
   }
}


But that wont work, so how can it be done?
0
sharpmail
Asked:
sharpmail
  • 4
  • 3
1 Solution
 
kanduraCommented:
It won't work, because the inner statement handle $sth invalidates the outer $sth. That's the price you pay for doing the wrong kind of abstraction with your Do_SQL subroutine.

I'll give you one line of advice. You can come back with questions if you get your script to run again with that line added:

    use strict;

Put it at the top of your script.
0
 
ahoffmannCommented:
simply get rid of the inner while loop by specifying a proper SELECT statement ;-)
0
 
sharpmailAuthor Commented:
what do you mean a proper select statment? and what am i doing wrong with the Do_SQL sub?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sharpmailAuthor Commented:
I have managed to make it work by making another sub of Do_SQL and renaming $sth to $sth1 so they dont invalidate each other. but if there is a better way, i would like to know ;)
0
 
kanduraCommented:
copy/paste coding is not recommended :-)

The interface to your Do_SQL is wrong. You should make it a proper function, with input and output parameters. Then you call it like this:

    my $sth = Do_SQL($SQL);

and in the inner loop:

    my $inner_sth = Do_SQL($SQL);

I'd suggest something like this (assuming your $DBH is a global variable):

    sub Do_SQL {
        my $sql = shift;
        my $sth = $DBH->prepare($sql);
        $sth->execute;
        return $sth;
    }

Note that I do not approve of this kind of code: it still uses a global variable; but most importantly, you're hiding away the wrong kind of complexity.
In any case, the above function will solve your problem.
0
 
sharpmailAuthor Commented:
ah I see what you mean! now that is much tidier than my idea. With reference to the global var whats wrong with using that? and what do you mean by hiding the wrong kind of complexity?
0
 
kanduraCommented:
Global variables are bad (in general), because they make it harder to track bugs; they can cause side effects; they break encapsulation. By keeping every variable in as small a scope as possible, you keep everything nicely together; it makes your code more modular, easier to read and maintain, and generally better.

As for hiding the wrong complexity: you can no longer pass specific attributes, or use placeholders to prepare and execute. That means you're missing out on all kinds of neat features; plus it will be harder to catch errors, because they now all seem to come from Do_SQL.

Anyway, you're helped for now, and that is what counts. Maybe all this is a bit over your head right now; you'll become more experienced at programming over time, and then you'll find those things out anyway.
0
 
sharpmailAuthor Commented:
well the var SQL is always reset in this instance so wont cause a problem, but I realise what your saying. As for getting better, maybe not, this is year 5 of programming, while I can write and do what I need to do in order to run my site im still using the basics of perl the site is run completly in perl and could be done more efficiently but as always time isnt on my side!

Thank you for your assistance!

Danny
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now