use strict;
use vars qw($DEBUG $buffer);
-use FS::Record qw(dbh);
+use FS::Record;
+use FS::UID qw(myconnect driver_name);
use Scalar::Util qw(refaddr);
-$DEBUG = 0;
+$DEBUG = 2;
# this might become a parameter at some point, but right now, you can
# "local $FS::Cursor::buffer = X;"
sub new {
my $class = shift;
my $q = FS::Record::_query(@_); # builds the statement and parameter list
+ my $dbh = myconnect();
my $self = {
query => $q,
class => 'FS::' . ($q->{table} || 'Record'),
buffer => [],
+ dbh => $dbh,
+ position => 0, # for mysql
};
bless $self, $class;
$self->{pid} = $$;
$self->{id} = sprintf('cursor%08x', refaddr($self));
- my $statement = "DECLARE ".$self->{id}." CURSOR FOR ".$q->{statement};
- my $sth = dbh->prepare($statement)
- or die dbh->errstr;
- my $bind = 0;
+ my $statement;
+ if ( driver_name() eq 'Pg' ) {
+ $statement = "DECLARE ".$self->{id}." CURSOR FOR ".$q->{statement};
+ } elsif ( driver_name() eq 'mysql' ) {
+ # build a cursor from scratch
+ $statement = "CREATE TEMPORARY TABLE $self->{id}
+ (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
+ $q->{statement}";
+ }
+
+ my $sth = $dbh->prepare($statement)
+ or die $dbh->errstr;
+ my $bind = 1;
foreach my $value ( @{ $q->{value} } ) {
my $bind_type = shift @{ $q->{bind_type} };
$sth->bind_param($bind++, $value, $bind_type );
$sth->execute or die $sth->errstr;
- $self->{fetch} = dbh->prepare("FETCH FORWARD $buffer FROM ".$self->{id});
+ if ( driver_name() eq 'Pg' ) {
+ $self->{fetch} = $dbh->prepare("FETCH FORWARD $buffer FROM ".$self->{id});
+ } elsif ( driver_name() eq 'mysql' ) {
+ # make sure we're not holding any locks on the tables mentioned
+ # in the query
+ $dbh->commit if driver_name() eq 'mysql';
+ $self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+ }
$self;
}
sub refill {
my $self = shift;
my $sth = $self->{fetch};
+ $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
$sth->execute or die $sth->errstr;
my $result = $self->{fetch}->fetchall_arrayref( {} );
$self->{buffer} = $result;
+ $self->{position} += $sth->rows;
scalar @$result;
}
sub DESTROY {
my $self = shift;
return unless $self->{pid} eq $$;
- dbh->do('CLOSE '. $self->{id}) or die dbh->errstr; # clean-up the cursor in Pg
+ if ( driver_name() eq 'Pg' ) {
+ $self->{dbh}->do('CLOSE '. $self->{id})
+ or die $self->{dbh}->errstr; # clean-up the cursor in Pg
+ } elsif ( driver_name() eq 'mysql' ) {
+ # nothing; the temporary table will evaporate when the
+ # session closes.
+ }
+ $self->{dbh}->rollback;
+ $self->{dbh}->disconnect;
}
=back
=head1 BUGS
-Doesn't support MySQL.
+MySQL doesn't support cursors in interactive sessions, only in stored
+procedures, so we implement our own. This has not been extensively tested.
The cursor will close prematurely if any code issues a rollback/commit. If
you need protection against this use qsearch or fork and get a new dbh