Complex multiple command query
Hi All
I am trying to perform a 'complex' multiple command query against my MySQL 5.0.45 database via either 5.1 or 3.51 ODBC connection using Perl. The relevant perl code is given below.
#This loads the ODBC stuff
use Win32::ODBC;
# Open a log file
unless (open LOG, ">c:\\local\\logs\\RegData.log") {
die "Cannot create logfile: $!";
}
# Connect to the image analysis results database
$ImageAnalysisDataDSN="DSN=ImageAnalData;";
if (!($ImageAnalDB=new Win32::ODBC($ImageAnalysisDataDSN))) {
print LOG "Error connecting to $ImageAnalysisDataDSN\n";
print LOG "Error: " . Win32::ODBC::Error() . "\n";
} else {
print LOG "ImageAnalysisData database opened...\n";
}
# Define the data query
my $ImageAnalDataQuery = '
SET @num := 0, @fldid := \'\';
SELECT Pat_ID1, Field_ID, IMG_ID, Staff_ID
FROM (
SELECT Pat_ID1, Field_ID, IMG_ID, Staff_ID,
@num := IF(@fldid = Field_ID, @num + 1, 1) AS row_number,
@fldid := Field_ID as dummy
FROM tblresults
ORDER BY System, Type, Algorithm, Pat_ID1, Field_ID, IMG_ID
) AS x WHERE x.row_number <= 2;';
if ($ImageAnalDB->Sql($ImageAnalDataQuery)) {
print LOG "SQL failed.\n";
print LOG "Error: " . $ImageAnalDB->Error() . "\n";
print LOG $ImageAnalDataQuery . "\n";
} else {
while($ImageAnalDB->FetchRow()){
my %Data = $ImageAnalDB->DataHash();
The object of all this is to group the data in some fashion but then only select the first 2 rows out of each group. If I run the command using phpmyadmin it all works really nicely and I get what I want but when it is run from the Perl above the query fails and I get the following message in the LOG file.
ImageAnalysisData database opened...
SQL failed.
Error: [1064] [2] [0] "[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
SELECT Pat_ID1, Field_ID, IMG_ID, Staff_ID
FROM (
SELECT Pat_I' at line 1"
SET @num := 0, @fldid := '';
SELECT Pat_ID1, Field_ID, IMG_ID, Staff_ID
FROM (
SELECT Pat_ID1, Field_ID, IMG_ID, Staff_ID,
@num := IF(@fldid = Field_ID, @num + 1, 1) AS row_number,
@fldid := Field_ID as dummy
FROM tblresults
ORDER BY System, Type, Algorithm, Pat_ID1, Field_ID, IMG_ID
) AS x WHERE x.row_number <= 2;
Connection closed.
As far as I can see the problem is related to ending one command and starting another. I have tried loads of things to correct this:
- BEGIN END around the statements
- changed ; to #
- tried to use DELIMITER to change it to something else
Nothing seems to work. I have also tried 2 simpler select commands one after the other in order to try and remove the variable complexity which I thought may be causing the problem or at least clouding the issue.
So my questions are:
- are multiple command sql queries like this possible across this type of odbc connection
- if so what must I do to the syntax to get it to work.
Many thanks to anyone who is able to give me some guidance on this.
Regards
Andrew