MySQL Forums
Forum List  »  Perl

Complex multiple command query
Posted by: Andrew Hoole
Date: June 26, 2009 04:45AM

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

Options: ReplyQuote


Subject
Written By
Posted
Complex multiple command query
June 26, 2009 04:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.