use vars qw($DEBUG $buffer);
use FS::Record;
use FS::UID qw(myconnect driver_name);
-use Scalar::Util qw(refaddr);
+use Scalar::Util qw(refaddr blessed);
$DEBUG = 2;
=over 4
-=item new ARGUMENTS
+=item new ARGUMENTS [, DBH ]
Constructs a cursored search. Accepts all the same arguments as qsearch,
and returns an FS::Cursor object to fetch the rows one at a time.
+DBH may be a database handle; if so, the cursor will be created on that
+connection and have all of its transaction state. Otherwise a new connection
+will be opened for the cursor.
+
=cut
sub new {
my $class = shift;
+ my $dbh;
+ if ( blessed($_[-1]) and $_[-1]->isa('DBI::db') ) {
+ $dbh = pop;
+ }
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;
my $statement;
if ( driver_name() eq 'Pg' ) {
+ if (!$dbh) {
+ $dbh = myconnect();
+ $self->{autoclean} = 1;
+ }
+ $self->{dbh} = $dbh;
$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}";
+ #
+ #
+ # there are problems doing it this way, and we don't have time to resolve
+ # them all right now...
+ #$statement = "CREATE TEMPORARY TABLE $self->{id}
+ # (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
+ # $q->{statement}";
+
+ # one of those problems is locking, so keep everything on the main session
+ $self->{dbh} = $dbh = FS::UID::dbh();
+ $statement = $q->{statement};
}
my $sth = $dbh->prepare($statement)
}
$sth->execute or die $sth->errstr;
- # in mysql, make sure we're not holding any locks on the tables mentioned
- # in the query; in Pg this will do nothing.
- $dbh->commit;
if ( driver_name() eq 'Pg' ) {
$self->{fetch} = $dbh->prepare("FETCH FORWARD $buffer FROM ".$self->{id});
} elsif ( driver_name() eq 'mysql' ) {
- $self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+ # 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");
+
+ # instead, fetch all the rows at once
+ $self->{buffer} = $sth->fetchall_arrayref( {} );
}
$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;
+ if (driver_name() eq 'Pg') {
+ 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;
+ } # mysql can't be refilled, since everything is buffered from the start
}
sub DESTROY {
my $self = shift;
+ return if driver_name() eq 'mysql';
+
return unless $self->{pid} eq $$;
- 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}->do('CLOSE '. $self->{id})
+ or die $self->{dbh}->errstr; # clean-up the cursor in Pg
+ if ($self->{autoclean}) {
+ # the dbh was created just for this cursor, so it has no transaction
+ # state that we care about
+ $self->{dbh}->rollback;
}
- $self->{dbh}->rollback;
- $self->{dbh}->disconnect;
}
=back
=head1 BUGS
-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
-handle.
-Normally this issue will represent itself this message.
-ERROR: cursor "cursorXXXXXXX" does not exist.
+Still doesn't really support MySQL, but it pretends it does, by simply
+running the query and returning records one at a time.
=head1 SEE ALSO