L
Lee
Hi,
I am sending this query to Net::MySQL, and it halts. The program
doesn't move forward to tell me if there is an error or to print the
next line after that. This query works in the query browser, and I
can make other queries in the same program. Thus, I know that there
is no general problem with making queries, and I know that there is no
problem with how I make the queries. Can you guys help me figure out
what is wrong with pairing this query with perl? Many thanks!
sub writeCsvFromQuery{
my($query,$newFilename)=@_;
my $time = time();
print "Running query\n$query\n";
$mysql->query($query);
print "Getting result..\n";
if($mysql->is_error()){
print "Cannot run query because " . $mysql-
}
my $result=$mysql->create_record_iterator();
my @field=$result->get_field_names();
open(SPREADSHEET,">$newFilename") or die ($!);
print SPREADSHEET join(',',@field) . "\n";
while(my $row=$result->each){
my $line = join(',',@$row) . "\n";
chomp($line);
print SPREADSHEET $line . "\n";
}
close SPREADSHEET;
print "Spreadsheet was written to $newFilename\n";
my $timeTook = time() - $time;
print "Query took " . ($timeTook) . " seconds to complete, or " .
($timeTook/60) . " minutes.\n";
return 1;
}
this query works:
SELECT * FROM network LIMIT 3;
this query does not work (but works in the query browser):
SELECT DISTINCT niNode1,niNode2,niR,maNode1,maNode2,maR FROM(
SELECT net.node1 AS niNode1,net.node2 AS niNode2,net.r AS
niR,cloneid
FROM intersection,
network net
WHERE lineage='NI'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) niEdge,
(
SELECT net.node1 AS maNode1,net.node2 AS maNode2,net.r AS
maR,cloneid
FROM intersection,
network net
WHERE lineage='MA'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) maEdge
WHERE (maNode1=niNode1 AND maNode2=niNode2)
OR (maEdge.maNode2=niEdge.niNode1 AND
maEdge.maNode1=niEdge.niNode2)
ORDER BY niNode1,maNode1;
I am sending this query to Net::MySQL, and it halts. The program
doesn't move forward to tell me if there is an error or to print the
next line after that. This query works in the query browser, and I
can make other queries in the same program. Thus, I know that there
is no general problem with making queries, and I know that there is no
problem with how I make the queries. Can you guys help me figure out
what is wrong with pairing this query with perl? Many thanks!
sub writeCsvFromQuery{
my($query,$newFilename)=@_;
my $time = time();
print "Running query\n$query\n";
$mysql->query($query);
print "Getting result..\n";
if($mysql->is_error()){
print "Cannot run query because " . $mysql-
return;
}
my $result=$mysql->create_record_iterator();
my @field=$result->get_field_names();
open(SPREADSHEET,">$newFilename") or die ($!);
print SPREADSHEET join(',',@field) . "\n";
while(my $row=$result->each){
my $line = join(',',@$row) . "\n";
chomp($line);
print SPREADSHEET $line . "\n";
}
close SPREADSHEET;
print "Spreadsheet was written to $newFilename\n";
my $timeTook = time() - $time;
print "Query took " . ($timeTook) . " seconds to complete, or " .
($timeTook/60) . " minutes.\n";
return 1;
}
this query works:
SELECT * FROM network LIMIT 3;
this query does not work (but works in the query browser):
SELECT DISTINCT niNode1,niNode2,niR,maNode1,maNode2,maR FROM(
SELECT net.node1 AS niNode1,net.node2 AS niNode2,net.r AS
niR,cloneid
FROM intersection,
network net
WHERE lineage='NI'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) niEdge,
(
SELECT net.node1 AS maNode1,net.node2 AS maNode2,net.r AS
maR,cloneid
FROM intersection,
network net
WHERE lineage='MA'
AND r>0.92
AND (net.node1 IN (SELECT cloneid FROM intersection) AND net.node2
IN (SELECT cloneid FROM intersection) )
AND (intersection.cloneid = net.node1 OR intersection.cloneid =
net.node2)
) maEdge
WHERE (maNode1=niNode1 AND maNode2=niNode2)
OR (maEdge.maNode2=niEdge.niNode1 AND
maEdge.maNode1=niEdge.niNode2)
ORDER BY niNode1,maNode1;