CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Mysql and Temporary Tables

 



tonynusall
New User

Mar 19, 2009, 7:47 PM

Post #1 of 3 (813 views)
Mysql and Temporary Tables Can't Post

For some reason I cannot get a mysql query to look past the first ';' in the query. I did some reading and found you could add

mysql_multi_statements=1

to have it deal with queries that contained ';' but for some reason it gets to the first ';' and stops.

In my query I need to create two temporary tables to do my select against it. Has anyone played with doing this?

I have a feeling I am missing something obvious. When I run my query with navicat it works just fine. When I put it in perl it stops right at the ';' but completes the query.


Code
# 
#!/usr/bin/perl -w
#

#
use DBI;

my $user= "myusername";
my $password= "mypassword";
my $host= "myhost";


my $dbh= DBI->connect("dbi:mysql:database=mydatabase:mysql_multi_statements=1;host=$host",$user,$password)
or die $DBI::errstr;

my $query = 'CREATE TEMPORARY TABLE tempitems
SELECT item_rollup.date_event,
SUM(item_rollup.costbase) AS points_spent,
count(item_rollup.item) AS items_acquired
from item_rollup
GROUP BY LEFT(item_rollup.date_event,10);

CREATE TEMPORARY TABLE tempraiders
SELECT event_attendance_rollup.date_event,
Count(event_attendance_rollup.name) AS name_count
FROM event_attendance_rollup
GROUP BY LEFT(event_attendance_rollup.date_event,10);

select tempraiders.date_event,
SUM(tempraiders.name_count) as attendance_month,
SUM(tempitems.items_acquired) as items_month,
SUM(tempitems.points_spent) as spent_month

from tempitems

right join tempraiders
on tempitems.date_event = tempraiders.date_event

group by left (tempraiders.date_event,7)';

print $query,"\n";
my $sth= $dbh->prepare($query)
or die $DBI::errstr;
$sth->execute();

my $numRows = $sth->rows;

print "Rows returned: $numRows\n";

my @row;

while ( @row = $sth->fetchrow_array )

{
print "@row\n";
}
$dbh->disconnect or die $DBI::errstr;


(This post was edited by tonynusall on Mar 19, 2009, 7:48 PM)


FishMonger
Veteran / Moderator

Mar 20, 2009, 5:15 AM

Post #2 of 3 (804 views)
Re: [tonynusall] Mysql and Temporary Tables [In reply to] Can't Post

I've never tried using mysql_multi_statements and I don't see the need for you to use it.

Have you tried executing the individual statements? The temporary tables will stay around until you close the connection, or possible only until the db handle goes out of scope.

Try this:

Code
#!/usr/bin/perl 
#

use warnings; # it's better to use this pragma instead of the -w switch
use strict; # this pragma should be in every perl script you write
use DBI;

my $user= "myusername";
my $password= "mypassword";
my $host= "myhost";


my $dbh = DBI->connect("dbi:mysql:mydatabase:$host",
$user,$password { RaiseError => 1 }
) or die $DBI::errstr;


$dbh->do('CREATE TEMPORARY TABLE tempitems
SELECT item_rollup.date_event,
SUM(item_rollup.costbase) AS points_spent,
count(item_rollup.item) AS items_acquired
from item_rollup
GROUP BY LEFT(item_rollup.date_event,10'
);


$dbh->do('CREATE TEMPORARY TABLE tempraiders
SELECT event_attendance_rollup.date_event,
Count(event_attendance_rollup.name) AS name_count
FROM event_attendance_rollup
GROUP BY LEFT(event_attendance_rollup.date_event,10'
);


my $sth = $dbh->prepare('select tempraiders.date_event,
SUM(tempraiders.name_count) as attendance_month,
SUM(tempitems.items_acquired) as items_month,
SUM(tempitems.points_spent) as spent_month'

from tempitems
right join tempraiders
on tempitems.date_event = tempraiders.date_event

group by left (tempraiders.date_event,7)'
);

$sth->execute;

my $numRows = $sth->rows;
print "Rows returned: $numRows\n";

while ( my @row = $sth->fetchrow_array ) {
print "@row\n";
}

$dbh->disconnect or die $DBI::errstr;



tonynusall
New User

Mar 20, 2009, 8:10 AM

Post #3 of 3 (801 views)
Re: [FishMonger] Mysql and Temporary Tables [In reply to] Can't Post

Using that approach worked perfectly.

Thanks!

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives