http://qs321.pair.com?node_id=655637

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

Dear Monks,

I'm having difficulty returning two records fields from this select statement. What is the best way of doing it? 


foreach (@regions_A_array){

  my $Select_two_records = "
    SELECT G.Region, G.Score FROM Gov_regions_scores_TEMP G, Region_lo
+okup R WHERE R.String = '".$_."' AND RTRIM(R.Place) = RTRIM(G.Region)
  ";
  

  my $sth_m_A = $dbh->prepare($Select_two_records) or die "Couldn't pr
+epare query: ".$dbh->errstr;
  $sth_m_A->execute() or die "Couldn't execute query: ".$sth_m_A->errs
+tr;
  
  my @regions_A;

  while (@regions_A = $sth_m_A->fetchrow_array) {
    print OUTPUT $_;
    print OUTPUT "\n";    
  }
}

[download]


Update: My file handle is declared earlier in the program.
  • Comment on Returning two records with SELECT statement and then printing to file
  • Download Code

Replies are listed 'Best First'.
Re: Returning two records with SELECT statement and then printing to file. Best way?
by andreas1234567 (Vicar) on Dec 07, 2007 at 13:06 UTC
    Win,

    You attempt to print to the (undeclared) filehandle OUTPUT without opening it first. Read perlopentut. 

    use strict;
use warnings;
my $FILEHANDLE = undef;
open ($FILEHANDLE, "> you.win.again.txt") or die "open failed";
print $FILEHANDLE "I couldnt figure why\n";
print $FILEHANDLE "You couldnt give me what everybody need\n";
print $FILEHANDLE "I shouldnt let you kick me when I'm down\n";
print $FILEHANDLE "My baby\n";
close $FILEHANDLE or die "close failed";
__END__

    [download]
    --
    Andreas
[reply]
[d/l]
[select]
Re: Returning two records with SELECT statement and then printing to file
by jrsimmon (Hermit) on Dec 07, 2007 at 13:16 UTC
    You're storing the return value of fetchrow_array in @regions_A, but then printing $_. Are you sure that fetchrow_array populates $_?

    Even if it turns out that $_ does get populated when fetchrow_array returns, you should use @regions_A while printing to make your code more readable.
[reply]
[d/l]
[select]
      fetchrow_array does not set $_: 
      use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("DBI:mysql:test", '*', '*') or die("booh");
my $sth = $dbh->prepare("select value from s") or die ("looh");
$sth->execute() or die("hoo");
while (my @array = $sth->fetchrow_array()) {
  defined($_) ? print "$_\n" : die "\$_ is undef\n";
}
__END__
$_ is undef

      [download]
      --
      Andreas
[reply]
[d/l]
[select]
Re: Returning two records with SELECT statement and then printing to file
by roboticus (Chancellor) on Dec 07, 2007 at 13:15 UTC
    Win:

    IIRC, you can do it without perl with:

    isql -S ssss -d dddd -U uuuu -P pppp -i yourSqlScript.sql -o LPT1:

    [download]
    Where ssss is the server name, dddd is the database name, uuuu is your user name, pppp is your password. Put your SQL statements in the yourSqlScript.sql file, and I'm assuming your printer is set up to be the local printer for your DOS shell.

    ...roboticus

[reply]
[d/l]
Re: Returning two records with SELECT statement and then printing to file
by izut (Chaplain) on Dec 07, 2007 at 13:27 UTC

    Why didn't you try to limit it on your query? It seems that you're retrieving all records from the specified statement because you're asking your database to do so...

    Igor Sutton
    your code, your rules.

[reply]
Re: Returning two records with SELECT statement and then printing to file
by graff (Chancellor) on Dec 08, 2007 at 01:39 UTC
    First, you should be using a placeholder in the query string, and preparing the query statement outside the foreach loop. Second, you can either include a "LIMIT 2" clause at the end of the query string, as suggested above, or you can simply exit the inner "fetch" loop after doing two rows (but using the LIMIT clause would probably be more efficient, assuming your database server supports it).

    Regarding the placeholder usage, it would go like this (and I'm throwing in both the LIMIT clause and a counter for the inner fetch loop): 

    my $sql = "SELECT G.Region, G.Score FROM Gov_regions_scores_TEMP G, Re
+gion_lookup R
  WHERE R.String = ? AND RTRIM(R.Place) = RTRIM(G.Region) LIMIT 2";
my $sth = $dbh->prepare( $sql );

foreach ( @regions_A_array ) {
    $sth->execute( $_ );
    for ( 1, 2 ) {
        my @regions_A = $sth->fetchrow_array;
        print OUTPUT "@regions_A\n";
    }
}
$sth->finish();

    [download]
    I don't have a means for testing that, but it should be pretty close to what you want. (If it doesn't work, show us exactly how you tried it, what error messages and output you got, if any, and what output you expected.)
[reply]
[d/l]

Back to Seekers of Perl Wisdom