Re: Passing multiple select statements to MySQL

[SSC_perl]

On Feb 19, 2014, at 4:20 AM, Dr.Ruud wrote:
 my $sth_4;
 ...
 (untested)

Thanks for the code but I still get the same error: DBD::mysql::st 
execute failed: You have an error in your SQL syntax;

- Long pause for trial and error --

Well with the help of the author, I was able to get it to work.  This 
is the code I used before, based on the CPAN page:

use SQL::SplitStatement;
my @statements = $sql_splitter-split($query_4);
my $sql_string = join ' ', @statements;
my $sth_4 = $dbh-prepare($sql_string);
$sth_4-execute();

and this is the working code that he sent me:

use SQL::SplitStatement;
my @statements = $splitter-split( $query_4 );
foreach my $statement (@statements) {
$sth_4 = $dbh-prepare($statement);
$sth_4-execute();
}

I know I had tried something similar, but it didn't work before and now 
it does.  :\

Thanks,
Frank

http://www.surfshopcart.com/
Setting up shop has never been easier!
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/

Re: Passing multiple select statements to MySQL

[Dr.Ruud]

On 2014-02-18 02:27, SSC_perl wrote:

I'm helping someone retrieve some info from a MySQL db and I'm having 
trouble sending multiple select statements with Perl.  Here's a portion of the 
code:

use SQL::SplitStatement;
my $query_4 = CREATE TEMPORARY TABLE `temp_ip` AS
(SELECT `ip`
FROM `ip_addresses` 
.$daily.
 AND TRIM(`referrer`) LIKE '' GROUP BY `ip` HAVING COUNT(*)  1);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses` 
.$daily.
AND TRIM(`referrer`) LIKE '' AND `ip` IN (SELECT `ip` FROM `temp_ip`);

my $sql_splitter = SQL::SplitStatement-new(
keep_terminators  = 1,
keep_extra_spaces = 1,
keep_comments = 0,
keep_empty_statements = 1
);
my @statements = $sql_splitter-split($query_4);
my $sql_string = join ' ', @statements;
my $sth_4 = $dbh-prepare($sql_string);
$sth_4-execute();

I believe what's throwing a wrench in it is the ; separating the 2 
select statements.  That's why I'm using SQL::SplitStatement, but that doesn't seem to 
help, so I'm sure I'm doing something wrong.  The SQL works fine, so my problem must be 
with my Perl code.

I've read everything I can get my hands on but no luck.  Any help would 
be greatly appreciated.



my $sth_4;

for ( my $sql = $sql_splitter-split($query_4) ) {

$sth_4 and $sth_4-{Active} and die Unfinished business;

if ($sql =~ /^\s*(?:create|delete|insert|set|update)\b/x {
$dbh-do( $sql );
}
else {
$sth_4 = $dbh-prepare( $sql );
$sth_4-execute();
}
}

(untested)

--
Ruud



--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/

Passing multiple select statements to MySQL

[SSC_perl]

I'm helping someone retrieve some info from a MySQL db and I'm having 
trouble sending multiple select statements with Perl.  Here's a portion of the 
code:

use SQL::SplitStatement;
my $query_4 = CREATE TEMPORARY TABLE `temp_ip` AS
(SELECT `ip`
   FROM `ip_addresses` 
.$daily.
 AND TRIM(`referrer`) LIKE '' GROUP BY `ip` HAVING COUNT(*)  1);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses` 
.$daily.
AND TRIM(`referrer`) LIKE '' AND `ip` IN (SELECT `ip` FROM `temp_ip`);

my $sql_splitter = SQL::SplitStatement-new(
   keep_terminators  = 1,
   keep_extra_spaces = 1,
   keep_comments = 0,
   keep_empty_statements = 1
);
my @statements = $sql_splitter-split($query_4);
my $sql_string = join ' ', @statements;
my $sth_4 = $dbh-prepare($sql_string);
$sth_4-execute();

I believe what's throwing a wrench in it is the ; separating the 2 
select statements.  That's why I'm using SQL::SplitStatement, but that doesn't 
seem to help, so I'm sure I'm doing something wrong.  The SQL works fine, so my 
problem must be with my Perl code.

I've read everything I can get my hands on but no luck.  Any help would 
be greatly appreciated.

Thanks,
Frank

http://www.surfshopcart.com/
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/