DBD and a MySQL query with a SET @num line

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

Hi, I am having a devilishly difficult time running a piece of SQL code that runs without a problem in MySQL but produces an error when executed by Perl. The SQL fragment in question is:

SET @num :=  0;
SET @id  := -1;

SELECT e.*, y.ChallengerID, CASE WHEN ep.isChampion IS TRUE THEN TRUE 
+ELSE FALSE END AS isCurrentChampion
FROM
(
.
.
.
[download]

And the error is:

DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near 'SET @id  := -1;

SELECT e.*, y.ChallengerID, CASE WHEN ep.isChampion IS TRUE THE' at li
+ne 2
[download]

The query executes without a problem in MySQL. I do not know why put placing the execution of the SET statement directly into the Perl code using \@ seems to result in something that works. In this case, I removed the SET statement from the code.sql file but left the remainder of the SQL code in place...

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;
use Data::Dumper;

# MYSQL CONFIG VARIABLES
my $host = "XYZ";
my $db = "XYZ";
my $dbuser = "XYZ";
my $dbpw = "XYZ";

# CONNECT TO MYSQL
my $dbh = DBI->connect("DBI:mysql:$db", $dbuser, $dbpw) || die "Could 
+not connect to database: $DBI::errstr";
my $myquery;

$myquery = "SET \@num := 0, \@id := -1;";
my $sth = $dbh->prepare($myquery);
$sth->execute();

# Retrieve all open tests
my $file = "../sql/code.sql";
{
  local $/ = undef;
  open FILE, $file or die "Couldn't open file: $!";
  $myquery = <FILE>;
  close FILE;
}

print "\nSQL scheduling query constructed... executing...";           
+ 
$sth = $dbh->prepare($myquery);
$sth->execute();
[download]

Comment on DBD and a MySQL query with a SET @num line Select or Download Code

Replies are listed 'Best First'.
Re: DBD and a MySQL query with a SET @num line by kcott (Archbishop) on Jan 19, 2014 at 16:05 UTC
G'day edieguez, Please copy and paste your Perl code verbatim. When we see it, we may be able to help you. You would probably also benefit from reading the guidelines in "How do I post a question effectively?". -- Ken	[reply]
Re^2: DBD and a MySQL query with a SET @num line by edieguez (Initiate) on Jan 19, 2014 at 16:09 UTC
Hi Ken, Here is the Perl code: #!/usr/bin/env perl use strict; use warnings; use DBI; use Data::Dumper; # MYSQL CONFIG VARIABLES my $host = "localhost"; my $db = "XYZ"; my $dbuser = "XYZ"; my $dbpw = "XYZ"; # CONNECT TO MYSQL my $dbh = DBI->connect("DBI:mysql:$db", $dbuser, $dbpw) \|\| die "Could +not connect to database: $DBI::errstr"; my $file = "../sql/code.sql"; my $myquery; { local $/ = undef; open FILE, $file or die "Couldn't open file: $!"; $myquery = <FILE>; close FILE; } my $sth = $dbh->prepare($myquery); $sth->execute(); my $tbl_ary_ref = $sth->fetchall_arrayref; my $nrows = scalar @{ $tbl_ary_ref }; [download]	[reply] [d/l]
Re^3: DBD and a MySQL query with a SET @num line by karlgoethebier (Abbot) on Jan 19, 2014 at 16:52 UTC
But what is in `code.sql`? Regards, Karl ŤThe Crux of the Biscuit is the Apostropheť	[reply] [d/l]
Re: DBD and a MySQL query with a SET @num line by kcott (Archbishop) on Jan 19, 2014 at 16:47 UTC
You've changed your OP without indicating what's been changed. Please don't do this. "How do I change/delete my post?" explains how to update a node (and why you should do it this way). You've added code to your OP which differs from the code you posted in reply to my original response. This is not helpful. Strings in double-quotes are interpolated. When `perl` sees `"... @whatever ..."`. it assumes `@whatever` is an array. You can use single-quotes to stop interpolation or escape the '`@`' character (as you did in `"SET \@num := 0, \@id := -1;"`). -- Ken	[reply] [d/l] [select]
Re^2: DBD and a MySQL query with a SET @num line by edieguez (Initiate) on Jan 20, 2014 at 05:08 UTC
Sorry about that. Thank you for your help. I'll make sure not to change the original posting in the future without referencing the link you provided.	[reply]
Re: DBD and a MySQL query with a SET @num line by clueless newbie (Curate) on Jan 19, 2014 at 16:20 UTC
There is a parameter in DBD::mysql's connect called mysql_multi_statements. Perhaps it needs to be enabled. "As of MySQL 4.1, support for multiple statements seperated by a semicolon (;) may be enabled by using this option. Enabling this option may cause problems if server-side prepared statements are also enabled."	[reply]
Re: DBD and a MySQL query with a SET @num line by Neighbour (Friar) on Jan 20, 2014 at 10:19 UTC
From the DBI documentation http://search.cpan.org/~timb/DBI-1.630/DBI.pm#General_Interface_Rules_&_Caveats: Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server). However, you can do it sequentially: `$dbh->do('SET \@num := 0, \@id := -1;'); my $ar_results = $dbh->selectall_arrayref( qq( QUERY GOES HERE ), { Sl +ice => {} });` [download]	[reply] [d/l]
Re^2: DBD and a MySQL query with a SET @num line by edieguez (Initiate) on Jan 22, 2014 at 04:10 UTC
Thank you. I missed that section of the DBI documentation (i.e., I should have read the caveats). Now I understand why I can break the query into two sequential executions and see it work but fail when its combined into a single code.sql file.	[reply]
Re: DBD and a MySQL query with a SET @num line by karlgoethebier (Abbot) on Jan 19, 2014 at 18:03 UTC
Did you try `SET @num=0;--other statement(s)`? Worked for me from mysql client. Sorry i can't copy & paste from that f*****g CITRIX/Putty terminal. Regards, Karl ŤThe Crux of the Biscuit is the Apostropheť	[reply] [d/l]